How to get identity field value after recordset update

I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.

This is what I have:

rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close

Forgive me if this has already been posted, but I couldn't find it.

[edit to add:]

rsRec is an ADODB.Recordset in case that wasn't clear.Look at @.@.IDENTITY and SCOPE_IDENTITY in BOL.|||1 intItemId set identity for replication
2 use keyset recordset type|||I appreciate the quick replies.

I am using @.@.Identity and it works well enough for my purposes.

[edit] I found the BOL. Never even looked at it before. Thanks for pointing it out

Thanks again all.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.

Agree. But it's good to know what differs those cases. Let the user choose what he needs. Give him fishing rod, not fish ;) .|||Due to time constraints I went with @.@.Identity. The database hits aren't very common so I will assume a certain small probability that there will be an error from time to time.

That being said, I would prefer Scope_Identity for 100% accuracy. I am continuing to work with SCOPE_IDENTITY when I can to try to make it work for me. As of this morning I got it to supply the correct ID using enterprise manager to test. I'll eventually get it to work in my code.

Thank you guys for helping me solve this problem.|||@.@.identity may acquire a value of anything else only when the target table contains a trigger that performs an insert into another table with identity field. if this is the case then scope_identity() needs to be used.

on another thought, you should put dml statements into a stored procedure and call it from your front end.|||I inherited this app. I would love to convert it to using stored procedures. I just need to get this section done so I can have a completed app, and a happy boss. Then I can convert as I go to make it all easier to work with.

Grrrr... I can't seem to get SCOPE_IDENTITY() to happen on the page.

I have:

set cn = Server.CreateObject("ADODB.Connection")
set rsRec = Server.CreateObject("ADODB.Recordset")
set rsIdent = Server.CreateObject("ADODB.Recordset")
cn.Open dbConnection

rsRec.Open sSQL,cn, 3 ,3
rsRec.AddNew
rsRec("ReqID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
rsRec.Close

sSQL = "SELECT SCOPE_IDENTITY() as Ident"
rsIdent.Open sSQL,cn, 3 ,3
intItemId = rsIdent("Ident")
rsIdent.Close

I have no idea why this doesn't work. I thought as long as I keep the connection(cn) open then this would work. Guess I'm wrong? Do I need to use the same recordset?|||sSQL="insert tbl (...) values (...) select ident=scope_identity()"
set rsRec=cn.execute(sSQL)|||I have simplified the statements that are actually in the code. Although that suggestion would likely make it work(as it works when I test it in the Query Analyzer), there are quite a few conditions that would affect the SQL statement and at this point I don't have the time to switch it to that style.

Its' done for the time being. I pushed it out for beta.

I'm only sorry I couldn't get it all to work fast enough, but that's what updates are for.

Thanks again for the help and pointing me in the right direction.