Posted by Rico on 04/13/06 20:48

Hi Tom,

Just so you know, triggers and other server side operations will not affect
the @@identity result and hence, will not return an incorrect result.

Rick


"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:Pwa%f.3830$L.26943@news20.bellglobal.com...
> Don't use @@IDENTITY. You can have incorrect results if your INSERT fires
> a
> trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in message news:sG9%f.5965$WI1.5577@pd7tw2no...
> Thanks Tom and Erland,
>
> I wound up researching Scope_Identity and that lead me to @@identity. I
> wound up changing my DAO code as follows;
>
> Instead of....
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
>
> I changed the code to
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
>
> MyNewID=db.OpenRecorset("SELECT @@Identity").Fields(0)
>
> This seems to work in every case, since the @@Identity line gets the last
> ID
> created on your specific connection whether someone else updates the
> database as the same time or not. In other words, if I update the
> database
> at the same time another user updates the database, the @@Identity will
> never pass me back the other users ID field since that wasn't created on
> my
> connection.
>
> Although my tests have proven successful, if anyone has exprience using
> this
> with DAO and has had any failures, please let me know.
>
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point
> I have to stick with what I know. Thanks for the info.
>
> Rick
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns97A2F243F7168Yazorman@127.0.0.1...
>> Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
>> CAPS)
>> writes:
>>> I am in the midst of converting an Access back end to SQL Server
>>> Express. The front end program (converted to Access 2003) uses DAO
>>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>>> autonum value for the new record. This doesn't occur with SQL Server,
>>> which of course causes an error (or at least in this code it does since
>>> there's an unhandled NULL value). Is there any way to retrieve this
>>> value when I add a new record from SQL server or will I have to do it
>>> programmatically in VB?
>>
>> It's better to use stored procedures to add data, rather than relying on
>> ADO generating code behind your back. It's easy for the Jet provider
>> to populate the Autonumber for you, because all operations are in your
>> process space. But since SQL Server is on the other end of the wire,
>> there is an extra roundtrip to get the value.
>>
>> Also, with SQL Server, make sure that all your cursors are client-side.
>>
>> A sample stored procedure:
>>
>> CREATE PROCEDURE insert_tbl @a int,
>> @b datetime,
>> @c varchar(23),
>> @id int AS
>> INSERT tbl (a, b, c)
>> VALUES (@a, @b, @c)
>> SELECT @id = scope_identity
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

[Back to original message]