sql server: is there a way to get last record inserted without stored procedure?

Question

is there a way to get last record inserted without stored procedure in a multi-user environment?

i am connected to sql server through vba. i insert some records and call:

SELECT SCOPE_IDENTITY()

this is not working. it is returning null every time.

here is the entire code:

Dim sqlstring1 As String
sqlstring1 = "delete from batchinfo where datapath='" & dpath & "' and analystname='" & aname & "' and reportname='" & rname & "' and batchstate='" & bstate & "'"



Dim rowid_batchinfo As String
rs.Filter = "datapath='" + dpath + "' and analystname='" + aname + "' and reportname='" + rname + "' and batchstate='" + bstate + "'"
If Not rs.EOF Then
    rowid_batchinfo = rs.Fields("rowid")
    cn.Execute "delete from batchinfo where rowid=" + rowid_batchinfo
    cn.Execute "delete from calibration where rowid='" + rowid_batchinfo + "'"
    cn.Execute "delete from qvalues where rowid='" + rowid_batchinfo + "'"
End If


With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("datapath") = dpath
    .Fields("analysistime") = atime
    .Fields("reporttime") = rtime
    .Fields("lastcalib") = lcalib
    .Fields("analystname") = aname
    .Fields("reportname") = rname
    .Fields("batchstate") = bstate
    .Fields("instrument") = instrument
    .Update ' stores the new record


End With



Set rs = cn.Execute("SELECT SCOPE_IDENTITY()", , adCmdText)

Set rs = rs.NextRecordset

the question is how do i get the last record inserted in a multi-user environment?

Answer 1

From the docs:

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

This means that you should send SCOPE_IDENTITY in the same batch (i. e. SQL call) with your insert query.

This is not possible with methods like AddNew, so you should use an explicit INSERT statement followed by SELECT SCOPE_IDENTITY.

Something like this:

SQL = "INSERT INTO mytable (datapath, analysistype, …) VALUES (@datapath, @analysistime, …) SELECT SCOPE_IDENTITY()"
Set cmd = Server.CreateOject("ADODB.Command")
With cmd
      .CommandType = adCmdText
      .CommandText = SQL
      .Parameters.Append .CreateParameter("@datapath", adVarWChar, adParamInput,, dpath)
      …
End With
Set rs = cmd.Execute
Set rs = rs.NextRecordset

Answer 2

This is what I use in my sp:

ALTER PROCEDURE [dbo].[usp_app_execSQL_insert] (@SQL AS text)
AS

set nocount on

exec(@sql + ' select scope_identity()')

--select @@identity  --works, but returns value from session scope, not insert scope.
--return @@identity  --doesn't work
--exec(@sql) return/select scope_identity()     -- doesn't work (insert outside scope)