|
Post by mackrackit on May 22, 2008 13:37:02 GMT -5
Is there a way to count the number of columns in a data base table? Something like rowcount(). I want to place the column names into an array. When I fill an array with row data this works. index = 0 sqliteconnect #inventory,"inventory.db" query$ = "select part from Parts" #inventory execute(query$) if #inventory hasanswer()then for x = 1 to #inventory rowcount() cnames$ = #inventory nextrow$(",") if arrayR$(index) = "" then arrayR$(index) = cnames$ index = index + 1 next x end if #inventory disconnect()
How can I do something similar. Cindex=1 index=0 sqliteconnect #inventory,"inventory.db" query$ = "select * from Parts" #inventory execute(query$) cnames$ = #inventory columnnames$() print cnames$ for x = 1 to 10 firstname$ = trim$(word$(cnames$,Cindex,",")) if arrayC$(index) = "" then arrayC$(index) = firstname$ index = index +1 Cindex = Cindex + 1 next x #inventory disconnect() listbox #CAV,arrayC$(),1
The above works if I know how many columns I have, and this would not matter, but I am making a list box with the info. If I make the array with empty places then the list box has empty places. I need to replace "for x = 1 to 10" to .... if I do not know the number of columns.
|
|
|
Post by StefanPendl on May 22, 2008 14:21:45 GMT -5
Is there a way to count the number of columns in a data base table? Something like rowcount(). '... query$ = "select * from Parts" '...
Some googling showed the following: SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =...So you would use: query$ = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = Parts"
|
|
|
Post by StefanPendl on May 22, 2008 14:29:01 GMT -5
Even easier, why not use a WHILE..WEND loop: Cindex=1 index=0 sqliteconnect #inventory,"inventory.db" query$ = "select * from Parts" #inventory execute(query$) cnames$ = #inventory columnnames$() print cnames$ WHILE word$(cnames$,Cindex,",") <> "" firstname$ = trim$(word$(cnames$,Cindex,",")) if arrayC$(index) = "" then arrayC$(index) = firstname$ index = index +1 Cindex = Cindex + 1 WEND #inventory disconnect() listbox #CAV,arrayC$(),1
|
|
|
Post by mackrackit on May 22, 2008 14:39:20 GMT -5
Thank you Stefan,
I saw something like you first example when I was googling and reading through Nabble, was not able to get it right though.
Never thought about you second example. That is the one I will use.
Thank you again for your help.
|
|