Listbox - how to use with a database?

General
1

Hello all,

Can anyone suggest sample code to use a grided list box with a database? This is to unfortunately, replace the many Einhugur Style Grids in my very large project.

Specifically, adding rows from a db, and adding icons in certain cells next to data from a database.

Thanks in advance!
Tim

2

Why do you need to replace the StyleGrids?

3

Hi Markus.
It does not work with 64 bit, and there are no plans for updating it - according to this forum and the product website.

If I am wrong, I’d love to hear how to make it work! That would save tremendous time and effort.

Thanks for you reply Markus,
Tim

4

Make a container control with a listbox subclass and 2 scrollbars
make a property in the CC that contains the sql request to your database
using the “LIMIT” and “OFFSET” options of the SELECT command, you can draw the list with only the visible part of the listbox that is redrawn.
limit = number of visible rows
offset = value of the vertical scrollbar
it’s not easy to do, it took me some months to make it work flawlessly but it can be done (I did it !)
that way you see almost no difference to display a local database with 100k rows, or a distant one because you search and display only 50 rows at a time.

5

Hmm… I don’t remember such an announcement. However, the DataGrid is still not Retina capable after 3 years.

There are specialized listboxes available for displaying a large amount of data.

@Ivez: any plans to make your code public?

6

I cannot get an app to compile to 64 bit using that object. Maybe I missed something else - has anyone been able to make an app compile to 64bit with the stylegrid?

Tim

7

Here is a link to the announcement that no work is planned to be perform by Einhugur.
https://einhugur.com/blog/index.php/frequently-asked-questions/frequently-asked-questions-grid-controls/

8

I went through this last year for the same reasons (Retina and upcoming 64-bit support), had to rip out about 5 or 6 StyleGrids and replace them with ListBoxes. You can show a pic in the CellBackgroundPaint and CellTextPaint events, fairly easy there should be examples all over the place in the forums and docs. My work does not involve a huge number of rows returned from a DB, so I can’t comment on how well that part will work for you. But under 1000 rows, the ListBox is just about instantaneous for displaying data from a database.

9

Hi Merv,

I think I read your post elsewhere about making the change. In my case, the record counts are all very low < 100. Do you know where I can get a sample that uses a db to fill a gridded listbox?

Thanks,
Tim

10

I just do a query on the database, returning the minimal amount of data needed for whatever view of the data I am showing and loop through the recordset, adding a row to the ListBox and then fill in the information you want to show. I use the RowTag for the DB record identifier so that I can lookup and make user edits easy. I use CellTags for flags that I can then react to in the two Cell paint events (things like pics). The Xojo Docs should show you how to both query the database (which I am sure you would already be doing to fill in the StyleGrid?) and also how to fill in the cells and celltags of a listBox, if you look in the ListBox section.

11

Found an example finally. The docs show

[quote]. See the example “Database Example” in the Examples folder.
[/quote]

But it is not in the Listbox examples. It is actually in the Database/SQLite/SQLiteExample.

Thanks for all the feedback!
Tim

12

[quote=225023:@Beatrix Willius]
@Ivez: any plans to make your code public?[/quote]

sorry no plans like that for now, there are too much imbricated code and modules to release it alone.
and seeing the time I spend on it I would surely sell it :wink:

13

Does anyone know why there are 2 AddRow methods to the Listbox?

Even in the examples there are 2 methods. It works in the examples, but when I try to use it .AddRow the compile says there are 2 and doesn’t know which to use.

Can anyone tell me what I am doing wrong with this?
Thanks,
Tim

14

Tim can you paste your code please?
Thanks

15

This is the line

StandardListBox.AddRow(Cstr(App.LicensedControllers.CorpID), App.LicensedControllers.SiteName, App.LicensedControllers.SiteID,Cstr(App.LicensedControllers.SiteID) )

Edit
This however works StandardListBox.AddRow "123"

Another edit
The problem appears to be no space between .AddRow and the “(”
Adding a space fixed it.

16

do you want it in 1 line with 4 columns?

dim s as string = Cstr(App.LicensedControllers.CorpID) + chr(9) + App.LicensedControllers.SiteName + chr(9) + App.LicensedControllers.SiteID + chr(9) + Cstr(App.LicensedControllers.SiteID)
StandardListBox.AddRow
StandardListBox.Cell(StandardListBox.LastIndex, -1) = s
17

Axel is fast :slight_smile:

18

Thanks guys - I appreciate that sample.
The docs show it using commas

Is there a reason why the Tab is necessary in this case? Or are the docs wrong?
Tim

19

Thanks again everyone.
I found the problem with my original code - two of the records are integers and needed the Cstr conversion. It was missing. The compiler threw an incorrect error - at least that is what it appears to be.

Changing from this

StandardListBox.AddRow (App.LicensedControllers.CorpID, App.LicensedControllers.SiteName,App.LicensedControllers.SiteID,App.LicensedControllers.SiteID)

This - makes it work

 StandardListBox.AddRow (Cstr(App.LicensedControllers.CorpID), App.LicensedControllers.SiteName, Cstr(App.LicensedControllers.SiteID),Cstr(App.LicensedControllers.SiteID))
20

commas should work, but you must set the column count in IDE
with tab it creates column that needed