How to get Identity value from SQL server after insert insert

Question

I am adding an entry to my database with an identity value. I want to get the id value after insert. I do not want to do this with a stored procedure.

This is my code:

 SQLString = " INSERT INTO myTable "; SQLString += " (Cal1, Cal2, Cal3, Cal4) "; SQLString += " VALUES(N'{0}',N'{1}',N'{2}',N'{3}') "; SQLString = string.Format(SQLString, Val1, Val2, Val3, Val4); SQLString += " Declare @_IdentityCode INT SET @_IdentityCode = @@IDENTITY RETURN @_IdentityCode"; int result; public int DoCommand2(string sql) { con = new SqlConnection(); cmd = new SqlCommand(); da = new SqlDataAdapter(); cmd.Connection = con; da.SelectCommand = cmd; string cs = GlobalConstants.SqlConnectionString; con.ConnectionString = cs; cmd.CommandText = sql; int i = cmd.ExecuteNonQuery(); return i; } 

but I get this error:

In this context, you cannot use the RETURN operator with a return value.

Answer 1

Add SELECT SCOPE_IDENTITY(); into your regular INSERT statement:

Replace the last concatenation with:

 SQLString += "; SELECT SCOPE_IDENTITY();" 

Then, to get the id:

 int ID = Convert.ToInt32(command.ExecuteScalar()); 

Answer 2

Take a look at the OUTPUT clause . You can infer your inserted identifier from an INSERT statement. There are examples in the link provided.

Answer 3

... or just run

 select @@identity