Solved

Using scope_identity() without a stored procedure

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 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