Devart Forums

Code: Select all

ADODataSet1.UpdateBatch(arCurrent);
ADODataSet1.UpdateBatch(arAll);

Code: Select all

MASTER DETAIL

qryAPInv


SELECT [APInvoiceID]
      ,[VendorID]
      ,[InvoiceNo]
      ,[Description]
      ,[InvoiceDate]
      ,[DueDate]
      ,[DiscountDate]
      ,[InvoiceAmount]
      ,[PaidAmount]
      ,[DiscountAmount]
      ,[DiscPC]
      ,[DiscountTaken]
      ,[Owing]
      ,[PurchaseOrderNo]
      ,[GLPeriod]
      ,[GLYear]
      ,[Posted]
      ,[CurrencyID]
      ,[ReceiptID]
      ,[UpdateNo]
      ,[timestamp]
  FROM [APInv]
WHERE [APInvoiceID]=10001



qryAPInvDet



SELECT [APInvoiceID]
      ,[Seq]
      ,[GLAccountID]
      ,[SubAccount1ID]
      ,[SubAccount2ID]
      ,[SubAccount3ID]
      ,[GLAmount]
  FROM [APInvDet]
WHERE [APInvoiceID]=10001





procedure TfrmAPInvoice.btnOKClick(Sender: TObject);
begin
  if qryAPInv.State in [dsInsert, dsEdit] then qryAPInv.post;
  if qryAPInvDet.State in [dsInsert, dsEdit] then qryAPInvDet.post;

  frmKaifa.ADOConnection1.BeginTrans;
  try
    qryAPInv.UpdateBatch(arCurrent);
    qryAPInvDet.UpdateBatch(arAll);
    frmKaifa.ADOConnection1.CommitTrans;
  except
    on E:EOleException do begin
      frmKaifa.ADOConnection1.RollbackTrans;

      qryAPInv.UpdateCursorPos;   //Master error.
      if (rsNew in qryAPInv.RecordStatus) or (rsModified in qryAPInv.RecordStatus) then begin
        //showmessage(IntToStr(frmKaifa.ADOConnection1.Errors[0].NativeError) + ' ' + E.Message);
        if frmKaifa.ADOConnection1.Errors[0].NativeError = 2627 then begin   //Key violation (key already exists).
          with editInvoiceNo do begin Show; SetFocus; end;
          raise(exception.create('Invoice no. already exists for this vendor'));
        end else if frmKaifa.ADOConnection1.Errors[0].NativeError = 32 then raise(exception.create('Unable to insert or update invoice...' +#13 + 'It has been changed or deleted since last retrieved'))
        else raise(exception.create('Unable to insert or update invoice...' +#13 + E.Message));
      end;

      DBGrid1.Setfocus;   //Detail error.

      qryAPInvDet.first;   //Move to invalid record.
      while not qryAPInvDet.EOF do begin
        qryAPInvDet.UpdateCursorPos;
        if (rsNew in qryAPInvDet.RecordStatus) or (rsModified in qryAPInvDet.RecordStatus) then break;
        qryAPInvDet.next;
      end;

      Application.MessageBox(PChar('Unable to insert or update invoice...' +#13 +#13 + E.Message), PChar(Application.Title), mb_OK + mb_DefButton1 + mb_IconStop);
      btnCancelClick(sender);
      exit;

    end else begin   //Other type of exception.
      frmKaifa.ADOConnection1.RollbackTrans;
      raise;
    end;
  end;

  if qryAPInvUpdated then begin
    try
      if qryAPInvInserted then begin
        frmAPInvoices.qryAPInv.requery;   //Refresh list to add new record.
        frmAPInvoices.qryAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []);
      end else begin
        frmAPInvoices.qryAPInv.Locate('APInvoiceID', qryAPInvAPInvoiceID.value, []);
        frmAPInvoices.qryAPInv.UpdateCursorPos;
        frmAPInvoices.qryAPInv.Recordset.Properties['Unique Table'].Value := 'APInv';
        frmAPInvoices.qryAPInv.Recordset.Properties['Resync Command'].Value :=
        'SELECT I.*, V.VendorNo, V.VendorName, V.Phone, V.Email ' +
        'FROM APInv I ' +
        'LEFT OUTER JOIN Vendor V ON V.VendorID = I.VendorID ' +
        'WHERE I.APInvoiceID = ?';
        frmAPInvoices.qryAPInv.RecordSet.resync(adAffectCurrent, adResyncAllValues);   //Refresh existing record on the list.
        frmAPInvoices.qryAPInv.resync([]);
      end;
    except; end;
    if not qryAPInvReceiptID.IsNull then begin   //If invoice was created from a PO receipt, refresh receipts list to show invoice no.
      try
        frmInvReceipts.mnuRefreshClick(sender);
      except; end;
    end;
  end;

  Close;
end;

Code: Select all

JournalID   JournalName

1001           Payment Journal

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1000
1001        2                 1-002         1000
1001        3                 1-003        -2000

Code: Select all

JournalID   JournalName

1001           Payment Journal USD

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1500
1001        2                 1-002         1500
1001        3                 1-003        -3000

Code: Select all

JournalID   JournalName

1001           Payment Journal

Code: Select all

JournalID LineNumber  GLAccount  Amount
1001        1                 1-001         1000
1001        2                 1-002         1000
1001        3                 1-003        -2000

Code: Select all

procedure TMainForm.ButtonOKClick(Sender: TObject);
begin
  UniConnection.StartTransaction;
  try
    UniQuery.ApplyUpdates; // cached data goes to the database, but the changes are not committed to the database because there is an active transaction
    UniConnection.Commit;  // if there were no errors, commit transaction
  except
    // if there were errors
    UniQuery.RestoreUpdates; // return the cache of updates to its state before calling ApplyUpdates
    UniConnection.Rollback;  // roll back transaction
  end;
end;

procedure TMainForm.ButtonCancelClick(Sender: TObject);
begin
  UniQuery.CancelUpdates; // clear all pending cached updates from cache and restore dataset in its prior state
end;

Code: Select all

procedure TMainForm.ButtonOKClick(Sender: TObject);
begin
  UniConnection.StartTransaction;
  try
    UniQuery.ApplyUpdates; // cached data goes to the database, but the changes are not committed to the database because there is an active transaction

    UniQuery1.ApplyUpdates;
    UniConnection.Commit;  // if there were no errors, commit transaction
  except
    // if there were errors
    UniQuery.RestoreUpdates; // return the cache of updates to its state before calling ApplyUpdates 

    UniQuery1.RestoreUpdates;
    UniConnection.Rollback;  // roll back transaction
  end;
end;

procedure TMainForm.ButtonCancelClick(Sender: TObject);
begin
  UniQuery.CancelUpdates;
  UniQuery1.CancelUpdates; // clear all pending cached updates from cache and restore dataset in its prior state
end;