Returning @@IDENTITY back to an ASP Page

By Bill Graziano on 18 August 2000 | Tags: Identity

Gareth writes "Hi, I'd be so relieved if you could sort this out . . . I'm trying to get the value of @@IDENTITY from SQL Server into an ASP variable (via a recordset) - however I get an ASP error reporting that the value in the recordset (selected as 'ident') can't be found. What's going on . . . Thanks!"

Here's the offending code...

dim idnum

SQL = "INSERT INTO users(u_name,u_password) values ('b','b') SELECT @@IDENTITY AS 'ident';"
cnn.Open strConnection 'open db connection
Set rs = cnn.Execute(SQL)
idnum = rs("ident") 'this causes the error
cnn.Close

and the error...

"Item cannot be found in the collection corresponding to the requested name or ordinal"

This is a great question! It took me a while to figure out what's going on but here's the scoop. First off, one of things I always recommend is running offending code in the SQL Query Analyzer to try and figure out what's going on. When you run the above SQL statement, SQL Server is returning something like this:

(1 row(s) affected)

ident                           
---------------------------------------- 
131

(1 row(s) affected)

The first line (1 row affected) is messing up your result set. That is the value in your rs object. What you need to do is get rid of those comments telling you how many rows were affected. Fortunately SQL Server gives you a way to do this. You can use the SET NOCOUNT statement. SET NOCOUNT ON will prevent SQL Server from telling you how many rows each statement affected. SET NOCOUNT OFF will return SQL Server back to it's default setting. The variable @@ROWCOUNT will always contain the number of rows affected by the previous statement regardless of the setting of NOCOUNT.

Getting back to your question . . . Here's some sample code I wrote that puts new topics into my Topics table. You should be able to have a look at this and modify your code appropriately.

strSQL = "Set Nocount on "
strSQL = strSQL + " Insert Topics (TopicName, SortOrder) VALUES ('X', -1) "
strSQL = strSQL + " select IdentityInsert=@@identity"
strSQL = strSQL + " set nocount off"

Set objRS = objConn.Execute(strSQL)
    
Response.Write objRS("IdentityInsert")

You can see where I turn on NOCOUNT and then turn it back off. Kind of cumbersome but it works. Thanks for the great question Gareth!