Insert record and retrieve automunber value

[jasonhuibers]

I am using a sql 2008 database and when I use an Insert statement, I would like to be able to grab the autonumber id of that record I just inserted. How can I do this using asp?

 

[gmmastros]

With a sql server database, it's called an identity column. I mention this because google searches on sql & autonumber will not be as effective as a google search on sql & identity.

Anyway, to retrieve the newly inserted id, you should call the code as though it will return a recordset (which is actually does). The query would look something like this:

[tt]
Set NoCount On
-- Your Insert Statement Here
Select Scope_Identity() As MyNewIdValue
[/tt]

Ex:

Set NoCount On
Insert Into People(Name) Values('Jack')
Select Scope_Identity() As PersonId

The query appears to be 3 separate queries, but you'll want to execute them together.

Do NOT be tempted to use @@Identity. With a SQL Server database, it's possible to get the wrong value. Scope_Identity() will ALWAYS return what you expect it to.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tsuji]

check this?

http://www.tek-tips.com/viewthread.cfm?qid=1597499

 

[tsuji]

gmmastros gives a more thorough answer.