Filtering, Searching/Finding and Sorting records in ADO Datasets Delphi (TAdoQuery, TAdoTable, TAdoStoredProc)

Note. - Here in my article I have used Dataset word for TAdoQuery Component but we can apply the same for other TCustomADODataSet descendent component. And this example assumes that TAdoQuery component is already opened with a valid SQL and have some data in memory.

Filtering records in TAdoQuery by using Filter, Filtered property

Filter records in TAdoquery means to access or show some specific records that exists in Adoquery dataset memory as per User's requirement. After opened a TAdoquery dataset with valid SQL, we can still filter the fetched data at our client application side. When we filter a dataset, we restrict access to a subset of records contained in the dataset's in-memory store. For example, imagine that you have a AdoQuery dataset that includes Vendor's records, world-wide. Without filtering, all Vendor records are accessible in the dataset. That is, it is possible to navigate, view, and edit any Vendor in the dataset. Through filtering we can make the dataset appear to include only those Vendors who live in the Miami or Chicago. This example, of course, assumes that there is a field naming 'City' in the Adoquery Dataset that contains City name of a Vendor.

A Adoquery dataset provides following mechanisms to filter existing records...

Filter           - String Property that can contain Filter string like SQL WHERE clause format. Ex - CITY = 'MIAMI';

Filtered           -  Boolean property that we use to turn on and off the filter

FilterOptions - Specifies whether or not filtering is case insensitive, and whether or not partial comparisons are permitted when filtering records.

foCaseInsensitive = Ignore case when comparing strings.

foNoPartialCompare = Disable partial string matching; that is, don't match strings that end with an asterisk (*).

In its simplest case, a filter requires the use of two of these properties: Filter and Filtered. Just set the required Filter string and If you want to filter records, set Filtered to True, otherwise set Filtered to False. When Filtered is set to True, the DataSet uses the value of the Filter property to identify which records will be displayed. We assign a Boolean expression to this property containing at least one comparison operation involving at least one field in the dataset. We can use any comparison operators, include =, >, <, >=, <=, and <>.

Filter expression to display only Vendors from Miami:

Ex...

qryVendors.Filter := 'City = ' + QuotedStr('Miami');

qryVendors.Filtered := True;

We can use standard SQL wildcards such as percent (%) and underscore (_) in the condition when you use the LIKE operator. 

The following filter condition retrieves all Vendor Names beginning with 'Dive':

Ex... 

qryVendors.Filter := 'VendorName LIKE %'+ QuotedStr('Dive')+'%';

qryVendors.Filtered := True;

To view rows that have a NULL value in the Country column and City is not NULL, use the IS operator: 

Ex... 

qryVendors.Filter := 'Country is NULL and City is not NULL';

qryVendors.Filtered := True;

Ex...

with qryVendors do begin

Filtered := False;

Filter := 'City = ' + QuotedStr('Miami') + ' OR ' + 'Country = ' + QuotedStr('U.S.A.');

Filtered := True;

end;

Note* When filtering is enabled, user edits to a record may mean that the record no longer meets a filter's test condition. The next time an attempt is made to the read the record from the dataset while the filter is in effect, the record is not visible. If that happens, the next record that passes the filter condition becomes the active record.

Using onFilterRecord Event to filter AdoQuery records manually

You can write code to filter records using the AdoQuery.OnFilterRecord event fired by the dataset for each record it retrieves. This event is only triggered when the Filtered property is set to True. This event handler implements a boolean condition to test that determines if a record should be included in those that are going to be visible to the application or not. To include a record in query we need to set its Accept parameter to True or False to exclude it.

Ex...

procedure TForm1.ADOQuery1FilterRecord(DataSet: TDataSet; var Accept: Boolean);

begin

  Accept := DataSet['State'].AsString = 'CA';

end;

The above code to display only those records with the State field set to 'CA'.

Searching individual records in TAdoQuery

In addition to filtering and sorting records in a AdoQuery dataset, TAdoQuery provides a number of methods for quickly locating a specific record. The search methods are described in detail in the following.

Locate

Locate is the most general purpose of the TAdoQuery search methods. You can use Locate to search for a record based on any given field or combination of fields. Locate can also search for records based on a partial match, and can find a match without respect to case. Locate returns True if a matching record is found, and False if no match is found. In case of a match, the record is made current and Recordset control moves to that record.

Locate is defined like this:

function Locate(const KeyFields: string; const KeyValues: Variant; Options: TLocateOptions): Boolean; override;

The first parameter, KeyFields, designates the field (or fields) to search. When searching multiple fields, separate them by semicolons.

Ex...

'Name;Birthday'

The second parameter, KeyValues, represents the values to search for. The number of values must match the number of key fields exactly. If there is only one search field, you can simply pass the value to search for here. 

To search for multiple values, you must pass the values as a variant array. One way to do this is by calling VarArrayOf, like this:

Ex...

VarArrayOf(['John Smith', '4/15/1965'])

The final parameter, Options, is a set that determines how the search is to be executed. 

loPartialKey = KeyValues do not necessarily represent an exact match. Locate finds the first record whose field value starts with the value specified in KeyValues.

loCaseInsensitive = Locate ignores case when searching for string fields.

Ex...

AdoQuery1.Locate('Name', 'John Smith', []);

This searches for a record where the name is 'John Smith'.

AdoQuery1.Locate('Name;Birthday', VarArrayOf(['John', '4/15/1965']), []);

This searches for a record where the name begins with 'John' and the birthday is April 15, 1965

Lookup

Lookup is similar in concept to Locate, except that it doesn't change the current record pointer. And Lookup returns the values of one or more fields in the record. Also, Lookup does not accept an Options parameter, so you can't perform a lookup that is based on a partial key or that is not case sensitive.

Lookup is defined like this:

function Lookup(const KeyFields: string; const KeyValues: Variant; const ResultFields: string): Variant; override;

KeyFields and KeyValues specify the fields to search and the values to search for, just as with the Locate method. 

ResultFields specifies the fields for which you want to return data. 

For example, to find the record in the CustTable where the value of the Company field is "Professional Divers, Ltd.", and returns the company name, a contact person, and a phone number for the company:

// Custquery: TAdoquery //

var

  LookupResults: Variant;

  sCompany: string;

begin

  LookupResults := Custquery.Lookup('Company', 'Professional Divers, Ltd.', 'Company');

end;

If ResultFields contains a single field name, then on return from Lookup, LookupResultsis a variant containing the value of the field listed in ResultFields.

So we can get the resulted data..

sCompany := LookupResults      \\Company

Note* If the requested record is not found, LookupResults is set to NULL.

If ResultFields contains multiple field names, then on return from Lookup, LookupResults is a variant array containing the values of the fields listed in ResultFields.

For multiple column search...

var

  LookupResults: Variant;

  sCompany, sContact, sPhone: string;

begin

  LookupResults := Custquery.Lookup('Company; City', VarArrayOf(['Sight Diver', 'Christiansted']), 'Company;Contact;Phone');

end;

we can get the resulted data..

sCompany := LookupResults[0]   \\Company

sContact := LookupResults[1]      \\Contact

sPhone := LookupResults[2]       \\Phone

Sorting records in TAdoQuery

We can sort TAdoQuery dataset records by using Sort property. Sort is a string property and we just need to assign field names with comma separated that we want to sort. And Ascending and Descending we can use ASC and DESC keywords with field names.

Note* ASC, DESC must be in capital letter.

Ex...

qryVendors.Sort = 'County';

or

qryVendors.Sort = 'County,State,City';

Ascending 

qryVendors.Sort = 'County ASC';

Descending

qryVendors.Sort = 'County DESC';

Ascending and Descending

qryVendors.Sort = 'County ASC,State DESC,City';

if you have field names with space then you can sort as following

qryVendors.Sort := '[LAST NAME]';

Sorting records in Delphi DBGrid by Clicking on Column Title

Delphi DBGrid is a powerful component if you are developing data aware applications. You are probably using the DBGrid component every day. And sometime we need to Sort DBgrid records on Ascending or Descending order on Column Title click. So here you'll see how to sort DBGrid dataset records on DbGrid Title click.

Ex...

private

  PreviousColumnIndex: integer;

  

procedure TForm1.DBGrid1TitleClick(Column: TColumn);

begin

  if DBGrid1.DataSource.DataSet is TCustomADODataSet then

  begin

 with TCustomADODataSet(DBGrid1.DataSource.DataSet) do

 begin

try

 DBGrid1.Columns[PreviousColumnIndex].title.Font.Style := DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];  

except

end;

Column.title.Font.Style := Column.title.Font.Style + [fsBold];

PreviousColumnIndex := Column.Index;

if (Pos(Column.Field.FieldName, Sort) = 1) and (Pos(' DESC', Sort)= 0) then

 Sort := Column.Field.FieldName + ' DESC'

else

 Sort := Column.Field.FieldName + ' ASC';

 end;

  end;

end;