1 Topic by AD1408 (edited by AD1408 2017-07-07 00:30:47)

  • Registered: 2016-07-28
  • Posts: 1,028

Topic: Help with script please

I was trying to implement EHW's script for saving existing record as a new one.
I have one Invoice table that contains all sales, purchases, sale refunds, purchase refunds and products fields.


With the following script I get "Foreign key constraint" error?

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// -------------------- >> SAVING REFUNDS as a NEW RECORD -------------------------------------------------------------------- >>
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
procedure frmInvoice_btnInvSave_OnClick (Sender: string; var Cancel: boolean);
begin
  If (Form1Button = 'Purchase Refund') or (Form1Button = 'Sale Refund') then
    Begin
     SqlExecute('Insert into Invoice (Counter1,Counter2,Counter3,Counter4,DBCRvalue,saleInvDate,saleInvRefundDate,purchInvDate,purchInvRefundDate,'
       + 'prodCode,prodName,prodDesc,prodPrice,prodNotes,id_TaxRates,'
       + 'SIprice,SIdiscount,SInetPrice,id_TaxRates1,SItaxAmt,SIgrossTotal,SIdelivIncTax,SIinvTotal,SIRprice,SIRdiscount,SIRnetPrice,SIRtaxAmt,SIRgrossTotal,SIRdelivIncTax,SIRinvTotal,SIRrefundTypeValues,'
       + 'PIprice,PIdiscount,PInetPrice,id_TaxRates2,PItaxAmt,PIgrossTotal,PIdelivIncTax,PIinvTotal,PIRprice,PIRdiscount,PIRnetPrice,PIRtaxAmt,PIRgrossTotal,PIRdelivIncTax,PIRinvTotal,PIRrefundTypeValues,'
       + 'SIpubNotes,SIprivNotes,SIRpubNotes,SIRprivNotes,PIpubNotes,PIprivNotes,PIRpubNotes,PIRprivNotes,'
       + 'id_Customer,id_Supplier,id_CoInfo,id_delivMethod,id_payMethod,id_Priority,id_returnReason,id_returnReason1) Values ("'

       + frmInvoice.EdCounter1.Text
       + '", "' + frmInvoice.edCounter2.Text
       + '", "' + frmInvoice.edCounter3.Text
       + '", "' + frmInvoice.edCounter4.Text
       + '", "' + frmInvoice.edDBCR.Text

       + '", "' + frmInvoice.edProdCode.Text
       + '", "' + frmInvoice.edProdName.Text
       + '", "' + frmInvoice.memProdDesc.Text
       + '", "' + frmInvoice.memProdNotes.Text

       + '", "' + frmInvoice.memSIpublicNotes.Text
       + '", "' + frmInvoice.memSIprivateNotes.Text
       + '", "' + frmInvoice.memSIRpublicNotes.Text
       + '", "' + frmInvoice.memSIRprivateNotes.Text
       + '", "' + frmInvoice.memPIpublicNotes.Text
       + '", "' + frmInvoice.memPIprivateNotes.Text
       + '", "' + frmInvoice.memPIRpublicNotes.Text
       + '", "' + frmInvoice.memPIRprivateNotes.Text

       + '", ' + frmInvoice.edProdUnitPrice.sqlValue
       + ', ' + frmInvoice.cbProdTaxRate.sqlValue
       //+ ', ' + frmInvoice.dbiProdImage01.sqlDBImage
       //+ ', ' + frmInvoice.dbiProdImage02.sqlDBImage

       + ', ' + frmInvoice.dtpSIdate.sqlDateTime
       + ', ' + frmInvoice.dtpSIRdate.sqlDateTime
       + ', ' + frmInvoice.dtpPIdate.sqlDateTime
       + ', ' + frmInvoice.dtpPIRdate.sqlDateTime

       + ', ' + frmInvoice.cbSIcustName.sqlValue
       + ', ' + frmInvoice.cbPIsuppName.sqlValue
       + ', ' + frmInvoice.cbInvCoName.sqlValue
       + ', ' + frmInvoice.cbInvDelivMethod.sqlValue
       + ', ' + frmInvoice.cbInvPayMethod.sqlValue
       + ', ' + frmInvoice.cbInvPriority.sqlValue
       + ', ' + frmInvoice.cbSIRrefundReason.sqlValue
       + ', ' + frmInvoice.cbPIRrefundReason.sqlValue

                // Sale and Sale refund Invs
       + ', ' + frmInvoice.edSIprice.sqlValue
       + ', ' + frmInvoice.edSIdiscount.sqlValue
       + ', ' + frmInvoice.edSInetPrice.sqlValue
       + ', ' + frmInvoice.cbSItaxRate.sqlValue
       + ', ' + frmInvoice.edSItaxAmt.sqlValue
       + ', ' + frmInvoice.edSIgrossTotal.sqlValue
       + ', ' + frmInvoice.edSIdelivIncTax.sqlValue
       + ', ' + frmInvoice.edSIinvTotal.sqlValue

       + ', ' + frmInvoice.edSIRprice.sqlValue
       + ', ' + frmInvoice.edSIRdiscount.sqlValue
       + ', ' + frmInvoice.edSIRnetPrice.sqlValue
       + ', ' + frmInvoice.cbSIRtaxRate.sqlValue
       + ', ' + frmInvoice.edSIRtaxAmt.sqlValue
       + ', ' + frmInvoice.edSIRgrossTotal.sqlValue
       + ', ' + frmInvoice.edSIRdelivIncTax.sqlValue
       + ', ' + frmInvoice.edSIRinvTotal.sqlValue

                // Purch and Purch refund Invs
       + ', ' + frmInvoice.edPIprice.sqlValue
       + ', ' + frmInvoice.edPIdiscount.sqlValue
       + ', ' + frmInvoice.edPInetPrice.sqlValue
       + ', ' + frmInvoice.cbPItaxRate.sqlValue
       + ', ' + frmInvoice.edPItaxAmt.sqlValue
       + ', ' + frmInvoice.edPIgrossTotal.sqlValue
       + ', ' + frmInvoice.edPIdelivIncTax.sqlValue
       + ', ' + frmInvoice.edPIinvTotal.sqlValue

       + ', ' + frmInvoice.edPIRprice.sqlValue
       + ', ' + frmInvoice.edPIRdiscount.sqlValue
       + ', ' + frmInvoice.edPIRnetPrice.sqlValue
       + ', ' + frmInvoice.cbPIRtaxRate.sqlValue
       + ', ' + frmInvoice.edPIRtaxAmt.sqlValue
       + ', ' + frmInvoice.edPIRgrossTotal.sqlValue
       + ', ' + frmInvoice.edPIRdelivIncTax.sqlValue
       + ', ' + frmInvoice.edPIRinvTotal.sqlValue

       + ')');
     Cancel := True;
     frmInvoice.Close;
     frmInvoice_btnInvSave_OnAfterClick('');
     Exit;
    End;
  If (frmInvoice.dbAction = 'ShowRecord') and (Form1Button = 'Add Sale') then frmInvoice.edCounter3.Value := 0;
end;

I'm not clear about use of single and double quotes in this script.
Also I couldn't find the correct type for DBImage field. Therefore they are commented out but I like to use DBImage fields too.


Hopefully EHW is around and not busy...

Adam
God... please help me become the person my dog thinks I am.