Getting AutoNumber from Access via "SELECT @@IDENTITY" needs to be done in same connection as the INSERT.
There are a few documents out there describing how to get the newly inserted AutoNumber in an Access table.
See for example,
"Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)"
https://msdn.microsoft.com/en-us/library/bb384432.aspx
"Retrieving Identity or Autonumber Values (ADO.NET)"
https://msdn.microsoft.com/en-us/library/ks9f57t0.aspx
"HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET"
https://support.microsoft.com/default.aspx?scid=kb;en-us;815629
"Tackle Data Concurrency Exceptions Using the DataSet Object"
https://msdn.microsoft.com/en-us/magazine/cc188748.aspx
However, what some people seems to have missed or do not know is that this must be done on the same connection that executed the insert.
So, to see this, simply follow the steps below:
1. Create a new Access Database (Test.mdb) with a table in it (TestTable) that has two columns "ID" (which is AutoNumber) and "OurTxt" (which is Text).
2. Create a new console application.
DON'T FORGET TO CHANGE "Any CPU" to x86 if running on a x64 machine. Otherwise you will get the:
“System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.”
See for example:
"System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
3. Set the code to be:
class Program
{
static string path = @"<your path>";
static string db = @"Test.mdb";
static void Main(string[] args)
{
string cs = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\{1}", path, db);
// Using the same connection for the insert and the SELECT @@IDENTITY
using (OleDbConnection con = new OleDbConnection(cs))
{
con.Open();
OleDbCommand cmd = con.CreateCommand();
for (int i = 0; i < 3; i++)
{
cmd.CommandText = "INSERT INTO TestTable(OurTxt) VALUES ('" + i.ToString() + "')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY";
Console.WriteLine("AutoNumber: {0}", (int)cmd.ExecuteScalar());
}
con.Close();
}
// Using a new connection and then SELECT @@IDENTITY
using (OleDbConnection con = new OleDbConnection(cs))
{
con.Open();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT @@IDENTITY";
Console.WriteLine("\nNew connection, AutoNumber: {0}", (int)cmd.ExecuteScalar());
con.Close();
}
}
}
This should produce the self-explanatory output:
AutoNumber: 1
AutoNumber: 2
AutoNumber: 3
New connection, AutoNumber: 0
Comments
Anonymous
April 29, 2015
This is the only clear and simple example in a sea of over-complicated explanations. Thank you so much!Anonymous
October 19, 2015
Can it be that simple while I have seen soo much very complicated ways..Even from Microsoft themselves......!!! And... It works like a charm......... Thanks a lot!!