[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Using scope_identity() without a stored procedure

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
greatseats
Asked:
greatseats
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
Are you trying to get the identity of what you just inserted?  

Select Ident_Current('Mytable') as ID
0
 
greatseatsAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Anthony PerkinsCommented:
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
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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