473,836 Members | 1,407 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy
Nov 17 '05 #1
6 6532
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*******@hotm ail.com> a écrit dans le message de
news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

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

Nov 17 '05 #2
If I drop trigger, @@IDENTITY returns the correct value.

I think I re-use the same connection to run SCOPE_IDENTITY by
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.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******** ******@tk2msftn gp13.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*******@hotm ail.com> a écrit dans le message de
news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

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


Nov 17 '05 #3
Some points here:

1. What are you trying to do with the DataAdapter here? DataAdapter is used
as bridge between DataSet(DataTab le) 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_IDENT ITY()
RETURN

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

int ID=(int)cmd.Par ameters["@ID"].Value;
"Hardy Wang" <ha*******@hotm ail.com> wrote in message
news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

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

Nov 17 '05 #4
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*******@hotm ail.com> a écrit dans le message de
news:O0******** ******@TK2MSFTN GP12.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.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.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******** ******@tk2msftn gp13.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*******@hotm ail.com> a écrit dans le message de
news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

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



Nov 17 '05 #5
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******** ******@TK2MSFTN GP10.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*******@hotm ail.com> a écrit dans le message de
news:O0******** ******@TK2MSFTN GP12.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.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.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******** ******@tk2msftn gp13.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*******@hotm ail.com> a écrit dans le message de
> news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
>> conn.Open();
>>
>> //Create the dataadapter
>> SqlDataAdapter da = new SqlDataAdapter( );
>>
>> //Assign the connection & Create and execute the Insert Command
>> da.InsertComman d = new SqlCommand("ins ert into table1......");
>> da.InsertComman d.Connection = conn;
>>
>> da.InsertComman d.ExecuteNonQue ry();
>>
>> //Create,assign and Execute the Identity statement
>> da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
>> da.InsertComman d.Connection);
>> //da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
>> da.InsertComman d.Connection);
>> int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
>> conn.Close();
>> return(intID);
>>
>> --
>> WWW: http://hardywang.1accesshost.com
>> ICQ: 3359839
>> yours Hardy
>>
>>
>
>



Nov 17 '05 #6
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*******@hotm ail.com> wrote in message
news:eW******** ******@TK2MSFTN GP15.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(c onnectionString );
conn.Open();

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

//Assign the connection & Create and execute the Insert Command
da.InsertComman d = new SqlCommand("ins ert into table1......");
da.InsertComman d.Connection = conn;

da.InsertComman d.ExecuteNonQue ry();

//Create,assign and Execute the Identity statement
da.SelectComman d = new SqlCommand("SEL ECT SCOPE_IDENTITY( )",
da.InsertComman d.Connection);
//da.SelectComman d = new SqlCommand("SEL ECT @@IDENTITY",
da.InsertComman d.Connection);
int intID = Convert.ToInt32 (da.SelectComma nd.ExecuteScala r());
conn.Close();
return(intID);

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

Nov 17 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
7767
by: Richard Golebiowski | last post by:
I have been trying to figure this out for quite some time and cannot find any examples in VB.Net or in VB that work correctly. I am working on an application where I want the user to be able to select a peinter and printer tray to print reports out. In Crystal Reports 8.5, I can select a printer and tray and it prints correctly. I did a test report that I use to tell me the value that Crystal Reports is using for the paper source. When I...
6
3253
by: michael | last post by:
Below is a simplified version of table cell mouseover script, running separate from the HTML code, that was posted on this group yesterday: var d=document, td; function mover(){ this.style.background="red"; td.style.background="lime"; // TEST: change 1st cell via any td mouseover alert(this.td.style.index.value) // HOW TO RETURN THIS TD INDEX VALUE? }
2
1662
by: eight02645999 | last post by:
hi i use odbc to update a table in a database but i always get return value of -1 even though i tried to return an integer. the table is updated though .... sql = """ update table set column = 0 where col = "%s" select @@rowcount
3
8648
by: Oberon | last post by:
How do I deal with this? I am getting an error for each get in the Game class (see code below). In the simplified example below I have reduced this to just 3 fields, one which can be NULL. I have added 3 records to the table and ran the program but it fails with the error above. The application is supposed to create the hashtable of records as a static feature which will be permanently available to my application. To demonstrate that...
7
960
by: JerryW | last post by:
I just reinstalled .NET 2003 (after repeated attempts to get ASP.NET Web Applications to work). I first did a complete uninstall of .NET 2003, .NET Framework 1.1, and IIS. I also completely deleted c:\inetpub\wwwroot and rebooted before reinstalling. I created a new virtual directory from within IIS (e.g. MyWebTest). Then I tried to create a new Visual C# ASP.NET Web Application pointing to the location http://localhost/MyWebTest. I get...
2
1794
by: Jim Langston | last post by:
I'm a little confused. I have a class function declared like: const CItem& operator << (const std::string &sIn); Which I use like this: CItem Item; Item << "blaster01,1,0,...,0,0,20,0,0,20,eol"; At the end of the method it does:
0
2905
by: Eric | last post by:
I'm trying to run a C# web service that I ported from VS 2003 to VS 2005. I'm unable to run it, I see: Failed to execute the request because the ASP.NET process identity does not have read permissions to the global assembly cache. Error: 0x80070005 Access is denied. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
5
11880
by: kenneth6 | last post by:
int Class::function() { if(a>b) { if(c==1) { if(d==2) { return 2; }
2
7416
by: Mohammadtaqi | last post by:
warning C4715: 'Pow' : not all control paths return a value warning C4715: 'Sery' : not all control paths return a value #include "stdafx.h" #include <iostream> double Pow(double x, int n) { if (n == 0) return 1;
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9671
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10549
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6979
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5650
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5828
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4456
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.