Listbox to Database UPDATE error

I cannot figure out what I am doing wrong, I assume it’s some kind of syntax error.
I am reading the UPDATING RECORD documentation and applying this to my project. Right now I am trying to get the UPDATE option to work. When I make changes in a listbox row text (by double clicking on it) and then click the UPDATE button, I get a near “22”: syntax error message. What am I doing wrong? Here is the code I am working on:

try

var rs as RowSet = app.db.SelectSQL(“select * from IdeaDrawer where id=”+ListBoxIdeaDrawer.text)

rs.EditRow
rs.Column(“ListBoxIdeaDrawer”).StringValue = textIdeaName.Text
rs.SaveRow

catch

end try

What is in ListBoxIdeaDrawer.text ?

I would be more inclined at this stage to say:

Var sql as String = "select * from IdeaDrawer where id=" + ListBoxIdeaDrawer.text
msgbox (sql)

So you can see what you’re doing.

Unless you control all the values, you should never write SQL like this (and it might the source of the error anyway). You are opening yourself up to SQL injection.

Assuming SQLite, try this:

var rs as RowSet = _
    app.db.SelectSQL("select * from IdeaDrawer where id=?", ListBoxIdeaDrawer.text)

Also, when posting code, please use the code tags here. It will help us help you. (Type three backticks on a line by themselves before and after the code block.)

I would also be inclined to put the id (assuming you mean it to be an integer) inside the row’s RowTag, specifically defined as an integer, so that when you pull it out you know you’ve got an integer 27 and not a string 27 with some odd characters there which give you a syntax error. The integer can easily then be converted to the desired string using .ToString.

Even better would be to formulate your SelectSQL as Kem says.

You can also format your code you post here by:

  1. Select it with the mouse

  2. click the </> icon.

The listboxIdeaDrawer contains only text. This is how it looks like:

Screenshot 2022-08-17 at 16.48.24

So after double clicking a row in the ListBoxIdeaDrawer and changing it, then clicking the Update button, this change should be made in the database table IDEADRAWER in the ListBoxIdeaDrawer column, but I get the error instead.

Well, for one thing you are not specifying which row the id is coming from. If ListBoxIdeaDrawer is the name of the listbox, then ListBoxIdeaDrawer.text has no meaning and the compiler will complain. Then, even if you include the row number so the app compiles, your SQL will look like this:

select * from IdeaDrawer where id=idea1

If that is your intention, then SQLite will complain and give you a syntax error, because idea1 is a string, not an integer, so it should be enclosed in single-quotes, thus:

select * from IdeaDrawer where id='idea1'

(or better, supplied as Kem suggests)

But even that may not be what you want. What is the schema of your database? is id defined as a string or an integer?

In your ‘IdeaDrawer’ table how many columns/fields do you have? I guess at least:

  • ID
  • ListBoxIdeaDrawer

but you may have more.

Is the ID column the index defined by Integers?
For your listbox, ListBoxIdeaDrawer.text what is the value? Is that an Integer that matches one of the IDs on your IdeaDrawer table?

Are you saving the IDs from your table as a RowTag on your Listbox? With that, you can match the record ID again to select the correct record to update.

And is it the PRIMARY KEY? For the RowSet edit to work, you must select the primary key in the record. (In SQLite, that’s rowid if you didn’t specifically define a PRIMARY KEY column.)

But that’s after you get the SELECT working in the first place.

Assuming you have a property “DB” of tye “SQLiteDatabase” in App and the database is connected, then you can create your table “IdeaDrawer” with this statement:

App.DB.ExecuteSQL("CREATE TABLE  IF NOT EXISTS IdeaDrawer (ID  NOT NULL PRIMARY KEY AUTOINCREMENT, GUID TEXT, ListBoxIdeaDrawer TEXT, IdeaDrawerDescription TEXT);")

GUID is meant to store a unique user generated ID. There are several ways to generate such a unique ID (I am using the UUIDMBS from the MBS plugins). There are several improvements in using a GUID instead of the ID (e.g. creating new records, merging two databases etc.)

Create a method “Listbox_IdeaDrawer” to populate the listbox.

Var rs As RowSet
Var LB As DesktopListBox
LB = IdeaDrawer // the name of yout listbox
LB.RemoveAllRows // clear the listbox before populating with data

Try
	rs = App.DB.SelectSQL("SELECT GUID, ListBoxIdeaDrawer, IdeaDrawerDescription FROM IdeaDrawer")

Catch e As DatabaseException
  MessageBox("Error loading data: " + e.Message)
  Return
End Try

If rs <> Nil Then

// hide ListBox for faster loading
LB.visible = False

For i As Integer = 1 To rs.RowCount
  LB.AddRow(rs.Column("ListBoxIdeaDrawer").StringValue )
  LB.CellTooltipAt(LB.LastAddedRowIndex, 1 ) = rs.Column("IdeaDrawerDescription").StringValue
  LB.RowTagAt(LB.LastAddedRowIndex) = rs.Column("GUID").StringValue

rs.MoveToNextRow
Next

// show ListBox again
LB.visible = True

// close Rowset
rs.Close

End If

Create a property “mCurrentGUID” of type “String” in your window, to store the current selected GUID. Create an eventhandler “DoublePressed” in your listbox to do a database request and to populate the fields.

// stop, if nothing is selected
If Me.SelectedRowIndex = -1 Then Return

// store GUID in mCurrentGUID an then perform database select
mCurrentGUID = Me.RowTagAt(Me.SelectedRowIndex).StringValue

// ---------------------------------------------------------------------
// search and retrieve data
// ---------------------------------------------------------------------
Var rs As RowSet
Try
  rs = App.DB.SelectSQL("SELECT * FROM IdeaDrawer WHERE GUID = ?", mCurrentGUID)
  
Catch e As DatabaseException
  MessageBox("Error loading data: " + e.Message)
  Return
End Try


If rs <> Nil Then
  
  tf_IdeaName.Text = rs.Column("ListBoxIdeaDrawer").StringValue
  ta_IdeaDescription.Text = rs.Column("IdeaDrawerDescription").StringValue
  
End If

// close RowSet
rs.Close

Finaly you can put this in the “Pressed” event of your “Update” button:

// check if mCurrentGUID is not empty
If mCurrentGUID = "" Then Return

// we have a mCurrentGUID, so now update database record
Try
 App.DB.ExecuteSQL("UPDATE IdeaDrawer SET ListBoxIdeaDrawer = ?, IdeaDrawerDescription = ? WHERE GUID = ?",  tf_IdeaName.Text, ta_IdeaDescription.Text, mCurrentGUID)

Catch e As DatabaseException
MessageBox( "Error updating IdeaDrawer: " + e.Message )
Return
End Try

// reload listbox to show changes
Listbox_IdeaDrawer
1 Like

Thanks so much for this! This will give me some extra material to study :slight_smile: