By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,983 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,983 IT Pros & Developers. It's quick & easy.

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

P: n/a
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
Nov 17 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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...
--

Patrice

"Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
news:eW**************@TK2MSFTNGP15.phx.gbl...
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy

Nov 17 '05 #2

P: n/a
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?
"Patrice" <no****@nowhere.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
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...
--

Patrice

"Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
news:eW**************@TK2MSFTNGP15.phx.gbl...
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy


Nov 17 '05 #3

P: n/a
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;
"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:eW**************@TK2MSFTNGP15.phx.gbl...
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy

Nov 17 '05 #4

P: n/a
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 ?

--
Patrice

"Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
news:O0**************@TK2MSFTNGP12.phx.gbl...
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?
"Patrice" <no****@nowhere.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
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...
--

Patrice

"Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
news:eW**************@TK2MSFTNGP15.phx.gbl...
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy



Nov 17 '05 #5

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

Thanks a lot!

"Patrice" <no****@nowhere.com> wrote in message
news:OV**************@TK2MSFTNGP10.phx.gbl...
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 ?

--
Patrice

"Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
news:O0**************@TK2MSFTNGP12.phx.gbl...
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?
"Patrice" <no****@nowhere.com> wrote in message
news:eq**************@tk2msftngp13.phx.gbl...
> 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...
> --
>
> Patrice
>
> "Hardy Wang" <ha*******@hotmail.com> a écrit dans le message de
> news:eW**************@TK2MSFTNGP15.phx.gbl...
>> 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);
>>
>> --
>> WWW: http://hardywang.1accesshost.com
>> ICQ: 3359839
>> yours Hardy
>>
>>
>
>



Nov 17 '05 #6

P: n/a
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.ma.../13/63199.aspx
----------------------------------------------------------------------------

"Hardy Wang" <ha*******@hotmail.com> wrote in message
news:eW**************@TK2MSFTNGP15.phx.gbl...
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);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy

Nov 17 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.