• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4430
  • Last Modified:

SELECT SCOPE_IDENTITY() returns DBNull Value

Hey all,

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))
{
      connection.Open();
      command.Connection = connection;
      command.ExecuteNonQuery();

      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
SCOPE_IDENTIY VALUE:

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?

Regards,

Razzie
0
Razzie_
Asked:
Razzie_
  • 4
  • 4
  • 2
  • +2
2 Solutions
 
lengreenCommented:
Hi Razzie_,

There are no identities created in that scope so you will select nothing
SELECT SCOPE_IDENTITY()

you would need " insert into xxxx values(xxxx) SELECT SCOPE_IDENTITY()"; to get any values back



Cheers!
0
 
lluthienCommented:
you could consider using the ident_current  function,
which basically gets the last inserted item in a specific table

ident_current ( tablename )
returns the last id for THAT table.

cheers
0
 
Razzie_Author Commented:
lluthien: I can't use ident_current since that isn't bound to a session and I get wrong results if multiple users will insert values.

lengreen: from what I understand, @@IDENT works over one connection and I thought SCOPE_IDENTITY() does that also. In other words, if I use the same connection I should get a result back? So directly doing the SELECT after the INSERT would work...?

Anyway, in your example using insert into xxxx values(xxxx) SELECT SCOPE_IDENTITY() I don't I can read the result if I use two queries at once.

Any more help is appreciated,

Razzie
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HilaireCommented:
Creating a new connection creates a new scope.
You should run the select statement jsut after the insert, using the same connection object.

Creating a new conneciton is a bad habit, since it
- slows the execution
- prevents using transaction
- drops the current scope and hence prevents scope_identity usage

If you want to use fuctions to factorize your code, pass the connection object as a parameter to reuse it.

in your code you could issue a single statement as follows :

"insert into table(cols) values (values); select scope_identity"
0
 
HilaireCommented:
or (might be a better option)
create a stored procedure with an output parameter.
It will be more efficient since :
- the code is precompiled and parsed
- you can get a result whithout creating a datareader, just execute the SQLCommand and read the output parameters (faster)
0
 
HilaireCommented:
>>just execute the SQLCommand <<
with the ExecuteNonQuery method
0
 
Razzie_Author Commented:
Hilaire,

Thanks for your reply.

>> Creating a new connection creates a new scope. You should run the select statement jsut after the insert, using the same connection object

Yes, and that's exactly what I did as you can see in my code example. So shouldn't SCOPE_IDENTITY() return a value, or am I missing something? It doesn't HAVE to be a single statement, it is still the same scope, right? Why does it return a DB NULL value?
0
 
HilaireCommented:
>>that's exactly what I did as you can see in my code example<<

I can't see an insert in your code above
where do you set the commandtext  for the insert statement ?
0
 
lluthienCommented:
>lluthien: I can't use ident_current since that isn't bound to a session and I get wrong results if multiple users will insert values.

that can be solved by creating a transaction around this, imho
0
 
Razzie_Author Commented:
You're right, my bad, it's no visible in the code.
The command object is passed as a parameter to the function with the commandText already set.

Anyway, it is fixed now :) Somehow I really need to use a single statement with an output parameter, that fixed it. So I now have:

command.CommandText += "; SELECT @latestid = SCOPE_IDENTITY()";
IDataParameter param = command.CreateParameter();
param.ParameterName = "@latestid";
param.Direction = System.Data.ParameterDirection.Output;
param.DbType = System.Data.DbType.Decimal;
command.Parameters.Add(param);
command.ExecuteNonQuery();

and then I can get the ID using param.Value;

I will split points between Hilaire and lengreen for pointing me in the right direction.

THanks,

Razzie

0
 
Razzie_Author Commented:
And also, it seems like I HAVE to use an output parameter... but at least it works now :)
0
 
Anthony PerkinsCommented:
>>And also, it seems like I HAVE to use an output parameter... but at least it works now :)<<
Nope.  You can do it as lengreen suggested, however it is more efficient if you wrap it in a stored procedure.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now