Populating a listbox control from a database

Hi everyone,
Since I am learning the XOJO framework, I noticed that the standard method to populate a ListBox control with records from a database is the following:

If mDB Is Nil Then Return

TeamList.DeleteAllRows

Dim sql As String
sql = "SELECT * FROM Team"

Dim rs As RecordSet
rs = mDB.SQLSelect(sql)

If mDB.Error Then
  MsgBox("DB Error: " + mDB.ErrorMessage)
  Return
End If

If rs <> Nil Then
  While Not rs.EOF
    TeamList.AddRow(rs.Field("ID").StringValue, rs.Field("Name").StringValue, _
     rs.Field("Coach").StringValue, rs.Field("City").StringValue)
    
    rs.MoveNext
  Wend
End If

The code snippet above has been taken from the ODBC example project that comes with the XOJO.
What I would like to clarify is, if it is a safe method regarding the system resources since a database can have thousands of records.
As far as I know, other frameworks have mechanics that ensures the stability of the application’s list control in those cases.

no this method is ok until some thousands of records, after that it will become slow to very slow.
you must use the ‘limit’ and ‘offset’ sql keywords in your select query to display only the records that are visible on the listbox.
you must use a listbox without scrollbars, and add your own scrollbars as their values will be defferent from those of the listbox
and put all these in a containercontrol.
there are some add ons for xojo that does this.
I made my own, it’s not that hard, but once you have it you can display any number of records you need.
it also handle transparently remote databases that can be painfully slow if you don’t do this caching thing.

If you are worried about lack of system resources you can always catch outofmemory exceptions .

If you are presenting “thousands” of records in a list box you have failed to provide a useful UI. Use a “WHERE” clause to limit the data to that which may be pertinent to the user.

Yeah, this is what we developers always say. My customers though occasionally need to see all of their data, I guess because it gives them a reassuring warm feeling in their tummies to know the data is really still all there even though they just updated one record.

Thank you all for your replies.

Could you please specify any of them?

This is my preferred solution. I would appreciate If you can provide guidelines to this topic.

In that event I show a dialog with the number of records to be retrieved and giving a continue/open in Excel option which allows the user to know that the data exists or export it to a csv file and open in Excel. The user gets the warm and fuzzies from the number of records or the deluge of data in Excel.

This is still managing the UI correctly - at least in my opinion.

[quote=366773:@George Papadopoulos]there are some add ons for xojo that does this.
Could you please specify any of them?[/quote]
pidog dataview : https://www.pidog.com/piDogScrollingCanvas/DataView.shtml
data-on-demand : http://www.mactechnologies.com/index.php?page=downloads#dod
that pops up to my mind, but as I made my own listbox, I don’t remember all the others.
there was einhugur datagrid, but it’s no more supported.

when you are searching in your records, it’s nice to have the whole view of all your datas.
the failed UI comes because often with too many records the UI becomes too slow to be usable.
my listboxes presents one million records for some of them, and are perfectly usable in a consistent UI.

ps: I did not (still) make these big listboxes for web app, but will try to use the same UI when it will be needed.

[quote=366773:@George Papadopoulos]I made my own, it’s not that hard, but once you have it you can display any number of records you need.
This is my preferred solution. I would appreciate If you can provide guidelines to this topic.[/quote]
I already did …

[quote]you must use the ‘limit’ and ‘offset’ sql keywords in your select query to display only the records that are visible on the listbox.
you must use a listbox without scrollbars, and add your own scrollbars as their values will be defferent from those of the listbox
and put all these in a containercontrol.[/quote]

I disagree. Who has the right to stop the user to query as much as records he wants? That’s why computers are for.
Imagine you have a book in your hands and someone prevents you from opening at any page you like, just because you cannot read all the book at a moment.

I have the same need :slight_smile:

I believe that the XOJO, as a rapid app tool, must offer a built in list control for databases for all those who cannot build one, or don’ have the time

Which is precisely the point of only loading visible rows. You get the best of both worlds. Regardless of the size of the database, you can have fast results. Even large remote databases should perform fine.

That said, scrolling a large database is often not too productive, even if the load and scroll are both fast. Optional filters tend to be very effective here. But with a load on demand approach, you still get fast UI regardless of the size of the result set. (Providing of course the database has proper indexes for the WHERE and ORDER BY clauses used.)

they do, even if the server is on a lousy adsl line