return IDENTITY after SQL Insert?

Discussion in 'ASP .Net' started by =?Utf-8?B?RGFiYmxlcg==?=, Jul 20, 2006.

  1. 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!
    =?Utf-8?B?RGFiYmxlcg==?=, Jul 20, 2006
    #1
    1. Advertising

  2. =?Utf-8?B?RGFiYmxlcg==?=

    Mischa Kroon Guest

    "Dabbler" <> wrote in message
    news:...
    > 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()
    Mischa Kroon, Jul 20, 2006
    #2
    1. Advertising

  3. =?Utf-8?B?RGFiYmxlcg==?=

    Naveen Guest

    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:...
    > > 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, Jul 20, 2006
    #3
  4. 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:...
    >> > 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], Jul 20, 2006
    #4
  5. =?Utf-8?B?RGFiYmxlcg==?=

    tfsmag Guest

    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:...
    > >> > 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, Jul 20, 2006
    #5
  6. =?Utf-8?B?RGFiYmxlcg==?=

    bartekm

    Joined:
    Aug 17, 2008
    Messages:
    7
    Just in case you want a full example using the ReturnValue method on either an SqlDataSource or ObjectDataSource then you can find one here
    bartekm, Mar 2, 2011
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Quentin Huo
    Replies:
    2
    Views:
    8,309
    diehardguy
    Jul 18, 2006
  2. Phil Winstanley [Microsoft MVP ASP.NET]

    Re: how to get the new identity ID just after insert a new row?

    Phil Winstanley [Microsoft MVP ASP.NET], Jun 11, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    11,330
    Quentin Huo
    Jun 11, 2004
  3. Frederick D'hont
    Replies:
    0
    Views:
    305
    Frederick D'hont
    Jul 25, 2005
  4. Replies:
    6
    Views:
    447
  5. Popezilla
    Replies:
    2
    Views:
    922
    Popezilla
    Mar 18, 2007
Loading...

Share This Page