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

Question

I have the following function that executes a query and returns true on success and false on error. No, I wanted to extend the method so that every time I start an insert request, the var insertId class contains the identifier of the last inserted row.

The problem is that insertId always 0, so somehow executeScalar() does not return an identifier.

Any ideas? Or other solutions to get the identifier of the last insert request ....

  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 must change your INSERT to return that inserted identifier to you right away (in the OUTPUT clause)! This works with SQL Server 2005 on - the OUTPUT clause is not available in SQL Server 2000 or earlier (you did not specify which version of SQL Server you are using in your question ..). Learn more about the OUTPUT clause in the MSDN online books .

Change your insert to something like:

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

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

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

and your result variable should now contain the correct, newly inserted value!

Answer 2

Try SCOPE_IDENTITY () instead of @@ IDENTITY, if that doesn't work, you can publish the table schema and insert request that you use.