Howto Know Identity Field Values after Update
Good Day to everyone.
I make a simple addnew of a record into a SQL Server DB Table from a VB project (using ADO).
For Example if my Table has three fields: ID(with identity), Field1 and Field2
If I do like this:
rsADO.Addnew
rsADO!Field1 = "Value1"
rsADO!Field2 = "Value2"
rsADO.Update
At this point (before the rsADO.Close) how can I know the actual value of rs!ID?
Thanks for yr help
Regards
TdP
# 2 Re: Howto Know Identity Field Values after Update
Actually, this doesn't work. I had the same problem some time ago.
After rsADO.Update, you can get the value of the last identity inserted with the following SQL command:
SELECT IDENT_CURRENT('your table here') as identity
You will need another recordset for this.
This only works after rsADO.Update. Also, you might want to consider concurrent access: if several people use your application at the same time and they all perform the same operation, IDENT_CURRENT() will return the identity of the last row that was inserted (ie, the last rsADO.Update that was executed), regardless of user, session or batch operation involving that table. However, the chance of this actually happening is fairly slim and normally you don't have to worry about it. I have not yet seen this happening with my application.
# 3 Re: Howto Know Identity Field Values after Update
Sorry, my mistake. Using DAO with an Access database, the value of an AutoIncrement column is available immediately after AddNew, but with ADO that is no longer the case. Victor's solution is correct; see http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q233299 for more information.
