Changing from realSQLdatabase to SQLiteDatabase

I’m working on some old Real Studio code and upgraded my database type from realSQLdatabase to SQLiteDatabase.
When I update a recordset it only updates if I select the entire row of the table.
Is this a difference between the two database formats?

This works:

Dim pstrSQL as string = "SELECT * FROM tblPictures WHERE nameId = '" + A.id + "'"
Dim rs as RecordSet = db.SQLSelect(pstrSQL)
    rs.Edit
    rs.Field(fieldname).Value = 0
    rs.update
   db.Commit

This doesn’t:

Dim pstrSQL as string = "SELECT fieldname FROM tblPictures WHERE nameId = '" + A.id + "'"
Dim rs as RecordSet = db.SQLSelect(pstrSQL)
    rs.Edit
    rs.Field(fieldname).Value = 0
    rs.update
   db.Commit

Code is a simple illustration (please no lectures about using prepared statements).

Was the table originally created as a realSQLdatabase or a SQLiteDatabase?

Does it have a field called “rowid”?

Does it have a primary key?

Created as realSQLdatabase
Yes there is a field called rowId (I use it a lot)
“id” field is Primary key

Note both SELECT statements create a valid recordset but only one can update the database.

where is “fieldname” getting a value from?

  rs.Field(fieldname).Value = 0

or did you mean to say

  rs.Field("fieldname").Value = 0

Sorry
rs.Field(“fieldname”).Value = 0

may you need to include id in the sql statement??

From the docs: https://documentation.xojo.com/index.php/RecordSet.Update

[quote] Notes

Check the Error property after this call. If the record could not be updated then Error will be True. You can then display the error message using the ErrorMessage property.

Some databases do not allow you to update if the SQL statement used to get the RecordSet contains more than one table. Additionally, some databases may require that the SQL statement specifically include the Primary Key for the table. [/quote]

Thank you I will try the example using db.error to catch the error message.

Yes including the primary key makes it work with SQLiteDatabase.

Dim pstrSQL as string = "SELECT id, fieldname FROM tblPictures WHERE nameId = '" + A.id + "'"