Mega Code Archive
Modifing a TQuery using common VCL components
<![CDATA[
Title: Modifing a TQuery using common VCL components
Question: Imagine you have a big table, you need to give the end user search capability and network trafic is a problem.
In this case we can use an TComboBox to determine which column to search and TEditBox for the search criteria.
Answer:
table structure:
| CODE | NAME | LOCATION | GENDER |
Needed
1) a form
2) TQuery named MyQuery
3) TDBGrid or other db aware fields, TDataSource, DataModule etc.
4) TEditBox named edtSelect
5) TComboBox named cbSelect
6) TBitButton named bbtnSelect (to fire the query)
7) Some global variables
SString -- string
RecordsRetrieved -- integer
8) A statusbar (optional)
The Query is a parametrized type.
Properties:
SQL = Select CODE, NAME, LOCATION, TYPE from
MYTABLE MYTABLE
where CODE like :SearchString
Active = false
Parameters -- SearchString -- ftString
As mentioned before, cbSelect will indicate the column searched.
In the sql statement the column is located after 'where'
Items of cbSelect:
CODE
NAME
LOCATION
GENDER
Events:
On form create
procedure TForm1.FormCreate(Sender: TObject);
begin
inherited;
SString :='%%';
cbSelect.ItemIndex := 0;
end;
Typing in the search criteria in the TeditBox
procedure TForm1.edtSelectChange(Sender: TObject);
begin
inherited;
SString := '%' + edtSelect.Text + '%';
end;
Firing the query
procedure TForm1.bbtnSelectClick(Sender: TObject);
begin
inherited;
with Myquery do
begin
close;
SQL.Clear;
SQL.Add('SELECT CODE, NAME, LOCATION, TYPE');
SQL.Add('FROM MYTABLE MYTABLE');
case cbSelect.ItemIndex of
0: begin
SQL.Add('WHERE CODE LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
1: begin
SQL.Add('WHERE NAME LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
2: begin
SQL.Add('WHERE LOCATION LIKE :SearchString');
ParamByName('SearchString').AsString := SString;
end;
3: begin
SString := 'M';
edtSelect.Text:= 'M';
SQL.Add('WHERE GENDER = "M"');
end;
4: begin
SString := 'F';
edtSelect.Text:= 'F';
SQL.Add('WHERE GENDER = "F"');
end;
End; {case}
SQL.Add('ORDER BY CODE, GENDER');
open;
end; { with }
RecordsRetrieved := MyQuery.RecordCount;
sbStatusBar.Panels[2].text:= IntToStr(RecordsRetrieved) + ' Records';
//update statusbar
end;
The user do not have to use wildcards to find for example edward, any character will satisfy the search. Leaving the edtSelect blank, will retrieve all the records.
]]>