Loading listbox with dbkit

Getting Started
1

Lets just start by saying I’m just missing the plot here (or more like being dense!), but for the life of me I can’t seem to figure this out .

I have a sqlite database with table name of products, all I want to do is open the window and have a list box display the table contents using dbkit.

I’ve followed the tutorial but when it comes to bypassing the searchfield on my own project I get nada with no errors.

Could someone be kind enough to put a step by step guide for the above just to help this simple (and somewhat embarrassed) sole.

Thanks in advance

2

Have-you read SQLite basics ?

I am at my local McDo and meal is hot. I can come back when I finished.

BTW: please, define what you mean by dbkit.

3

OK, here you are:

image
image2744×1910 338 KB

Put the following code into the OK button:

Var f As New FolderItem
Var db As New SQLiteDatabase

// Get the db located in the Desktop, named Products.sqlite
f = SpecialFolder.Desktop.Child("Products.sqlite")

// Assign the file to the SQLiteDatabase Class
db.DatabaseFile = f

Try
  // Connect to it
  db.Connect
  System.DebugLog "DB connected !"
Catch e As DatabaseException
  
  MessageBox(e.Message)
  
End Try

// Now, the read from the db / display in the ListBox (LB) process
Var rowsFound As RowSet
Var Loop_Idx As Integer

Try
  rowsFound = db.SelectSQL("SELECT * FROM List_Data") // Use your db TABLE name
  
  For Each row As DatabaseRow In rowsFound
    // Set the ListBox number of Columns
    If LB.ColumnCount <> Row.ColumnCount Then
      LB.ColumnCount = Row.ColumnCount - 1 // Skip the Row ID
    End If
    LB.AddRow ""
    For Loop_Idx = 0 To row.ColumnCount - 1
      LB.CellTextAt(LB.LastAddedRowIndex, Loop_Idx) = row.ColumnAt(Loop_Idx).StringValue
    Next
  Next
  rowsFound.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

I changed the way the number of columns (ListBox) is set to avoid showing the RowID (provided it is in the last Column); and it is now set automatically (depends on the number of columns in the database)…

Result with my db:

image
image1424×1082 256 KB

The main window

4

Hi Emile, thanks for the response, The way you showed above is how I’m currently doing it, but looking at the docs for the dbkit it looks as if this and other things like adding/editing and deleting records can be achieved with less coding, and (in my case) more reliability. It may be that I’ve misunderstood the purpose of the dbkit or expecting to much from it but I was hoping it would simply things.

5

OK, this dbkit. I skipped that.

There is no specific reliability lack in the shared code not better reliability (IMHO) elsewhere.

The code I shared was rough and done using the documentation.

You can create the TABLEs too using this kind of code.

You can also crate your own utility (import / export / Add Column / Delete Column, etc.) with this kind of code.

At last, some people find that using a specialized application (one they do not wrote) will be better; the only good thing is… the time they do not takes to develop their own solution. The downside is… they do not have the inside knowledge and must later rely on these external utilities.

Your opinion may vary.

6

Maybe you can share your sample project, I’m sure someone will review it and suggest some changes.

There are several things that can go wrong, like not having the db when we think it should be, wrong connection to dbkit, etc.

7

Hi Alberto

I’ve uploaded a sample project as sugested.

Thanks

DBKIT Test.zip (63.5 KB)

8

The test project makes Xojo 2024r1 crash hard.

9

Managed to figure it out by adding a method called at opening with

Var rs As rowset
rs=TableConnection1.Connection.SelectSQL("select * from Product")
lstData.AddAllRows(rs)
10

I’ve resolved this by modifying SearchField|Methods|Search to include an ‘if’ statement for each affected table. For example:

If searchValue.IsEmpty Then
  if table = "clients" then
    SQL = "SELECT * FROM clients ORDER BY company"
  End
  if table = "clientshistory" then
    selectedClientID = WebPageCRM.ListBoxClients.CellTextAt(WebPageCRM.ListBoxClients.SelectedRowIndex, 0).StringValue
    SQL = "SELECT * FROM clienthistory where clientid = " + selectedClientID + " ORDER BY datecreated"
  End
  rs = Connector.Connection.SelectSQL(SQL)
11

Hi Jon

Thanks for the reply I’ll give it a go