I have a listbox filled with table values. The primary key is stored in the row tag. When a user wants to change the contents of the record I retrieve a recordset with the record values. When the user hits ‘update’ I put this recordset in ‘edit’ mode, change the contents and do ‘update’. This should work.
The problem is that if I update the contents of the listbox by retrieving all the records again I suddenly have 2 records; one with the new values and one with the old, untouched values??? I perform listbox.deleteallrows before populating.
After the update I check dberror, but no problems. It looks like the record is nevertheless locked or something?? Never had this before…
If you are using the SQLiteDatabase instead of the REALSQLdatabase, the select statement you use for the recordset update needs to explicitly have the rowid in it. For example “Select Rowid, * From MyDatabase”.
What doesn’t work anymore is “Select * From MyDatabase”. It won’t give you any errors, but it will no longer update the record.
Stuff like this trips me up all the time. If I can’t find the problems in a few minutes of visually looking, I’ve got into the habit of just setting a breakpoint just before the call to create the recordset and get the actual contents of the sqlString, and taking it to Navicat and running the query there. The results window quickly tells me if the query is legitimate but just returning no records or whether there is a db query or a syntax error.
What I normally do is prepare the SQL string in a Sqlite db editor like Valentina and if it runs ok I copy the string to the source but you have to manually insert the variables. This part is always causing the trouble.
[I must admit that I have some hesitation for prepared statements]
I’m talking about PreparedStatement, a Xojo class. It will let you type out a complete Select statement with placeholders for the fields, and that makes it easier to spot those errors.
Where user interaction is allowed, this is the only way you should be doing Selects.
Yes, we are talking the same thing. Umm… it seems complex though I know it isn’t. Maybe I should give it a try. My current project has no user interaction in the queries.
[quote=58645:@Alexander van der Linden]What I normally do is prepare the SQL string in a Sqlite db editor like Valentina and if it runs ok I copy the string to the source but you have to manually insert the variables. This part is always causing the trouble.
[I must admit that I have some hesitation for prepared statements][/quote]
I’d use them without hesitation as they get rid of a huge amount of complexity and possibility for bugs
In particular they get rid of any possibility for sql injection (deliberate or accidental)
You write it once & then don’t sweat it again unless you have to alter the sql
This is what the incorrect code would have looked like with a PreparedStatement:
sqlString = "select * from CDD2_Person where P_ID + ?" // Easy to spot this error
dim ps as PreparedStatement = db.Prepare( sqlString )
ps.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT ) // Assuming SQLite
dim rs as RecordSet = ps.SQLSelect( perListbox.RowTag( perListbox.ListIndex ).StringValue )
Note how neither the quoting nor the escaping is needed, and the statement is much easier to read while the “cost” is two extra lines.
If this is reusable code, you can even do this:
static ps as PreparedStatement
if ps is nil then // Initialize it
sqlString = "select * from CDD2_Person where P_ID + ?" // Easy to spot this error
ps = db.Prepare( sqlString )
ps.BindType( 0, SQLitePreparedStatement.SQLITE_TEXT ) // Assuming SQLite
end if
// The statement only has to be prepared once
dim rs as RecordSet = ps.SQLSelect( perListbox.RowTag( perListbox.ListIndex ).StringValue )