Returning @@IDENTITY back to an ASP Page

By Bill Graziano on 18 August 2000 | 4 Comments | 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!

Discuss this article: 4 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

DUPLICATE VALUE WHEN JOINING (0 Replies)

Please help me to write down a query (0 Replies)

Backing Up (4 Replies)

SQL Server Erorr Log (2 Replies)

more results expected (3 Replies)

Instance Creation (0 Replies)

Multiple Joins (19 Replies)

File Format (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -