Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Active Server Pages (ASP)
Expertise: Beginner
May 26, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Obtaining @@Identity with VBScript and ADO

Using VBScript, how can I use the @@Identity SQL Statement to set the keyfield of a record just entered to a variable?


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 & vbCrLf
strSQL = 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, lngIdentityValue
set 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.

DevX Pro
Thanks for your registration, follow us on our social networks to keep up-to-date