The Final Guide

The Microsoft Access Database provides AutoNumber fieldtype which is generated automatically when a record is added in such tables which contains AutoNumber. We can fetch the auto incremented value from MS-Access database to our applications using built in function @@IDENTITY( ).

The Jet 4.0 provider supports @@Identity( ) to obtain new Identity value from Access .mdb file which returns the value of an autoincrement column that is generated on the same connection. That means the Connection object used for the Insert query must be re-used without closing it and opening it up again to fetch the Identity value as shown in the below example.

[C#]
string strSQL = "INSERT INTO PRODUCTS (PRODUCT_NAME, PRODUCT_MODEL) VALUES (@PRODUCT_NAME,@PRODUCT_MODEL)";
int intID;
string strAcsCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|MyDb.mdb";
using (OleDbConnection dbCon = new OleDbConnection(strAcsCon))
{
     dbCon.Open( );
     using (OleDbCommand Cmd = new OleDbCommand())
     {
          Cmd.Connection = dbCon;
          Cmd.CommandType = CommandType.Text;
          Cmd.CommandText = strSQL;
          Cmd.Parameters.AddWithValue("@PRODUCT_NAME", strProdName);
          Cmd.Parameters.AddWithValue("@PRODUCT_MODEL", txtProdModel);
          Cmd.ExecuteNonQuery( );
          strSQL = "SELECT @@IDENTITY";
          Cmd.CommandText = strSQL;
          intID = (int)Cmd.ExecuteScalar( );
     }
     dbCon.Close( );
}

The above built-in function @@IDENTITY( ) can be used to retrieve Identity value from SQL Server too with the same above coding.

You may also like to read ASP.Net FileUpload control inside Ajax UpdatePanel ASP.Net connect to database using HttpHandler ASHX file ASP.Net connect to database via AJAX using JQUERY How to create Pdf file in ASP.Net Date Formatting in ASP.Net Fetch the incremented Identity value from MS-SQL Server Error: The Operation must use an updatable query Highlight ASP.Net Gridview Row on Mouse Hover

This entry was posted on Wednesday, June 10th, 2009 at 3:28 pm and is filed under ASP.Net, Web. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.