Problems getting @@IDENTITY and SCOPE_IDENTITY after INSERT

>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!

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.

and I can be sure that with only that sql by itself, it's not possible.
so, please, show use the relevant asp code.

side note:
as from sql 2000, forget @@IDENTITY.
only use SCOPE_IDENTITY()

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

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.

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

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.

Sorry, that should be, when I run it with the first sql statement commented out - not the first line.

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? "

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

Changed the SQL to insert without the SET NOCOUNT or the SELECT SCOPE_IDENTITY() and it has created one row in the table.

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"

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...

Removing the semi-colons from the sql string does not have any effect - still get the same results.

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

@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?

SQL Server driver setup as a System DSN.

Is that what you mean?

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'"

Ok, done that.

It gives the same results - two rows inserted.

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...

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.

so it MUST be the code, somehow...

can you show me:
CheckForError()

I've commented out CheckForError()  in the OpenRS() function - same result.

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?

honestly, I have no idea about the why...
started searching around (even in my head/memory), but to no avail...

Why do you call it the "brute force" method?

Are there issues I should be aware of?

>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

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.

All seems to work fine now - thanks for your efforts angelIII - a rather strange problem, definitely one for the KB.