UPDATE Sqlite database code not working

Hello

I’m trying to modify a record in a Sqlite Database Table selected by a Popup Menu RowTag. I want to replace the string text contents of Item1 and Item2 with string text in TextFields txtItem1.Text and txtItem2.Text. The code below is not working. This is a smaller simplified example of my code.

Dim sql As String
Dim RowId As Integer

sql = "UPDATE Table1 SET Item1 = ?, Item2 = ?, WHERE ID = ?"

  Dim ps As SQLitePreparedStatement = db.Prepare(sql)
  
  iRowID = PopUpMenu.RowTag(PopUpMenu.ListIndex).IntegerValue
  
  ps.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)  'ID
  ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)   'Item1
  ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)   'Item2
  
  ps.Bind(0, iRowID)
  ps.Bind(1, txtItem1.Text)
  ps.Bind(2, txtItem2.Text)
  
  ps.SQLExecute()
  
  // CHECK FOR DATABASE ERROR
  If db.Error Then
    MsgBox("Error: " + Str(db.ErrorCode) + " - " + db.ErrorMessage)
    Return
  Else
    MsgBox("Edited Fingering Sucessfully Saved To Database")
  End If

Looks like your Bind is out of order.
Bind 0 is for Item1 = ?
Bind 1 is for Item2 = ?
Bind 2 is for ID = ?

2 Likes

If this is the complete sql string you use, then this comma between ? and Where is too much.

What error message do you get?

1 Like

Either re-order your binds as has been suggested, or use ?1, ?2, ?3 to force the order.

1 Like

Thomas and Alberto are correct. There’s at least a syntax error plus a parameter order error.

1 Like

Thanks Guys - That was it. The orders for ID in my original long code. I accidentally put the extra comma by cutting & pasting here. Cheers