Solved

Using scope_identity() without a stored procedure

Posted on 2012-03-29
7
580 Views
Last Modified: 2012-06-04
Hello,

Here is my SQL:

INSERT INTO MYTABLE (myvalue) VALUES ('test'); SELECT SCOPE_IDENTITY()

How can I execute this via classic ASP and get the identity of the table returned?  Here is what I am trying now:

      set conn = server.createobject("adodb.connection")
      conn.open myconnection
      conn.CommandTimeout = 600
      set record_set = connt.Execute(mysql)
      myarray = record_set.getrows()
      conn.close()

I get an error when running this - "Operation is not allowed when the object is closed."  Is what I want to do even possible?

I realize that using a stored procedure would be best here; unfortunately I am unable to create stored procedures on the database I am working with (restriction at request of client).  I am open to any other suggestions.

Thanks,

Jason
0
Comment
Question by:greatseats
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 37782810
Are you trying to get the identity of what you just inserted?  

Select Ident_Current('Mytable') as ID
0
 

Author Comment

by:greatseats
ID: 37782831
Padas,

Yes - I am trying to get the identity of what was just inserted.

If I run my INSERT statement and then separately run my SELECT statement to get the identity I run into problems.  Occasionally I get back the ID of a different row than what I inserted.  There are a lot of things doing inserts on this table and I need to be guaranteed that I get back the identity of the row that I inserted.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37783544
If nothing else works, you could create a temp table and use the OUTPUT clause to insert the identity value(s) just INSERTed into the temp table.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37785149
All you are missing is SET NOCOUNT ON as in:
SET NOCOUNT ON; INSERT INTO MYTABLE (myvalue) VALUES ('test'); SELECT SCOPE_IDENTITY()

If you need an explanation as to why that makes all the difference, let me know.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 37802449
not sure if this is a typo in the form or the code, but this line would cause that error to appear:

set record_set = connt.Execute(mysql)

you have an extra "t" in the conn variable name. then when you go to close the conn object, it'll throw that error since it was never opened
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question