Copy link to clipboard
Copied
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,
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,
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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,
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
<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>