Return SQL Identity to VB Variable on INSERT

Question

Hi I have some code below and I wish to return the resultant Identity Number when I execute the SQL Statement.

set objCnn = Server.CreateObject("ADODB.connection")

CurrMachine = "my-test-box"

ObjCnn.Open("Provider=SQLOLEDB;Persist Security Info=False;User ID=Test;password=Test123;Language=British;Initial Catalog=TestDB;Data Source=" & CurrMachine)

strSQL = "INSERT INTO dbo.xyz" & _ " (field) " & _ " VALUES  (" & date & ")" & _ " SELECT @@Identity "

objCnn.Execute strSQL

I have added SELECT @@Identity which will return the unique ID number when I do this in SQL. If I response.write strSQL I get the SQL String rather than the resultant text.

Cheers!

EDIT

For you Richard

    set objCnn = Server.CreateObject("ADODB.connection")

    CurrMachine = "my-test-srv"

    ObjCnn.Open("Provider=SQLOLEDB;Persist Security Info=False;User ID=Test;password=Test123;Language=British;Initial Catalog=Test;Data Source=" & CurrMachine)

        strSQL = "INSERT INTO xyz" & _
                " ([date])" & _
                 " VALUES  ( " & date & " )" & _
                 " SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() ID; "

    Set rs = objCnn.Execute(strSQL)
    response.write rs("ID")

Answer 1

ADODB.Connection.Execute runs a SQL statement, but does not modify the string, so of course when you inspect (or response.write) strSQL, it is unchanged - still contains the SQL command text.

You can capture the recordset created in the SELECT statement into a ADODB.recordset.

strSQL = "SET NOCOUNT ON; INSERT INTO dbo.xyz" & _
                 " (field) " & _
                 " VALUES  (" & date & ");" & _
                 " SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() ID; "
Dim rs
Set rs = objCnn.Execute(strSQL)
response.write rs("ID")

Note that you should use SCOPE_IDENTITY() instead of @@IDENTITY with very very few exceptions (if one even exists).