title: Return SQL Identity to VB Variable on INSERT
body: <p>Hi I have some code below and I wish to return the resultant Identity Number when I execute the SQL Statement.</p>
  
  <pre><code>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=" &amp; CurrMachine)
  
  strSQL = "INSERT INTO dbo.xyz" &amp; _ " (field) " &amp; _ " VALUES  (" &amp; date &amp; ")" &amp; _ " SELECT @@Identity "
  
  objCnn.Execute strSQL
  </code></pre>
  
  <p>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.</p>
  
  <p>Cheers!</p>
  
  <h2>EDIT</h2>
  
  <p>For you Richard</p>
  
  <pre><code>    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=" &amp; CurrMachine)
  
          strSQL = "INSERT INTO xyz" &amp; _
                  " ([date])" &amp; _
                   " VALUES  ( " &amp; date &amp; " )" &amp; _
                   " SET NOCOUNT OFF; SELECT SCOPE_IDENTITY() ID; "
  
      Set rs = objCnn.Execute(strSQL)
      response.write rs("ID")
  </code></pre>