Author: Embarcadero USA
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
Article originally contributed by Borland Staff