Grabbing identity key from INSERT command

Printable View

Apr 4th, 2003, 04:30 PM
Halon

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!!
Apr 4th, 2003, 04:43 PM
andreys

You may need use an AddNew instead. It's easy way to do it.

Code:

Dim rs, conn conn=Your_Connection_String set rs=server.createobject("ADODB.Recordset") rs.cursorlocation=3 rs.open "SWRegister", conn, 3, 3 rs.AddNew idnum=rs("UserID") rs("FieldName1")=Variable1 ... ... ... rs.Update rs.close set rs=nothing
Apr 6th, 2003, 10:14 AM
VBGuy
Here is a small snippet of using @@IDENTITY without a stored proc.
VB Code:
```
sSQL = "SET NOCOUNT ON INSERT INTO  tblAuthors (EntryDate) VALUES ('" & Date & "') SELECT @@IDENTITY"
rsTemp.Open sSQL, cn, adOpenStatic, adLockReadOnly
mlngRSKey = rsTemp.Fields(0)
mlngAu_ID = mlngRSKey
rsTemp.Close
```
What we did (not the most efficiant but worked flawlessly) was to open the connection, add an empty record and get the key, do an update to populate the rest of the record then close the connection. I no longer remember what the project spec was that required that type of situation anymore. In the end the code worked very well. The key was the SET NOCOUNT ON. We could have added the entire record in the update and not done an update. The end result would have been the same.