This post originated from an RSS feed registered with .NET Buzz
by Duncan Mackenzie.
Original Post: Short but sweet little example from today's .NET Wire
Feed Title: Code/Tea/Etc...
Feed URL: /msdnerror.htm?aspxerrorpath=/duncanma/rss.aspx
Feed Description: Duncan is the Visual Basic Content Strategist at MSDN, the editor of the Visual Basic Developer Center (http://msdn.microsoft.com/vbasic), and the author of the "Coding 4 Fun" column on MSDN (http://msdn.microsoft.com/vbasic/using/columns/code4fun/default.aspx). While typically Visual Basic focused, his blogs sometimes wanders off of the technical path and into various musing of his troubled mind.
I was just reading the latest dotnetwire newsletter and this article caught my eye.
Synchronize Identity Values between Database and DataSet During Updates
After inserting the rows in the database your DataTable does not automatically reflect the identity values of as assigned by the database. The problem can be solved by the clever use of stored procedures and output parameters.
It gives a very brief description of an important idea, how to return identity values from an insert without having to do another complete select query (even if you batch it together). I see only one small problem with the sample (besides how little detail it covers) and it is a very common mistake; the use of @@IDENTITY to return the PK value from an INSERT.
Assuming you have SQL Server 2000 or later, I wouldn't recommend using @@IDENTITY to return the PK of the last inserted record, I'd use SCOPE_IDENTITY( ) instead. @@IDENTITY returns the last inserted identity value, which isn't necessarily the record you were just inserted. If a trigger, or multiple chained triggers, has fired in response to your insert it is possible you will retrieve a PK value from a completely different table. SCOPE_IDENTITY( ), on the other hand, returns the last identity value in the same scope, which is the Insert you just executed.
If you are looking for information on this topic, check out William Vaughn's article on just this subject: Managing an @@IDENTITY Crisis.
Previous Topic
