ADO.NET Cookbook

4.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.

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataTable and programmatically defining the schema to match the Categories table in Northwind. The AutoIncrementSeed and AutoIncrementStep property values are both set to -1 for the AutoIncrement primary key column, the CategoryID. A DataAdapter is created and used to fill the DataTable. The insert command and its parameters are defined for the DataAdapter so that new rows can be added to the data source. An event handler is defined for the RowUpdated event of the DataAdapter to retrieve the AutoNumber value generated for the CategoryID by Access. The default view of the table is bound to the data grid on the form.

Add Button.Click

Creates a new row in the Categories DataTable using the entered CategoryName and Description values and the automatically generated CategoryID field. The Update( ) method of the DataAdapter is used to insert the row into the data source and the DataAdapter RowUpdated event handler synchronizes the AutoNumber value generated by Access to the AutoIncrement column value; its value, both before and after is displayed.

DataAdapter.RowUpdated

Retrieves the AutoNumber CategoryID value generated by Access for inserted rows and updates the DataRow with that value synchronizing it with the Access database.

The C# code is shown in Example 4-4.

Example 4-4. File: MsAccessAutonumberValueForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

// DataTable name constants
private const String CATEGORIES_TABLE        = "Categories";

// Field name constants
private const String CATEGORYID_FIELD        = "CategoryID";
private const String CATEGORYNAME_FIELD      = "CategoryName";
private const String DESCRIPTION_FIELD       = "Description";

// Stored procedure name constants
public const String GETCATEGORIES_SP         = "GetCategories";
public const String INSERTCATEGORIES_SP      = "InsertCategories";

// Stored procedure parameter name constants for Categories dt
public const String CATEGORYID_PARM          = "@CategoryID";
public const String CATEGORYNAME_PARM        = "@CategoryName";
public const String DESCRIPTION_PARM         = "@Description";

private DataTable dt;
private OleDbDataAdapter da;

//  . . . 

private void MsAccessAutonumberValueForm_Load(object sender,
    System.EventArgs e)
{
    // Create the Categories dt.
    dt = new DataTable(CATEGORIES_TABLE);

    // Add the identity column.
    DataColumn col = dt.Columns.Add(CATEGORYID_FIELD,
        typeof(System.Int32));            
    col.AllowDBNull = false;
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    // Set the primary key.
    dt.PrimaryKey = new DataColumn[] {col};

    // Add the other columns.
    col = dt.Columns.Add(CATEGORYNAME_FIELD, typeof(System.String));
    col.AllowDBNull = false;
    col.MaxLength = 15;
    dt.Columns.Add(DESCRIPTION_FIELD, typeof(System.String));

    // Create the DataAdapter.
    String sqlSelect = "SELECT CategoryID, CategoryName, Description " +
        "FROM Categories";
    da = new OleDbDataAdapter(sqlSelect,
        ConfigurationSettings.AppSettings["MsAccess_ConnectString"]);

    // Create the insert command for the DataAdapter.
    String sqlInsert = "INSERT INTO Categories " +
        "(CategoryName, Description) VALUES (?, ?)";
    da.InsertCommand = new OleDbCommand(sqlInsert,
        da.SelectCommand.Connection);
    da.InsertCommand.Parameters.Add(CATEGORYNAME_PARM, OleDbType.Char,
        15, CATEGORYNAME_FIELD);
    da.InsertCommand.Parameters.Add(DESCRIPTION_PARM, OleDbType.VarChar,
        100, DESCRIPTION_FIELD);

    // Handle this event to retrieve the autonumber value.
    da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);

    // Fill the table with data.
    try
    {
        da.Fill(dt);
    }
    catch (OleDbException ex)
    {
        MessageBox.Show(ex.Message);
    }

    // Bind the default dt view to the grid.
    dataGrid.DataSource = dt.DefaultView;        
}

private void addButton_Click(object sender, System.EventArgs e)
{
    // Add the row to the Category table.
    DataRow row = dt.NewRow( );
    row[CATEGORYNAME_FIELD] = categoryNameTextBox.Text;
    row[DESCRIPTION_FIELD] = descriptionTextBox.Text;
    dt.Rows.Add(row);

    resultTextBox.Text = "Identity value before update = " +
        row[CATEGORYID_FIELD] + Environment.NewLine;

    // Update the table with the new row.
    try
    {
        da.Update(dt);
        resultTextBox.Text += "Identity value after update = " +
            row[CATEGORYID_FIELD] + Environment.NewLine +
            Environment.NewLine;
    }
    catch(OleDbException ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void OnRowUpdated(object Sender, OleDbRowUpdatedEventArgs args)
{
    // Retrieve autonumber value for inserts only.
    if(args.StatementType == StatementType.Insert)
    {
        // SQL command to retrieve the identity value created
        OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY",
            da.SelectCommand.Connection);

        // Store the new identity value to the CategoryID in the table.
        args.Row[CATEGORYID_FIELD] = (int)cmd.ExecuteScalar( );
    }
}

Discussion

Microsoft Access does not support stored procedures or batch command processing. It is therefore not possible to map returned stored procedure output parameters, or a result set, back to the row being inserted or updated, as is possible for Microsoft SQL server (see Recipe 4.2). Microsoft Access 2000 and later does support @@IDENTITY, which allows the last AutoNumber value generated to be retrieved.

To use @@IDENTITY, attach a handler to the OnRowUpdated event of the DataAdapter. The OnRowUpdated event will be called after any update to the row is made in the data source. The AutoNumber is only generated for rows that are inserted, so check that the update type of the event has a StatementType of Insert. Next, retrieve the new AutoNumber value by executing the following command:

SELECT @@IDENTITY

Finally, store this value, that is the AutoNumber value generated by Microsoft Access, to the AutoIncrement column in the DataRow.

This solution will only work using the Jet 4.0 OLE DB provider or later.