asp classic - Return SQL Identity to VB Variable on INSERT -

this question has answer here:

• how last inserted id? 13 answers

hi have code below , wish return resultant identity number when execute 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 dbo.xyz" & _ " (field) " & _ " values  (" & date & ")" & _ " select @@identity "  objcnn.execute strsql 

i have added select @@identity return unique id number when in sql. if response.write strsql sql string rather resultant text.

cheers!

edit

for 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 xyz" & _                 " ([date])" & _                  " values  ( " & date & " )" & _                  " set nocount off; select scope_identity() id; "      set rs = objcnn.execute(strsql)     response.write rs("id") 

adodb.connection.execute runs sql statement, not modify string, of course when inspect (or response.write) strsql, unchanged - still contains sql command text.

you can capture recordset created in select statement adodb.recordset.

strsql = "set nocount on; insert dbo.xyz" & _                  " (field) " & _                  " values  (" & date & ");" & _                  " set nocount off; select scope_identity() id; " dim rs set rs = objcnn.execute(strsql) response.write rs("id") 

note should use scope_identity() instead of @@identity very few exceptions (if 1 exists).