INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Database Programming

Search capability in a Tquery using common vcl components by svanels
Posted: 18 May 03

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.


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;
-------------------------------------------
Remarks
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.

Back to Embarcadero: Delphi FAQ Index
Back to Embarcadero: Delphi Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close