last_updated_id

  • Thread starter Thread starter Shelly
  • Start date Start date
S

Shelly

In MySql there is a function call (using php) that returns the value of the
last autoincremented value after doing an insert. How is it done here with
vb.net and Sql Server? I researched it and saw something about "@@
IDENTITY", however, I am having trouble using it to get that value (the
order number). Are there any simple examples out there?

Shelly
 
Well on SQL you can either use

SELECT scope_identity()

or


SELECT @@IDENTITY

The first one is better because it removed the likelyhood of two users
getting the same new id number as scopy_identity is unique for that specific
connection. @@identity just returns the last allocated ID number
 
Theres a lot of examples out there
http://aspalliance.com/892_CodeSnip_How_to_Get_Id_of_the_Record_Using_ASPNET_and_SQL_Server_2000

This one is well written, albeit for SQL2000 it still applies to 2005.

You can though take another approach and make your life very simple. Pass a
GUID with your insert query and simpy issue a SELECT to get the record ID
from the one with that GUID once your insert is completed. An old trick,
but very effective.


Regards

John Timney (MVP)
http://www.johntimney.com
http://www.johntimney.com/blog
 
Well on SQL you can either use

SELECT scope_identity()

or

SELECT @@IDENTITY

The first one is better
Click to expand...

Yes it is.
because it removed the likelyhood of two users getting the same new id
number as scopy_identity is unique for that specific connection.
@@identity just returns the last allocated ID number
Click to expand...

But not for that reason... Both scope_identity() and @@IDENTITY are unique
per connection, so there's no chance of two users getting the same ID
number.

The difference is that @@IDENTITY will return the absolute last IDENTITY
value called by anything within the current SQL batch - this is particularly
problematic when inserting records into tables which have insert triggers
which also have identity fields. In this case, scope_identity would return
the ID of the table to which the insert is directed, but @@IDENTITY would
return the identity of the table updated by the trigger...

http://www.google.co.uk/search?sour...H_en-GBGB220GB220&q=scope_identity+@@IDENTITY
 
ok thanks for that



Mark Rae said:
Yes it is.


But not for that reason... Both scope_identity() and @@IDENTITY are unique
per connection, so there's no chance of two users getting the same ID
number.

The difference is that @@IDENTITY will return the absolute last IDENTITY
value called by anything within the current SQL batch - this is
particularly problematic when inserting records into tables which have
insert triggers which also have identity fields. In this case,
scope_identity would return the ID of the table to which the insert is
directed, but @@IDENTITY would return the identity of the table updated by
the trigger...

http://www.google.co.uk/search?sour...H_en-GBGB220GB220&q=scope_identity+@@IDENTITY
Click to expand...
 
Just to add more information here, in researching this further I found this,

IDENT_CURRENT('tablename')

Returns the last identity value generated for a specified table or view in
any session and any scope.

Could be useful in some situations.




Just Me said:
ok thanks for that
Click to expand...
 
Back
Top