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