Quote:
Originally Posted by TimSki
Hi,
In my asp page I am inserting a record in to the sql server 2005 db as follows...
OpenDataConnection()
oConn.BeginTrans
set cm = CreateObject("ADODB.Command")
set cm.ActiveConnection = oConn
cm.CommandText ="INSERT INTO t_test( blah blah")
cm.execute
oConn.commitTrans
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)
-
SELECT MAX(ClientID) FROM table
-
