Grabbing identity key from INSERT command

[Halon]

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

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!!

[andreys]

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

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

[VBGuy]

Here is a small snippet of using @@IDENTITY without a stored proc.

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.