Solved

Using scope_identity() without a stored procedure

Posted on 2012-03-29
7
573 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
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:ScottPletcher
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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 4 45
Help with SQL - TOP 10 by date and by group 13 34
How to use odbc in vb to connect to ms sql 14 38
Sql query 107 29
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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now