主页 / coding / 问题 / 79078818
Accepted
Ivan
Asked: 2024-10-11 23:13:06 +0800 CST

Delphi：过滤 DBGrid 时未执行 SQL 查询

  • 772

我正在尝试过滤从数据库加载到 DBGrid 的产品。当我尝试过滤时，无法执行查询。它返回一个空白sql.Text。我不知道为什么会这样？

这是我的代码

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;

但是， 当我直接输入查询时sql.Text，它可以工作，但是当设置变量时，它不起作用。

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;
delphi

1 个回答

  1. Best Answer

    不确定这是否是问题所在，但我在您的代码中看到的一个问题是，如果控件prizvodFilterTEdit为空，那么您的Filter字符串最终将变成格式错误的SQL 语句（无论其他内容如何TEdit），因为WHERE子句将为空，例如：

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

    尝试使用该 SQL 时您应该会收到运行时错误！

    您没有足够准确地处理WHEREandAND子句，因此无法省略过滤子句（正如您的“工作”案例所做的那样）。WHERE除非您确实需要过滤某些内容，否则不要包含子句。除非过滤器中已经有更早的子句，否则不要包含子句AND。

    尝试一下这个：

    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;
    • 0

相关问题