Getting SCOPE_IDENTITY() from SQL SERVER
Hi,
In a Business Operation i have the following code.
I need the identity of the latest row inserted in a table in MSSQL.
i Have the following code:
---------------------------------------------------------
set insertmaster= "INSERT INTO dbo.dataMasterArchive(deviceToPatientLogID) values (?)"
set tSC = ..Adapter.ExecuteUpdate(.nrows1,insertmaster,pRequest.deviceToPatientLogID)
$$$ThrowOnError(tSC)
set sql ="SELECT SCOPE_IDENTITY()"
set tSC = ..Adapter.ExecuteQuery(.tResult,sql)
$$$ThrowOnError(tSC)
set maxID = tResult.Get(1)
------------------------------------------------------------
the result of maxID after the above is NULL.
what am i doing wrong?.
Thanks
Working with Yakov on this, we saw that for SCOPE_IDENTITY() to work on the SQL Serve side it needs to be in the same "scope" of the INSERT (for example in the same Stored Procedure), see here for reference.
So what Yakov ended up doing was encapsulating the INSERT and SELECT SCOPE_IDENTITY() into a Stored Procedure which returns the newly inserted Row ID, and call the SP via the Adapter, thus inserting the new record and getting back the new ID.
Thanks