In a C# project I am inserting some values in a table and immediately try to get the latest ID back.
First, I've tried it with SELECT @@IDENTITY, which works fine. However, I know that @@IDENTITY is 'unsafe' when it comes to triggers on tables etc, and although I never use those I want to give SCOPE_IDENTITY() a try anyway.
However, replacing the simple SELECT @@IDENTITY with SELECT SCOPE_IDENTITY() does not work. I always get a NULL value back.
For those of you that know C#, here's a piece of the relevant code:
using(SqlConnection connection = new SqlConnection(this.ConnectionString))
command.Connection = connection;
command.CommandText = "SELECT SCOPE_IDENTITY()";
object o = command.ExecuteScalar();
System.Diagnostics.Debug.WriteLine("SCOPE_IDENTIY TYPE: " + o.GetType().FullName);
System.Diagnostics.Debug.WriteLine("SCOPE_IDENTIY VALUE: " + o.ToString());
if(o != DBNull.Value)
// Do something }
So this will print in the debug window:
SCOPE_IDENTIY TYPE: System.DBNull
Again, if I replace it with @@IDENTITY it works just fine... what am I missing here? Does it only work within stored procedures or something?