C# + SQL Server ExecuteScalar() not returning last inserted id

Question

I have the following function that executes an query and returns true on success and false on failure. No I wanted to extend the method so that with every insert query that is fired, the class var insertId contains the ID of the last inserted row.

The problem is that insertId is always 0, so somehow the executeScalar() is not returning the ID.

Any ideas? Or other solutions to get the ID of the last insert query....

    public int insertId;        

    public bool executeCommand(string q) {
        q += "; SELECT @@IDENTITY AS LastID";
        bool retour = true;
        SqlConnection myCon = new SqlConnection(Settings.DSN);
        SqlCommand cmd = new SqlCommand(q, myCon);
        try {
            cmd.Connection.Open();
            insertId = (int)cmd.ExecuteScalar();
            if (insertId > 0) {
                MessageBox.Show(insertId.ToString());
            }

            myCon.Close();
        } catch (Exception ex) {
            this.error = ex.Message;
            retour = false;
        }
        return retour;
    }

Answer 1

You should change your INSERT to return that inserted ID to you right away (in an OUTPUT clause)! This works from SQL Server 2005 on - the OUTPUT clause isn't available in SQL Server 2000 or earlier (you didn't specify which version of SQL Server you're using in your question...). Read more about the OUTPUT clause on MSDN Books Online.

Change your insert to be something like:

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)   
OUTPUT Inserted.ID
VALUES(Val1, Val2, ..., ValN);

and then when you execute your insert statement from C#, you should be able to do:

using(SqlCommand cmdInsert = new SqlCommand("INSERT.....", myCon))
{
   myCon.Open();
   var result = cmdInsert.ExecuteScalar();
   myCon.Close();
}

and your result variable should now contain the proper, freshly inserted value!

Answer 2

Try SCOPE_IDENTITY() instead of @@IDENTITY, if that doesn't work can you post the table schema and the insert query you are running.