Using @@IDENTITY and SCOPE_IDENTITY()

G

Guest

I need to obtain an identity value from a row I just added to a table. I
would prefer to use SCOPE_IDENTITY() rather than @@IDENTITY but have found
that the only way I can get SCOPE_IDENTITY() to work is to place a SELECT
SCOPE_IDENTITY() just after the INSERT.... code of a single SQLCommand
object(and then fire ExecuteScalar). If I try to use 2 seperate SQLCommand
objects, the first doing the INSERT and the second the SELECT
SCOPE_IDENTITY(), the SCOPE_IDENTITY() returns a DBNULL every time. I've made
sure that both SQLCommand objects use the same open connection.

I don't seem to have the same problem with the @@IDENTITY, even if I use the
2 SQLCommand object technique. Each time I get the correct answer.

Can you help me with use of the SELECT SCOPE_IDENTITY() in a SQLCommand
object(s)? Must it be in a single object (with the INSERT and
SCOPE_IDENTITY() statements), sort of like a stored procedure, or can it be
made to work with 2 sequential SQLCommand objects.

Happy Holidays
 
J

Joyjit Mukherjee

Hi Michael,

I think you're issuing the SCOPE_IDENTITY() from a separate scope than the
first one, which actually INSERTS data in the table. So, you're getting a
NULL value because SCOPE_IDENTITY works within the current session & scope.

HTH

regards
Joyjit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SCOPE_IDENTITY won't return correct value, but @@IDENTITY does 6
SqlCommand which INSERTS and then SELECTs single value 2
Why isn't my SCOPE_IDENTITY() working? 3
scope_Identiy() problem vs @@Identity with daAdapter.InsertCommand 1
Using Scope_Identity with a data adapter 2
system.Transcation versus ADO transaction 1
SCOPE_IDENTITY and parameters during insert 6
Scope_Identity returning Decimal for an Integer field 16

Top