The Delphi Column 

Navigation in a DataSet

In a DataSet, there can be only one "active" record at a time. If you need to access data in multiple records, you must move to each of them, process the data, then move again, and so on.

To move around the dataset, you can use methods of the TDataSet class:

  • First: move to the first record
  • Last: move to the last record
  • Next: move to the next record, if not already at the last record.
  • Prior: move to the previous record, if not already at the first record.
  • MoveBy: jump forth or back by a given number of records

These dataset operations are also available in the DBNavigator component.

 

A DataSet has two runtime properties, EOF (End-of-file) and BOF (Beginning-of-file), useful when you want to iterate through all the records.

When EOF is True, it indicates that the cursor is at the last row in a dataset. EOF becomes True when an application:

- Opens an empty dataset.
- Calls a dataset's Last method.
- Calls a dataset's Next method, and the cursor is already at the last row in the dataset.
- Calls SetRange on an empty range or dataset.

When you want to process of all records of a dataset, you test EOF in a loop condition. Example:

CustTable.First;  // go to first record, which sets EOF to False
while not CustTable.Eof do 
  begin
    //  Process each record here 
    //  ...
    CustTable.Next; 
  end;

When BOF is True, it indicates that the cursor is at the first row in a dataset. BOF becomes True when an application:

- Opens a dataset.
- Calls a dataset's First method.
- Calls a dataset's Prior method, and the cursor is already at the first row in the dataset.
- Calls SetRange on an empty range or dataset.

Bookmarks: marking and returning to records

A bookmark is a special variable that stores the current position of a record in a dataset. You use GotoBookmark to return to a bookmarked record, and FreeBookmark to delete a bookmark. Example:

Prodedure ProcessRecords;
var
  Bookmark1: TBookmark;
begin
  Bookmark1 := DataSet1.GetBookmark;
  // Process a number of records
  // ...
  DataSet1.GotoBookmark(Bookmark1);
  DataSet1.FreeBookmark(Bookmark1);
end;

Status of a Dataset

A dataset can be in different states, indicated by the State property:

  • dsBrowse: used to look at the data and scan the records.
  • dsEdit: a dataset enters this state when the program calls the Edit method or the DataSource has the AutoEdit property set to True, and the user begins editing a data-aware control, such as a DBGrid or DBEdit. When the changed record is posted, the dataset exits the dsEdit state.
  • dsInsert: a new record is being added to the dataset. This might happen when calling the Insert or Append methods, moving to the last line of a DBGrid, or using the corresponding command of the DBNavigator component. When the new record is posted, the dataset exits the dsInsert state.
  • dsFilter: the dataset is setting a filter.
  • dsCalcFields: a field calculation is taking place.
  • dsInactive: indicates a closed dataset.

The transitions between these states are handled automatically. For example, when a program requests an Edit operation, the dataset fires the BeforeEdit event just before entering edit mode. After entering edit mode, the AfterEdit event is fired. After the user has finished editing and requests to store the data by executing the Post command, the dataset fires the BeforePost event. Finally, the AfterPost event is fired after the operation has been successfully completed.

DataSet Filtering

When a filter is applied to a dataset, only those records that meet the filter's condition are available. The property Filter describes the filter condition.
For example, to only view records whose value of the field PRODUCT alphabetically comes after 'D', in the Object Inspector set the following dataset properties:
- set Filter to product > 'D'
- and set Filtered to True.

You can use SQL wildcards such as percent (%) and underscore (_) in the condition when you use the LIKE operator.
'_' (underscore) stands for one character and '%' stands for one or more characters. The following filter condition retrieves all Products beginning with 'D':

Product LIKE 'D%'

You can also use more complex expressions in filter conditions, by combining conditions with the operators AND, OR and NOT.

You can also set these properties in your source code:
DataSet1.Filter := QuotedStr(product > 'D');
Filtered := True;

The function QuotedStr() inserts a single quote at the beginning and at the end of a string. This is necessary because Filter must be a string.

Note: by using the event OnFilterRecord, you can set up combinations of complex conditions, based on multiple lines of code. OnFilterRecord is generated by the dataset for each record when Filtered is True. Example:

Procedure TForm1.DataSet1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
  Accept := (Uppercase(DataSet1.FieldByName('product').AsString) > 'BOX') and
            (DataSet1.FieldByName('price').AsInteger < 20);
end;


Database Tutorials  FAQ  Crash Course Delphi  Tips Source Code  Downloads  Links

Copyright 1999-2022 
DelphiLand