How to stop my coldfusion Cfquery from insertering duplicate rows?

Question

I have a very basic cfquery insert that is inserting my form information 3 times on submit.

Anyone know how to stop this?

Here is my form.

And my page that the form is sent to contains the following cfquery :

    <cfquery datasource="OSAnet-College" result="qDonate">
        INSERT into V2_OnlineDonations(FNAME,LNAME,PHONE,EMAIL,CAUSE,DATEDONATE)
        VALUES (
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.FNAME#">
        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.LNAME#">
        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.PHONE#">
        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.EMAIL#">
        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.CAUSE#">
        , <cfqueryparam cfsqltype="cf_sql_timestamp" value="#NOW()#">
        )
    </cfquery>

Any help would be appreciated.

Consider an SQL Merge statement. That way it will do an INSERT or UPDATE as needed. See: http://stackoverflow.com/questions/14806768/sql-merge-statement-to-update-data — James A Mohler, Dec 15 '16 at 18:35
Would this be added in addition to my insert query or as a replacement? I'm having trouble seeing how this would be used. — Matthew Johnson, Dec 15 '16 at 19:17
Need to see a bit more code like the cfc function that is being executed and how the function is being called. The fiddle link currently generates a bunch of errors. — snackboy, Dec 15 '16 at 19:30
My thinking is that you really can't control what data users will put into the form. Hence make the DB interaction smarter. The MERGE has an INSERT section that would server as the replacement for the plain insert. — James A Mohler, Dec 15 '16 at 19:36
I updated the fiddle to simplify it and removed the link to my own testing page in it's form post. — Matthew Johnson, Dec 15 '16 at 19:51
i mean... if the form is submitted three times, three records will be inserted. Did you want to prevent duplicate records regardless of whether it was on purpose or a double submit? in your case they likely aren't really duplicates, due to the use of #now()#. A constraint on email/first/last to force them to be unique would prevent dupes, no? — Kevin B, Dec 15 '16 at 19:54
Turn on debugging and run the page. How many insert queries do you see? — Dan Bracuk, Dec 15 '16 at 20:48
I only see one insert query when I do a debug. It is so strange... — Matthew Johnson, Dec 15 '16 at 21:08
When running the query the timestamp is the only thing that's different: 2016-12-15 15:21:41.493 2016-12-15 15:21:41.633 2016-12-15 15:21:41.647 — Matthew Johnson, Dec 15 '16 at 21:23
like @snackboy said, you'll need to show some more code. that query on its own isn't the problem, so it must be whatever is calling that query — luke, Dec 16 '16 at 02:21
No threads or a cfc. Just the query above in one page and the jsfiddle of my form on another page. — Matthew Johnson, Dec 16 '16 at 15:48
If you dump the qdonate variable, does it say anything about the number of records affected? — Dan Bracuk, Dec 16 '16 at 17:17
As mentioned, the query by itself isn't the problem. Most likely something is invoking it multiple times, leading to multiple records. However, hard to say what that is without a *self-contained* repro case. Please separate out the essential bits into an small, but complete [MCVE](http://stackoverflow.com/help/mcve) that reproduces the issue. — Leigh, Dec 16 '16 at 18:57
The fiddle above is not an MCVE? Is there one specific to coldfusion? — Matthew Johnson, Dec 16 '16 at 21:00
Did any of the form field values have commas in them? If so, it may be misinterpreted as a list and running the query for each item in that list. — Jules, Dec 18 '16 at 23:48
No commas, but curiously enough, if I move the same code out of my CMS and into a standalone page, I do not get this issue. — Matthew Johnson, Dec 19 '16 at 14:48
*The fiddle above is not an MCVE?* Well the idea is to isolate the code into the smallest example that reproduces the issue. If you are getting that result using ONLY the plain html form and query code posted, then yes it would be complete. However, nothing in that code would cause the issue you described under normal circumstances. So there must be something more in the code, your application, environment, etc... than we are aware of ... — Leigh, Dec 19 '16 at 15:54
Thanks Leigh, the code is within our MURA CMS site, I moved the processing code where the cfquery is to a blank page and all seems to be well now. It appears the issue is with the CMS. — Matthew Johnson, Dec 19 '16 at 16:28
I'm voting to close this question as off-topic because the OP has discovered that the problem lies in his CMS, not with his ColdFusion code. — Dan Bracuk, Dec 19 '16 at 18:34

Xavier L. · Accepted Answer · 2016-12-22T16:20:32.670

try something like this:

<cfquery datasource="OSAnet-College" result="qDonate">
IF 
(
    NOT EXISTS
    (
        SELECT * FROM V2_OnlineDonations 
        WHERE FNAME = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.FNAME#">
        AND LNAME = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.LNAME#">
        AND PHONE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.PHONE#">
        AND EMAIL = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.EMAIL#">
        AND CAUSE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.CAUSE#">
    ) 
)
BEGIN 
    INSERT into V2_OnlineDonations(FNAME,LNAME,PHONE,EMAIL,CAUSE,DATEDONATE)
    VALUES (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.FNAME#">
    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.LNAME#">
    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.PHONE#">
    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.EMAIL#">
    , <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.CAUSE#">
    , <cfqueryparam cfsqltype="cf_sql_timestamp" value="#NOW()#">
    )
END
</cfquery>

How to stop my coldfusion Cfquery from insertering duplicate rows?

1 Answers1