Have an Access Database table with an AutoNumber field.

In my application I have a Typed Dataset, created via the Dataset component,
with the XML schema for it including the  msdata:AutoIncrement="true"
attribute.

After calling the Update method on the DataAdaptor, the inserted records in
the DataSet are populated with what ADO believes is the next number in the
AutoNumber sequence, however this is not the same as the number that Access
allocates.  What is the best way of getting the key values created by Access
and ensuring my Dataset truly does reflect new Autonumber values assigned to
the record in the database?

Hope this makes sense.  Thanks in advance for any help.

Raymond Francis

Do one or the other, not both. If you want access to do it, leave the field alone and do not assign it
in your update or insert statements. If you want to do it in your sql, the turn off the autoincrementing in access but leave the
field there, and update it through your sql instead. 

- Kruse


-----Original Message-----
From: Raymond Francis <comkraft@p...>
To: ADO.NET <ado_dotnet@p...>
Sent: Thu Feb 06 18:18:57 2003
Subject: [ado_dotnet] AutoNumber, Access and Typed Datasets

Have an Access Database table with an AutoNumber field.

In my application I have a Typed Dataset, created via the Dataset component,
with the XML schema for it including the  msdata:AutoIncrement="true"
attribute.

After calling the Update method on the DataAdaptor, the inserted records in
the DataSet are populated with what ADO believes is the next number in the
AutoNumber sequence, however this is not the same as the number that Access
allocates.  What is the best way of getting the key values created by Access
and ensuring my Dataset truly does reflect new Autonumber values assigned to
the record in the database?

Hope this makes sense.  Thanks in advance for any help.

Raymond Francis


===
Fast Track ADO.NET with C# is a concise introduction to the concepts, techniques, and libraries that you will need in order to start
using ADO.NET in your applications. The book covers DataSets and Typed DataSets, accessing data using DataReaders and DataAdaptors,
the close relationship between ADO.NET and XML, how and where to use ADO.NET in your enterprise applications, and how to use Web
Services and ADO.NET to easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

This message is for the named person's use only. It may contain sensitive and private proprietary or legally privileged information.
No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately
delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or
indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. CREDIT SUISSE
GROUP and each legal entity in the CREDIT SUISSE FIRST BOSTON or CREDIT SUISSE ASSET MANAGEMENT business units of CREDIT SUISSE
FIRST BOSTON reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are
those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views
of any such entity.
Unless otherwise stated, any pricing information given in this message is indicative  only, is subject to change and does not
constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as 
preliminary only and subject to our formal written confirmation.

I know nothing about Access, but in SQL Server you append a SELECT to
the INSERT statement to return the record just created, using the server
variable @@IDENTITY to identify the new primary key. Perhaps there's an
equivalent in Access..

brian

-----Original Message-----
From: Raymond Francis [mailto:comkraft@p...] 
Sent: Thu, 06 Feb 2003 23:19
To: ADO.NET
Subject: [ado_dotnet] AutoNumber, Access and Typed Datasets


Have an Access Database table with an AutoNumber field.

In my application I have a Typed Dataset, created via the Dataset
component, with the XML schema for it including the
msdata:AutoIncrement="true" attribute.

After calling the Update method on the DataAdaptor, the inserted records
in the DataSet are populated with what ADO believes is the next number
in the AutoNumber sequence, however this is not the same as the number
that Access allocates.  What is the best way of getting the key values
created by Access and ensuring my Dataset truly does reflect new
Autonumber values assigned to the record in the database?

Hope this makes sense.  Thanks in advance for any help.

Raymond Francis


===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
 

Thanks for all your help.

For those that are interested I stumbled across the solution on Microsofts
site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconretrievingidentityorautonumbervalues.asp

Brian, you weren't far off with @@IDENTITY, but I didn't realise Access 2000
supported it.