Coldfusion unexplainable duplicate issues, cannot figure this out!

Report · Dec 08, 2013

So I've been fighting this for days. I dont have the slightest idea where I can go from here. I have your standard coldfusion insert queries set up to insert new data into the database. Something along the lines of:

Select * from table where tableuniqueID = Form.UniqueID

Cfif databasecheck.recordcount EQ 0, then insert new row with form.uniqueID as the uniqueID. Well recently, its been throwing the generic Violation of PRIMARY KEY constraint 'PK__OLMS_Dat__9C04DA075F0A3F61'. Cannot insert duplicate key in object 'dbo.table'.

I dont understand that in the slightest, the only way it inserts is if the query right above it is defined and equal to 0 recordcount.

I did some tooling around, and found someone suggesting to insert into my SQL "IF NOT EXISTS" to have SQL do the actual check to see if data exists. Well we put that in place, and just recently, ANOTHER duplicate happened, same error, cannot insert duplicate showing the new SQL structure.

This is beyond me. I have two statements checking to see if it exists and, yet, Coldfusion still tries to execute a database insert. What is going on?? Any ideas? You would figure this would be the basics.

Report · Oct 25, 2014

Hello. I know it's been awhile but did you figure this out? I have a very similar thing happening. Something along the lines of

INSERT INTO WorkSheet (CODE_KEY)

SELECT TOP (1) EI.CODE_KEY

FROM EmpInfo AS EI LEFT OUTER JOIN

WorkSheet AS WS ON WS.CODE_KEY = EI.CODE_KEY

WHERE (EI.CODE_KEY = 'param1') AND (WS.CODE_KEY IS NULL)

where the field "CODE_KEY" is a the primary key in SQL Server 2008. This is similar to what you're doing except that the check for an existing value happens as part of the insert query. Just like you every once in awhile I get the "[Macromedia][SQLServer JDBC Driver][SQLServer]Violation of PRIMARY KEY constraint" error. I'd say I get one a week.

The only hint I have is that there is an insert that succeeds. This insert happens at the same time as the one that fails. That seems that the code is executing twice at the exact same time which seems impossible. In fact it seems to me that a second query would have to happen so fast that the join shows no value (otherwise I'd get records updated = 0 rather than the error) but then the first (successful) record is created prior to the 2nd one being inserted.

Report · Oct 27, 2014

The most likely scenario is that the form is submitting twice (I am gathering that from the fact that you are having CF generate the UUID in the form and submitting it as part of the process instead of letting the DB do it.)

If you are using jQuery or any other form of AJaX to submit the form, this is a fairly frequent issue.

php - Why is my form submitting twice? - Stack Overflow

My form submit is called twice (HTML, CSS and JavaScript forum at JavaRanch)

One click on a form submits twice! | Coding Forums

Another common mistake is to have both the FORM and JavaScript do the submitting (ie, the form has a submit button that when clicked calls a function for form validation that also submits the form.)

Check to make sure that none of the above scenarios applies to your situation. If that's not the case, then let us know.

HTH,

^_^

Report · Oct 31, 2014

Mine (October 25 above) gets the same error but note that the key value is not passed in as a direct "insert value [key]" but rather a a select value [key] from another table where value [key] does NOT exist within the table being inserted into. In theory even if I submitted form twice (or more) the first submit should return 1 row updated and the next one(s) 0 rows updated. Or at least that's what I'm thinking should happen. As an aside, this is a straight form submit.

Irv