SCOPE_IDENTITY won't return correct value, but @@IDENTITY does

[Hardy Wang]

Hi all,
I have the following codes, but SCOPE_IDENTITY() just returns NULL to
me. If I comment out SCOPE_IDENTITY() line and run @@IDENTITY line, it works
fine!! Since I have a trigger on the table, I have to use SCOPE_IDENTITY().
Any ideas?

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

//Create the dataadapter
SqlDataAdapter da = new SqlDataAdapter();

//Assign the connection & Create and execute the Insert Command
da.InsertCommand = new SqlCommand("insert into table1......");
da.InsertCommand.Connection = conn;

da.InsertCommand.ExecuteNonQuery();

//Create,assign and Execute the Identity statement
da.SelectCommand = new SqlCommand("SELECT SCOPE_IDENTITY()",
da.InsertCommand.Connection);
//da.SelectCommand = new SqlCommand("SELECT @@IDENTITY",
da.InsertCommand.Connection);
int intID = Convert.ToInt32(da.SelectCommand.ExecuteScalar());
conn.Close();
return(intID);

 

[Patrice]

IMO the problem is that you are running those two statement separately i.e.
the global @@IDENTITY still returns a (wrong) value but SCOPE_IDENTITY is
not in the same scope and returns NULL.

Sending INSERT/SELECT in the same round trip should solve the problem...

 

[Hardy Wang]

If I drop trigger, @@IDENTITY returns the correct value.

I think I re-use the same connection to run SCOPE_IDENTITY by
da.SelectCommand = new SqlCommand("SELECT SCOPE_IDENTITY()",
da.InsertCommand.Connection). I pass the existing connection object back to
this command again, in this case are these two SQL statements in the same
scope?

 

[Norman Yuan]

Some points here:

1. What are you trying to do with the DataAdapter here? DataAdapter is used
as bridge between DataSet(DataTable) and data store. If you simply want to
execute some SQL code (dynamic SQL or store procedure), just use SqlCommand,
no need to put Command into a DataAdapter.

2. From what I saw, you are inserting record and then want to get the
record's ID, which is Identity column in database. Since you use SQL Server,
the better approach is to use SqlCommand to pass parameters to a stored
procedure, which uses those parameters to insert a new record and return the
record ID. This way, your app only need one round trip to the Sql Server,
instead of two as your code does, one for inserting and one for retrieving
(and retriving possible wrong ID, as you are experiencing).

3. SCOPE_IDENTITY(), as its name implies, only gets the newly generated ID
in certain scope. In your case, you try to call it in a seperate trip to the
SQL Server, of course you get nul, because the inserting execution has been
done in previour trip and out of SCOPE already.

4. Yes, @@Identity MAY give you correct ID, as you have seen. But it is not
reliable, especially in you case (tow seperate trips to get the ID), because
after the execution of inserting and before your next call to @@Identity,
other user may also insert a new record. If so, your second trip to call
@Identity will definitely give you wrong ID.

So, as I mentioned, you'd better use SP to do the inserting and returning ID
in one shot, like this

CREATE PROCEDURE InsertNewRecord
(
@ID int OUTPUT
@Col1 ..
@Col2..
...
)
AS
INSERT INTO TheTable (Col1,Col2...) VALUES (@Col1,@Col2...)
SET @ID=SCOPE_IDENTITY()
RETURN

The in your app, you just create a SlqCommand and populate its Parameters
collection and call Command.ExcuteNonQuery(). Then retrieve ID from output
parameter:

int ID=(int)cmd.Parameters["@ID"].Value;

 

[Patrice]

Yes, @@IDENTITY is global but takes always the very last ID (i.e. this is
the one used by your trigger).

It just means you run them on the same connection. The "scope" is likely
narrower than that (and in particular IMO the scope doesn't cross batches).
Have you tried my suggestion ?

 

[Hardy Wang]

I appended ";Select Scope_Identity()" to end of my insert statement, and
ran with "ExecuteScalar", then I got what I need.

Thanks a lot!

 

[Sahil Malik [MVP]]

Hey Hardy,

Yeah this is that one odd case where you may need to use @@IDENTITY instead
of SCOPE_IDENTITY (because of scope - I haven't seen your stored procs, but
I bet that is the reason).

Anyhow, just put a SET NOCOUNT OFF and SET NOCOUNT ON in your trigger, and
@@IDENTITY will give you the correct keys.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx