|
|
Posted by Tom Moreau on 04/12/06 20:09
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]
|