Using VB6 and ADO 2.5 made the task of inserting a record into an Access 

table and returning its unique Row ID (using an AutoNumber field type in 

the table) very easy. You called the add new method followed by the Update 

method on a Recordset and hey presto, the record was refreshed with the ID 

field set.



This does not seem to work in the .Net world where the Recordset is 

replaced by a disconnected DataSet. The Update method no longer refreshes 

data from the database at the end of any insert. This is not a problem 

with a database that supports stored procedures where you can call a 

transaction that returns a value. But in Access it appears that you have 

to do the hard work of querying table to find the highest Row ID which is 

not ideal in a multi user situation. 



Does anyone know of a simple way to insert a record and get its Row ID all 

in one go?

Hi Steve,



Setting the DataAdapter MissingSchemaAction equal to

MissingSchemaAction.AddWithKey while AcceptChangesDuringFill is set to

true will update the Primary Key in the disconnected dataset.  While on

the surface it worked I didn't beat it up to see it work for deletes,

inserts after deletes, etc. because I had some additional problems with

ConcurrencyExceptions when using the data adapter update with Access.  I

unchecked the Advanced Concurrency option in the data adapter

configuration and looked at the update command the adapter created and

it appeared as though it shouldn't be checking for concurrency but I

still got exceptions anyway.  Because I needed reliability and a quick

solution I ended up adding a row to the dataset, in my case through a

dataview, and then inserting a row in the sql database and then

retrieving the row to update the dataview with the primary key.  Not

that elegant but so far it works every time.



Kyle



_____________________________________________



Kyle Dunn

Chief Information Officer

Funeral Services, Inc.

xxx-xxx-xxxx  x 309

Kyle@F...



-----Original Message-----

From: Steve Grant [mailto:steve@f...]

Sent: Thursday, January 03, 2002 5:27 PM

To: ADO.NET

Subject: [ado_dotnet] ADO.NET and Access Row ID's



Using VB6 and ADO 2.5 made the task of inserting a record into an Access



table and returning its unique Row ID (using an AutoNumber field type in



the table) very easy. You called the add new method followed by the

Update

method on a Recordset and hey presto, the record was refreshed with the

ID

field set.



This does not seem to work in the .Net world where the Recordset is

replaced by a disconnected DataSet. The Update method no longer

refreshes

data from the database at the end of any insert. This is not a problem

with a database that supports stored procedures where you can call a

transaction that returns a value. But in Access it appears that you have



to do the hard work of querying table to find the highest Row ID which

is

not ideal in a multi user situation.



Does anyone know of a simple way to insert a record and get its Row ID

all

in one go?




$subst('Email.Unsub').