database table grid

[meerkat]

Here is a simple program to present any database table as a grid.

You can:
Change data on the grid
Update your changes or not
Delete a record from the table
Add a blank record. You can then make changes to it on the grid
Sort and filed. If you select the field again it reverses the sequence
Turn off input checking. This allows you to put characters in numeric or date fields
Page up or down or go directly to any page
Change the number of lines per page.

At the end if the grid it shows the minimum, maximum, and average

If you find any bugs... let me know..
Good luck..


' ========================================================================
' database table grid
' Presents a grid of your selected table and database
' Change data in the grid
' Update your changes or not
' Delete a record from the table
' Add Blank Record. Adds a record that you can change. Assigns a new rowid at the end
' Sort - click header field to sort. Click it again and it reverses the sequence
' Input checking. if no input checking, you will be able to put characters in numeric and date fields
' Page - Go to the Previous, Next or directly to any page.
' LPP - Lines per page. Change the number of lines per page
' At then end of page it shows minimum, maximum, and average values
' =========================================================================
bf$ = "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:12pt'>"
CSSClass ".extBtn", "{background-color:pink;}"

'word-Wrap style for different browsers
wordWrap$ = "style='
white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -pre-wrap;
white-space: -o-pre-wrap;
word-wrap: break-word'"

dirName$ = "c:\rbp101\projects\RBgen_project\data\"
dbName$ = ".db"

[getTbl]
cls
html bf$;"<center><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=2 BGCOLOR=silver ALIGN=CENTER>Data Grid</TD>"
html "</TR><TR>"
html "<TD BGCOLOR=silver ALIGN=RIGHT>Directory</TD>"
html "<TD><INPUT TYPE=TEXT NAME='dirName' id='dirName' SIZE=50 VALUE='";dirName$;"'></TD>"
html "</TR><TR>"
html "<TD BGCOLOR=silver ALIGN=RIGHT>Db name</TD>"
html "<TD><INPUT TYPE=TEXT NAME='dbName' id='dbName' SIZE=50 VALUE='";dbName$;"'></TD>"
html "</TR><TR>"
html "<TD BGCOLOR=silver ALIGN=RIGHT>Table</TD>"
html "<TD><INPUT TYPE=TEXT NAME='tblName' id='tblName' SIZE=50 VALUE='";tblName$;"'></TD>"
html "</TR><TR>"
html "<TD COLSPAN=2 BGCOLOR=silver align=center>"
button #del, "Go Grid", [goGrid]
html "      "
button #ext, "Exit",    [exGrid]
html "</TD></TR></TABLE>"
wait

[exGrid]
cls
print "That's all folks"
end

[goGrid]
dirName$ = trim$(#request get$("dirName"))
if right$(dirName$,1) <> "\" then dirName$ = dirName$ + "\"
dbName$ = trim$(#request get$("dbName"))
tblName$ = trim$(#request get$("tblName"))
DB$       = dirName$ + dbName$
sqliteconnect    #sql, DB$

sql$ = "pragma table_info("+tblName$+")"
#sql execute(sql$)
rows = #sql ROWCOUNT()
if rows < 1 then
print "Table:";tblName$;" does not exist"
wait
end if
orderBy$ = " ORDER BY rowid"
dataChk$ = "Yes" ' check the input data or not
lpp = 20 ' 20 lines per page
pageNum = 1
lastPageNum = 1
' -----------------------
' get number of records
' -----------------------
sql$ = "SELECT count(*) as numRecords FROM '";tblName$;"'"
#sql execute(sql$)
result$ = #sql nextrow$(" |")
numRecords = val(word$(result$,1,"|"))

sql$ = "PRAGMA table_info(";tblName$;")"
#sql execute(sql$)
numFlds = #sql rowcount() + 1
dim fldName$(numFlds)
dim colHdr$(numFlds)
dim fldType$(numFlds)
dim fldSize(numFlds)

fldName$(1) = "rowid" ' first field is rowid
colHdr$(1) = "ID"
fldType$(1) = "CHAR"
fldSize(1) = 6

delim$ = "|"
for i = 2 to numFlds
row$ = #sql nextrow$(delim$)
colVal$ = word$(row$,1,delim$)
fldName$(i) = trim$(word$(row$, 2,delim$))

ch$ = ""
c$ = fldName$(i)
for c = 1 to len(c$) ' break column headers
if asc(mid$(c$,c-1,1)) > 90 and asc(mid$(c$,c,1)) < 97 then
ch$ = ch$ + chr$(13);mid$(c$,c,1)
else
ch$ = ch$ + mid$(c$,c,1)
end if
next c
colHdr$(i) = ch$
fldType$(i) = upper$(word$(row$, 3, delim$))
a$ = word$(fldType$(i)+"()",2,"(")
siz = val(word$(a$,1,")"))
fldType$(i) = word$(fldType$(i)+"(",1,"(")
if fldType$(i) = "DATETIME" then fldType$(i) = "DATETIME-local"
if fldType$(i) = "TEXT" then siz = 22
if instr(fldType$(i),"INT") > 0 then fldType$(i) = "NUMBER"
fldSize(i) = val(using("####",siz))
next i

[list]
cls
gosub [heading]

html bf$;"<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR BGCOLOR=lightgray ALIGN=CENTER valign=bottom><td></td>"
for i = 1 to numFlds
html "<TD>"
link #srt,colHdr$(i),[srtGrid]
#srt setkey(fldName$(i))
html "</TD>"
next i
html "</TR>"

sql$ = "SELECT rowid,* FROM ";tblName$;orderBy$;" ";limit$
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows

' ----------------------------------
' Show data on the grid
' ----------------------------------
for r = 1 to rows
result$  = #sql nextrow$(" |")
html "<TR>"
for c = 1 to numFlds
curFldData$ = trim$(word$(result$,c,"|"))
if fldName$(c) = "rowid" then
rowid$ = curFldData$
html "<TD>"
button #del, "Del", [delRow]
#del setkey(rowid$)

html "</TD><TD>";rowid$;"</TD>"
goto [exType]
end if
gridKey$ = rowid$;"|";fldName$(c)
ln = max(2,len(curFldData$))
ft$ = fldType$(c)
if dataChk$ = "No" and ft$ <> "TEXT" then ft$ = "CHAR"
html "<TD>"
if ft$ = "DECIMAL" then
html "<input type='number' step='any' name='";gridKey$;"' id='";gridKey$;"' value='";curFldData$;"' size=";fldSize(c);"/>"
goto [exType]
end if
if ft$ = "TEXT" then
html "<textarea name='";gridKey$;"' id='";gridKey$;"' rows=1 cols=35 ";wordWrap$;">";curFldData$;"</textarea>"
else
html "<input type='";ft$;"' name='";gridKey$;"' id='";gridKey$;"' value='";curFldData$;"' size=";fldSize(c);"/>"
end if
[exType]
html "</TD>"
next c
html "</TR>"
next r

' --------------------------------
' Show min, max, average
' --------------------------------
cma$ = ""
dim mxa$(3)
mxa$(1) = "min"
mxa$(2) = "max"
mxa$(3) = "avg"

a$ = "SELECT "
for j = 1 to 3
for i = 1 to numFlds
a$ = a$ +cma$+mxa$(j);"(";fldName$(i);") as ";mxa$(j);fldName$(i)
cma$ = ",";chr$(13)
next i
next j
a$ = a$ + " FROM ";tblName$
#sql execute(a$)
result$ = #sql nextrow$(" |")
for j = 1 to 3
html "<TR bgcolor=lightgray><TD>";mxa$(j);"</TD>"
for i = 1 to numFlds
x = i + (j-1) * numFlds
a$ = trim$(word$(result$,x,"|"))
if instr(fldType$(i),"DATE") = 0 then a$ = left$(a$,8)
html "<TD>";a$;"</TD>"
next i
html "</TR>"
next j

html "</TABLE>"
button #up, "Update", [updateGrid]
html "        "
button #ex, "Exit",[exGrid]
#ex  cssclass("extBtn")
wait

' -----------------------------------
' Update any changes to the grid
' -----------------------------------
[updateGrid]
#sql execute(sql$)
upd$ = ""
for r = 1 to rows
result$  = #sql nextrow$(" |")
'print r;" result:";result$
rowid$ = trim$(word$(result$,1,"|"))
updt$ = "UPDATE ";tblName$;" SET "
cma$ = ""
for c = 2 to numFlds
gridKey$ = rowid$;"|";fldName$(c)
curFldData$ = trim$(word$(result$,c,"|"))
chgFldData$ = trim$(#request get$(gridKey$))
'print c;" gk:";gridKey$;" cur:";curFldData$;" chg:";chgFldData$
if curFldData$ <> chgFldData$ then
a$ = strRep$(chgFldData$,"'","''")
a$ = strRep$(a$,"""","""""")
updt$ = updt$ + cma$;" ";fldName$(c);"='";a$;"'"
cma$ = ","
end if
next c
if cma$ <> "" then
upd$ = upd$ + updt$ ; " WHERE rowid=";rowid$;";"
end if
next r
i = 1
while word$(upd$,i,";") <> ""
a$ = word$(upd$,i,";")
#sql execute(a$)
i = i + 1
WEND
goto [list]

' ---------------------
' Delete row from table
' ---------------------
[delRow]
sql$ = "DELETE FROM ";tblName$;" WHERE rowid = ";EventKey$
#sql execute(sql$)
goto [list]

' ------------------------------------
' sort order - go reverse if ask twice
' ------------------------------------
[srtGrid]
d$ = ""
if preSort$ = EventKey$ AND instr(orderBy$," desc") = 0 then d$  = " desc"
orderBy$ = " ORDER BY ";EventKey$;d$
preSort$ = EventKey$
goto [list]

' ============================================
' Heading
' ============================================
[heading]
' ---------------------------------------
' Did they change the lines per page lpp
' ---------------------------------------
x = #lpp ISNULL()
if x = 0 then lpp = val(#lpp contents$())
pageNum  = max(1,pageNum) ' make sure it has a page number
if lpp < 1 then lpp = 30 ' lines per page must be specified default = 30
lpp = max(5,lpp) ' make sure it has a least 5 lines per page
lpp = min(100,lpp) ' don not allow over 100 lines per page

totPages = int(numRecords / lpp)
if lpp * totPages <> numRecords then totPages = totPages + 1
pageNum = min(totPages,pageNum)
pageNum = max(1,pageNum)
limitBeg = (pageNum * lpp) - lpp  'limit begin value

dispLine = 0

limit$ = " LIMIT " ; limitBeg ; "," ; lpp

html bf$;"<center><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR=lightGray>"
html "<TR align=center><td></TD>"

html "<TD>"
button #up, "Update", [updateGrid]
html "</TD><TD>"
button #ext, "Exit",[exGrid]
#ext  cssclass("extBtn")

html "</TD><TD>"
button #dc, "Input Check ";dataChk$,[dataChk]

html "</TD><TD>"
button #ab, "Add Blank Record",[addBlk]
html "</TD><TD>"
button #ab, "Change Table",[getTbl]

html "</TD><TD>DB:";dbName$;"</TD><TD>Table:";tblName$
html "</TD><TD>Records:";numRecords
html "</TD><TD>Pages:";totPages
html "</TD><TD ALIGN=right>"
button #prev, "Prev",[doPrev]
#prev setkey(pageNum)
html "</TD><TD width=2%>"
TEXTBOX #pageNum, pageNum,2

html "</TD><TD align=left>"
button #next, "Next",[doNext]
#next setkey(pageNum)
html "<TD ALIGN=right><A title='Lines Per Page'>"
button #lpp, "Lpp",[doLpp]
html "</A></TD><TD align=left>"
TEXTBOX #lpp, lpp,2
html "</TD></TR></TABLE>"
RETURN

' ----------------------------
' Add Blank Record
' ----------------------------
[addBlk]
sql$ = "INSERT INTO ";tblName$;" default values"
#sql execute(sql$)
goto [list]

' ----------------------------
' Lines per page
' ----------------------------
[doLpp]
if lpp = 0 then lpp = 20
lpp = min(10,lpp)
goto [list]
' ----------------------------
' Next page
' ----------------------------
[doNext]
lastPageNum = val(EventKey$)
pageNum = val(#pageNum contents$())
if lastPageNum = pageNum then pageNum = pageNum + 1
goto [list]

' -------------------------
' input checking on or off
' -------------------------
[dataChk]
if dataChk$ = "Yes" then
dataChk$ = "No"
else
dataChk$ = "Yes"
end if
goto [list]

' ----------------------------
' Previous page
' ----------------------------
[doPrev]
lastPageNum = val(EventKey$)
pageNum = val(#pageNum contents$())
if lastPageNum   = pageNum then pageNum = pageNum - 1
if pageNum < 1 then pageNum  = 1
goto [list]

' --------------------------------
' string replace rep str with
' --------------------------------
FUNCTION strRep$(str$,rep$,with$)
ln  = len(rep$)
ln1 = ln - 1
i = 1
while i <= len(str$)
if mid$(str$,i,ln) = rep$ then
strRep$ = strRep$ + with$
i = i + ln1
else
strRep$ = strRep$ + mid$(str$,i,1)
end if
i = i + 1
WEND
END FUNCTION