HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual C# .NET (816112)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework) 1.0
  • Microsoft Visual C# .NET (2002)
For a Microsoft Visual Basic .NET version of this article, see 815629.

IN THIS TASK

SUMMARY

This step-by-step article discusses how to retrieve the identity column value from an Access database.

Retrieving the Identity value from a Jet database is different from that of SQL Server, because a Jet database does not support multi-statement batch commands. The Jet OLE DB Provider version 4.0 supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field that is generated on your connection. To run the SELECT @@Identity query, use another OleDbCommand object. This article describes how to use a second OleDbCommand to retrieve the Identity column value.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Jet OLE DB Provider version 4.0
  • Microsoft Visual C# .NET
This article assumes that you are familiar with the following topics:
  • Visual C# .NET programming
  • Visual Studio .NET IDE
back to the top

Connect to the Access Database

To connect to the Access database, and create a table with the Identity column, follow these steps:
  1. Start Microsoft Visual Studio .NET 2002.
  2. On the File menu, point to New, and then click Project.
  3. Under Project Types, click Visual C# Projects, and then under Templates, click Console Application. By default, Class1.cs is created.
  4. Name the project MyJetApplication, and then click OK.
  5. Replace the existing code with the following code:
    using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication30
{
	class Class1
	{
      [STAThread]
      static void Main(string[] args)
      {
         // Open Connection 
         OleDbConnection cnJetDB = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDatabase");
         cnJetDB.Open();

         // If the test table does not exist then create the Table
         string strSQL; 
         strSQL = "CREATE TABLE AutoIncrementTest " +
            "(ID int identity, Description varchar(40), " +
            "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))";

         // Command for Creating Table
         OleDbCommand cmdJetDB = new OleDbCommand(strSQL, cnJetDB);
         cmdJetDB.ExecuteNonQuery();

         // Create a DataAdaptor With Insert Command For inserting records
         OleDbDataAdapter oleDa = new OleDbDataAdapter("Select * from AutoIncrementTest", cnJetDB);

         // Command to Insert Records
         OleDbCommand cmdInsert = new OleDbCommand();
         cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)";
         cmdInsert.Connection = cnJetDB;
         cmdInsert.Parameters.Add(new OleDbParameter("Description", OleDbType.VarChar, 40, "Description"));
         oleDa.InsertCommand = cmdInsert;

         // Create a DataTable
         DataTable dtTest = new DataTable();
         oleDa.Fill(dtTest);

         DataRow drTest;

         // Add Rows to the Table
         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 1";
         dtTest.Rows.Add(drTest);

         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 2";
         dtTest.Rows.Add(drTest);
      }
	}
}
  6. Modify the Data Source name in the connection string to point to your Access database.
back to the top

Trap the Identity Column Value

In the RowUpdated event of the DataAdapter, you can trap the identity column value that is generated for a column of a Table in an Access database. In the RowUpdated event, run the SELECT @@IDENTITY query by using another Command object, and then assign the value that is returned by the query to the Identity column. Call the AcceptChanges method of the DataRow object to accept the column value. To do this, follow these steps:
  1. Add the following code before the Main method to create a second OleDbCommand object for the SELECT @@IDENTITY query:
          // Create OleDbCommand for SELECT @@IDENTITY statement
      private static OleDbCommand cmdGetIdentity;
  2. Add the following code to the Main method to create a new instance of the OleDbCommand class:
             // Create another Command to get IDENTITY Value
         cmdGetIdentity = new OleDbCommand();
         cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
         cmdGetIdentity.Connection = cnJetDB;
  3. Add the following code to Main method to handle the RowUpdated event:
             // Delegate for Handling RowUpdated event
         oleDa.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated);
  4. Add the following code to the Main method to update the data. The RowUpdated event is raised after calling the Update method: 
             // Update the Data
         oleDa.Update(dtTest);
  5. Add the following code to Main method to drop the AutoIncrementTest table and release the resources:
             // Drop the table
         cmdJetDB.CommandText = "DROP TABLE AutoIncrementTest";
         cmdJetDB.ExecuteNonQuery();

         // Release the Resources
         cmdGetIdentity = null;
         cmdInsert = null;
         cmdJetDB = null;
         cnJetDB.Close();
         cnJetDB = null;
  6. Add the following RowUpdated event handler code to Module1: 
          // Event Handler for RowUpdated Event
      private static void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
      {
         if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert )
         {
            // Get the Identity column value
            e.Row["ID"] = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
            System.Diagnostics.Debug.WriteLine(e.Row["ID"]);
            e.Row.AcceptChanges();
         }
      }
  7. On the Debug menu, click Start to run the application. Identity column values are displayed in the Output window.
back to the top

Complete Code Listing

using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication30
{
	class Class1
	{
      // Create OleDbCommand for SELECT @@IDENTITY statement
      private static OleDbCommand cmdGetIdentity;

      [STAThread]
      static void Main(string[] args)
      {
         // Open Connection 
         OleDbConnection cnJetDB = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDatabase");
         cnJetDB.Open();

         // If the test table does not exist then create the Table
         string strSQL; 
         strSQL = "CREATE TABLE AutoIncrementTest " +
            "(ID int identity, Description varchar(40), " +
            "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))";

         // Command for Creating Table
         OleDbCommand cmdJetDB = new OleDbCommand(strSQL, cnJetDB);
         cmdJetDB.ExecuteNonQuery();

         // Create a DataAdaptor With Insert Command For inserting records
         OleDbDataAdapter oleDa = new OleDbDataAdapter("Select * from AutoIncrementTest", cnJetDB);


         // Command to Insert Records
         OleDbCommand cmdInsert = new OleDbCommand();
         cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)";
         cmdInsert.Connection = cnJetDB;
         cmdInsert.Parameters.Add(new OleDbParameter("Description", OleDbType.VarChar, 40, "Description"));
         oleDa.InsertCommand = cmdInsert;

         // Create a DataTable
         DataTable dtTest = new DataTable();
         oleDa.Fill(dtTest);

         DataRow drTest;

         // Add Rows to the Table
         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 1";
         dtTest.Rows.Add(drTest);

         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 2";
         dtTest.Rows.Add(drTest);

         // Create another Command to get IDENTITY Value
         cmdGetIdentity = new OleDbCommand();
         cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
         cmdGetIdentity.Connection = cnJetDB;

         // Delegate for Handling RowUpdated event
         oleDa.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated);

         // Update the Data
         oleDa.Update(dtTest);

         // Drop the table
         cmdJetDB.CommandText = "DROP TABLE AutoIncrementTest";
         cmdJetDB.ExecuteNonQuery();

         // Release the Resources
         cmdGetIdentity = null;
         cmdInsert = null;
         cmdJetDB = null;
         cnJetDB.Close();
         cnJetDB = null;
      }

      // Event Handler for RowUpdated Event
      private static void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
      {
         if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert )
         {
            // Get the Identity column value
            e.Row["ID"] = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
            System.Diagnostics.Debug.WriteLine(e.Row["ID"]);
            e.Row.AcceptChanges();
         }
      }
	}
}
back to the top

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

232144 INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity

For more information, visit the following Microsoft Developer Network Web site:

OleDbDataAdapter.RowUpdated Event
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdataoledboledbdataadapterclassrowupdatedtopic.asp



back to the top
Modification Type:MinorLast Reviewed:1/23/2004
Keywords:kbTSQL kbJET kbHOWTOmaster kbhowto kbSystemData KB816112 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.