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;
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 TEdit
s), 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;