How do I read back autonumber after do an insert in asp.net

[COHENMARVIN]

Is there any way using a 'datareader' to find out the autonumber field
value of a record that I've just inserted?
Thanks,
-- cohenmarvin

[T. DAVIS JR]

Well it depends on what database you are using. If you are using SQL Server
this should work:

SELECT @@Identity

--
T. DAVIS JR

- .NET will change the way developers develop applications
"COHENMARVIN" <co*********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Is there any way using a 'datareader' to find out the autonumber field
value of a record that I've just inserted?
Thanks,
-- cohenmarvin

[Curt_C [MVP]]

COHENMARVIN wrote:

Is there any way using a 'datareader' to find out the autonumber field
value of a record that I've just inserted?
Thanks,
-- cohenmarvin

You're best bet is to avoid using autonumber in the first place.
Life is MUCH easier if you assign it yourself

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

[Lucas Tam]

"Curt_C [MVP]" <software_at_darkfalz.com> wrote in news:#OTlPBfnFHA.2156
@TK2MSFTNGP14.phx.gbl:

COHENMARVIN wrote:

Is there any way using a 'datareader' to find out the autonumber field
value of a record that I've just inserted?
Thanks,
-- cohenmarvin

You're best bet is to avoid using autonumber in the first place.
Life is MUCH easier if you assign it yourself

??? Then you run into the possiblity of generating a duplicate...

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Lucas Tam]

"T. DAVIS JR" <so*****@hotmail.com> wrote in
news:ev**************@tk2msftngp13.phx.gbl:

Well it depends on what database you are using. If you are using SQL
Server this should work:

SELECT @@Identity

@@IDENTITY is potentially a BAD thing! SELECT SCOPE_IDENTITY() is a
better choice since @@identity could return the wrong value if there are
triggers on the table.

Example:

http://www.trigonblue.com/sp_identity.htm

Here's a table explaining the difference:

SELECT @@IDENTITY <- Session Scope
SELECT IDENT_CURRENT('tablename') <- Table scope
SELECT SCOPE_IDENTITY <- statement scope
In detail:

http://www.sqlteam.com/item.asp?ItemID=319

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[T. DAVIS JR]

Well I agree, but for 95% of us that don't use triggers it shouldn't really
matter. However, selecting scope_identity() wouldn't hurt I guess.

--
T. DAVIS JR

- .NET will change the way developers develop applications
"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn**************************@127.0.0.1...

"T. DAVIS JR" <so*****@hotmail.com> wrote in
news:ev**************@tk2msftngp13.phx.gbl:

Well it depends on what database you are using. If you are using SQL
Server this should work:

SELECT @@Identity

@@IDENTITY is potentially a BAD thing! SELECT SCOPE_IDENTITY() is a
better choice since @@identity could return the wrong value if there are
triggers on the table.

Example:

http://www.trigonblue.com/sp_identity.htm

Here's a table explaining the difference:

SELECT @@IDENTITY <- Session Scope
SELECT IDENT_CURRENT('tablename') <- Table scope
SELECT SCOPE_IDENTITY <- statement scope
In detail:

http://www.sqlteam.com/item.asp?ItemID=319

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Lucas Tam]

"T. DAVIS JR" <so*****@hotmail.com> wrote in
news:O1**************@TK2MSFTNGP10.phx.gbl:

Well I agree, but for 95% of us that don't use triggers it shouldn't
really matter. However, selecting scope_identity() wouldn't hurt I
guess.

It's not only triggers... if you do two inserts in a row, it could return
the wrong value ; )

And yes, it's better to be safe than sorry! I found out the hardway
wondering why my identity fields were all wrong ; )

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Curt_C [MVP]]

Lucas Tam wrote:

"Curt_C [MVP]" <software_at_darkfalz.com> wrote in news:#OTlPBfnFHA.2156
@TK2MSFTNGP14.phx.gbl:

COHENMARVIN wrote:

Is there any way using a 'datareader' to find out the autonumber field
value of a record that I've just inserted?
Thanks,
-- cohenmarvin

You're best bet is to avoid using autonumber in the first place.
Life is MUCH easier if you assign it yourself

??? Then you run into the possiblity of generating a duplicate...

true... but if you put in a proper id generator you wont have this
issue. Very few "professional" apps use the auto generator, they ID it
themselves.

--
Curt Christianson
site: http://www.darkfalz.com
blog: http://blog.darkfalz.com

[Lucas Tam]

"Curt_C [MVP]" <software_at_darkfalz.com> wrote in news:#3kzrEinFHA.2484
@TK2MSFTNGP15.phx.gbl:

??? Then you run into the possiblity of generating a duplicate...

true... but if you put in a proper id generator you wont have this
issue. Very few "professional" apps use the auto generator, they ID it
themselves.

Of course there are situations where this maybe needed - but for the
average application how much do you gain from rolling your own generator?
Building your own id generator means you'll need to maintain state, insure
that IDs are unique, concurrency issues - too much work especially when any
decent database already does it for you.
--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Patrick.O.Ige]

Prevention is better that cure..
But at the same time if your not inserting 2 rows
then the latter should be fine

"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn***************************@127.0.0.1...

"T. DAVIS JR" <so*****@hotmail.com> wrote in
news:O1**************@TK2MSFTNGP10.phx.gbl:

Well I agree, but for 95% of us that don't use triggers it shouldn't
really matter. However, selecting scope_identity() wouldn't hurt I
guess.

It's not only triggers... if you do two inserts in a row, it could return
the wrong value ; )

And yes, it's better to be safe than sorry! I found out the hardway
wondering why my identity fields were all wrong ; )

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/

[Kevin Spencer]

Avoiding the autogenerator issue, I would simply create a Stored Procedure
that selects the records and returns the ID (however generated) as an output
parameter.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

"Lucas Tam" <RE********@rogers.com> wrote in message
news:Xn**************************@127.0.0.1...

"Curt_C [MVP]" <software_at_darkfalz.com> wrote in news:#3kzrEinFHA.2484
@TK2MSFTNGP15.phx.gbl:

??? Then you run into the possiblity of generating a duplicate...

true... but if you put in a proper id generator you wont have this
issue. Very few "professional" apps use the auto generator, they ID it
themselves.

Of course there are situations where this maybe needed - but for the
average application how much do you gain from rolling your own generator?
Building your own id generator means you'll need to maintain state, insure
that IDs are unique, concurrency issues - too much work especially when
any
decent database already does it for you.
--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/