setting up a grid

[meerkat]

Need help setting up a grid.
First I don't know how many columns, the widths, or the column names, or row names will be until the program runs and creates it.
Maybe I need to create another .bas program and "include it" or "run it" - not sure this is possible.

Here is a simple program that reads a SQLite fine.
It finds the column names and number of columns
It determines the maximum length of data in each column in the database.
Then tries to create the grid column widths with the information.
#w.grid columnwidths(theGrid$)   <<== how do I create this??
If someone can show me how to do the dynamic widths then I can probably figure out how to do the rest..
Thanks for the help.

Here is the code I have so far:

' Get the directory and name of the database
' -------------------------------------------------
DefaultDir$ = "C:\rbp101"
projectDir$ = "elks_project"
elksDb$     = DefaultDir$ + "\projects\" + projectDir$ + "\data\elks.db"

sqliteconnect    #sql,  elksDb$  ' Connect to the DB

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

' -----------------------------------------
' how many columns and names of the columns
' -----------------------------------------
sql$ = "SELECT * FROM member LIMIT 1"
#sql execute(sql$)
'rows = #sql ROWCOUNT()       'Get the number of rows
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 each column
' theGrid$ contains a comma seperated width of each column
'   such as: "10,20,30...."
'   colWidth(i) = width of each column
' -----------------------------------------
dim colWidth(numCols)

x$ = strRep$(colNames$,",",")),max(length(")
sql$ = "SELECT max(length("+x$+")) FROM member LIMIT 1"
#sql execute(sql$)
result$ = #sql nextrow$(" |")
cma$ = ""
for i = 1 to numCols
colWidth(i) = val(val(word$(result$,i,"|"))
theGrid$ = cma$ + word$(result$,i,"|")
cma$ = ","
next i

'********************** how to make this?? ******************
#w.grid columnwidths(theGrid$) 'set sizes for columns




[Carl Gundel]

Mar 2, 2019 7:08:24 GMT -5 meerkat said:

If someone can show me how to do the dynamic widths then I can probably figure out how to do the rest..
Thanks for the help.

Okay, the grid needs a little more work to make it flexible, including some easy way to fill it from an SQL result.

So, to hold you over in the meanwhile, you can use the eval() function.

  a = eval("#win.grid columnwidths(10, 20, 30, 40)")

or produce your comma delimited column widths into a string variable by your clever programming and do:

  colwid$ = myMagicFunction$(xprs$)
  a = eval("#win.grid columnwidths("+colwid$+")")

You get the idea?

This dynamic use of eval() can be really powerful, but there will be less obfuscated ways of doing this. 

[meerkat]

Ok.. Finally tried some test with eval.

The code:
open "Grid Test" for window as #win ' open the grid
colWid$ = "2,4,6,8"
a = eval("#win.grid columnwidths("+colWid$+")")
end

Is this the correct syntax?
It throws error:Unhandled exception: Invalid handle #win.grid

Thanks for the help.. Dan

[Carl Gundel]

Mar 2, 2019 18:01:56 GMT -5 meerkat said:

Ok.. Finally tried some test with eval.

The code:
open "Grid Test" for window as #win ' open the grid
colWid$ = "2,4,6,8"
a = eval("#win.grid columnwidths("+colWid$+")")
end

Is this the correct syntax?
It throws error:Unhandled exception: Invalid handle #win.grid

Thanks for the help.. Dan

Dan, the #win.grid reference should be changed to whatever handle you are using for your grid control.

[meerkat]

Ok.. Being new to LB maybe I missed a lot.
Isn't the open statement above ok. Or do I need some other control.

I'm using #win in the open and the eval uses #win. What am I missing?
The open in the above post is:

open "Grid Test" for window as #win ' open the grid

[cundo]

What about the grid, does match?

[meerkat]

Duh!
Ok got it.. I'm a little slow, but I eventually catch on.

It works now that I have included this command:

grid #win.grid, a$(), [clicked], 10, 10, 400, 444 'create a grid widget

Thanks for the help..
Dan

[meerkat]

Ok. Got the grid setup working for any database and table supplied.
It only list the first page.

I'm experimenting but still haven't figured out how to do the following:
1. show images in a cell
2. do grid within a existing window
3. place widgets in a grid like buttons etc

Any help will be appreciated..

Here is what I've tested so far..
' -------------------------------------------------
' Supply database and table name
' -------------------------------------------------
db$ = "C:\rbp101\projects\elks_project\data\elks.db"
tblName$ = "member"

sqliteconnect    #sql,  db$  ' Connect to the DB

' -------------- 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$)
result$ = #sql nextrow$(",")

dim colWid(numCols)
colWid$ = ""
cma$ = ""
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
cma$ = ","
next i

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

grid #w.grid, a$(), [clicked], 10, 10, 800, rows*17.5 ' create a grid widget
open "Grid Test" 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

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


[Rod]

Your desires for functionality seem extreme. Images, buttons and no doubt lists in cells? We might be asking too much. I am not even sure a spreadsheet can do that. Perhaps a web page but not in a cell structure.

Can you give us an overview of what your ultimate user interface does? How does it look, how does the user interact? Do they press an image or press a button next to an image? Is the image contained in the text size cell or does it expand?

It all just seems a stretch too far.


To be clear, it is probably perfectly possible to code a unique solution using graphicboxes et al but to expect a single widget to deliver such functionality is asking a lot.

[meerkat]

Rod
Thank you for your concerns. I think you may be correct. Those requirements would be the least I could live with.
Liberty Basic is great, but it may not be a match for my requirements. At least it looks that way.
If the interface could so something like the html <table>, that would be great.
I'll use LB5 where it fits. Right now it looks like I'll continue with what I'm currently using for my major projects.

Thanks for your insight..
Dan