We help IT Professionals succeed at work.

Coldfusion and Mysql database: How to prevent multiple insert of records

SonnyRey
SonnyRey asked
on
Hi, I have a web application that inserts a record in mysql table using coldfusion.
Once in a while it will do two inserts and sometimes three.  I believe it is executing
the same query twice.  How do I prevent this from happening?
Thanks.
Comment
Watch Question

Can you post your code, please? If it's not supposed to be doing this, you likely just need to modify your code.
are ou trying to do a conditional INSERT or check all the code!

scenario 1# check for conidtional INSERT IN MYSQL:

@ check this: http://www.dougboude.com/blog/1/2010/05/CONDITIONAL-INSERT-IN-A-SINGLE-QUERY.cfm

scenario 2#: like this:

<cfquery name="check">
select [columnnames Followed by Comma] from table
where column1 = value
and column2 = value
and column3 = value
and column4 = value
</cfquery>
<cfif check.recordcount>
<--- If you want to show user that Duplicates not allowed, show them here, otherwise, leave it blank>
<cfelse>
<cfquery>
INSERT Your QUERY HERE
</cfquery>
</cfif>

Come back and check

Author

Commented:
Here's the code that I use:

<cffunction name="PaidTicket" access="remote" returntype="string">
     <cfargument name="ticketID" required="yes" type="string">
     <cfargument name="accountID" required="yes" type="numeric">            
     <cfargument name="result" required="yes" type="numeric">
     <cfargument name="tellerID" required="yes" type="numeric">      
            
     <cfset DateTimeNow = #DateFormat(Now(),"yyyy-mm-dd")#&" "&#TimeFormat(Now(), "HH:mm:ss")#>
     <cfset TimeNow = #TimeFormat(Now(), "HH:mm")#>

     <cftransaction>
     <!--- get balance --->
     <cfquery name="Account" datasource="#dsn#">
          select balance
          from accounts
          where accountID = #arguments.accountID#
     </cfquery>
                                    
     <cfset newBal = account.balance + arguments.result>
     <cfset remark = #TimeNow# &" Paid Ticket " & arguments.ticketID>
      
     <!--- update balance --->
     <cfquery name="updBalance" datasource="#dsn#">
         update accounts
         set balance = #newBal#
         where accountID = #arguments.accountID#
     </cfquery>      
            
     <!--- record transaction --->
     <cfquery name="addToTransact" datasource="#dsn#">
          insert into transact
          (accountID, amount, old_bal, new_bal, remark, trantype, tellerID)
          values
         (#arguments.accountID#, #arguments.result#, #account.balance#,
          #newBal#, '#remark#', 'PD', #arguments.tellerID#)
     </cfquery>      
     </cftransaction>
</cffunction>

99% of the time the code will execute correctly.
Once in a while it would insert two records in the transact table and update the
balance twice. Sometimes it inserts three records.

Once in a while I would get this result in the transact table:

ID            DATE                 REMARK                                          AMOUNT    OLD    NEW BALANCE
27915   Jun-11-2010   23:35 Paid Ticket 4404100611164753    4,776   -631,032   -626,256    
27916   Jun-11-2010   23:35 Paid Ticket 4404100611164753    4,776   -626,256   -621,480    
27917   Jun-11-2010   23:35 Paid Ticket 4404100611164753    4,776   -621,480   -616,704

It looks like the code was executed three times because the balance was updated 3 times.
you this part do it like this:

<cffunction name="PaidTicket" access="remote" returntype="string">
     <cfargument name="ticketID" required="yes" type="string">
     <cfargument name="accountID" required="yes" type="numeric">            
     <cfargument name="result" required="yes" type="numeric">
     <cfargument name="tellerID" required="yes" type="numeric">      
     <cfset DateTimeNow = #DateFormat(Now(),"yyyy-mm-dd")#&" "&#TimeFormat(Now(), "HH:mm:ss")#>
     <cfset TimeNow = #TimeFormat(Now(), "HH:mm")#>
      <cfset var str = "">
     <cftransaction>
     <!--- get balance --->
     <cfquery name="Account" datasource="#dsn#">
          select balance
          from accounts
          where accountID = #arguments.accountID#
     </cfquery>
                                   
     <cfset newBal = account.balance + arguments.result>
     <cfset remark = #TimeNow# &" Paid Ticket " & arguments.ticketID>
     
     <!--- update balance --->
     <cfquery name="updBalance" datasource="#dsn#">
         update accounts
         set balance = #newBal#
         where accountID = #arguments.accountID#
     </cfquery>      
           
     <!--- record transaction --->
     <cfquery name="checkold" datasource="#dsn#">
          SELECT * from transact
          WHERE
          accountID = #arguments.accountID#
          AND amount = #arguments.result#
          AND old_bal = #account.balance#
          AND new_bal = #newBal#
          AND remark = '#remark#'  
          AND trantype = 'PD'
          AND tellerID = #arguments.tellerID#
     </cfquery>
     <cfif checkold.recordcount>
     <cfset str = "Error! Already Inserted">
     <cfelse>
     <cfquery name="addToTransact" datasource="#dsn#">
          insert into transact
          (accountID, amount, old_bal, new_bal, remark, trantype, tellerID)
          values
         (#arguments.accountID#, #arguments.result#, #account.balance#,
          #newBal#, '#remark#', 'PD', #arguments.tellerID#)
     </cfquery>
     <cfset str = "Cool! Processed Successfully"     >
     </cfif>
     </cftransaction>
     <cfreturn str>
</cffunction>