473,836 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Retrieving autonum / IDENTIFIER value from SQL table using DAO.

Hello,

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.AddNe w 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 programmaticall y in VB?

Any direction would be great.

Thanks!
Apr 10 '06 #1
17 2705
Try:

select
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:1fB_f.527$ 7a.323@pd7tw1no ...
Hello,

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.AddNe w 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 programmaticall y in VB?

Any direction would be great.

Thanks!
Apr 11 '06 #2
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.AddNe w 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
programmaticall y 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 11 '06 #3
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.OpenRecordse t("MyTable")
rst.AddNew
rst!MyTextfield ="My New Text"
MyNewID=rst!IDf ield ' (this is the autonum field from the previous Access
db)
rst.Update
I changed the code to

dim MyNewID as long
set rst = db.OpenRecordse t("MyTable")
rst.AddNew
rst!MyTextfield ="My New Text"
rst.Update

MyNewID=db.Open Recorset("SELEC T @@Identity").Fi elds(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" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 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.AddNe w 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
programmaticall y 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 12 '06 #4
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@pd7tw 2no...
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.OpenRecordse t("MyTable")
rst.AddNew
rst!MyTextfield ="My New Text"
MyNewID=rst!IDf ield ' (this is the autonum field from the previous Access
db)
rst.Update
I changed the code to

dim MyNewID as long
set rst = db.OpenRecordse t("MyTable")
rst.AddNew
rst!MyTextfield ="My New Text"
rst.Update

MyNewID=db.Open Recorset("SELEC T @@Identity").Fi elds(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" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 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.AddNe w 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
programmaticall y 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 12 '06 #5
Tom Moreau (to*@dont.spam. me.cips.ca) writes:
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( ).


Then again, there are cases where @@identity will give you the correct
result, and scope_identity( ) will not.

Now, I don't know how DAO works, but the suggestion to use scope_identity( )
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity( ) means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @@identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 12 '06 #6
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 wound up researching Scope_Identity and that lead me to @@identity. I
wound up changing my DAO code as follows;
...
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.


Not only that, DAO is an API that has been deprecated for a long time.
The recommended API for an Access application today, I guess still is
ADO. (Which, I will have to admit, is an API that I don't like very
much at all.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 12 '06 #7
It is enormously absurd to use DAO with MS-SQL Server.
It is enormously absurd for the OP to say he will not learn about
Stored Procedures.
It is enormously absurd to use ODBC and DAO with MS-SQL.
I KNOW, knowledgeable insiders say that is the route to take.
I say the knowledgeable insiders say so because they want to promote
Access as a front end for MS-SQL to those who are too lazy or and or
too stupid to learn MS-SQL and ADO.
Moreover, to those who are offended by this I say, "Get off you ass and
learn your trade and then you won't be!"

Apr 13 '06 #8
Hi Erland
Then again, there are cases where @@identity will give you the correct
result, and scope_identity( ) will not.
Could you give an example of when this might occur?

--
-Dick Christoph
dc******@mn.rr. com
612-724-9282
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1... Tom Moreau (to*@dont.spam. me.cips.ca) writes:
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( ).


Then again, there are cases where @@identity will give you the correct
result, and scope_identity( ) will not.

Now, I don't know how DAO works, but the suggestion to use
scope_identity( )
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity( ) means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @@identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 13 '06 #9
DickChristoph (dc********@yah oo.com) writes:
Then again, there are cases where @@identity will give you the correct
result, and scope_identity( ) will not.


Could you give an example of when this might occur?


CREATE TABLE #xyz(a int IDENTITY, b int NOT NULL)
go
EXEC sp_executesql N'INSERT #xyz(b) VALUES(@b)', N'@b int', 12
SELECT scope_identity( ), @@identity
do
DROP TABLE #xyz

While the example may look contrived, many client API uses sp_executesql
or similar under the hood. scope_identity( ) returns the latest generated
identity value in the current scope, so if you call back a second time
from the client to get the value, you can only hope the both commands
excecuted in the top scope of the connection.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 13 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1589
by: Alistair | last post by:
Hi all, I am creating a database based site that keeps track of books, who has read them and the comments they have. After a little help in M.P.I.asp.DB I managed to create a database (access 2000) as follows USERS TABLE
3
1534
by: Jen LaForge | last post by:
Thanks for the help before I even start... I'm working in Access and I have right now 1024 records. We have a field set to Autonum that gives each new record a new file number. The problem? We have had a number of records deleted and even though our file number is up to 1033, we only have 1024 records. I need to know how to fill in the gaps (have 1033 records and 1033 files (data can be filled in later)) without screwing up the order...
4
1995
by: Rico | last post by:
Hello, I'd like to change a field from an Autonum to a Long data type using DAO. I knwo how to set the attribute, but don't know how to remove it. Any ideas? Thanks!
17
4900
by: Rico | last post by:
Hello, 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...
15
3545
by: gunnar.sigurjonsson | last post by:
I´m having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following CREATE TABLE T1 ( id BIGINT GENERATED ALWAYS AS IDENTITY ( START WITH 1
5
1986
by: Sanjay Pais | last post by:
I have a table with over 1.3 million rows. I am retrieving only 20 at a time using the with - over clauses In query analyser, the data is retrieved in under a second. When retrieving using the data adaptor.fill or datareader to retrieve the data it takes over 24 seconds. public System.Data.SqlClient.SqlDataReader List1(int PageIndex, int PageSize, string ItemName, string UserIDs, DateTime DateStart, DateTime DateEnd, int status,...
0
3400
bmallett
by: bmallett | last post by:
First off, i would like to thank everyone for any and all help with this. That being said, I am having a problem retrieving/posting my dynamic form data. I have a form that has multiple options within options. I have everything being dynamically named from the previously dynamically named element. (I hope this makes sense.) I am not able to retrieve any of the dynamically created values. I can view them on the source page but can't pull them...
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5649
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4452
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3115
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.