DBKit - Accessing table fields not in the window layout

Databases
1

By way of example of a table of three fields: Name1, Name2, Name2:

If Name1 and Name2 are in the layout as DBKit.TextField, the user can modify their contents.
While the user is making these modifications, is there a way to modify the contents Name3 by code so DBKit can do the work of saving its modifications without Name3 having to be in the layout?

Thanks -KC

2

While I’ve never used the DBKit yet, maybe change the visibility of a DBKit.TextField for Name3 so the user can’t see it but you can still change it via code?

3

Placing the field in the layout and making it invisible definitely works, and gives easy access to DBKit’s instance of the field. I was wondering if there is a way get that access while avoiding placing another object in the layout. There will be databases with a lot of fields and I’m not sure how expensive placing an object in the layout is.

On the other hand something like having a layout tool that placed all or selected fields at once, invisible in a small part of a form, would reduce some of the work.

4

I think this post from 2024.01 is another way of asking the same question. I cannot find where a RowSet is associated with a DBKit query. There is this, “listbox.CellTextAt(listbox.SelectedRowIndex, column_num)”, but it only accesses columns contained in the listbox.

Would the best workaround be to create RowSets from queries on a primary key in a hidden listbox column, testing for selected rows with ListBox1.RowSelectedAt(i) or ListBox1.SelectedRowIndex?

(Sorry, I see there is a “Getting Started” category where I should have originally posted this thread.)

5

As far as I can see this earlier post asks a different question, and the answer to that one should be: Yes, you do need to refetch the data. The usual way of loading data to a listbox is to store the records id in the row’s rowtag and to reload the data for your detail view (or form) purposes. It’s not uncommon to create 2 database views (e.g. customer_listview and customer_detailview) for this purpose.

As to your original question, any trickery with fields not visible to the user could be handled on the database side using triggers. That’s what they are for. But maybe the DBKit provides some mechanism as well, I’ve never used it.

6

Thanks,
I don’t see it confirmed in the docs, but the RowTag content does match the primary key in the DBKit listbox I created from the tutorial.

7

I have been working on a DBKit project for a year now, but I’m not using the most recent version so I will be out of date, so here is my take.

If I fetch the rows to bind to a listbox:

Var rs As RowSet = EmergencyConnector.Connection.SelectSQL("SELECT * from Emergency")

I get the rowTag of the selected row:

var emergencyid as Integer = SearchResults.RowTagAt(SearchResults.SelectedRowIndex)

And then do the action I need (send a push notification).
Then update that row as required, e.g.

EmergencyConnector.Connection.ExecuteSQL("UPDATE Emergency SET modified = ?, requested = ? WHERE id = ?", now, now, emergencyid)

But I do wish the Connection CurrentRow was public instead of private, then I could access all the variables I originally fetched in that rowset.

8

Thanks,
This is what I landed on to get access to all the fields in the selected row of the listbox. A new connection (db2) was created since I couldn’t figure out how to access the DBKit connection the listbox is using. I’m not familiar with Xojo or SQL yet, so I’ll need to study some to know if we are doing the same sort of thing.

Var SQL As String
Var db2 As New SQLiteDatabase
Var dbFile As FolderItem = SpecialFolder.Desktop.child("DB.sqlite")
Var PK As Integer

PK = cardbox.RowTagAt(cardbox.SelectedRowIndex)   // cardbox is the listbox with the selected record
db2.DatabaseFile = dbFile
db2.Connect      // this is the same database as cardfile is connected to
SQL =  "select * from card where id = "+PK.ToString   // just the first selected in the listbox
rs = db2.SelectSQL(SQL)  '  got the rowset
db2.close