Best way to insert a record from a listbox to a database

Databases
1

Hi,

I took a class in Xojo this weekend and am excitedly exploring designing my first little app. I’ve already run into a problem and I was referred here to get help.

I have a two column, multirow listbox that I need to insert into a database. I have two database tables: one called ‘people_ids’ and one called ‘people_details’. My tables look like this

======================= ========================
| PEOPLE_IDS | | PEOPLE_DETAILS |
======================= ========================
| dateEntered | datetime | | id | integer |
| timeEntered | datetime | | name | varchar |
======================= | age | integer |
========================

Now, the ‘id’ in PEOPLE_DETAILS is taken from the MAX(rowid) of the PEOPLE_IDS table. Now, here is the code I have:

Dim rs as RecordSet
Dim dr as new DatabaseRecord

rs = app.db.SQLSelect(“SELECT MAX(rowid) FROM people_ids”)
dr.IntegerColumn(“id”) = Val(rs.Field(“rowid”).IntegerValue)

This is where I reach my limit. I need to now loop through the listbox and retrieve the value of current row, cell 0 and current row, cell 1, assign them to a dr column and insert them into the database. I know how to insert into the database but I don’t know 1) how to grab the text from a specific cell in the current row and 2) how to advance to the next row.

Can anyone help? Sorry if this is a lot of useless information. I am trying to give a complete picture.

Thanks!
David

2

to get column 0 and 1 from every row in the listbox try:

for i as integer = 0 to lstbox.rowCount - 1 (could be lstbox.listCount -1 in desktop app)
firstColumnText = lstbox.cell(i,0) {whatever you’re gonna do with the data}
secondColumnText = lstbox.cell(i,1)
do something
next i

3

Not sure I quite understand as the code you’re using looks as if your doing sql statements… rather than pulling data that is in your listbox and inserting it into your database…

But this should help you get in the right direction assuming your listbox is full of the data that you need to insert your listbox in a database table. Hopefully the code isn’t to messy.

  dim dr as new database record
  if app.db.Connect then

    for x = 0 to Listbox1.ListCount-1
      // dr is the database record and looping through the rows (x)
      // cells defined as cell(rows, columns)

      dr.column("dateEntered")=Listbox1.cell(x,0)
      dr.column("timeEntered")=Listbox1.cell(x,1)
      dr.column("name")=Listbox1.cell(x,2)
      dr.column("theme")=Listbox1.cell(x,3)
      dr.column("age")=Listbox1.cell(x,4)
      dr.column("id")=Listbox1.cell(x,5)
     
      App.db.InsertRecord ("PEOPLE_DETAILS", dr)
      // Checking for DB errors
      If App.DB.Error Then
        MsgBox("DB Error: " + App.DB.ErrorMessage)
        return
      End If
    next

  else
    
   MsgBox("DB Error: " + App.DB.ErrorMessage)
  end if
1 Like
4

we got the same code … I just don’t use database records.