SELECT @@IDENTITY in Access always returning 0

Question

I have been trying to find a solution to this problem but so far nothing worked.

private void Insert()
    {
        string ConnectionStringAccess = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=###Jet OLEDB:Database Password=###;
        string query2 = "Select @@Identity";

        int id = -1;

        string Query = "INSERT INTO tblTable (EmpNo, Name) VALUES (132, 'TestName');";

        OleDbConnection con = new OleDbConnection(ConnectionStringAccess);

        OleDbCommand cmd = new OleDbCommand(Query, con);
        try
        {
            con.Open();
            if (cmd.ExecuteNonQuery() == 1)//the insert succeded
            {
                cmd.CommandText = query2;
                id = Convert.ToInt32(cmd.ExecuteScalar());
            }
        }
        catch (Exception ex)
        {
            //log the ex
        }
        finally
        {
            con.Dispose();
            con.Close();
        }
    }

Each time I use the above method I always get a return of 0 in "id". What am I doing wrong? I tried using a different connection string or another way to get latest identifier:

• Provider=Microsoft.Jet.OLEDB.4.0;
• SCOPE_IDENTITY()

but again nothing. The Access db is 2003 or older (not sure exactly).

Coagulum

Answer 1

The ms access db is 2003 or older (not sure exactly)

I was able to recreate your issue with an Access 97 database. SELECT @@IDENTITY worked correctly with an Access 2000 database file (even when run from the same OleDbCommand object as the INSERT), but it always returned zero when run against an Access 97 database.

It appears that you will need to upgrade your database file to a newer version if you want SELECT @@IDENTITY to work.

Polygnotus

Well done, Gord. Microsoft confirms "This feature works with Jet 4.0 databases but not with older formats." – Inkblot

Answer 2

Create two different commands for your queries, execute non query then execute scalar. It will return the first column of the first row in the result set returned by the query and it should be the id you're looking for.

private void Insert()
{
    string ConnectionStringAccess = Provider=Microsoft.ACE.OLEDB.12.0;Data Source=###Jet OLEDB:Database Password=###;

    int id = -1;

    string Query = "INSERT INTO tblTable (EmpNo, Name) VALUES (132, 'TestName')";
    string Query2 = "SELECT @@Identity";

    OleDbConnection con = new OleDbConnection(ConnectionStringAccess);

    OleDbCommand cmd = new OleDbCommand(Query, con);
    OleDbCommand cmd2 = new OleDbCommand(Query2, con);
    try
    {
        con.Open();
        cmd.ExecuteNonQuery();
        id = (int)cmd2.ExecuteScalar();
    }
    catch (Exception ex)
    {
        //log the ex
    }
    finally
    {
        con.Dispose();
        con.Close();
    }
}

Catechize

No, that won't work with Access. The Access Database Engine does not support multiple statements in a single SQL string. – Polygnotus

@GordThompson Yes, you're right, I corrected my answer, thanks – Catechize

Answer 3

You are using the same command object for both the insert and retrieval of @@identity.

According to this article you should create a separate command object for retrieving the @@identity value:

http://support.microsoft.com/kb/815629

Also, just to verify, the table you are inserting to does have an auto increment column, is that correct? If not, @@identity would not return anything.

Ceremonial

Answer 4

thanks for all the responses. I found out what the problem was. Apparently the access file is very old, 1997 to be exact and that was the problem. As soon as a tried a new access 2010 file it worked.

Thanks again

Coagulum

Answer 5

My soluce with my very older databases (VB6 and ACCESS) With VB NET and before upgrade Database to 4.

'MyInsertCommand.CommandText = "Select @@Identity" Don't work with old Access database

MyInsertCommand.CommandText = "SELECT TOP 1 ME_idn FROM MESURE ORDER BY ME_idn Desc"

Dim MyInsertIdn As Integer = MyInsertCommand.ExecuteScalar()

Mab