Reset an Access autonumber field in C#

[autonumber]

The post Get an autonumber value that was just inserted in C# explains how to use autonumbered fields. This post explains how you can reset the value used by an autonumber field.

To reset an autonumber field in an Access database, execute an SQL statement similar to the following:

ALTER TABLE tablename
    ALTER COLUMN fieldname AUTOINCREMENT(startvalue, increment)

Where:

  • tablename is the name of the table
  • fieldname is the name of the autonumber field in the table
  • startvalue is the next value that should be used for the field
  • increment is the amount (default 1) by which the values should be incremented each time you use a value

The following code shows how this example resets the autonumber value for the Students table’s StudentId field.

private void btnReset_Click(object sender, EventArgs e)
{
    // Create the command.
    OleDbCommand cmd = new OleDbCommand(
        "ALTER TABLE Students ALTER " +
            "COLUMN StudentId AUTOINCREMENT(" +
            txtAutoNumberStart.Text + ",1)",
        Conn);

    // Execute the command.
    Conn.Open();
    cmd.ExecuteNonQuery();

    // Close the connection.
    cmd.Dispose();
    Conn.Close();

    MessageBox.Show("Ok");
}

This code simply executes the previous SQL statement using the value that you entered in the txtAutoNumberStart TextBox.

You need to exercise some caution when you use this statement. The database does not automatically check that new auto-generated values are not already in the table. For example, suppose you insert some records with autonumber values 1, 2, 3, …, 10. You then reset the autonumber to start over at 1. When you insert a new record, the autonumber field will try to use the value 1 again. If the field must be unique, then that causes an exception. Often autonumber fields are used to generate unique values such as student IDs so that will be a problem.

The morale is, you probably shouldn’t reset the autonumber value to a smaller value than it already has, at least unless you’ve looked over the values in the database and you’re sure you won’t have any collisions.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in ADO.NET, database and tagged , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *