Help with recordsets: How to get the value of a primary key?
-
I have been struggling with several things in Visual Studio .Net when I upgraded from version 6.0 One thing I have had lots of problems with is the elimination of support for DaoRecordsets. I have an Access database that I want to access, and I am struggling to do it through ODBC. However, one problem I have had that I cannot seem to get past is trying to get the value of a primary auto number key out of a newly added record. With DAO there were a couple of ways to do it: rs.SetBookmark(rs.GetLastModifiedBookmark()); updates the appropriate field in the recordset. Or alternatively, using GetFieldValue which forced the field to be read from the database. CRecordset does not have a GetLastModifiedBookmark function, and when I call GetFieldValue it throws an exception. I've tried various other strategies, but can't seem to find anything that works. Any help would be much appreciated.
-
I have been struggling with several things in Visual Studio .Net when I upgraded from version 6.0 One thing I have had lots of problems with is the elimination of support for DaoRecordsets. I have an Access database that I want to access, and I am struggling to do it through ODBC. However, one problem I have had that I cannot seem to get past is trying to get the value of a primary auto number key out of a newly added record. With DAO there were a couple of ways to do it: rs.SetBookmark(rs.GetLastModifiedBookmark()); updates the appropriate field in the recordset. Or alternatively, using GetFieldValue which forced the field to be read from the database. CRecordset does not have a GetLastModifiedBookmark function, and when I call GetFieldValue it throws an exception. I've tried various other strategies, but can't seem to find anything that works. Any help would be much appreciated.
nde_plume wrote: However, one problem I have had that I cannot seem to get past is trying to get the value of a primary auto number key out of a newly added record. There are several methods to do this, however here is just one that you can use in Access. Right after the insert you can run this:
SELECT max([Auto_Number_Field])FROM [YourTable];You might also consider taking the auto number off and finding the maximum value in that column before you do the insert and then insert the maximum value + 1 for your field. HTH Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
-
nde_plume wrote: However, one problem I have had that I cannot seem to get past is trying to get the value of a primary auto number key out of a newly added record. There are several methods to do this, however here is just one that you can use in Access. Right after the insert you can run this:
SELECT max([Auto_Number_Field])FROM [YourTable];You might also consider taking the auto number off and finding the maximum value in that column before you do the insert and then insert the maximum value + 1 for your field. HTH Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
This is dangerous! Use it only inside a transaction. If another user inserts a register between your insert and your select, Ka-boom! My latest articles: Desktop Bob - Instant CP notifications XOR tricks for RAID data protection
-
This is dangerous! Use it only inside a transaction. If another user inserts a register between your insert and your select, Ka-boom! My latest articles: Desktop Bob - Instant CP notifications XOR tricks for RAID data protection
Daniel Turini wrote: This is dangerous! Use it only inside a transaction. Very true, just another option. Transactions are a good thing, however I didn't know the exact scope of the application where this idea may work fine. Thanks for the reminder. :) Nick Parker
The greatest lesson in life is to know that even fools are right sometimes. - Winston Churchill
-
I have been struggling with several things in Visual Studio .Net when I upgraded from version 6.0 One thing I have had lots of problems with is the elimination of support for DaoRecordsets. I have an Access database that I want to access, and I am struggling to do it through ODBC. However, one problem I have had that I cannot seem to get past is trying to get the value of a primary auto number key out of a newly added record. With DAO there were a couple of ways to do it: rs.SetBookmark(rs.GetLastModifiedBookmark()); updates the appropriate field in the recordset. Or alternatively, using GetFieldValue which forced the field to be read from the database. CRecordset does not have a GetLastModifiedBookmark function, and when I call GetFieldValue it throws an exception. I've tried various other strategies, but can't seem to find anything that works. Any help would be much appreciated.
If you're using Jet 4 / Access 2000, you can use
SELECT @@IDENTITYto return the value.
