How to locally update checkbox state before applying to database?

Question

Delphi 10.4. Firebird database table has a boolean field PickedUp. The Dataset is a TFDQuery component.

There are records of documents in a TDBGrid. On the form I dropped a TDBCheckBox component. The TDBCheckBox is associated with the PickedUp field of TFDQuery.

My goal is when I check the CheckBoxes in the TDBGrid, changes of states are stored locally in an array, and when I click on the Apply button then all changes of states of checked documents are saved in the PickedUp field of the table.

The problem is that when I click on a CheckBox, it is toggled "internally" but I don't see it, I couldn't see which Checkboxes state have changed and which have not.

After clicking on the Apply button and refresh the dataset, I see that the states of CheckBoxes have successfully changed, but how can I see the changed states before applying?

I create an array for storing local changes, and after the Apply button is clicked, I reflect these changes in the database.

OnCreate event of form:

dbcheckbox1.DataSource := dsOutBound;
dbcheckbox1.DataField := 'PickedUp';
dbcheckbox1.Visible := False;
dbcheckbox1.Color := dbgOutBound.Color;
dbcheckbox1.Caption := '';
dbcheckbox1.Parent := dbgOutBound; 

Other events:

type
  TDocumentInfo = record
    DocumentID: Integer;
    PickedUp: Boolean;
  end;
    
var
  DocumentsToUpdate: TArray<TDocumentInfo>;
    
procedure Tform1.dbgrid1CellClick(Column: TColumn);
var
  DocumentID: Integer;
  PickedUp: Boolean;
begin
  if Column.Field.DataType = ftBoolean then
  begin
    // Store the changes locally without immediately updating the database
    DocumentID := dbgrid1.DataSource.DataSet.FieldByName('DOCUMENTID').AsInteger;
    PickedUp := not Column.Field.AsBoolean; // Toggle the value
    
    SetLength(DocumentsToUpdate, Length(DocumentsToUpdate) + 1);
    DocumentsToUpdate[High(DocumentsToUpdate)].DocumentID := DocumentID;
    DocumentsToUpdate[High(DocumentsToUpdate)].PickedUp := PickedUp;
    
    // Update the checkbox state locally
    Column.Field.AsBoolean := PickedUp;
  end;
end;
    
procedure Tform1.UpdateCheckedDocumentsPickedUp;
var
  i: Integer;
  Bookmark: TBookmark;
  DocumentID: Integer;
  PickedUp: Boolean;
begin
  if Length(DocumentsToUpdate) = 0 then
    Exit;
    
  Bookmark := dbgrid1.DataSource.DataSet.GetBookmark;
  dbgrid1.DataSource.DataSet.DisableControls;
  try
    for i := Low(DocumentsToUpdate) to High(DocumentsToUpdate) do
    begin
      DocumentID := DocumentsToUpdate[i].DocumentID;
      PickedUp := DocumentsToUpdate[i].PickedUp;
    
      // Locate the record by DocumentID
      if dbgrid1.DataSource.DataSet.Locate('DOCUMENTID', DocumentID, []) then
      begin
        // Apply the change only for the checked records
        dbgrid1.DataSource.DataSet.Edit;
        dbgrid1.DataSource.DataSet.FieldByName('PICKEDUP').AsBoolean := PickedUp;
        dbgrid1.DataSource.DataSet.Post;
      end;
    end;
  finally
    dbgrid1.DataSource.DataSet.GotoBookmark(Bookmark);
    dbgrid1.DataSource.DataSet.FreeBookmark(Bookmark);
    dbgrid1.DataSource.DataSet.EnableControls;
  end;
    
  // Clear the local changes after applying them
  SetLength(DocumentsToUpdate, 0);
end;
    
procedure Tform1.Button1Click(Sender: TObject);
begin
  UpdateCheckedDocumentsPickedUp;
end;
    
procedure Tform1.dbgrid1DrawColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TColumn; State: TGridDrawState);
const
  CtrlState: array[Boolean] of integer = (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED) ;
  IsChecked : array[Boolean] of Integer =
     (DFCS_BUTTONCHECK, DFCS_BUTTONCHECK or DFCS_CHECKED);
begin
  if Column.FieldName = 'PICKEDUP' then
    if Column.Field.AsBoolean then
      DrawGridCheckBox(dbgrid1.Canvas, Rect, true)
    else
      DrawGridCheckBox(dbgrid1.Canvas, Rect, false);
end;
    
procedure Tform1.DrawGridCheckBox(Canvas: TCanvas; Rect: TRect; Checked: boolean);
var
  DrawFlags: Integer;
begin
  Canvas.TextRect(Rect, Rect.Left + 1, Rect.Top + 1, ' ');
  DrawFrameControl(Canvas.Handle, Rect, DFC_BUTTON, DFCS_BUTTONPUSH or DFCS_ADJUSTRECT);
  DrawFlags := DFCS_BUTTONCHECK or DFCS_ADJUSTRECT;// DFCS_BUTTONCHECK
  if Checked then
    DrawFlags := DrawFlags or DFCS_CHECKED;
  DrawFrameControl(Canvas.Handle, Rect, DFC_BUTTON, DrawFlags);
end;

Answer 1

You can call TDataSet.UpdateRecord. The docs say:

Ensures that data-aware controls and detail datasets reflect record updates.