<Cfquery> insert into generates duble entry

[aijr]

Does anyone know, why an “ insert into “ could generates a double entry on a SQL table?

I use it in other routines and programs with success, but in this specific program, it’s being defiant, generating two entry with the same content.

Please, let me know if someone has the same problem or what I’m doing wrong?

I appreciate any help.

[Dan_Bracuk]

There are many possible reasons.  I try to prevent it like this:

insert into myTable

(list_of_fields)

select list_of_values

where not exists

(subquery to identify exisitng records)

[aijr · Accepted solution]

Dan,

Thanks, for your help, I'm using something similar to work around, but I'd to know why this is happening, it is true that I' m a novice here, but it is the 1st time I'm facing this kind of problem with insert. Do you have any idea what is causing this behavior?

Again thanks,

[Steve Sommers]

The query itself is not causing the issue, you need to step back and figure out what is calling this code twice. In my experience, the most likely cause is someone double clicking on a link or button and the page executing this code is being called twice.

[aijr]

As I told you, I find a way to work around, but it is a far cry from a clean solution, I will review my code paying a special attention to these points, thanks for the tips.

[Miguel-F]

Can you post the relevant query code so we can have a look?  A proper sql insert statement will not create double entries for sure.

Is your cfquery statement in a loop?

[aijr]

<cfif Parm1 eq "IncluirItem">

                <!--- Incluir Item --->

    <cfif NOT IsNumeric(Form.itQuantidade)>

                <cfset mensaErro = "Cantidade inválida">

    <cfelse>

                                <cfset wPN = Trim(Form.itPartNumber)>

        <cfquery name="qCkPN" datasource="#application.BancoDados#">

            SELECT LMItemId FROM ListamaterialItens WHERE ListaMaterialId = #wListaMaterialId# AND ItemId = '#wPN#'

        </cfquery>

        <cfif qCkPN.recordCount eq 0>

            <cfquery datasource="#Application.BancoDados#">

                INSERT INTO ListaMaterialItens(ListaMaterialId,ItemId,NumeroNoDesenho,Descricao,Quantidade,KitBasico,Observacao)

                      VALUES('#wListaMaterialId#','#wPN#','#Form.itNumeroDesenho#','#Form.itDescricao#',#Form.itQuantidade#,'#Form.itKitbasico#', '#Form.itObservacao#')

            </cfquery>

            <!--- Atualiza Tabela Lm x Obs --->

            <cfif Form.itObservacao neq "">

                                                                <!--- Incluir Observação --->

                <cfquery name="qCkId" datasource="#Application.BancoDados#">

                    SELECT Observacao FROM ListaMaterialObs

                    WHERE ListaMaterialId = #wListaMaterialId# AND (Observacao = '#Form.itObservacao#')

                </cfquery>

                <cfif qCkId.RecordCount eq 0>

                    <cfquery name="qInsObs" datasource="#Application.BancoDados#">

                        INSERT INTO ListaMaterialObs(ListaMaterialId,Observacao)

                            VALUES('#wListaMaterialId#','#Form.itObservacao#')

                    </cfquery>

                </cfif>

            </cfif>

        </cfif>

   </cfif>

</cfif>