@@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

Marabelle

Could you clarify what database server you're using and possibly inline the references to InitializeAndOpenConnection / connection.CreateCommand as they may affect our answers to you? :) – Impermeable

Also - the whole "SELECT OID FROM x where OID = SCOPE_IDENTITY()" thins is somewhat over complex as you're saying (for a record inserted with an identity value of 3): "SELECT 3 FROM table_x WHERE 3 = 3" - kinda redundant – Impermeable

@Rob: it may seem redundant, but OID is typed int, where scope_identity() is not, so you can directly cast (int)cmd.ExecuteScalar() – Diphtheria

You're not using an Access database -- you're using a Jet database. – Becka

I definitive answer for this question would be really nice. This entire thread just seems to be filled with misinformation and solutions for SQL Server - not the Jet engine. – Hixon

Answer 1

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.

Diphtheria

The reason why is because otherwise you're issuing two commands, and the second command knows zilch about the first command and so whilst you have created an IDENTITY value you're not getting it out of the second (disconnected) command. – Grille

This gives me the "Characters found after end of SQL statement" error – Marabelle

just use "SELECT SCOPE_IDENTITY()" after the first statement. see my link below for a good explanation of this. – Footprint

-1 The database is Jet/ACE so this answer is completely invalid. SQL is a generic tag. – Bangs

@Remou have a look at the history: the tags were added after my answer, so I updated my answer. Your comment may not be "completely invalid", but at least 3 years late. – Diphtheria

From my own experience, Stackoverflow is a "living" site, and a correct answer that has subsequently become incorrect gets down voted. I am glad I have drawn you attention to your post, because it is by far the highest voted and could have caused some confusion in anyone looking for an answer to the problem stated and as presently tagged. You have got 11 votes for an answer that had become incorrect. That is pretty good going and not something that happens often for correct answers in the Access tag :) – Bangs

Answer 2

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.

Twentytwo

After having the same problem I've come to the same conclusion. All other "solutions" in the other answers are simply not applicable to Access/Jet. – Unlookedfor

Answer 3

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.

Footprint

Answer 4

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.

Chemosphere

Answer 5

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

Hyponasty

Answer 6

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.

Basting

I think you should be using SCOPE_IDENTITY as it returns the last identity value generated for any table in the current session and the current scope so if it is called just after the insert to you table you are guaranteed to get the proper value. – Zobias

With IDENT_CURRENT although you specify the table name you do not guarantee that it is within the scope of your operation, see definition: "Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope" – Zobias

I think either has its drawbacks then. The chances of getting "another" identity from the same table would be similar to getting an identity from another table in the same scope. Either is better than @@Identity. – Basting

Hi Matt,if you call "insert myTable" then "scope_identity" you know it concerns myTable and you are guaranteed that it is within the current scope. When calling IDENT_CURRENT('myTable') you are not guaranteed the scope so it is outside you control. Would be nice to have scope_identity('myTable') :-) – Zobias

Do not use Ident_current. One of our programmers did this and caused havok in the database as concurrent processses caused related tables to contain records related to the wrong parent. Please change to scope_identity. – Rutter

Answer 7

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.

Kegan

Answer 8

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"

Morphology

Doh, just read that you were using MS Access, and not SQL Server. Not sure if my answer applies to MS Access... – Morphology

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.

Dysgenics

"MS Access 2000 and later doesn't support @@IDENTITY" -- you've got that the wrong way round. @@IDENTITY is only supported from MS Access 2000 onwards. The OP said they are using Jet 4.0, which first shipped with MS Access 2000. – Chemosphere

Most answerers aren't forgetting that - the fact that the target database engine was Access wasn't stated until after a fair few answers had been made :) – Impermeable

The moral of the story: don't answer questions at 6 a.m. – Dysgenics

The database engine is not Access, as Access is not a database engine, but a database application development platform that ships with a default db engine, i.e., Jet. – Becka

Answer 10

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();
}   

Briquet

Answer 11

The short answer:
1. Create two commands each accepting a single query.
2. First sql query is the INSERT record.
3. Second sql query is "SELECT @@Identity;" which returns the AutoNumber.
4. Use cmd.ExecuteScalar() which returns a first column of first row.
5. The returned result output is the AutoNumber value generated in the current insert query.

It is referenced from this link. The example code is as under. Note the difference for "SAME Connection VS NEW Connection". The SAME Connection gives the desired output.

 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 <br>
AutoNumber: 2 <br>
AutoNumber: 3 <br>

New connection, AutoNumber: 0

Cuffs

Answer 12

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.

Impermeable

Answer 13

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

Phlebotomize