[RESOLVED] Getting the ID from an Inserted record created by connection.execute

[Anthonynz]

Greetings

I am creating a new record in an Auto Increment sql table (by copying an existing record) and I want to retrieve the new id created.
So far I have got the following, which creates the record nicely, but I dont know how to retrieve the New id

Private Sub CopyRecord(OriginalTable As String, ByRef ID As Integer)
myconnection.Execute("SELECT * INTO TempTable FROM " & OriginalTable & " WHERE ID = " & ID)
myconnection.Execute("ALTER TABLE TempTable DROP COLUMN ID")
myconnection.Execute("INSERT INTO " & OriginalTable & " OUTPUT INSERTED.ID as ID SELECT * FROM TempTable")
myconnection.Execute("DROP TABLE TempTable")
End Sub

How can I get the subroutine to return the new Id in the subroutines ID parameter
Thank you for your help

(Note: myconnection is an ADODB.Connection and has been established earlier by the following
ConnectionString = "Driver={SQL Server};Server=" & strSQLPath & ";Database=TESTING;Trusted_Connection=yes"
myconnection.Open(ConnectionString)

[jmcilhinney]

I would question why you're using ADO in the first place, rather than using ADO.NET. Regardless, you can execute a query to get the value of SCOPE_IDENTITY within the same scope or @@IDENTITY in any scope.

[Anthonynz]

Thank you for your reply. As I only want the ID in this case, SCOPE_IDENTITY would be fine. I believe that I add that to the end of my SQL Statement, so that would be like the following?
myconnection.Execute("INSERT INTO " & OriginalTable & " SELECT * FROM TempTable; Select Scope_Identity()")

But i dont understand is how to return the resultant value in my variable ID ?

Googling other sites I see some suggestions such as ID = CInt(myCommand.ExecuteScalar()), but I dont have the ExecuteScaler option on myconnection

[jmcilhinney]

Googling other sites I see some suggestions such as ID = CInt(myCommand.ExecuteScalar()), but I dont have the ExecuteScaler option on myconnection

That's because ExecuteScalar is a member of an ADO.NET command object. Again, why aren't you using ADO.NET in the first place? If you're determined to use ADO then you would get the ID from that query the same way you would usually get a value from a query. If you don't know how to do that then you certainly can't use existing knowledge as a reason for using ADO so, yet again, why not use ADO.NET?

[Anthonynz]

I am modifying an existing program and it uses ADO....best not to change any more than necessary

After more googling I found the answer.
For anyone else who has a similar problem, simply save the output into another recordset as follows.

Private Sub CopyRecord(OriginalTable As String, ByRef ID As Integer)
myconnection.Execute("SELECT * INTO TempTable FROM " & OriginalTable & " WHERE ID = " & Val(ID))
myconnection.Execute("ALTER TABLE TempTable DROP COLUMN ID")
rsTemp = myconnection.Execute("INSERT INTO " & OriginalTable & " OUTPUT INSERTED.* SELECT * FROM TempTable")
ID = CStr(rsTemp.Fields("ID").Value)
myconnection.Execute("DROP TABLE TempTable")
End Sub

(Where rsTemp has been previously Dimensioned as an ADODB.Recordset)