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).

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.