Viewing SQLiteDatabase content in a Web App

I am building my first Xojo Web App for a small club and have set up an SQLite Database with tables and fields and some dummy text. Now I need a simple way to view content entered by club members. I hoped I could do this with a ListBox and so far the following code seems to work.

'Session property db is declared, has its database file set
'and is connected in the Session Opening event

var rs as RowSet = Session.db.SelectSQL("select * from members")
if rs<>nil and not rs.AfterLastRow then
  for each r as DatabaseRow in rs
    viewerListBox.AddRow rs.Column("account").StringValue
end if

The trouble is this code only displays the content of one column “account” from 15 complete rows of data (and it doesn’t show the column header). Looking at the Language specs, I’m not sure where to go from here.

When viewing the content, I’m not really bothered about showing the column headers but I need to see every row, displayed in columns, from the entire table or from a table selection. There will be several tables when I have finished, each with a different number of columns (although there are unlikely to be more than 10-12 in each table) and I could specify all the column headers if necessary but would rather not. The ListBox has 10 columns).

Can I do this using a ListBox, and if not, is there another simple method I can use?

Currently you list only one column in your listbox.addrow - if you want to show more, enter them behind that column, each seperated with a comma.

I did try that unsuccessfully, but have tried it again to make sure. Entered

viewerListBox.AddRow rs.Column(“id”, “account”, “email”).StringValue

Too many arguments: got 3, expected only 1

Type int32 has no member named StringValue

There is more than one method with this name but this does not match any of the available signatures

You have to use separate rs.Column(“xyz”).StringValue entries, each seperated with a comma…

viewerListBox.AddRow rs.Column("id").StringValue, rs.Column("account").StringValue, rs.Column("email").StringValue

OK, that is now working, thanks, although the ListBox still doesn’t display the column headers so why does it need them? Is it possible to specify the columns in order (Col1, Col2, Col3 etc) rather than by name? If not, I guess I can set up a method to call the content for each table in turn.

Do you mean there are no headers showing or that there are, but they are empty?

Coumns in a listbox are numbered from zero from left to right. You can, in the IDE at design time, define how many columns there are.

If you don’t want/need a WebListbox header you can go to the Inspector and turn it off (Has Header option)

Tim, not at my desk just now but the headers are blank apart from up down arrows that seem to order the rows in alphabetical order of a sort (0, 1, 10, 2 etc) or in reverse. I created a new web page for the viewer and dropped a ListBox on it then later decided to give it 10 columns. From memory I went into the ListBox Opening event and put in code me.columncount = 10 or something similar. Then I put a button on the page and set it to call a Method containing the code I posted earlier, which I then modified as suggested to populate the first three column of the 10. Still no headers. Thanks

You are seeing this, right?

You need to assign the header contents. See Ivan’s first link.

This code:

Me.HeaderAt(0) = "Test"
Me.HeaderAt(1) = "Other"
Me.HeaderAt(2) = "Last"

will show the listbox as:

I use the PaintHeaderContent event handler to paint the headers, and so achieve like this:

Screenshot 2023-03-24 at 21.49.20

Steve is building a Xojo Web App.

Hmmm. Appears to be a bit short of events.

I have replaced the ListBox with a new one and in the IDE set it to have 10 columns with the Has Header switch set to on. The columns show as columns 0 to 9 in the header.

When the app runs and the testPage web page appears with the ListBox on it (testPage.Show) the headers are blank. When I click on the button that selects and displays the RowSet selection, the ListBox is populated with the data in columns but the headers are left blank.

It doesn’t matter whether I use this code

for each r as DatabaseRow in rs
  viewerListBox.AddRow rs.Column("id").StringValue, rs.Column("account").StringValue, rs.Column("email").StringValue

or this code

for each r as DatabaseRow in rs
  viewerListBox.AddRow rs.ColumnAt(0).StringValue, rs.ColumnAt(1).StringValue, rs.ColumnAt(2).StringValue, rs.ColumnAt(3).StringValue

Both work but do not show the headers from the selected table.

It isn’t a massive problem for me as I know what the headers should be and there won’t be a huge number of searches I will need to do. It would be pointless for me to manually enter them at AlbertoD seems to be suggesting, but for completeness sake, I am curious why these are not accessible from the RowSet data and displayed automatically. It would make the ListBox a much more useful control for displaying database search data.

Code it that way !

Add a “Header” TABLE and fill a Row with the Strings you want to display in the WebListBox Heading.
So, at run time, use a RecordSet and set automatically the Header Strings…

Or follow Alberto advice.

The headers in a Listbox have nothing to do with the column names in a table in a database, if that’s what you’re asking. You want headers, you gotta fill them in.

Please keep in mind that this is no spreadsheet where headers are automatically filled when importing data…
You have to set the contents in the headers - wether manually (in IDE) or while running your app with HeaderAt()

Well if a RowSet isn’t able to record the header names and a ListBox can’t read them it’s easier for me to use the rs.ColumnAt() syntax and do without the headers altogether. I can tell the difference between an id index, a name, an address, an email address, a phone number and a date.

Worse than what I said before is that a database and a listbox are not “connected” in any way, so there’s no reason for a RowSet to know anything about your listbox or to have anything to do with populating the header row. As you will have seen from the image I posted upthread, at least on a DesktopListbox there are more options as to what to put there than just text.

A database table doesn’t have headers; it has columns with names. You can use the names to access column content, as you’ve been doing, but AFAIK there’s no way to ask a RowSet what the names might be. Or one might read the schema from the database’s master table and parse it, I suppose, but that’s a bit heavy.