Recordset field not readable after update (1 Viewer)

[amorosik]

Why the row InserisciRecordPrincipale = rs("ID_FERICEVUTE") not recognize rs("ID_FERICEVUTE")?
When RS.UPDATE is invoked, rs("ID_FERICEVUTE") lost his value
Going step by step, you can see that the rs("id_fericevute") is correctly readable up to the rs.update line
Without Close, or Move, why the field is not readable?

Private Function InserisciRecordPrincipale(db As DAO.Database, NOME_FILE As String, hash As String, b64 As Boolean, nom_fil_ser_ftp As String) As Long
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("FERICEVUTE", dbOpenDynaset)
   
    rs.AddNew
    rs("ID_FERICEVUTE") = NUOVO_VALORE_DA_GENERATORE_GLOBALE()
    Call dati_testata_record(rs, "INSERIMENTO")
   
    rs("ID_DOCUMENTITESTATE") = 0
    rs("ID_MOVIMENTICONTABILITESTATE") = 0
    rs("NOME_FILE_COMPLETO") = NOME_FILE
    rs("NOME_FILE") = solo_nome_file(NOME_FILE)
    rs("HASH_MD5_DEL_FILE") = FileToMD5(NOME_FILE, b64)
    rs("HASH_SHA256_DEL_FILE") = hash
    rs("HASH_SHA512_DEL_FILE") = FileToSHA512(NOME_FILE, b64)
    rs("DATA_RICEZIONE") = data_ora_file(NOME_FILE, "CREAZIONE")
    rs("DATA_IMPORTAZIONE") = Format$(Now, "dd/mm/yyyy hh:mm:ss")
    rs("FLAG_SELEZIONA") = 0
    rs("FLAG_CARICATO") = 0
    rs("FLAG_CONTABILIZZATO") = 0
    rs("NOME_FILE_SU_FTP_SERVER") = nom_fil_ser_ftp
    rs("FLAG_VENDITA") = IIf(vendita_acquisto = "VENDITA", -1, 0)
    rs("NUMERO_FILE_ALLEGATI") = 0
    rs("ANNOTAZIONI") = ""
    rs.Update
   
    InserisciRecordPrincipale = rs("ID_FERICEVUTE")
    rs.Close
End Function

 

[Minty]

When you issue rs.update the recordset doesn't stay on that record.

Assuming your NUOVO_VALORE_DA_GENERATORE_GLOBALE() function retrieves the next ID simply store that in a variable at the beginning of the process.

 

[amorosik]

By debugging step by step, up to the line rs("ANNOTATIONS")="" the value of rs("ID_FERICEVUTE") is valid
After running rs.update, the value of rs("ID_FERICEVUTE") is no longer valid
If it were something dependent on the previous routines, you would have seen it before row rs("ANNOTATIONS")=""

 

[Minty]

By debugging step by step, up to the line rs("ANNOTATIONS")="" the value of rs("ID_FERICEVUTE") is valid
After running rs.update, the value of rs("ID_FERICEVUTE") is no longer valid
If it were something dependent on the previous routines, you would have seen it before row rs("ANNOTATIONS")=""

I think you misunderstood me. I know it will be valid until you issue .Update
Store the value at the start of the insert:

Dim NewID as Long

  rs.AddNew

    NewID = NUOVO_VALORE_DA_GENERATORE_GLOBALE()    ''' This will store the new value

    rs("ID_FERICEVUTE") = NewID
    Call dati_testata_record(rs, "INSERIMENTO")
    ....
    rs.Update
  
    InserisciRecordPrincipale = NewID
 rs.Close

 

[amorosik]

Yes, of course, if I move the

InsertPrincipalRecord = NewID

line before the rs.UPDATE, everything works fine
But I was curious to understand why the value of rs("ID_FERICEVUTE") is no longer available immediately after the update.

 

[xavier.batlle]

Yes, of course, if I move the

InsertPrincipalRecord = NewID

line before the rs.UPDATE, everything works fine
But I was curious to understand why the value of rs("ID_FERICEVUTE") is no longer available immediately after the update.

When you call .Update on a DAO Recordset, the current record’s buffer is written to the database and may be repositioned or refreshed — depending on the recordset type and underlying query.

This means that the record you were editing might no longer be current, or DAO needs to requery to see the updated data.

Example:

rs.Edit
rs!Field1 = "New Value"
rs.Update
rs.Bookmark = rs.LastModified ' Reposition to the updated record
Debug.Print rs!Field1

 

[arnelgp]

concur, you need to go to the Bookmark (Lastmodified record).

...
...
    rs.Update
    rs,BookMark = rs.LastModified 
    InserisciRecordPrincipale = rs("ID_FERICEVUTE")

 

[MarkK]

When adding a row to recordset, grab the new ID after an edit, but before the update...

    Dim newRowID As Long
    With CurrentDb.OpenRecordset("SELECT RowID, SomeField FROM Table1")
        .AddNew
        !SomeField = "Updated"
        newRowID = !RowID
        .Update
    End With

 

[Minty]

When adding a row to recordset, grab the new ID after an edit, but before the update...

    Dim newRowID As Long
    With CurrentDb.OpenRecordset("SELECT RowID, SomeField FROM Table1")
        .AddNew
        !SomeField = "Updated"
        newRowID = !RowID
        .Update
    End With

Just as a warning: That only works with an Access back end. The ID isn't committed with a SQL Server BE until the update is carried out.

 

[cheekybuddha]

rs("DATA_IMPORTAZIONE") = Format$(Now, "dd/mm/yyyy hh:mm:ss")

Why do you store the import date in a text field?

 

[cheekybuddha]

    rs("HASH_MD5_DEL_FILE") = FileToMD5(NOME_FILE, b64)
    rs("HASH_SHA256_DEL_FILE") = hash
    rs("HASH_SHA512_DEL_FILE") = FileToSHA512(NOME_FILE, b64)

What is the purpose of having 3 different hashes for the same thing? How often do you need to compare against the hash[es]?

 

[cheekybuddha]

Private Function InserisciRecordPrincipale(db As DAO.Database, NOME_FILE As String, hash As String, b64 As Boolean, nom_fil_ser_ftp As String) As Long

  Dim lRet As Long, strSQL As String

  strSQL = "INSERT INTO FERICEVUTE (" & _
              "ID_DOCUMENTITESTATE," & _
              "ID_MOVIMENTICONTABILITESTATE" & _
              "NOME_FILE_COMPLETO," & _
              "NOME_FILE," & _
              "HASH_MD5_DEL_FILE," & _
              "HASH_SHA256_DEL_FILE," & _
              "HASH_SHA512_DEL_FILE," & _
              "DATA_RICEZIONE," & _
              "DATA_IMPORTAZIONE," & _
              "FLAG_SELEZIONA," & _
              "FLAG_CARICATO," & _
              "FLAG_CONTABILIZZATO," & _
              "NOME_FILE_SU_FTP_SERVER," & _
              "FLAG_VENDITA," & _
              "NUMERO_FILE_ALLEGATI," & _
              "ANNOTAZIONI," & _
           ") VALUES ("
  strSQL = strSQL & "(" & _
              "0,0," & _
              "'" & Replace(NOME_FILE, "'", "''") & "'," & _
              "'" & solo_nome_file(NOME_FILE) & "'," & _
              "'" & FileToMD5(NOME_FILE, b64) & "'," & _
              "'" & hash & "'," & _
              "'" & FileToSHA512(NOME_FILE, b64) & "'," & _
              "'" & data_ora_file(NOME_FILE, "CREAZIONE") & "'," & _
              "'" & Format$(Now, "dd/mm/yyyy hh:mm:ss") & "'," & _
              "0,0,0," & _
              "'" & Replace(nom_fil_ser_ftp, "'", "''") & "'," & _
              IIf(vendita_acquisto = "VENDITA", -1, 0) & _
              "0, ''" & _
            ");"
  With CurrentDb
    .Execute strSQL, dbFailOnError
    If .RecordsAffected > 0 Then
      With .OpenRecordset("SELECT @@IDENTITY")
        lRet = .Fields(0)
        .Close
      End With
    End If
  End With 
  InserisciRecordPrincipale = lRet
 
End Function

(Nb aircode!)

 

[amorosik]

... may be repositioned or refreshed ....

I haven't found this in the official documentation yet

 

[amorosik]

Why do you store the import date in a text field?

rs("DATA_IMPORTAZIONE") is a timestamp

 

[amorosik]

What is the purpose of having 3 different hashes for the same thing? How often do you need to compare against the hash[es]?

This is what the technical specifications said
I don't know how to use the three values
The code I write only uses sha256

 

[amorosik]

When adding a row to recordset, grab the new ID after an edit, but before the update...

    Dim newRowID As Long
    With CurrentDb.OpenRecordset("SELECT RowID, SomeField FROM Table1")
        .AddNew
        !SomeField = "Updated"
        newRowID = !RowID
        .Update
    End With

Yes, I know that's how it works
I don't understand why it doesn't work even after the update
I've never found any justification for this in official Microsoft documentation.

 

[cheekybuddha]

rs("DATA_IMPORTAZIONE") is a timestamp

Is your backend in a non-Access system?

 

[amorosik]

Just as a warning: That only works with an Access back end. The ID isn't committed with a SQL Server BE until the update is carried out.

BE is Firebird Sql

 

[cheekybuddha]

If you wanted to test the version I posted you may need to adjust the timestamp value passed to the SQL statement:

' ...
              "'" & data_ora_file(NOME_FILE, "CREAZIONE") & "'," & _
              Format$(Now, "\#yyyy\-mm\-dd hh:mm:ss\#") & "," & _
'...

The Firebird ODBC driver should take care of the conversion to Firebird date/timestamp format.