Hi all -
I have a pretty serious issue with data duplication on form submit that I have to resolve. I'm using ColdFusion 9, and we found that the root of the problem was using javascript to submit a form on older IE browsers was double submitting, and, thus, running requests concurrently (I'm assuming) which was creating the duplicate.
We fixed the javascript, but the way I look at it, its just merely a Band-Aid for a large problem I have on my server side stuff (ColdFusion).
My current script runs something like this:
- Cfquery ---> check to see if serialized form is defined (I have a unique ID I'm passing in the form, check to see if that unique ID is defined in the database).
- CFIF recordcount of query is greater than 0
- Cfquery ----> Insert
- Cfelse
- Cfquery ----> Update
What I'm GUESSING is happening is that when the double click happens, the queries to check are happening simultaneously, and by the time the first click inserts, the second click has already checked and found zero recordcount, thus inserting a duplicate.
I've tried to do some digging on how to solve this, I'm clueless. Someone said stored procedure, someone said use IF NOT EXISTS in the SQL (but I, believe it or not, managed to generate a duplicate with this method somehow as well).
Time to ask the experts...what is the BEST way to avoid duplicate inserts when working with ColdFusion? How should I resolve?
if recordcount is gt 0 then you update - otherwise you insert.
it may be that you just worded it wrong in this post - but if not then that is a big issue!
it's hard to guess what javascript you are using and if it is problematic or not.
but as far as sql for an insert / update - you would do something like:
IF NOT EXISTS (select 1 from yourtable with (nolock) where ID = '#form.ID#')
BEGIN
INSERT INTO yourtable
(ID,someotherfield)
VALUES
('#form.ID#','#form.someot
END
ELSE
BEGIN
UPDATE yourtable
SET someotherfield = '#form.someotherfield#'
Where ID = '#form.ID#'
END
note that you may still have faulty javascript submitting the form.
did you try removing it and using a standard submit button to test if the problem is the javascript or not?