Delphi - Check if a record already exists in the database

Question

I'm trying to make a comparison between the products of one cxGrid with products already registered to the base, but I have doubts.

I have the following tables:

product: id (pk), description, reference

items_note: product (pk), description, unit

Trying to do at the event cxGridProdutoCustomDrawCell, but it's not working

procedure TFrmCompra.cxGridProdutoCustomDrawCell(Sender: TcxCustomGridTableView;
  ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo;
  var ADone: Boolean);
begin
  if QryItemNota.fieldByName('PRODUTO').asString <> QryProduto.fieldByName('REFERENCIA').asString then
  begin
     ACanvas.Font.Color := clRed
  end
  else
     ACanvas.Font.Color := clBlack;
end;

Used Delphi RIO 10.3, Firebird Database with Firedac components

Answer 1

It's a pity you didn't actually say whether the text in the cell is drawn with the font set to red or to black. It should work fine so if its only using black or red for you, your if test is going wrong. First, temporarily change your code to

procedure TFrmCompra.cxGridProdutoCustomDrawCell(Sender: TcxCustomGridTableView;
  ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo;
  var ADone: Boolean);
begin
  ACanvas.Font.Color := clRed
end;

and satisfy yourself that the text is indeed red. Then, temporarily change your code to

procedure TFrmCompra.cxGridProdutoCustomDrawCell(Sender: TcxCustomGridTableView;
  ACanvas: TcxCanvas; AViewInfo: TcxGridTableDataCellViewInfo;
  var ADone: Boolean);
var
  S1, 
  S2 : String;
begin
  S1 := QryItemNota.fieldByName('PRODUTO').asString;
  S2 := QryProduto.fieldByName('REFERENCIA').asString;
  if S1 <> S2 then
  begin
     ACanvas.Font.Color := clRed
  end
  else
     ACanvas.Font.Color := clBlack;
end;

Then, put a breakpoint on the if S1 <> S2 then line and run the program until the breakpoint hits. Use F7 to evaluate S1 and S2 and you should immediately see why you aren't getting the result you're expecting. Most likely S1 and S2 are always equal or never equal.

Another piece of information you didn't include is which of the two queries is supplying the grid view and which is the one you are checking it against. Unless you know that the other query is correctly synchronised with the displayed one, you should be doing something like

if QueryB.Locate({query B field name}, QueryA.FieldByName({query A field name}).AsString, []) then
   //  take record found action
else
   //  take record not found action

This checks whether the value from the current row in QueryA is (which is being processed in the CustomDraw event) can be found in QueryB.