Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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 & 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
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

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