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

 

[Curt_C [MVP]]

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

 

[Lucas Tam]

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]

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

 

[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.

 

[Lucas Tam]

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 ; )

 

[Curt_C [MVP]]

"Curt_C [MVP]" <software_at_darkfalz.com> wrote in @TK2MSFTNGP14.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.

 

[Lucas Tam]

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.

 

[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

 

[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.