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!


Related Articles

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

How to Insert Values into an Identity Column in SQL Server (6 August 2007)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Understanding Identity Columns (9 March 2002)

Identity and Primary Keys (28 February 2001)

Alternatives to @@IDENTITY in SQL Server 2000 (19 September 2000)

Uniqueidentifier vs. IDENTITY (12 September 2000)

Other Recent Forum Posts

T-sql - we created Message from app1 and trying to disable from app2 (2h)

SQL select Top 10 records for unique combination of two columns (15h)

SSRS Report Sorting with Grouping Issue (1d)

ORA-01476: divisor is equal to zero (1d)

Create new columns based on min and max values of a record with multiple rows (1d)

Memory Required for Reporting Services 2022 (1d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -