Viewing SQLiteDatabase content in a Web App

The listbox header is basically another row that you need to fill.

If you don’t want a header, then you can instruct the WebListbox to not have a header.

If you want a header, with information on it, you need to fill in the information that you want either using the IDE
image

image

or using code.

Note: in Initial Value I used TAB to separate the words.

Doh ! You already stored the Headers in a Table ?

Doh! And I didn’t want to have to type them all in again every time I wanted to see the contents of some table or other.

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

Thanks for all the helpful suggestions - Steve

That one can do this, is useful to know, even although as it happens I’ve not needed it.

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 ?

I must be overlooking something obvious ?

1 Like

My guess is that Greg thinks that the first row of the database has the headers for the (web)listbox.

That’s the impression I got. Sorry I misunderstood.

Worst here: I do not understand at all what was done.

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

So the Header Strings are in the same RowSet (rs) where you store the regular Records ?

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:

image

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

The table only has 3 rows.

In principle, an SQLite database can return any name it likes for a column, unless you use the AS keyword:

select somecol as wiggy from thistable …

in which case the name returned is wiggy. Whether this is somehow finessed by Xojo in the shim I know not.

You are confusing me now, Alberto. The code I used to create the tables was

Self.db.ExecuteSQL("CREATE TABLE members(id INTEGER PRIMARY KEY AUTOINCREMENT, account VARCHAR(20), email VARCHAR(20), otherName VARCHAR(20))")

The headers loaded into the Listbox were id, account, email and otherName

There are four columns, 0 to 3 and there are actually 19 rows since the data was created after the table was setup by different code

…and it certainly seems to me that they are in the same RowSet as the “regular Records” since they are provided to the ListBox by

rs.ColumnAt(i).Name

in

viewData.HeaderAt(i) = rs.ColumnAt(i).Name

viewData is the ListBox

As this is an SQLIte database, you can throw out the VARCHAR(20) and just say TEXT (all text in SQLite is of unlimited length).

1 Like

Thank you, Tim, I didn’t know that.

Sorry. My data is as an example I was not sure about what you used for your table

Don’t think that’s true @TimStreater