Anyway, I think there is a solution that does show all the headers without me having to type them all in again. I’ve tested it, it seems to do everything I wanted, and here it is. I have renamed the ListBox viewData. It had 10 columns in the IDE but these are reduced by the code to the number in the rs
Session.db.connect
var rs as RowSet = Session.db.SelectSQL("select * from members")
if rs<>nil and not rs.AfterLastRow then
'initialise ListBox...
viewData.RemoveAllRows
'...then get the row headers from rs
viewData.ColumnCount = rs.ColumnCount
for i as integer = 0 to rs.LastColumnIndex
viewData.HeaderAt(i) = rs.ColumnAt(i).Name
next
'...then get the rs row data
while not rs.AfterLastRow
viewData.AddRow("")
for i as integer = 0 to rs.LastColumnIndex
viewData.CellTextAt(viewData.LastAddedRowIndex,i) = rs.ColumnAt(i).Stringvalue
next
rs.MoveToNextRow
wend
end if
rs.close
Steve, keeping the headers in the table will work right up until it doesn’t. The rows in a database table are not guaranteed to be returned in any particular order unless you specify an ORDER BY clause in your SQL statement. Beyond that, what’s going to happen later when you want to only show the members whose names begin with the letter A? That header line won’t be there if you add
WHERE last_name LIKE "A%"
Save yourself some grief down the line and don’t do it this way.
Not sure I follow @Greg_O - what has the order of rows to do with a problem of using the .Name member and also why would specifying the condition you mention not result in a header row ?
'...then get the row headers from rs
//Doh!
//counts the number of columns in the row set and resets the number in the ListBox
viewData.ColumnCount = rs.ColumnCount
//for each column in the RowSet gets the Name of the Row, ie the Header,
//and makes that the Header of the ListBox column
//(turns out that RowSet does store this information after all)
for i as integer = 0 to rs.LastColumnIndex
viewData.HeaderAt(i) = rs.ColumnAt(i).Name
next
'...then get the rs row data
//takes each data entry in the RowSet and puts it
//in the right place in the ListBox
while not rs.AfterLastRow
viewData.AddRow("")
for i as integer = 0 to rs.LastColumnIndex
viewData.CellTextAt(viewData.LastAddedRowIndex,i) = rs.ColumnAt(i).Stringvalue
next
rs.MoveToNextRow
wend
No, the code asks the db for the colum/field names for the table, so if the table is created as: ID, team_name, coach_name, city_name the Listbox will show that as the headers.
Dim sql As String = "CREATE TABLE Team (ID INTEGER NOT NULL, team_name TEXT, coach_name TEXT, city_name TEXT, PRIMARY KEY(ID));"
Think of it as a database table viewer. Something like this:
This is the code to fill the header for this example:
//we ask the database for the name of each of the table's colum/field name
For i As Integer = 0 To data.LastColumnIndex
datalist.HeaderAt(i) = data.ColumnAt(i).Name
Next