Using @@IDENTITY and SCOPE_IDENTITY()

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
Back
Top