writing to a db how do i get the id number after post

[mavrick]

Hi guys

In a bit puzzled. I am adding to a sql db using asp. The form that i am using does two things:
1. adds to the db (which works fine)
2. It also needs to get the ID of the item added to the db and put it into another form field.(all in one post)

Everything works fine except, I am puzzled as to how to get the 2nd part working.

The from works fine, the second form gets posted to the correct page, but I don’t know how to get the ID form the db, from the items that was just added.

Any ideas

Hope this makes sense.

[john_k]

If the Id is in an Identity column, then the new Id automatically placed into the global Sql Server variable @@IDENTITY.

So immediately after you do the INSERT, do a SELECT @@IDENTITY.

[edit]I had thought I saw SQL Server in your post, but upon review, that isn't what you said. If your DB is something different, then let us know what it is as the method of getting the Id will vary.[edit]

[mavrick]

hi

i am using a SQL Db, Sorry! My Insert Statement is stored in a stored procedure. i have included the SELECT @@IDENTITY, Now? haven’t used SQL much so i am not so clued up..

[john_k]

Ok - you're almost there. There are two different ways to get a single numeric value back from a query. One is to use Return, and I won't go into that. The other is to simply do a SELECT.

So, structure your stored proc along these lines:

SET NOCOUNT ON 
INSERT yada yada yada 
  
SET NOCOUNT OFF 
SELECT @@IDENTITY 

Then run the query from your code as if you were getting a result set back (you are). So in ASP, that would be somethingl like:

Set rs = conn.Execute(sSql)
lNewId = rs.Fields(0).value

Add in any error checking you see fit.

[mavrick]

Hi john_k

Thanks for the help. Did not do it exactly as u did but got it to work..

Thanks again

[TheNige]

You may want to check out using SCOPE_IDENTITY() instead off @@identity.

Using SCOPE_IDENTITY() will give you the latest identity that you created within the "Scope" of the stroed procedure. Using @@identity could actually give you an identity from another transaction if lots of them were happening at the same time.