I’m using a SQLiteDatabase in a Desktop App.
I have a Table TB01_TEST with two record:
TB01_ID TB01_INTEGER
1 44
2 396
when I update the first recordset everything seems to work, but as soon as I move the recordset forward and then go back the recordset object returns the old values, those before the update. Why?
Dim db As New SQLiteDatabase
db.DatabaseFile = f
Dim bRes As Boolean = db.Connect
If bRes = True Then
Dim rs As RecordSet = db.SQLSelect("select * from TB01_TEST")
If rs <> nilt Then
Dim OldIntVal As Integer = rs.Field("TB01_INTEGER").IntegerValue
MsgBox("OldIntVal: " + OldIntVal.ToText)
'OldIntVal is 44
rs.Edit
rs.Field("TB01_INTEGER").IntegerValue = 155
rs.Update
'now the databse is update
Dim NewIntVal As Integer = rs.Field("TB01_INTEGER").IntegerValue
MsgBox("NewIntVal: " + NewIntVal.ToText)
'NewIntVal is 155
'I move forward and back
rs.MoveNext
rs.MovePrevious
Dim NewIntValAfterMove As Integer = rs.Field("TB01_INTEGER").IntegerValue
MsgBox("NewIntValAfterMove: " + NewIntValAfterMove.ToText)
'NewIntValAfterMove still has the old value 44
End If
End If
db.Close
How can I get the recordset with updated values even when I move?
if I reread the table the values are updated, but I can NOT always read the table again with each new update.
@Jean-Yves Pochez
Does this mean that I can not move forward or backward otherwise I lose the new values? OMG
It’s a very strange thing because the values are actually written to the database file, but the recordset in memory loses new values…
Should not it be considered a BUG? Should not they correct it?
[quote=401630:@Gabriele Marchionni]@Jean-Yves Pochez
Does this mean that I can not move forward or backward otherwise I lose the new values? OMG
It’s a very strange thing because the values are actually written to the database file, but the recordset in memory loses new values…
Should not it be considered a BUG? Should not they correct it?[/quote]
sure you can’t move backward, it’s not recognized by most of the database plugins.
the move forward is akward: it moves the cursor, but sometimes I noticed that, more if you update the record, it looses it’s values.
I made a class, that copies the recordset in an array just after the sqlselect, and then you can move in the array the way you want.
but I had problems with updating recordsets and moving the cursor at the same time.
also I think it’s better ( and most of the time easier) to use tue UPDATE sql command to do that.
but as Paul mentionned above, what does any error check show on this ?
@Massimiliano Chiodi - ho provato anche con db.SQLExecute(“BEGIN TRANSACTION”), poi l’update, poi il commit, sempre uguale. Il file di SQLite è aggiornato ma il database in memoria appena mi sposto perde i valori e riprende quelli vecchi che aveva appena ho fatto db.SQLSelect.
@Paul Budd - No error when I check.
I tried both with db.commint and without, with begin transaction and not. In both cases, immediately after the rs.update, the database file is written correctly. The problem is not the database file, but it is the recordset object in memory.
From what you tell me it is not my mistake but it is xojo that behaves like that. In all other platforms I could update, move, and the recordset was always correct. In Xojo no, you can not. Right?
if you use sqlite, this is the only database that should work …
As I told you, I had problems with this years ago and found a workaround, not using rs.update almost everywhere.
today, I almost use only UPDATE sql queries, or remote functions.
you can work without using rs.update.
don’t know if it’s a bug or a feature …
Recordsets are snapshots. They are the values as they were at the time you made the query. When you update one the snapshot (recordset) and the database are now no longer in sync. You’d have to recreate the recordset to see the updated value in the snapshot.