Avoid Duplicate Inserts

[wmanu]

Hi All,

I?m trying to do a normal INSERT in to a SQL server database. I take the
values from a form and pass it on to another page and calling a stored
procedure, which does the inserts (only one ISERT statement). I submit the page
only once but two records got inserted with the same values. I have the same
date time value for both rows and both are same, even the seconds are same.

Why is it inserting multiple records with same values in single click? How do
I avoid it? Please advice. Any help is greatly appreciated.

Many thanks / Manu.

[wmanu]

Hi All,

I?m trying to do a normal INSERT in to a SQL server database. I take the
values from a form and pass it on to another page and calling a stored
procedure, which does the inserts (only one INSERT statement). I submit the
page only once but two records got inserted with the same values. I have the
same date time value for both rows and both are same, even the seconds are
same.

Why is it inserting multiple records with same values in single click? How do
I avoid it? Please advice. Any help is greatly appreciated.

Many thanks / Manu.

[pukkel]

Well, it does sound very much like you are unaware of the fact that you double
click the submit button... I encountered a similar situation once and after a
long time of debugging I found out it happened because of users who doubleclick
the submit button. But that was with ASP.. I never saw it with CF.

Could you show the code, so we can take a look...

[wmanu]

Hi,

Thanks for your input. Unfortunately I do not have the exact code at the
moment but will get you tomorrow. For your reference, its just a normal form
with text fields and on submit it goes to another page, which calls the SP and
do the insert and then CFLOCATION to another page where all the records are
displayed. I hope this make sense.

I?m aware that F5 or more than one submit can create duplicate rows. So I?m
not doing them and I?m the only one who operates these pages. If I try
inserting from faster machine it works well, I mean no rows are getting
duplicated.

But if I try from my laptop, which is a bit slow (AMD850, 256RAM), it adds 2
records with same data almost all the time. Even in that one or two records got
inserted with out duplication.

I experienced it several times; only now I took a chance to discuss with you
guys.

Thanks a million for your time. Any help is greatly appreciated.

Thanks / Manu.

[OldCFer]

Just curious, but if it's a simple insert why are you using a SP?

[xcoldnet]

I have the same problem with my code, how it is possible to avoid more then one
entry of the same record?

I would also add this second scenario, a user will try to add an identical
record later in time for the simple reason the stress my application... in this
case how do I prevent double entries?

I shold run a query first the check if the same record I want to enter is
already present but I have no idea of the code. Any help will be appreciated.

Thank you,

Dario

[wmanu]

Hi Larry,

Thanks for your input on this. Yes, I confirm that this is a simple insert. I
know it is not necessary to have a SP for a single insert. But the way I?m
doing is, all database operations are done through SP?s.

Something wrong in doing that? But even when I insert through CF, without SPs,
it inserts two records. Please advice.

Thanks / Manu.

[pukkel]

No, there is nothing wrong with using SP's for all your transactions... It's
quite a good idea, to centralize your queries. I would go for grouping them in
CFC's but so many people so many opinions, right? :)
But could you show the code so we can take a look at it, maybe there is
something wrong with the insert statement.. (I doubt it, but you never know)

[wmanu]

Hi All,

Please find the code attached. I tried the same code from official PC and it
inserting only one record but if I do this from my laptop (slower) it inserting
two records. Any help is greatly appreciated.

Many thanks for your time.


User Input Form

<TABLE CELLPADDING="#SUBCELLPADDING#" CELLSPACING="#SUBCELLSPACING#"
BGCOLOR="#SUBBGCOLOR#">
<FORM NAME="AddProduct" METHOD="post">

<TR BGCOLOR="FFFFFF"><TD>Product Name</TD>
<TD><INPUT TYPE="Text" NAME="Name" SIZE="30" MAXLENGTH="30" <CFIF
IsDefined('Name')>VALUE="#Name#"</CFIF>></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Price Full</TD>
<TD><INPUT TYPE="Text" NAME="PriceFull" SIZE="10" MAXLENGTH="10" <CFIF
IsDefined('PriceFull')>VALUE="#PriceFull#"</CFIF>></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Price Loose</TD>
<TD><INPUT TYPE="Text" NAME="PriceLoose" SIZE="10" MAXLENGTH="10" <CFIF
IsDefined('PriceLoose')>VALUE="#PriceLoose#"</CFIF>></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Stock Full</TD>
<TD><INPUT TYPE="Text" NAME="StockFull" SIZE="10" MAXLENGTH="10" <CFIF
IsDefined('StockFull')>VALUE="#StockFull#"</CFIF>></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Stock Loose</TD>
<TD><INPUT TYPE="Text" NAME="StockLoose" SIZE="10" MAXLENGTH="10" <CFIF
IsDefined('StockLoose')>VALUE="#StockLoose#"</CFIF>></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Sales Tax</TD>
<TD><INPUT TYPE="Checkbox" NAME="Salestax" <CFIF IsDefined('Salestax') AND
Salestax EQ 1>CHECKED</CFIF> <CFIF NOT IsDefined('Salestax')>CHECKED</CFIF>>
<FONT COLOR="GRAY">(#SalesTaxResultSet.ItemValue#%)</FONT></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Aditional Sales Tax</TD>
<TD><INPUT TYPE="Checkbox" NAME="AditionalSalestax" <CFIF
IsDefined('AditionalSalestax') AND AditionalSalestax EQ 1>CHECKED</CFIF>> <FONT
COLOR="GRAY">(#AditionalSalestaxResultSet.ItemValu e#%)</FONT></TD></TR>

<TR BGCOLOR="FFFFFF"><TD>Excise</TD>
<TD><INPUT TYPE="Checkbox" NAME="Excise" <CFIF IsDefined('Excise') AND Excise
EQ 1>CHECKED</CFIF>> <FONT
COLOR="GRAY">(#ExciseResultSet.ItemValue#%)</FONT></TD></TR>

<TR BGCOLOR="FFFFFF"><TD COLSPAN="2" ALIGN="right"><INPUT TYPE="Submit"
NAME="DoSubmit" VALUE="Add Product" ONCLICK="return Validate();"></TD></TR>

<INPUT TYPE="Hidden" NAME="CategoryID" VALUE="#IDX#">
</FORM>
</TABLE>

Calling SP for Insert

<CFTRY>
<CFSTOREDPROC PROCEDURE="PutProduct" DATASOURCE="#DSNNAME#">
<CFPROCPARAM VALUE="#Name#" DBVARNAME="@strName"
CFSQLTYPE="CF_SQL_VARCHAR">
<CFPROCPARAM VALUE="#PriceFull#" DBVARNAME="@decPriceFull"
CFSQLTYPE="CF_SQL_MONEY">
<CFPROCPARAM VALUE="#PriceLoose#" DBVARNAME="@decPriceLoose"
CFSQLTYPE="CF_SQL_MONEY">
<CFPROCPARAM VALUE="#StockFull#" DBVARNAME="@decStockFull"
CFSQLTYPE="CF_SQL_MONEY">
<CFPROCPARAM VALUE="#StockLoose#" DBVARNAME="@decStockLoose"
CFSQLTYPE="CF_SQL_MONEY">
<CFPROCPARAM VALUE="#CategoryID#" DBVARNAME="@intCategoryID"
CFSQLTYPE="CF_SQL_INTEGER">
<CFPROCPARAM VALUE="#SalesTax#" DBVARNAME="@intSalesTax"
CFSQLTYPE="CF_SQL_BIT">
<CFPROCPARAM VALUE="#Excise#" DBVARNAME="@intExcise" CFSQLTYPE="CF_SQL_BIT">
<CFPROCPARAM VALUE="#Vat#" DBVARNAME="@intVat" CFSQLTYPE="CF_SQL_BIT">
<CFPROCPARAM VALUE="#AditionalSalesTax#" DBVARNAME="@intAditionalSalesTax"
CFSQLTYPE="CF_SQL_BIT">
</CFSTOREDPROC>
<CFSET STATUS = 1>

<CFCATCH>
<CFSET STATUS = 0>
</CFCATCH>

</CFTRY>


Stored Procedure for doing INSERT

CREATE Proc PutProduct
@strName Varchar(40),
@decPriceFull Float(8),
@decPriceLoose Float(8),
@decStockFull Float(8),
@decStockLoose Float(8),
@intCategoryID Int,
@intSalesTax Int,
@intAditionalSalesTax Int,
@intExcise Int,
@intVat Int

As

Begin
INSERT INTO Products ( Name,
CategoryID,
PriceLoose,
SalesTax,
AditionalSalesTax,
Excise,
Vat,
PriceFull,
Created,
Updated,
Status,
UserID
)
VALUES ( @strName,
@intCategoryID,
@decPriceLoose,
@intSalesTax,
@intAditionalSalesTax,
@intExcise,
@intVat,
@decPriceFull,
GetDate(),
GetDate(),
1,
1
)

DECLARE @ProductIDX Int
SET @ProductIDX = SCOPE_IDENTITY()

INSERT INTO ProductStock ( IDX,
StockFull,
StockLoose
)
VALUES ( @ProductIDX,
@decStockFull,
@decStockLoose
)
End


GO

[pukkel]

On the laptop you're using the same browser? There are no javascript errors of
some sort? (what does that return Validate() do?)
And, which record gets inserted twice, the product or the productstock?

[wmanu]

Hi,

Thanks again for your input on this.

I?m using the same settings for my PC and Laptop with Windows XP & IE6. Even
the software installed is the same. The only difference is the Hardware
configuration.

There is no JavaScript error on any of the page, function Validate() is just
checking whether all the fields have been entered or not. If all entered then
submit the form or return an error message.

Both Products and Stock are getting inserted twice through Laptop, even the
seconds are the same.

Many thanks for your time.

Regards / Manu.

[pukkel]

Are you really sure the form is not submitted twice?
And does the same behaviour take place when you use EXEC inside a cfquery tag instead of the cfstoredproc? Maybe it's some form of caching..

[wmanu]

Hi,

Your input on this is much appreciated. As far as I know, I?m clicking only
once. If you EXEC the SP, it inserts only one record. The problem occurs when
we try to insert through a form from a low performance system.

I?m using <META HTTP-EQUIV="Pragma" CONTENT="no-cache">on every page to avoid
caching.

I experienced this problem many times in different occasions. Is this
incurable disease?

Regards / Manu.

[pukkel]

Hmm I never seen anything like this happening... My clues are running out.
Maybe this could shed some light on things:
[url]http://coldfusion.sys-con.com/read/41757.htm[/url] (scroll down to "A Bug")..
That article states (in short) that if there are problems with variables sent
to a stored procedure (in this case through cfprocparam) that could lead to an
error, which in turn could lead to a second insert without rolling back the
first insert.
And I see one thing in regard to this with your cfstoredproc call: the
cfstoredproc tag does not send the variables in the order which SQLServer
expects them.
But then again, this all doesn't cover the situation where the query only
flaws on a low performance system.
To that question I think I don't have an answer..

[xcoldnet]

I have sorted this problem on my site in this way:

<cftransaction>

if record is already present... do nothing, or display a message

else.. insert the record

</cftransaction>

In this case if there is a double submission for any reason, it is only
inserted once.

IHTH,

Dario

[mxstu]

I agree with xcoldnet. I always use some type of if exists (.... ) logic and if the record exists I don't insert it. It avoids having to worry about double form submissions.

[wmanu]

Hi All,

Sorry for the delay in response. I rectified the problem by implementing Dario?s solution, with IF EXISTS (?). Thanks a million for all your time and help.

Many thanks / Manu.