Solved

Using scope_identity() without a stored procedure

Posted on 2012-03-29
7
577 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 52

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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