The recordset loses the updated values after moving

Databases
1

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.

2

Try a db.commit after rs.update.

3

Yes, I’ve already tried, but I have the same problem. The recordset proposes to me the old values.
but why, does it work for you?

4

I update one recordset only after a select query.
if you need to update another recordset, then make another query just before.

also it’s better to use the UPDATE query syntax, than use the recordset update method
here better = works for every database.

5

In that case check after the rs.update for an error message, something you should always do after a database operation.

If db.error then
Msgbox(db.errormessage)
End if
6

@Jean-Yves Pochez
Does this mean that I can not move forward or backward otherwise I lose the new values? OMG :frowning:

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?

7

Devi rieseguire necessariamente la query.
soprattutto devi effettuare il commit.
Ricordati begin transaction prima di update.

8

[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 :frowning:

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 ?

9

@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?

10

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 … :wink:

11

This is from the Recordset.Edit notes:

If you want dynamic recordsets, you’d have to create a custom class, possibly based on JSON or Dictionary.

12

[quote=401599:@Gabriele Marchionni]rs.Field(“TB01_INTEGER”).IntegerValue = 155
[/quote]
As people say, you might be better off with an SQL update:

db.SQLExecute ("update TB01_TEST set TB01_INTEGER=155")

I never update a recordset.

13

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.

14

From the RecordSet.Edit Notes:

15

But the value in the database is changed right?

16

As long as you do

rs,update
db.commit

after all fields are edited in the recordset for a record.