Listbox and databases

Databases
1

I am building a web project with a SQLite database that has about 3200 records. I’m using the following code to populate a listbox:

rec=db.SQLSelect("SELECT * FROM tblCompanies ORDER BY fldCompany")

While not rec.EOF
  lvCompanies.AddRow(rec.Field("fldAccount").StringValue,rec.Field("fldCompany").StringValue)
  lvCompanies.RowTag(lvCompanies.LastIndex) = rec.Field("ID").IntegerValue
  rec.MoveNext
Wend

I’m having a couple of issues. The first problem is that this method is very slow to populate the WebListBox. Is there a quick way to do this other than a loop, such as tying the recordset to the listbox as a datasource? The other issue is that once it does populate all of the records maneuvering the listbox is very slow. Moving the slider up and down is jerky and slow. I find it hard to believe that a WebListBox can’t handle that many records. Is there a better way to do this?

2

Don’t push more than about 100 records to the listbox. If you look around, most websites don’t even give you the option of showing more than that. Some do, I know but the point is those that do will get REALLY slow.

We implemented a data ‘paging’ controls and it’s fairly easy to page use using SQL Limit and Offset keywords. We have an hour long video (along with source code) showing this technique to subscribers over at http://xojo.bkeeney.com/XojoTraining/. Works for desktop apps too so you can limit how much data is pushed their too.

3

Thanks for the reply Bob. I was afraid that would be the case.

4

It’s not really that hard. Really. You do it once and everything else is copy and paste and change some variables.

The thing about Listboxes in general is that they were never really intended to hold thousands of rows. After a couple of hundred they’re not a very good selection mechanism. Even more true for web because the server has to put together all that HTML and shove it down the internet pipeline. More rows equals more text which means longer transmit times. Then, your browser has to reconstruct all that text into a UI and show it to you. Lots of data means poor performance.