Double Row Insert w Stored Proc & Command Obj

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Double Row Insert w Stored Proc & Command Obj

Author

Topic

dcunningham
Starting Member

25 Posts

Posted - 2005-09-10 : 00:22:08

I have a stored proc that is inserting new data into a table. I'm using a stored proc and previously a command obj for the record inserts. The same duplicate data is being inserted about 5 ms apart from the initial insert. Has anyone ever encountered this phenomenon before? There are no errors returned and the asp code executes to completion.

Heres the stored proc:

CREATE PROCEDURE spdac_InsertNewAccount

@CID int ,
@OPCID int ,
@UPID int ,
@PAY varchar(50),
@GUEST varchar(50) ,
@PHASE int ,
@BOARD int ,
@REC int ,
@SEND int ,
@NEW int ,
@ACT int

AS

INSERT INTO dbo.Accounts ( CustomerID_FK, OPCMemberID, UplineMemberID, PayPal_Acct, GuestCode, CurrentPhaseID_FK, CurrentBoardID_FK, RecycleInThisPhase,
SendEmailIsAllowed, CanGetANewAccount, Active )
Values ( @CID, @OPCID , @UPID , @PAY , @GUEST , @PHASE , @BOARD, @REC , @SEND , @NEW , @ACT )

return @@IDENTITY
GO

----------------------------------------------------
Here's the command object code that produced the same duplication (no, it is not in a loop)

strSQLQuery = "INSERT INTO dbo.Accounts ( CustomerID_FK, OPCMemberID, UplineMemberID, PayPal_Acct, GuestCode, CurrentPhaseID_FK, CurrentBoardID_FK, RecycleInThisPhase, SendEmailIsAllowed, CanGetANewAccount, Active ) Values ( " + Replace( TempID_PK , "'", "''") + " , " + Replace((rsNewMemCustInfo.Fields.Item("RequestedOPCMemberNumber").Value), "'", "''") + " ," + Replace(PlacementSponAccountIDPK, "'", "''") + " ,'" + Replace(Email, "'", "''") + "' ,'" + Replace( (rsNewMemCustInfo.Fields.Item("RequestedGuestCode").Value), "'", "''") + "' ," + Replace( intPhase , "'", "''") + " ," + Replace(CInt("1"), "'", "''") + " ," + Replace(CInt("0"), "'", "''") + " , " + Replace(CInt("1"), "'", "''") + " ," + Replace(CInt("0"), "'", "''") + " , " + Replace(Active, "'", "''") + " ) "

cmdAcct.CommandText = ""
cmdAcct.CommandText = "SET NOCOUNT ON; " & strSQLQuery & "; SELECT AcctID = @@IDENTITY"
Response.Write("SQL Account Insert: " & cmdAcct.CommandText & "<br>")

Set rs2 = cmdAcct.Execute
if NOT rs2.EOF Then
AcctTempID_PK = rs2.Fields.Item("AcctID").Value

Response.Write("Here's the new Account Insert Identity: " & AcctTempID_PK & "<br>")
Response.Write("<h1><font color=""red"">New Account PK ID is: " & AcctTempID_PK & "</font></h1><br>")

Any help is appreciated.. Thanks...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 07:14:16

could it be that you've got both insert into and the sproc inserting same data??
maybe you forgot to comment a line? sometimes happens to me....

oh and use scope_identity() instead of @@identity.
It's better. Read why in Books Online.

Go with the flow & have fun! Else fight the flow

dcunningham
Starting Member

25 Posts

Posted - 2005-09-10 : 09:20:36

Thanks for the reply, but no they aren't on the same page. The command object code is on a backup copy of the page before I turned to a stored proc to do the inserts. So they aren' on the same page and only stored proc code is executed by the page. Thanks you.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-10 : 10:58:03

is maybe the first page doing a redirect to the second?

Go with the flow & have fun! Else fight the flow

dcunningham
Starting Member

25 Posts

Posted - 2005-09-11 : 01:42:31

Thanks for your help so far. I solved the issue.. The page was being called twice by the credit card gateway during the silent post process.

Whew..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources