Best way to get ID of inserted row??

[louise raisbeck]

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]

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" <lo************@discussions.microsoft.com> ¦b¶l¥ó
news:25**********************************@microsof t.com ¤¤¼¶¼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?

[Deepak]

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" <lo************@discussions.microsoft.com> wrote in
message news:25**********************************@microsof t.com...

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?

[louise raisbeck]

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" <lo************@discussions.microsoft.com> wrote in
message news:25**********************************@microsof t.com...

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]

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" <lo************@discussions.microsoft.com> wrote in
message news:CA**********************************@microsof t.com...

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" <lo************@discussions.microsoft.com> wrote in
message news:25**********************************@microsof t.com...

> 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?