@@IDENTITY and ASP problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

@@IDENTITY and ASP problem

Author

Topic

globemast
Starting Member

32 Posts

Posted - 2003-08-24 : 15:50:49

Hello i have an ASP page on my site and i want to insert some values to my table called Posts on my SQL Server.

After the insert i want to retrieve the identity of the table using @@IDENTITY into an ASP variable. However a get an ASP error saying that the value in the recordset can't be found.

Here is my code:
strSQL="Set nocount on "
strSQL=strSQL + " INSERT INTO dbo.Posts (CreatedBy, CreateDate, Message) VALUES ('"& AddPost__varBy &"', '" & AddPost__varDate &"' , '"& AddPost__varMessage &"')"
strSQL=strSQL + " select @@IDENTITY as IdentityInsert"
strSQL=strSQL + " set nocount off"
set AddPost = Server.CreateObject("ADODB.Command")
AddPost.ActiveConnection = MM_Panther_STRING
AddPost.CommandText = strSQL
AddPost.CommandType = 1
AddPost.CommandTimeout = 0
AddPost.Prepared = true
AddPost.Execute()
varNewID=AddPost("IdentityInsert") 'this line causes the error.

The error that appears is :" Item cannot be found in the collection corresponding to the requested name or ordinal"

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-08-24 : 17:37:18

I hope not to seem like an idiot here, because I'm not sure what version of ADO you're using and I don't know ADO.NET, but it looks like what's happening is that you are trying to access the IdentityInsert value as if it was a parameter of the command, which it isn't since you didn't declare it as an output parameter, and you are using a SELECT @@Identity instead of RETURN @@Identity which would make the value a parameter. If you are using a SELECT in a command, the command will return the results of the SELECT in a recordset, which you don't have.

Sarah Berger MCSD

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-24 : 18:41:56

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")

Here's a link to an article on returning @@IDENTITY in ASP.

Sam

chadmat
The Chadinator

1974 Posts

Posted - 2003-08-26 : 00:56:02

You really should use SCOPE_IDENTITY.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources