?
Solved

Problems getting @@IDENTITY and SCOPE_IDENTITY after INSERT

Posted on 2007-10-03
31
Medium Priority
?
460 Views
Last Modified: 2010-08-05
I've got an ASP page that needs to INSERT a record into a table and then use the new record ID to INSERT a record into another table.

Have used the following, which has worked just fine in the past

INSERT INTO MyTable (.......) VALUES (....)
SELECT @@IDENTITY AS NewRecordID

But NewRecordID is null after the record has been inserted.

Read somewhere that I should be doing the following:

SET NOCOUNT ON
INSERT INTO MyTable (.......) VALUES (....)
SELECT @@IDENTITY AS NewRecordID
SET NOCOUNT OFF

This appeared to run correctly.  However, having looked at MyTable, I've found that the record has been inserted twice.

If I remove the SET NOCOUNT, it is only inserted once, but the ID is null.

Have tried both the above using SCOPE_IDENTITY() instead - but same results.

What's going on here then?

I'm using an ODBC connection to SQL 2000.  No triggers.

Need to resolve this quickly - so max points.
0
Comment
Question by:devon-lad
  • 16
  • 13
  • 2
31 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20004908
>This appeared to run correctly.  However, having looked at MyTable, I've found that the record has been inserted twice.
can you show the code that you use to run this sql?

the sql, by itself, will NOT insert the record twice!
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20004918
Well, I'm assuming it is the SQL because if I remove the SET NOCOUNT from the SQL statement - not making any changes to the code - the INSERT works fine and only inserts 1 record.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20004934
and I can be sure that with only that sql by itself, it's not possible.
so, please, show use the relevant asp code.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20004938
side note:
as from sql 2000, forget @@IDENTITY.
only use SCOPE_IDENTITY()
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005038
The following If block is part of a loop, but I have inserted a simply Response.Write in the block to double check it's only being processed once.

If I use the following code, the record is inserted twice and iOrderID is set to the id of the second record.

If iOrderID = 0 Then
   sTimeStamp = Date & " " & Time
   sql = "SET NOCOUNT ON;
   sql = sql & "INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email) VALUES(" & iSID & ",'" & sTimeStamp & "','" & Request("txtEmail") & "');"
   sql = sql & "SELECT SCOPE_IDENTITY() AS NewOrderId;"
   sql = sql & "SET NOCOUNT OFF"
   OpenRS(sql)
   iOrderID = Rs("NewOrderId")
   CloseRS()
End If

If I use the following code, the record is inserted once, but iOrderID is null

If iOrderID = 0 Then
   sTimeStamp = Date & " " & Time
   sql = "INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email) VALUES(" & iSID & ",'" & sTimeStamp & "','" & Request("txtEmail") & "');"
   sql = sql & "SELECT SCOPE_IDENTITY() AS NewOrderId;"
   OpenRS(sql)
   OrderID = Rs("NewOrderId")
   CloseRS()
End If

OpenRS is a function as follows

Dim Rs

Function OpenRS(sql)
   Err.Clear      
   RsIsEmpty = False
   On Error Resume Next
   Set Rs = Server.CreateObject("ADODB.Recordset")
   Rs.Open sql, DbConnection, adOpenStatic
   CheckForError()
   If Rs.BOF And Rs.EOF Then
      RsIsEmpty = True
   Else
      Rs.MoveFirst
   End If
End Function

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005066
thanks.

ok, take the response.write of the genrated SQL, and run it in Query Analyser.
does it insert 1 or 2 rows?

if it inserts 1 rows, your code/page is called twice, even if you only see the response.write once.
if it inserts 2 rows, there MUST be some trigger on the table.
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005126
Only inserts once!?

I can't see how the page is being called twice.

Firstly from the user side - the Submit button is disabled after clicking, so the form data is only being submitted once.

Secondly, if I use the code without the SET NOCOUNT, the record is only inserted once.  So the page is obviously not called twice.

Ah...wait a minute.  When I remove the SET NOCOUNT, although it only inserts one record - the page doesn't finish processing because of a later error due to iOrderID being null.  Maybe if it continued it would actually end up inserting two records as well.

Let me just check
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005355
I have created a new page with only the folowing lines of code in it

OpenDB()
      
   sql = "SET NOCOUNT ON;INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email) VALUES(9,'" & Date & " " & Time & "','someone@somewhere.com');SELECT SCOPE_IDENTITY() AS NewOrderId"

'   sql = "INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email) VALUES(9,'" & Date & " " & Time & "','someone@somewhere.com')"
            
OpenRS(sql)

If I run it with the second sql statement commented out, it inserts two rows.  If I run it with the first line commented out, it inserts one row.
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005362
Sorry, that should be, when I run it with the first sql statement commented out - not the first line.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005672
ok, let's see:

* what is the exact version / sp of your sql server? ( select @@version )
* what is the full table structure ?

* can you try on another test table, with 0 rows in it?

can you show the contents of the table after the "single insert request, 2 rows generated? "
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005874
Version is

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

The table is very simple

Order_Id  int  4 - Primary key, Identity, auto-increment
Order_Date  datetime  8
Order_Site_Id int 4 - Foreign key
Order_Email varchar 50

Created a test table - same structure - but didn't link the foreign key.

Ran the test page for the single insert and got the following

Test_Id      Test_Date                           Test_Site_Id                   Test_Email
1                03/10/2007 13:34:43                  9                             someone@someplace.com
2                03/10/2007 13:34:43                  9                             someone@someplace.com
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005891
Changed the SQL to insert without the SET NOCOUNT or the SELECT SCOPE_IDENTITY() and it has created one row in the table.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20005915
what is the connection string?

can you try this (remove the ; from the sql)
 
   sql = "SET NOCOUNT ON   INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email) VALUES(9,'" & Date & " " & Time & "','someone@somewhere.com')  SELECT SCOPE_IDENTITY() AS NewOrderId"
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20005966
Here's the OpenDB() function

Function OpenDB()
      
   On Error Resume Next
   sConnString = "DSN=tm_order;UID=dbUserName;PWD=dbPassword"
   Set dbOrder = Server.CreateObject("ADODB.Connection")
   dbOrder.Open(sConnString)
   CheckForError(sConnString)
   Set cmdTemp = Server.CreateObject("ADODB.Command")
   cmdTemp.CommandType = 1
   Set cmdTemp.ActiveConnection = dbOrder
End Function

Having looked at this now, not quite sure what the Set cmdTemp lines are doing - wrote this code years ago - don't do alot programming these days - maybe they are superfluous?

Will try the sql string without the semi-colons...
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20006071
Removing the semi-colons from the sql string does not have any effect - still get the same results.
0
 
LVL 7

Expert Comment

by:assyst
ID: 20006846
Here as I understand you are using multiple resultset. I guess you need to call like this

OpenRS(sql)
Set RS = RS .NextRecordset()
OrderID = Rs("NewOrderId")
Also use SET NOCOUNT ON and OFF

Have a nice day


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20006909
@assyst:
please note that using the "proposed answer" should NOT be used (yet), unless you are like at least 99% sure of your answer.
which cannot be the case, as you used "I guess".
also, you have not read the question and the comments carefully enough, otherwise you would not have posted that code ...

@devon-lad:
what driver does the DSN use?
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20006948
SQL Server driver setup as a System DSN.

Is that what you mean?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20007005
yes.

ok, let's check this:

create a procedure in sql server database:

CREATE PROCEDURE dbo.test_insert
 ( @date_time varchar(100)
 , @email varchar(100)
)
AS
  SET NOCOUNT ON  
   INSERT INTO ItemOrder (Order_Site_Id, Order_Date, Order_Email)
   VALUES(9, @date_time, @email)
    SELECT SCOPE_IDENTITY() AS NewOrderId
GO

and change your asp code to :
   sql = "EXEC dbo.test_insert '" & Date & " " & Time & "','someone@somewhere.com'"

0
 
LVL 1

Author Comment

by:devon-lad
ID: 20008445
Ok, done that.

It gives the same results - two rows inserted.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20008624
next idea:
run the query profiler to trace what queries are being submitted to the database.
I must assume that the procedure / query IS submitted twice...


0
 
LVL 1

Author Comment

by:devon-lad
ID: 20008720
You're right - it is submitted twice.

Yet if I change sql to do a straight INSERT without the SET NOCOUNT and SCOPE_IDENTITY it's only submitted once.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20008852
so it MUST be the code, somehow...

can you show me:
CheckForError()
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20008916
I've commented out CheckForError()  in the OpenRS() function - same result.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20008972
ok, let's use "brute force":

   OpenDB()
   sql = "EXEC dbo.test_insert '" & Date & " " & Time & "','someone@somewhere.com'
   set RS = dbOrder.Execute(sql)

if THAT still generates 2 inserts, it DEFINITIVELY is because that line of the code is called twice, ie the page is submitted twice !

in which case, comment out any parts of the page that are "not necessary" for this code to run, including the java code to disable the button, for example. get the page code down to the strict minimum, until it works correctly.
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20009912
It worked - only one row inserted.

Have replaced stored procedure with original code - this works as well.

But why does it work where RS.Open doesn't?

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20010159
honestly, I have no idea about the why...
started searching around (even in my head/memory), but to no avail...
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20010301
Why do you call it the "brute force" method?

Are there issues I should be aware of?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20010408
>Why do you call it the "brute force" method?
>Are there issues I should be aware of?

"brute force" to try to get around the recordset.open, that might run the sql twice (although that was not what I thought...)
not in this context. it simply opens a forward-only, read-only recordset
0
 
LVL 7

Expert Comment

by:assyst
ID: 20010754
This is really an interesting topic and I do really appreciate AngelIII's dedication in getting it solved. I regret putting my comments as proposed solution. All I was looking is to HELP.
You are an expert with good attitude.
0
 
LVL 1

Author Comment

by:devon-lad
ID: 20012755
All seems to work fine now - thanks for your efforts angelIII - a rather strange problem, definitely one for the KB.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question