«

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!

"Dabbler" <> wrote in message
news:8936C83E-99CF-4CFF-B48F-...
> 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 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" <> wrote in message
news:6e108$44bf4cfe$3ec3ac38$. ..
>
> "Dabbler" <> wrote in message
> news:8936C83E-99CF-4CFF-B48F-...
> > 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()
>
>
>

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" <> wrote in message
news:e9o27g$p9l$...
> 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" <> wrote in message
> news:6e108$44bf4cfe$3ec3ac38$. ..
>>
>> "Dabbler" <> wrote in message
>> news:8936C83E-99CF-4CFF-B48F-...
>> > 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()
>>
>>
>>
>
>

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" <> wrote in message
> news:e9o27g$p9l$...
> > 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" <> wrote in message
> > news:6e108$44bf4cfe$3ec3ac38$. ..
> >>
> >> "Dabbler" <> wrote in message
> >> news:8936C83E-99CF-4CFF-B48F-...
> >> > 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()
> >>
> >>
> >>
> >
> >

[bartekm]

Just in case you want a full example using the ReturnValue method on either an SqlDataSource or ObjectDataSource then you can find one here