0

Below I Insert a value into table "test" and retrieve the @@identity of the new row successfully. Then I insert a value into table test2 (successfully), but the @@identity retrieved is "0" always. What's going wrong with fetching the 2nd @@identity? How can I fix this?

Sub GoDo()

Dim testIdentity As Integer
Dim test2Indentity As Integer

Dim name As String
Dim concept As String

name = Cells(1, 1)
concept = Cells(1, 2)


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset

cn.ConnectionString = "Provider=SQLOLEDB;Data Source=MARS;Initial Catalog=automation;Trusted_connection=yes;"
cn.Open

cn.Execute ("INSERT INTO test(data) VALUES ('" & name & "')")
rs.Open "SELECT @@identity AS NewID", cn
testIdentity = rs.Fields("NewID")
rs.Close

cn.Execute ("INSERT INTO test2(data) VALUES ('" & concept & "')")
rs2.Open "SELECT @@identity AS NewID", cn
testIdentity = rs2.Fields("NewID")
rs2.Close

cn.Execute ("INSERT INTO test3(test_id, test2_id) VALUES ('" & testIdentity & "','" & test2Indentity & "')")

cn.Close

End Sub

Result e.g.: INSERT INTO Test3 (test_id, test2_id) VALUES ('18','0')

user743094
  • 329
  • 1
  • 6
  • 19
  • 1
    shouldn't it be **test2Identity** = rs2.Fields("NewID") – john Feb 11 '13 at 00:27
  • Please meet [little Bobby Tables](http://stackoverflow.com/q/332365/11683). – GSerg Feb 11 '13 at 00:31
  • John, I should be flogged. Thanks! GSerg what am I doing wrong with the naming? – user743094 Feb 11 '13 at 00:33
  • 2
    I would also recommend to use **`SCOPE_IDENTITY()`** instead of anything else to grab the newly inserted identity value. [See this blog post for an explanation as to WHY](http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/) – marc_s Feb 11 '13 at 05:47

1 Answers1

1

How about:

sql = "INSERT INTO test(data) VALUES ('" & name & "');" & _
      "SELECT NewID = SCOPE_IDENTITY();"
SET rs = cn.Execute (sql)
testIdentity = rs.Fields("NewID")

Now that said, you should at the very least be using parameterized statements, or better yet, stored procedures. Also I'm not sure why you're putting single quotes around your numeric values when you insert them. Are you storing numbers as strings? Why? If not, why are you treating them like strings?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490