Re: Serial columns and ADO

Hello Dmitry,
"Dmitry Samokhin" <sdld(at)mail(dot)ru> wrote:

DS> But there's still another problem. Let our table 't1' be initially
DS> empty and the sequence reset to 1. Consider the following ADO operations:
DS>
DS> rs.AddNew
DS> rs("col2") = "Sample string"
DS> rs.Update
DS> MsgBox rs("col1")
DS>
DS> rs.AddNew
DS> rs("col1") = 2
DS> rs("col2") = "Sample string"
DS> rs.Update
DS> MsgBox rs("col1")
DS>
DS> Look, the second insert operation sets the identity field
DS> explicitly. But the driver still lets 'SELECT currval...' to pass.
DS> As a result, my explicit value '2' is replaced by the currval()=1.
DS> Such distortion should be avoided anyway.

Generally, if you declared the column type as serial, you should not set it
explicitly while inserting records. In your example, rs.Update does not
guarantee changing the cursor position to the record inserted most recently.
(At least, with DAO against MS Access database.) IMHO, it is not the ODBC
driver to blame.
--
Sincerely,
Dmitriy Ivanov