Grabbing identity key from INSERT command
Hey people,
I am looking for tips or suggestions for a speedy way to Insert a record and then return the unique id that was created (the PK autoincrement)
Right now i do it like so
Code:
sSQLReg = "INSERT INTO SWRegister values (" & iclubnum & ",'" & ifirstname & "','n/a','" & ilastname & "')" 'new or existing user 1 new 0 existing
Set objRSReg = Server.CreateObject("ADODB.RecordSet")
objRSReg.Open sSQLReg,objConn
sSQLID = "SELECT UserID FROm SWRegister where ClubNumber =" & iclubnum & " and FirstName=" & ifirstname & ")"
Set objRSID = Server.CreateObject("ADODB.RecordSet")
objRSID.Open sSQLID,objConn
idnum = objRSID("UserID")
Inserting thedata and then doing a select statement to grab the key i just created.
Thing is I know there is a better way, as I do it in .NET using stored procedures that return an @@identity to a datareader etc.
I don't have access to the DB to use a stored procedure so stuck with calls within asp.
It works fine as is but once the app goes live there could be 100's of users trying to insert at once (hopefully) and i think my method may cause a bottleneck or errors
Thanks in advance everyone!!