Title: Get an autonumber value that was just inserted in C#

If a database field is an autonumber field, then the database automatically generates values for it. For example, suppose a Students tables has typical fields such as FirstName, LastName, EnrollmentYear, and so forth. You could make the StudentId field be an autonumber field. Then the database automatically sets this value to 1, 2, 3, and so on for new students as they are created.

Before I talk more about autonumber fields, let me mention that the program uses the following code to create a database connection when it starts.

// The connection object. private OleDbConnection Conn; // Make the database connection. private void Form1_Load(object sender, EventArgs e) { // Make the connection object. const string db_name = "Students.mdb"; Conn = new OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + db_name + ";" + "Mode=Share Deny None"); }

The form's Load event handler just creates an OleDbConnection object and sets its connection string to use OLE DB to connect to the database Students.mdb. At design time I added that database to the project, set its "Build Action" property to Content, and set its "Copy to Output Directory" property to "Copy if newer" so the database is automatically copied into the executable directory.

Okay, back to autonumbering.

To create a new record in a table that has an autonumber field, simply do not specify a value for that field and the database generates it automatically. If you enter first and last name values and click Create, this example uses the following code fragment to create a new Student table record.

// Create a new Students record. private void btnCreate_Click(object sender, EventArgs e) { // Create the command. OleDbCommand cmd = new OleDbCommand( "INSERT INTO Students(FirstName, LastName) VALUES (?, ?)", Conn); cmd.Parameters.Add( new OleDbParameter("FirstName", txtFirstName.Text)); cmd.Parameters.Add( new OleDbParameter("LastName", txtLastName.Text)); // Execute the command. Conn.Open(); cmd.ExecuteNonQuery(); ...

This code creates a new OleDbCommand object to execute the SQL INSERT statement on the Conn connection object. It creates parameters holding the values that should be inserted for the FirstName and LastName fields, opens the connection, and executes the command.

When the ExecuteNonQuery method executes, the database inserts a new record with the given FirstName and LastName values. The StudentId field is an autonumber field so its value is automatically created by the database.

Often the next thing a program needs to do is work some more with that record. Unfortunately it doesn't know what value was assigned to the autonumbered field. If the other fields can uniquely identify the record, that's not a problem. You can just search for those values to get the newly created record. Unfortunately autonumber fields are often used for the purpose of uniquely identify records. (For example, as student, customer, and employee IDs.)

So how can you find the new record if the autonumber field is its only unique identifier? In a C# program working with an Access database (possibly not older versions of Access, however), you can execute the query SELECT @@IDENTITY to retrieve the autonumber value you just added.

After it uses the preceding code to create a new Students record, this example uses the following code to fetch and display the new StudentId value.

... // Get the autonumber value. cmd = new OleDbCommand("SELECT @@IDENTITY", Conn); OleDbDataReader reader = cmd.ExecuteReader(); // Read the value. if (!reader.Read()) { MessageBox.Show("Error reading the autonumber value"); } else { // Display the StudentID and other values. txtFirstNameResult.Text = txtFirstName.Text; txtLastNameResult.Text = txtLastName.Text; txtStudentId.Text = reader.GetValue(0).ToString(); // Clear the input fields. txtFirstName.Clear(); txtLastName.Clear(); } // Close the connection. cmd.Dispose(); Conn.Close(); }

The code finishes by disposing of the command object and closing the connection.

Download the example to experiment with it and to see additional details.