browse with grid

[meerkat]

I'm writing a test program to see how browsing a database table might work with the grid.
Couple of problems:
1. How do you set the grid width. I try to make it larger - but no luck.
2. How do you reload a grid. It blows memory when i try it.

Anyway I was trying a program that you select a database and table.
It then list the table in a grid. 
Not complete yet.. but I should be able to:
1. List the next page
2. List the previous page
3. Go to a specific page.
4. Add, Change, or Delete data.

Still experimenting. But here is what I have so far: sWe will see if it does the trick.
I'm sure some of you experts have some better ideas on how to lay this out etc.

' -------------------------------------------------
' Supply database and table name
' -------------------------------------------------
[getDb]
filedialog "Find a Database File", "*.db", db$
if db$ = "" then
notice "No file selected. Thank you and have a great day!"
end
end if

print "open :";db$                 ' see if the file is sqlite format
open db$ for input as #1
a$    = lower$(input$(#1,15))
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
notice db$;" ** Not SQLite file format **";a$
goto [getDb]
end if

sqliteconnect    #sql,  db$  ' Connect to the DB
sql$ = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
dim tblList$(rows)
for i = 1 to rows
#row = #sql #nextrow()
tblList$(i) = #row name$()
print i;" ";tblList$(i)
next i
listbox #tbl.list, tblList$(), [tblSel], 5, 35, 200, 50+rows*8
button  #tbl.ex "Exit",     [tblEx], UR, 15, 5
WindowWidth = 270 : WindowHeight = 100 + rows*12
open "Select a Table" for window as #tbl
wait
[tblSel]
'tblIdx = #tbl.list selectionindex()
tblName$ = #tbl.list selection$() ' what was selected

[tblEx]
close #tbl
if tblName$ = "" then
#sql disconnect()
end
end if
print "tableName:";tblName$

' -------------- How many records are in the table -------------------
sql$ = "SELECT count(*) as numRecords FROM ";tblName$
#sql execute(sql$)
#row   = #sql #nextrow()
numRecords = #row numRecords()

' -----------------------------------------
' names and number of columns
' -----------------------------------------
sql$ = "SELECT * FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
colNames$ = #sql columnnames$()
j = 0
for numCols = 1 to len(colNames$)
j = instr(colNames$,",",j+1)
if j = 0 then exit for
next numCols

' --------------------------------------------------------
' get max widths of data in each column
' and force it between 2 and 12 characters
' --------------------------------------------------------
x$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length("+x$+")) FROM ";tblName$;" LIMIT 1"
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
if rows < 1 then
notice "No data in table:";tblName$
goto [getTbl]
end
end if
result$ = #sql nextrow$(",")

dim colWid(numCols)
colWid$ = ""
cma$ = ""
totWid = 0
for i = 1 to numCols
colWid(i) = min(12,max(2,val(word$(result$,i,",")))) ' width between 2 and 12
colWid$ = colWid$ + cma$ + str$(colWid(i)* 10) ' adjust for pixels
totWid = totWid + colWid(i) * 10
cma$ = ","
next i

' ----------------------------
numRows = 20 ' num of rows wanted for each grid page
dim a$(numCols, 20)
sql$ = "SELECT * FROM ";tblName$;" ORDER BY ";tblName$;".rowid LIMIT ";numRows
#sql execute(sql$)
rows = #sql ROWCOUNT() 'Get the number of rows
for i = 1 to rows
result$ = #sql nextrow$(" ";chr$(251))
for j = 1 to numCols
x$ = left$(trim$(word$(result$,j,chr$(251))),12)
a$(j,i -1) = x$
next j
next i
print "totWid:";totWid
print "rows:";rows
pagNum$ = "1"

grid #w.grid, a$(), [clicked], 120, 10, 100+totWid, 100+rows*17.5 ' create a grid widget
' ------------------------------------                            x   y   w   h
statictext #w.pgeHd, "_________Page_________" ,000,030,180,020
button #w.pre, "Prev", [pre]   , UL ,000,060,030,020
button #w.nxt, "Next", [nxt] , UL ,080,060,030,020
button #w.ext, "EXIT", [ext] , UL ,040,090,030,020
textbox #w.pge             ,040,058,030,020
statictext #w.pg1, pgeNum$     ,040,060,030,020

open "DB:";db$;"    Table:";tblName$ for dialog as #w ' open the grid
a = eval("#w.grid columnwidths("+colWid$+")") ' set grid col widths

q$ = chr$(34)
cma$ = ""
cn$ = "" ' column names for grid
for i = 1 to numCols ' set length of colNames and center then
cn$ = cn$ + cma$ + q$ + align$(word$(colNames$,i,","),colWid(i),"c") + q$
cma$ = ","
next i
a = eval("#w.grid columnnames("+cn$+")") ' put column names on the grid '

cma$ = ""
rn$ = "" ' row names for grid
for i = 1 to numRows ' set length of colNames and center then
rn$ = rn$ + cma$ + q$ + "Rec_" + right$("00" + str$(i),2) + q$
cma$ = ","
next i

a = eval("#w.grid rownames("+rn$+")") 'label the rows
#w.grid rowlabelwidth(40) 'set sizes for rows
wait

[clicked]
print "clicked: "; #w.grid cellxy$() 'column,row clicked
print "cell contents: ";#w.grid value$() 'contents of cell clicked
'#w.grid  "reload"
wait
[ext]
close #w
#sql disconnect()
end

[pre]
input #w.pge, pgeNum$
print pgeNum$
wait
[nxt]
input #w.pge, pgeNum$
print pgeNum$
wait


' ---------------------------------------
' 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

' -------------------------------------
' Align fld$ to left right or center
'  of a given width
' -------------------------------------
function align$(fld$,width,lrc$)
s    =  width - len(fld$)
fld$ = left$(fld$,width)
if s < 1 then
align$ = fld$
else
b$ = space$(width)
if lrc$ = "l" then align$ = fld$;left$(b$,s)
if lrc$ = "r" then align$ = left$(b$,s);fld$
if lrc$ = "c" then align$ = left$(b$,int(s / 2));fld$;left$(b$,int(s / 2) + (s and 1))
end if
end function