• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

cannot get a value from scope_identity after insert

In ASp i am doing this:

set objrs=objconn.execute("insert into cart (title) values ('something');select SCOPE_IDENTITY() as id;")

When I check objrs("ID") it returns blank.  Nothing.  This works fine access but not in SQL 2000.  I find that it seems to have something to do with it being in the second line of the SQL.  if I replace the scope_identity() with max(cartid) i get the same thing.  So the second line is not returning a value.

Running this in the query analyzer DOES return the value as expected.

Any ideas?
0
jglazer63
Asked:
jglazer63
  • 2
  • 2
  • 2
1 Solution
 
saoirse1916Commented:
Have you tried running this query as a stored procedure?  I seem to remember running into this a while back and setting up an SP fixed it.
0
 
jglazer63Author Commented:
I do not want to do this as a stored procedure.  I want to do this entirely with the ADO execute.  It needs to be very generic.
0
 
BrandonGalderisiCommented:
Or perhaps the "1 rows effected" by the insert (or subsequent triggers) is messing with it.


set objrs=objconn.execute("set nocount on; insert into cart (title) values ('something');select SCOPE_IDENTITY() as id;")
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
BrandonGalderisiCommented:
Also, although I know you say you don't want a proc, but a proc is the BEST thing for DB processing.
0
 
saoirse1916Commented:
That's it -- SET NOCOUNT ON...I knew there was something I was missing.  Brandon's right on the money.
0
 
jglazer63Author Commented:
Perfect.  You got it.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now