Get id of last record inserted

[TimSki]

Hi,

In my asp page I am inserting a record in to the sql server 2005 db as follows...

OpenDataConnect ion()
oConn.BeginTran s
set cm = CreateObject("A DODB.Command")
set cm.ActiveConnec tion = oConn

cm.CommandText ="INSERT INTO t_test( blah blah")

cm.execute
oConn.commitTra ns
set cm = nothing

i would now like to get the id of the record just inserted. i know about @@identity but records are being inserted into this table from multiple sources so i don't think i can gurantee this will be foolproof i.e. i insert a record, then another record is inserted from elsewhere immedialty after, i then run @@identity and it will give me the latter rather than the former...i think ?

Would greatly appreciate any ideas on how to do this. Thanks

[jeffstl]

Hi,

In my asp page I am inserting a record in to the sql server 2005 db as follows...

OpenDataConnect ion()
oConn.BeginTran s
set cm = CreateObject("A DODB.Command")
set cm.ActiveConnec tion = oConn

cm.CommandText ="INSERT INTO t_test( blah blah")

cm.execute
oConn.commitTra ns
set cm = nothing

i would now like to get the id of the record just inserted. i know about @@identity but records are being inserted into this table from multiple sources so i don't think i can gurantee this will be foolproof i.e. i insert a record, then another record is inserted from elsewhere immedialty after, i then run @@identity and it will give me the latter rather than the former...i think ?

Would greatly appreciate any ideas on how to do this. Thanks

The @@identity should be pretty full proof actually. Execution of code happens quicker then you think. If you are getting the ID in execution immediately after the insertion you should always have the correct ID.

This also might even be dependant on an individual users database session, which further makes it solid.

Anyway, I could be wrong but I'm pretty sure @@identity is your best option. Anything else would be even less reliable.

One note of mention however, if you need to get the latest record only and not necessarily the last one inserted by an active user, you can use SQL MAX on the ID column to get the highest valued ID (therefore the latest record at the time of the query)

[code=sql]
SELECT MAX(ClientID) FROM table
[/code]

[Delerna]

You could put the insert query into a stored proc
and have it return @@identity to your page.
That way subsequent calls to the stored proc by other users would have to wait their turn.