Best way to get ID of inserted row??

Discussion in 'ASP .Net' started by =?Utf-8?B?bG91aXNlIHJhaXNiZWNr?=, Nov 16, 2004.

  1. Hi, I do a sqlcommand.executenonquery on the click of a button, which
    performs an insert statement. What is the most reliable way (i.e. someone
    else could insert at similar time) to get the id i have JUST created?
     
    =?Utf-8?B?bG91aXNlIHJhaXNiZWNr?=, Nov 16, 2004
    #1
    1. Advertising

  2. Set a counter at another table.

    When you need new number, use a while loop to select the counter then
    increment the counter by 1 where it's value equal to the origional value. If
    returned affected number of rows = 0, you know the number has been used by
    the others then you should get the next one......

    "louise raisbeck" <> ¦b¶l¥ó
    news: ¤¤¼¶¼g...
    > Hi, I do a sqlcommand.executenonquery on the click of a button, which
    > performs an insert statement. What is the most reliable way (i.e. someone
    > else could insert at similar time) to get the id i have JUST created?
     
    Lau Lei Cheong, Nov 16, 2004
    #2
    1. Advertising

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

    Deepak Guest

    Louise,

    Use ExecuteScalar wchich returns the value of first column in the first row,
    and use the scope identity function to get the identity value you have just
    inserted. Below is some code to give you an idea.

    create table #temp
    ( myid int identity,
    n varchar(10)
    )

    insert into #temp
    values('a')

    select scope_identity() as [identity_value]



    --
    With Regards,


    Deepak
    [I code, therefore I am]

    "louise raisbeck" <> wrote in
    message news:...
    > Hi, I do a sqlcommand.executenonquery on the click of a button, which
    > performs an insert statement. What is the most reliable way (i.e. someone
    > else could insert at similar time) to get the id i have JUST created?
     
    Deepak, Nov 16, 2004
    #3
  4. Thanks.

    int intNewID = Convert.ToInt32(command.ExecuteScalar());

    I put select_scopeidentity() onto the end of my insert statement, ran the
    above code and obtained my new ID. it runs in the same connection so the ID
    shouldnt belong to someone else's query, should it?

    "Deepak" wrote:

    > Louise,
    >
    > Use ExecuteScalar wchich returns the value of first column in the first row,
    > and use the scope identity function to get the identity value you have just
    > inserted. Below is some code to give you an idea.
    >
    > create table #temp
    > ( myid int identity,
    > n varchar(10)
    > )
    >
    > insert into #temp
    > values('a')
    >
    > select scope_identity() as [identity_value]
    >
    >
    >
    > --
    > With Regards,
    >
    >
    > Deepak
    > [I code, therefore I am]
    >
    > "louise raisbeck" <> wrote in
    > message news:...
    > > Hi, I do a sqlcommand.executenonquery on the click of a button, which
    > > performs an insert statement. What is the most reliable way (i.e. someone
    > > else could insert at similar time) to get the id i have JUST created?

    >
    >
    >
     
    =?Utf-8?B?bG91aXNlIHJhaXNiZWNr?=, Nov 16, 2004
    #4
  5. =?Utf-8?B?bG91aXNlIHJhaXNiZWNr?=

    Deepak Guest

    Thats right, this ID value has now been used and will not be available to
    any other insert.

    --
    With Regards,


    Deepak
    [I code, therefore I am]

    "louise raisbeck" <> wrote in
    message news:...
    > Thanks.
    >
    > int intNewID = Convert.ToInt32(command.ExecuteScalar());
    >
    > I put select_scopeidentity() onto the end of my insert statement, ran the
    > above code and obtained my new ID. it runs in the same connection so the
    > ID
    > shouldnt belong to someone else's query, should it?
    >
    > "Deepak" wrote:
    >
    >> Louise,
    >>
    >> Use ExecuteScalar wchich returns the value of first column in the first
    >> row,
    >> and use the scope identity function to get the identity value you have
    >> just
    >> inserted. Below is some code to give you an idea.
    >>
    >> create table #temp
    >> ( myid int identity,
    >> n varchar(10)
    >> )
    >>
    >> insert into #temp
    >> values('a')
    >>
    >> select scope_identity() as [identity_value]
    >>
    >>
    >>
    >> --
    >> With Regards,
    >>
    >>
    >> Deepak
    >> [I code, therefore I am]
    >>
    >> "louise raisbeck" <> wrote in
    >> message news:...
    >> > Hi, I do a sqlcommand.executenonquery on the click of a button, which
    >> > performs an insert statement. What is the most reliable way (i.e.
    >> > someone
    >> > else could insert at similar time) to get the id i have JUST created?

    >>
    >>
    >>
     
    Deepak, Nov 17, 2004
    #5
    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. lucky
    Replies:
    0
    Views:
    521
    lucky
    Jan 12, 2005
  2. Dan Sikorsky
    Replies:
    2
    Views:
    11,522
    SiliconViper
    Apr 30, 2009
  3. Patrick.O.Ige
    Replies:
    0
    Views:
    532
    Patrick.O.Ige
    Oct 31, 2007
  4. D
    Replies:
    0
    Views:
    229
  5. middletree

    how to get row just inserted

    middletree, Dec 12, 2003, in forum: ASP General
    Replies:
    2
    Views:
    126
    middletree
    Dec 12, 2003
Loading...

Share This Page