devxlogo

Obtaining @@Identity with VBScript and ADO

Obtaining @@Identity with VBScript and ADO

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.

See also  Redefining Energy Management: Advanced Solutions for Modern Buildings
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist