delphidbgrid

SQL Query is not executed while filtering DBGrid


I am trying to filter products loaded from a database into a DBGrid. When I try to filter, it is not possible to execute the query. It returns me a blank sql.Text. I don’t know why this is happening.

This is my code

procedure TProizvodiForm.PronadjiButtonClick(Sender: TObject);

var Filter: string;

begin
     Filter := 'SELECT productid as ID, name as Proizvod, code as Kod, manufname as Proizvodjac, unitname as Pakovanje FROM product WHERE ';

    if prizvodFilterTEdit.Text <> '' then
      begin

          Filter := Filter + '`name` LIKE ' + QuotedStr(prizvodFilterTEdit.Text+'%');

      end;
    if kodFilterTEdit.Text <> '' then
      begin
        Filter := Filter + ' AND code LIKE ' + QuotedStr(kodFilterTEdit.Text+'%');
      end;
    if proizvodjacFilterTEdit.Text <> '' then
      begin
        Filter := Filter + ' AND manufname LIKE ' + QuotedStr(proizvodjacFilterTEdit.Text+'%');
      end;

     with DB.ZQuerySelect do
      begin
        Active := false;
        sql.Clear;
        sql.Text := Filter;
        Active := true;

        ShowMessage(sql.Text); // debug
      end;
     
end;

BUT THIS WORKS When I directly put my query in sql.Text that is working, but when set in variable not working.

with DB.ZQuerySelect do
      begin
        Active := false;
        sql.Clear;
        sql.Text := 'SELECT productid as ID, name as Proizvod, code as Kod, manufname as Proizvodjac, unitname as Pakovanje FROM product WHERE `name` LIKE ' + QuotedStr(prizvodFilterTEdit.Text+'%');
        Active := true;

        ShowMessage(sql.Text);
      end;

Solution

  • Not sure if this is THE problem, but ONE problem I see in your code is if the prizvodFilterTEdit control is blank then your Filter string will end up as a malformed SQL statement (regardless of the other TEdits), as the WHERE clause will be empty, eg:

    SELECT ... FROM product WHERE AND ...
                            ^^^^^^^^^
    

    You should get a runtime error trying to use that SQL!

    You are not handling the WHERE and AND clauses accurately enough to allow you to omit your filter clauses (as your "working" case is doing). Don't include a WHERE clause unless you actually have something to filter for. And don't include an AND clause unless there is already an earlier clause in the filter.

    Try this instead:

    procedure TProizvodiForm.PronadjiButtonClick(Sender: TObject);
    var
      SelectStmt, Filter: string;
    begin
      SelectStmt := 'SELECT productid as ID, name as Proizvod, code as Kod, manufname as Proizvodjac, unitname as Pakovanje FROM product';
    
      if prizvodFilterTEdit.Text <> '' then
      begin
        Filter := '(`name` LIKE ' + QuotedStr(prizvodFilterTEdit.Text+'%') + ')';
      end;
      if kodFilterTEdit.Text <> '' then
      begin
        if Filter <> '' then
        begin
          Filter := Filter + ' AND ';
        end;
        Filter := Filter + '(code LIKE ' + QuotedStr(kodFilterTEdit.Text+'%') + ')';
      end;
      if proizvodjacFilterTEdit.Text <> '' then
      begin
        if Filter <> '' then
        begin
          Filter := Filter + ' AND ';
        end;
        Filter := Filter + '(manufname LIKE ' + QuotedStr(proizvodjacFilterTEdit.Text+'%') + ')';
      end;
    
      with DB.ZQuerySelect do
      begin
        Active := false;
        SQL.Clear;
        SQL.Add(SelectStmt);
        if Filter <> '' then
        begin
          SQL.Add('WHERE ' + Filter);
        end;
        Active := true;
    
        ShowMessage(SQL.Text); // debug
      end;
         
    end;