Trying to Update a Row in a Database with NO Luck

Databases
1

Hi,

I am trying to update a row in a Database with no Luck. When I last used XOJO it was RealStudio. Any Ideas why this will NOT Work

There is data in Database:

Mins | Password
30:00 | TestPassword

InptBox_Minutes.text = 30:00

Dim LockdownDB As New SQLiteDatabase
LockdownDB.DatabaseFile = SpecialFolder.Documents.Child("MyDatabases").Child("LockdownDB.sqlite")

Dim sqlSavePassword as String
sqlSavePassword = "select Mins,Password from LockdownStore where Mins = '" + InptBox_Minutes.Text + "'"

If LockdownDB.Connect Then
  Dim DatabaseEditSet as RecordSet = LockdownDB.SQLSelect(sqlSavePassword)
  DatabaseEditSet.Edit
  DatabaseEditSet.Field("Password").StringValue = trim(InptBox_Password.Text)
  DatabaseEditSet.Update
  LockdownDB.Commit
  DatabaseEditSet.Close
else
  MsgBox("Error: " + LockdownDB.ErrorMessage)
end if

In DEBUG the SQLQuery is being built fine

select Mins,Password from LockdownStore where Mins = '30:00'

And I can see in Debug that

DatabaseEditSet.Update. , Updates the Field
2

there must be a primary key in the recordset if you want to edit and update it.
(but the msgbox of the error should have told you about it ?)

3

Nope, Im getting NO Errors what so ever!

4

To add to Jean-Yves reply, add “rowid” to your SELECT statement and it should work.

Also, in that code, you only checking for an error on Connect, but you probably get it after Update or Commit.

But you should not be selecting data like this as you’re opening yourself up to SQL injection or errors. Use a Prepared Statement instead. And while you’re at it, you can use a Prepared Statement to update the data too and bypass this issue entirely.

5

Thanks @Kem Tekinay I have started to read up on Prepared Statements before posting here :

https://documentation.xojo.com/index.php/SQLitePreparedStatement

But the examples, are confusing me some what. Is there anything simpler I could use as a reference point?

Im also a bit puzzled on how I would look up against a Primary Key as I have no way of passing it…

6

That example looks pretty simple to me, so let’s try it this way…

The point of a Prepared Statement it to use placeholders for the data so the statement can’t be modified by the data. In your code above, it would look something like this:

dim psUpdate as PreparedSQLStatement = LockdownDB.Prepare( _
    "UPDATE LockdownStore SET password = ? WHERE Mins = ?" )
psUpdate.BindType 0, SQLitePreparedStatement.SQLITE_TEXT
psUpdate.BindType 1, SQLitePreparedStatement.SQLITE_TEXT
psUpdate.SQLExecute InptBox_Password.Text, InptBox_Minutes.Text
// Check for errors

(Not tested so allow for errors and typos.)

The “?” take the place of the data which you subsequently bind to the statement. Does that make sense?

7

you should display the db.errormessage just after the “Update” command.
there it would tell you it needs a primary key to update a recordset.

8

@Kem Tekinay Now it Does Yes!

The above is WAY More easier to follow than that other page!

dim psUpdate as PreparedSQLStatement = LockdownDB.Prepare( _
“UPDATE LockdownStore SET password = ? WHERE Mins = ?” )

psUpdate.BindType 0, SQLitePreparedStatement.SQLITE_TEXT
psUpdate.BindType 1, SQLitePreparedStatement.SQLITE_TEXT

0 , 1 - Arguments to be passed in by in correct Order

psUpdate.SQLExecute InptBox_Password.Text, InptBox_Minutes.Text

Assume that the 0,1 Is an Index?

9

You forgot to check for the Database error after the update:

DatabaseEditSet.Update if LockdownDB.error then msgbox LockdownDB.ErrorMessage end

You must check for the error after every database action (insert, update, delete, select).

10

No, 0 and 1 are the indexes of the placeholders in order. The first “?” is index 0, the next is index 1, etc.