|
Hello,
Today i thought i'd share an easy way to return an identity after doing an insert with classic ASP and the ADODB object.
Basically we open a connection and perform actions on the open connection, then close when we are finished. By keeping the connection open we are re-using the same session. Following is a code listing.
Dim cnn, rsPK, sSql, iTablePK
' Open a connection:
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.Open ConnString
' Perform the insert into our table:
sSql = "INSERT INTO tbOrderDetails (FKOrderID, TourCode) values (1, 'Code')"
cnn.Execute sSql, , adCmdText + adExecuteNoRecords
' Retrieve the identity:
set rsPK = Server.CreateObject("ADODB.Recordset")
rsPK.Open "SELECT @@Identity", cnn, 0, 1, 1
' We now have the new record PK in iTablePK!
if (not rsPK.EOF) then
iTablePK = rsPK(0)
end if
' Clean up:
cnn.close
set cnn = nothing
So, we open a connection and while we keep it open we perform the insert into the table with the identity field. We then select the new table field using @@identity. Keep in mind @@identity is GLOBAL to the connection, meaning it will return the latest identity regardless of where the insert occured!! (i.e. table trigger performing an insert). To be safer you could use the SCOPE_IDENTITY() method, which is LOCAL to the table.
Ok, i hope this helps.
Cheers,
James
|