Technical Information Database

TI2367C.txt   Filtering Your DataSet in C++Builder
Category   :General
Platform    :All
Product    :C++Builder  All

Description:
Filtering your DataSet In C++ Builder
  
Filtering lets you specify criteria to temporarily restrict the
data being viewed.  For example, you may be interested in
retrieving or viewing only those records for companies based in
California in your customer database, or you may want to find
records that contain orders in excess of $2,000.00. C++Builder
supports filtering of a table or query to handle both of these
requirements.
  
Filters are similar to, though less powerful than, queries, with
the benefit that filters work on the dataset itself, meaning
that the result is always "live" (unlike queries which sometimes
produce result sets that can't be modified).
  
You can filter a dataset in three ways:
  
     Setting the Filter property of the dataset.
  
     Restricting record visibility at the time of record 
     retrieval using an OnFilterRecord event handler.
  
     Finding a record in a dataset that matches search values
     using the Locate method for the dataset.
  
If you use Locate, C++Builder automatically generates a filter
for you at runtime if it needs to Press the >> button to read
about properties and methods common to the first two methods of
filtering datasets as well as information specific to each
method.  
  
Note:   Filters are applied to every record retrieved in
a dataset. When you want to filter large volumes of data, it may
be more efficient to use a query to restrict record retrieval,
or to set a range on an indexed table rather than using filters.
  
The first way of filtering your DataSet is using the Filter
property of the TDataSet derived object (most commonly a TTable).
  
The following function uses an OnClick event for a TCheckBox to
apply a filter.  The Field, and the Value to filter will be
taken from two Tedit objects on the dialog.  The rest of the
dialog contains a TTable, a TDataSource, a TDBGrid, and a
TDBNavigator.  Hook the TTable up to a Database, and a table,
set the Active property to true.   For the TDataSource object,
set the DataSet property to the TTable, and set the Enabled
property to true.  Set the TDBGrid's DataSource property to the
TDataSource component, also set the TDBNavigator's DataSource
property to the TDataSource component.
  
Drop a TCheckBox, and two TEdit controls on your dialog.  For
the OnClick event for the TCheckBox control use the following
code:
  
  
  void __fastcall TForm1::CheckBox1Click(TObject *Sender)
  {
    if (CheckBox1->Checked) {
      if ((Edit1->Text != "") && (Edit2->Text != "")) {
        Table1->Filter = Edit1->Text + "=" + "'" + Edit2->Text + 
        "'";
        if ( Table1->Filtered==false) 
          Table1->Filtered=true;
      }
      else
        MessageBox(
          0, 
          "You need to enter a field, and a value to filter on.", 
          "Warning",
          MB_OK
        );
    }
    else
      Table1->Filtered = false;
  }
  
  
When the checkbox is checked the values from the edit controls
are used to set the filter.  The Edit1 control is used to enter
the field name you want to filter on, and the Edit2 control is
used to type in the filter value.
  
  
The second method will use the event OnFilterRecord for the
TDataSet component (again this will be a TTable control).  The
OnFilterRecord event handler sets the Accept property to either
true or false, depending on if the record falls into the filter
to be set.  The following code shows how to use the
OnFilterRecord to set the Accept property: 
  
  
void __fastcall TForm1::Table1FilterRecord(TDataSet *DataSet,
bool &Accept)
  {
    Accept = (Edit2->Text == 
    DataSet->FieldByName(Edit1->Text)->Value);
  }
  
This code checks to make sure that the value of the field that
was typed into Edit1, matches the value thas was typed into the
Edit2 control.  This event will be triggered when the Filtered
property of the TDataSet is set to true.
  
The third method of filtering data is to search for a record in
a dataset that matches values using the Locate function of the
DataSet.  The following example is a simple login rutine. This
code allows a user to type in a user name, and a password into
two TEdit.  When a button is clicked, the values are checked, to
see if a record exists in a table.  If the user is verified, the
program continues.  Otherwise it will not allow the user to
continue into the application.  
  
  void __fastcall TLoginDialog::btnOKClick(TObject *Sender)
  {
    if ((eLoginName->Text == "") || (ePassWord->Text == ""))
      MessageBox(
        0, 
        "Both Login Name and Password need to be entered.",
        "Warning", 
        IDOK
      );
    else {
      // the values are checked against the login table.  if the
      // user is in the login table, then the application is
      // unlocked.  Otherwise the application will terminate.
      //
      bool found;
      TLocateOptions flags;
  
      Variant keys[] = { eLoginName->Text, ePassWord->Text };
      found = dmTechRoof->tblLogin->Locate(
        "UserLogin;UserPassword", 
        Variant(keys, LASTIDX(keys)), 
        flags
      );
  
      if (found) {
        // Open up the main dialog of the application.
        //
        LoginDialog->Hide();
        MainDialog->Show();
      }
      else {
        MessageBox(
          0, 
          String("Could not find user " + eLoginName->Text + " in
          the login table.\nPlease check you login name and
          password and try again.").c_str(), 
          "Error", 
          IDOK
        );
      }
    }
  }
  

Reference:


7/2/98 10:40:48 AM