Retrieve inserted ID with Stored Procedure and @@identity

Here's a quick and dirty tut on how to retrieve the just inserted records ID with the use of Stored Procedures in SQL Server using the @@identity function

STEP 3

Ok, the final part!

Take a look at the bindings panel, it should look a little like this:

Now drag the marked item on your page.
Switch to code view (Ctrl - ~) and you will see this

<%= rsTest.Fields.Item("tID").Value %>

remove the .Fields.Item part so it looks like this

<%= rsTest("tID").Value %>

Ok were done!

The <%= rsTest("tID").Value %> will display the Identity of the item you just inserted!

Please post a comment if you have a question! I'll try to get back to that as soon as possible!

Matthijs

Comments

another way

November 15, 2002 by Bogos bogos

Hi there!

I Usually do in another way where i don't need to set the "SET NOCOUNT OFF".

Create Procedure TSI @test varchar(250),@outputid int

as insert into test(t1) values (@test)

set @id = @@identity

It always worked for me and you can drag the id into your layout and don't need to remove nothing from the code.

As i said, it always worked for me in all my web applications. :)

Does this replace the Insert Record Code

July 10, 2008 by Andrew Petrillo

This is very interesting, Will this function be used to replace the insert record function in DW, or is this something you can use along with the insert record function?

If it's used along with the insert record function, would you have to add in all of the fields that would be involved with the record insertion? for example, would I have to include firstname, lastname, fields if it's an info submission form? or will this basically plug into any form where you need to pull the new ID as it's inserted?

Sorry to sound soo green, im advanced in ASP, just never connected the dots in my head with SPs, I feel like im mentally missing a main ingredient so it's not clicking.

Thanks!

You must me logged in to write a comment.