@@IDENTITY after INSERT statement always returns 0

Question

I need a function which executes an INSERT statement on a database and returns the Auto_Increment primary key. I have the following C# code but, while the INSERT statement works fine (I can see the record in the database, the PK is generated correctly and rows == 1), the id value is always 0. Any ideas on what might be going wrong?

    public int ExecuteInsertStatement(string statement)
    {
        InitializeAndOpenConnection();
        int id = -1;


        IDbCommand cmdInsert = connection.CreateCommand();
        cmdInsert.CommandText = statement;
        int rows = cmdInsert.ExecuteNonQuery();

        if (rows == 1)
        {
            IDbCommand cmdId = connection.CreateCommand();
            cmdId.CommandText = "SELECT @@Identity;";
            id = (int)cmdId.ExecuteScalar();
        }

        return id;
    }
    private void InitializeAndOpenConnection()
    {
        if (connection == null)
            connection = OleDbProviderFactory.Instance.CreateConnection(connectString);

        if(connection.State != ConnectionState.Open)                 
            connection.Open();
    }

In response to answers, I tried:

public int ExecuteInsertStatement(string statement, string tableName)
    {
        InitializeAndOpenConnection();
        int id = -1;
        IDbCommand cmdInsert = connection.CreateCommand();
        cmdInsert.CommandText = statement + ";SELECT OID FROM " + tableName + " WHERE OID = SCOPE_IDENTITY();";
        id = (int)cmdInsert.ExecuteScalar();

        return id;
    }

but I'm now getting the error "Characters found after end of SQL statement"

I'm using an MS Access database with OleDb connection, Provider=Microsoft.Jet.OLEDB.4.0

Answer 1

I think you need to have the Select @@identity with the first create command - try appending it via ";SELECT @@Identity" and .ExecuteScalar the insert statement

Answer 2

Are there any triggers on your table that might be inserting into other tables? Generally we're advised against using @@Identity in favour of IDENT_CURRENT so that you can guarantee that the identity returned is for the table you just inserted into.

Answer 3

I think that @@identity is valid only in the scope of the command - in your case when you execute "statement".

Modify your "statement"so that the stored procedure itself will return the @@IDENTITY value right after the INSERT statement, and read it as the return code of the stored procedure execution.

Answer 4

1) combine the INSERT and SELECT statement (concatenate using ";") into 1 db command

2) use SCOPE_IDENTITY() instead of @@IDENTITY

INSERT INTO blabla... ; SELECT OID FROM table WHERE OID = SCOPE_IDENTITY()

-- update:

as it turned out that the question was related to MS ACCESS, I found this article which suggests that simply reusing the first command and setting its CommandText to "SELECT @@IDENTITY" should be sufficient.

Answer 5

As you're using Access, take a look at this article from aspfaq, scroll down to about half way down the page. The code's in classic ASP, but hopefully the principles should still stand.

---

The SELECT @@Identity ends up being treated as a separate execution context, I believe. Code that should work would be:

public int ExecuteInsertStatement(string statement)
{
    InitializeAndOpenConnection();

    IDbCommand cmdInsert = connection.CreateCommand();
    cmdInsert.CommandText = statement + "; SELECT @@Identity";
    object result = cmdInsert.ExecuteScalar();

    if (object == DBNull.Value)
    {
       return -1;
    }
    else
    {
       return Convert.ToInt32(result);
    }
}

You'd probably want/need to tidy up the concatenation that adds the 'SELECT @@Identity' onto the end of the code though.

Answer 6

you need to return the identity at the same time as you open the initial connection. Return a result set from your insert or an output variable.

You should also always use SCOPE_IDENTITY() not @@identity. Reference here

You should add

SELECT SCOPE_IDENTITY()

After the insert.

Answer 7

Check your database settings. I had a similar problem a while ago and discovered that the SQL Server connection setting 'no count' was enabled.

In SQL Server Management Studio, you can find this by right-clicking the server in the Object Explorer, select Properties and then navigate to the Connections page. Look at the settings for "Default connection options"

Answer 8

You are using Jet (not SQL Server) and Jet can only handle one SQL statement per command, therefore you need to execute SELECT @@IDENTITY in a separate command, obviously ensuring it uses the same connection as the INSERT.

Answer 9

Aren't most of the answerers forgetting that the asker is not using SQL Server?

Apparently, MS Access 2000 and later doesn't support @@IDENTITY. The alternative is "Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest @@IDENTITY value, and place that in the identity column of the local table in the DataSet."

And yes, this is for embedded VBA in the Access DB. That is still callable outside of Access via the Access Object Library.

Edit: ok, it is supported, sorry for the groggy early-morning answer. But the rest of this answer might help.

Answer 10

Microsoft.Jet.OLEDB.4.0 provider supports Jet v3 and Jet v4 database engines, however SELECT @@IDENTITY is not supported for Jet v3.

MSAccess 97 is Jet v3 and does not support SELECT @@IDENTITY; It supported on MSAccess 2000 and above.

Answer 11

If you would like to retrieve value of auto running number of transaction that you're inserting and your environment following 1. Database is MsAccess. 2. Driver is Jet4 with connection string like this "Provider=Microsoft.Jet.OLEDB.4.0;Password={0};Data Source={1};Persist Security Info=True" 3. use Oledb

You can apply my example to your code

OleDbConnection connection =  String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Password={0};Data Source={1};Persist Security Info=True",dbinfo.Password,dbinfo.MsAccessDBFile);
connection.Open();
OleDbTransaction transaction = null;
try{
    connection.BeginTransaction();
    String commandInsert = "INSERT INTO TB_SAMPLE ([NAME]) VALUES ('MR. DUKE')";
    OleDbCommand cmd = new OleDbCommand(commandInsert , connection, transaction);
    cmd.ExecuteNonQuery();
    String commandIndentity = "SELECT @@IDENTITY";
    cmd = new OleDbCommandcommandIndentity, connection, transaction);
    Console.WriteLine("New Running No = {0}", (int)cmd.ExecuteScalar());
    connection.Commit();
}catch(Exception ex){
    connection.Rollback();
}finally{
    connection.Close();
}

Answer 12

CREATE procedure dbo.sp_whlogin ( @id nvarchar(20), @ps nvarchar(20), @curdate datetime, @expdate datetime )

AS BEGIN DECLARE @role nvarchar(20) DECLARE @menu varchar(255) DECLARE @loginid int

SELECT @role = RoleID FROM dbo.TblUsers WHERE UserID = @id AND UserPass = @ps

if @role is not null BEGIN INSERT INTO TblLoginLog (UserID, LoginAt, ExpireAt, IsLogin) VALUES (@id, @curdate, @expdate, 1); SELECT @loginid = @@IDENTITY; SELECT @loginid as loginid, RoleName as role, RoleMenu as menu FROM TblUserRoles WHERE RoleName = @role END else BEGIN SELECT '' as role, '' as menu END END GO