ADO.NET 3.5 Cookbook, 2nd Edition

5.3. Getting an AutoNumber Value from Microsoft Access

Problem

If you add a row into a Microsoft Access table that has an AutoNumber column, the value assigned to the column in the DataTable is replaced by a value generated by the database. You need to retrieve the new value to keep the DataTable synchronized with the database.

Solution

Use the RowUpdated event handler to retrieve the new AutoNumber value generated by Microsoft Access using the @@IDENTITY function.

The solution creates a DataAdapter and sets its insert command to a parameterized query. An event handler is created for the RowUpdated event of the DataAdapter— when a row is inserted, the event handler retrieves the AutoNumber value from Access using the @@IDENTITY function and stores it to the ID value in the row that raised the event. Next, a DataTable is created, its schema filled from the Customers table, the AutoIncrementSeed and AutoIncrementStep values both set to –1, and the DataTable loaded with the data in the Customers table. A new row is added to the DataTable and the autoincrement ID value is output to the console. The Update() method of the DataAdapter is used to update the Access database with the inserted row. The OnRowUpdated() method is called in response to the RowUpdated event that this raises, the AutoNumber value for the ID column is retrieved from Access using the @@IDENTITY function, and the inserted row is updated with that value. Finally, the updated row is output to the console.

The C# code in ...