RecordSet.Edit will not Update the Table Unless it is ran Twice

  1. 2 weeks ago

    Hi Everyone,

    So here is the code that i am using to update values in a table. I am using Postgre as the database (and i looked up the RecordSet rule and the rules state that the only RecordSet method that works for Postgre is the MoveNext method (RecordSet Rules )). Anyways the code below does in fact work, but only if i run it 2 times, it does not work on the first try. any ideas why this is (or ways around it)?

     lngAdjId = lngAdjustId                   
      lngDlrAdjCode =ctlList.Cell(x,0).Val     
      strMasDlr = ctlList.Cell(x,1)                   
      dblAmt = ctlList.Cell(x,3).Val                 
      dblTaxRt = ctlList.Cell(x,4).Val              
    
        Dim Rst As RecordSet = mDBS.SQLSelect("SELECT * FROM TableName WHERE PrimaryKey="+_
        CStr(lngDlrAdjCode) + ";")
        If Rst<>Nil Then
          If Rst.RecordCount>1 Then
            strMsg= "Error Updating Dealer Rate..." + EndOfLine+EndOfLine + "Multiple Entries Found For Dealer Rate # " +_
            CStr(lngDlrAdjCode) + "!"
            GoTo ErrorIT
          Else
            Rst.Edit
            Rst.Field("daamteach").DoubleValue=dblAmt
            Rst.Field("dataxrate").DoubleValue = dblTaxRt
            Rst.Update
            If mDBS.Error= True Then
              strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage
              GoTo ErrorIT
            End If
          End If
        End If

    Thanks!

  2. Dave S

    Feb 12 San Diego, California USA

    I would use SQL and Prepared Statements instead of recordset Update

    NOT using PreparedStatements (I will leave that as a learning exercise)

    Dim Rst As RecordSet = mDBS.SQLSelect("SELECT * FROM TableName WHERE PrimaryKey="+ CStr(lngDlrAdjCode) + ";")
        If Rst<>Nil Then
          If Rst.RecordCount>1 Then
            strMsg= "Error Updating Dealer Rate..." + EndOfLine+EndOfLine + "Multiple Entries Found For Dealer Rate # " +_
            CStr(lngDlrAdjCode) + "!"
            GoTo ErrorIT
          Else
         mDBS.SQLExecute("UPDATE tablename SET dammteach="+str(dblamt)+","+dataxrate="+str(dbltaxrt)+" WHERE PrimaryKey="+ CStr(lngDlrAdjCode) + ";")
            If mDBS.Error= True Then
              strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage
              GoTo ErrorIT
            End If
          End If
        End If
  3. Jean-Yves P

    Feb 12 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    use an UPDATE...SET sql command instead of the SELECT and then recordset.edit method.
    it will be easier, faster if you have a lot of (remote) records, and works all the time even if the recordset does not contain the primary key.

  4. Dave S

    Feb 12 San Diego, California USA

    @Jean-YvesPochez use an UPDATE...SET sql command instead of the SELECT and then recordset.edit method.
    it will be easier, faster if you have a lot of (remote) records, and works all the time even if the recordset does not contain the primary key.

    which is what I just said

  5. Both of these answers I will consider for future coding projects. for now i did find the issue. in other parts of my project i am performing the same actions and in those actions, after the RecordSet.Update i had the statement Database.Commit which solved my problem.

  6. Jean-Yves P

    Feb 12 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @Dave S which is what I just said

    which is what I just said in the other thread ... ;)

  7. luciano m

    Feb 12 Pre-Release Testers, Xojo Pro

    try this :

     Rst.Edit
            Rst.Field("daamteach").DoubleValue=dblAmt
            Rst.Field("dataxrate").DoubleValue = dblTaxRt
            Rst.Update
            If mDBS.Error= True Then
              strMsg= "Error Updating Dealer Rate..." + EndOfLine + EndOfLine + mDBS.ErrorMessage
              GoTo ErrorIT
    else
            mDBS.commit
            End If
  8. Joost R

    Feb 12 Pre-Release Testers, Xojo Pro The Netherlands

    @Dave S I would use SQL and Prepared Statements instead of recordset Update

    The way to go. I would advise to write some generic code to be used all over in your projects, so ylu don't have to deal with complexity all the time.

  9. Tim H

    Feb 12 Pre-Release Testers Portland, OR USA

    Doesn't recordset.Update use a prepared statement behind the scenes anyway?

  10. Joost R

    Feb 13 Pre-Release Testers, Xojo Pro The Netherlands

    @Tim H Doesn't recordset.Update use a prepared statement behind the scenes anyway?

    They only thing I know is that recordset.update mostly doesn't work smoothly while you haven\t much control.

  11. Jean-Yves P

    Feb 13 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    also when you make mass batch updates, the UPDATE SET is far more faster then a loop on recordset.edit.
    I dont use recordset.edit anymore.

  12. Joost R

    Feb 13 Pre-Release Testers, Xojo Pro The Netherlands

    @Jean-YvesPochez I dont use recordset.edit anymore.

    #metoo

  13. last week

    Tobias B

    Feb 13 Pre-Release Testers, Xojo Pro Bern, Switzerland

    All these replies on avoiding RecordSet.Edit / Update and what to do instead may be good advice, but does not really it the point. To me the only issues known with RecordSet.Edit / Update for PostgreSQLDatabase are Feedback Case #11597 and Feedback Case #35835. The former seems not to apply here, the latter means you have to .Commit the changes after one or more updates, as was already indicated by

    @luciano m mDBS.commit

    But this does not explain, why you experienced running the Update twice does actually update the DB

    @James R Anyways the code below does in fact work, but only if i run it 2 times, it does not work on the first try.

    Can we try to isolate that issue? How do you test if it 'worked'? Do you have a Commit somewhere in the code before the Update?

  14. luciano m

    Feb 13 Pre-Release Testers, Xojo Pro

    Can we try to isolate that issue? How do you test if it 'worked'? Do you have a Commit somewhere in the code before the Update?

    yes

  15. To Answer Tobias Bussmann, no i did not have the .commit anywhere in that branch of code. i am not sure why it would work the second runtime and not the first either. as far as how i was testing it, i had a pg database function that watched for changes in the table, one the first run - nothing was triggered, on the second run - the function returned a change - which you could see in the table.

  16. Ulrich B

    Feb 14 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...
    Edited last week

    I would recommend to build a module or own class that wraps every database command in a db.error check routine and only use these methods.
    I did so recently on an older (working) project and was surprised (and ashamed) to find errors, sometimes simply due to field name typos, while the project was running before too.

    Recordset.Edit and Update are wrappers for a transaction, so if they fail there must be something causing an error. Could be that an earlier transaction is still open. Checking for error before and after each database access will reveal the reason.

  17. Tobias B

    Feb 15 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @Ulrich B I would recommend to build a module or own class that wraps every database command in a db.error check routine and only use these methods.

    sure, this is a good advice, even better if you does subclass the stuff and throw Exceptions on Error.

    @Ulrich B Recordset.Edit and Update are wrappers for a transaction

    Transaction as an abstract concept or as in SQL? The latter is definitely not the case (at least for PostgreSQLDatabase). I'd expect .Edit does an SELECT FOR UPDATE and .Update does UPDATE but that would either require an Transction to be open already or would need to stat one. (I'd rather like explicit control over them, and this is handled inconsitently across the Framework see Feedback Case #14477 which has gotten better with the move from REALSQLDatabase to SQLiteDatabase at least - but I digress...) However what happens is that .Edit does not interact with the DB at all and .Update does an BEGIN TRANSACTION and UPDATE was prepared parametrised statement (For details on the diff, see: Feedback Case #37805). That is what Feedback Case #35835 is about but does not explain the issue here.

    @Ulrich B Could be that an earlier transaction is still open

    that wouldn't be an error but rather issue a WARNING which is not accessible through the Xojo DB interface. (FR: Feedback Case #13158)

    But sure, testing for errors and trying to build a small self-containted demo should be the next steps for @James R

  18. Tobias B

    Feb 15 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @James R To Answer Tobias Bussmann, no i did not have the .commit anywhere in that branch of code

    Do you have a .Commit somewhere later in the code? You need to.

    @James R as far as how i was testing it, i had a pg database function that watched for changes in the table, one the first run - nothing was triggered, on the second run - the function returned a change - which you could see in the table.

    How do you "watch for changes"? With a Trigger on the Table? or with something that periodically polls it's content? After the first run, before a commit, the changed value should be only visible to readers within the same DB Session.

    @James R which you could see in the table.

    How do you 'see' it? In a different DB Management application using a different session to the DB e.g. with psql's \watch?

  19. Ulrich B

    Feb 15 Pre-Release Testers, Xojo Pro Europe (Germany, Berlin) · xo...

    @Tobias B Transaction as an abstract concept or as in SQL?

    I meant it as a concept. The implementation surely depends on the database engine, and I have no clue what goes on under the hood.
    I did not know the FB case you mentioned, so maybe this approach will not give a clue. It surely won’t hurt at least. ;)

  20. Emile S

    Feb 15 Europe (France, Strasbourg)

    @Jean-YvesPochez I dont use recordset.edit anymore.

    I never use it.

or Sign Up to reply!