Question:
Using VBScript, how can I use the @@Identity SQL Statement to set the keyfield of a record just entered to a variable?
Answer:
You can issue multiple SQL statements within a single ADO Execute statement. Just separate the statements with a carriage return – vbCrLf.
To get the @@Identity SQL Statement back for a just inserted row, you need to issue a ‘SELECT @@Identity’ statement within your original insert statement itself. For example, say, you were inserting a row in a table called Test. Your SQL Statement would then look like this:
"INSERT INTO Test (field1, field2) Values (value1, value2)"
To then obtain the Identity field (say field0) value of the just inserted row, you would need to use the following SQL Statement:
"SELECT @@Identity"
You can either write a stored procedure in SQL Server that concatenates the above two SQL statements and returns the Identity field value, or you can concatenate them within a single ADO query itself.
strSQL = "INSERT INTO Test (field1, field2) Values (value1, value2)"strSQL = strSQL & vbCrLfstrSQL = strSQL & "SELECT @@Identity"strSQL = strSQL & vbCrLf
When you execute that SQL statement, you will obtain a single row recordset back that contains the Identity field value. You can access that value by using the Recordset(0) notation.
Dim objRS, lngIdentityValueset objRS = Connection.Execute (strSQL)lngIdentityValue = objRS(0)set objRS = Nothing
Note that the above works with SQL Server 7. I have not tested it with other databases.
