return IDENTITY after SQL Insert?

[Dabbler]

I'm using an SQLCommand to insert row using a text command. Is there a way to
return the IDENTITY key value after the insert ?

Thanks much!

[Mischa Kroon]

"Dabbler" <Da*****@discussions.microsoft.comwrote in message
news:89**********************************@microsof t.com...

I'm using an SQLCommand to insert row using a text command. Is there a way
to
return the IDENTITY key value after the insert ?

set SQLCommand =
insert into table values ('string value');select scope_identity()

[Naveen]

Naveen Bhardwaj
This can be done by :-

using @@IDENTITY which returns the last-inserted identity value

But the preferable solution is to use IDENT_CURRENT('TableName') which
returns the last identity value generated for a specified table in any
session and any scope.

"Mischa Kroon" <ww*@bloggingabout.netwrote in message
news:6e***************************@news.chello.nl. ..

>
"Dabbler" <Da*****@discussions.microsoft.comwrote in message
news:89**********************************@microsof t.com...

I'm using an SQLCommand to insert row using a text command. Is there a

way

to
return the IDENTITY key value after the insert ?

set SQLCommand =
insert into table values ('string value');select scope_identity()

[Karl Seguin [MVP]]

In case anyone is curious, @@IDENTITY is dangerous 'cuz it returns the last
ID generated from any scope. If you are using triggers, look for this to
really bite you in the ass.

For example, you insert a new customer and select @@IDENTITY to get his/her
customerId...problem is you have a trigger on insert in your custom table
which adds a new audit record. You'll end up getting hte auto-generated
auditId wthout knowing it..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Naveen" <na*************@in.bosch.comwrote in message
news:e9**********@news4.fe.internet.bosch.com...

Naveen Bhardwaj
This can be done by :-

using @@IDENTITY which returns the last-inserted identity value

But the preferable solution is to use IDENT_CURRENT('TableName') which
returns the last identity value generated for a specified table in any
session and any scope.

"Mischa Kroon" <ww*@bloggingabout.netwrote in message
news:6e***************************@news.chello.nl. ..

>>
"Dabbler" <Da*****@discussions.microsoft.comwrote in message
news:89**********************************@microso ft.com...

I'm using an SQLCommand to insert row using a text command. Is there a

way

to
return the IDENTITY key value after the insert ?

set SQLCommand =
insert into table values ('string value');select scope_identity()

[tfsmag]

I had a similar issue to what Karl mentioned, was lucky enough to catch
it while still in development. I ended up doing away with using
triggers for auditing purposes and just made the audit table insert at
the end of the stored proc.
Karl Seguin [MVP] wrote:

In case anyone is curious, @@IDENTITY is dangerous 'cuz it returns the last
ID generated from any scope. If you are using triggers, look for this to
really bite you in the ass.

For example, you insert a new customer and select @@IDENTITY to get his/her
customerId...problem is you have a trigger on insert in your custom table
which adds a new audit record. You'll end up getting hte auto-generated
auditId wthout knowing it..

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Naveen" <na*************@in.bosch.comwrote in message
news:e9**********@news4.fe.internet.bosch.com...

Naveen Bhardwaj
This can be done by :-

using @@IDENTITY which returns the last-inserted identity value

But the preferable solution is to use IDENT_CURRENT('TableName') which
returns the last identity value generated for a specified table in any
session and any scope.

"Mischa Kroon" <ww*@bloggingabout.netwrote in message
news:6e***************************@news.chello.nl. ..

>
"Dabbler" <Da*****@discussions.microsoft.comwrote in message
news:89**********************************@microsof t.com...
I'm using an SQLCommand to insert row using a text command. Is there a

way

to
return the IDENTITY key value after the insert ?

set SQLCommand =
insert into table values ('string value');select scope_identity()