Specifying Ranges

There are two mutually exclusive ways to specify a range:

Specify the beginning and ending separately using SetRangeStart and SetRangeEnd.
Specify both endpoints at once using SetRange.

Call the SetRangeStart procedure to put the dataset into dsSetKey state and begin creating a list of starting values for the range. Once you call SetRangeStart, subsequent assignments to the Fields property are treated as starting index values to use when applying the range. Fields specified must apply to the current index.

For example, suppose your application uses a TSimpleDataSet component named Customers, linked to the CUSTOMER table, and that you have created persistent field components for each field in the Customers dataset. CUSTOMER is indexed on its first column (CustNo). A form in the application has two edit components named StartVal and EndVal, used to specify start and ending values for a range. The following code can be used to create and apply a range:

Copy Code

with Customers do
begin
  SetRangeStart;
  FieldByName('CustNo').AsString := StartVal.Text;
  SetRangeEnd;
  if (Length(EndVal.Text) > 0) then
    FieldByName('CustNo').AsString := EndVal.Text;
  ApplyRange;
end;

Copy Code

Customers->SetRangeStart();
Customers->FieldValues["CustNo"] = StrToInt(StartVal->Text);
Customers->SetRangeEnd();
if (!EndVal->Text.IsEmpty())
  Customers->FieldValues["CustNo"] = StrToInt(EndVal->Text);
Customers->ApplyRange();

This code checks that the text entered in EndVal is not null before assigning any values to Fields. If the text entered for StartVal is null, then all records from the beginning of the dataset are included, since all values are greater than null. However, if the text entered for EndVal is null, then no records are included, since none are less than null.

For a multi-column index, you can specify a starting value for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. If you try to set a value for a field that is not in the index, the dataset raises an exception.

Tip: To start at the beginning of the dataset, omit the call to SetRangeStart.

To finish specifying the start of a range, call SetRangeEnd or apply or cancel the range.

Setting the end of a range

Call the SetRangeEnd procedure to put the dataset into dsSetKey state and start creating a list of ending values for the range. Once you call SetRangeEnd, subsequent assignments to the Fields property are treated as ending index values to use when applying the range. Fields specified must apply to the current index.

Warning: Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, Delphi assumes the ending value of the range is a null value. A range with null ending values is always empty.

The easiest way to assign ending values is to call the FieldByName method. For example,
Copy Code

with Contacts do begin SetRangeStart; FieldByName('LastName').AsString := Edit1.Text; SetRangeEnd; FieldByName('LastName').AsString := Edit2.Text; ApplyRange; end;

Copy Code

Contacts->SetRangeStart(); Contacts->FieldByName("LastName")->Value = Edit1->Text; Contacts->SetRangeEnd(); Contacts->FieldByName("LastName")->Value = Edit2->Text; Contacts->ApplyRange();

As with specifying start of range values, if you try to set a value for a field that is not in the index, the dataset raises an exception.

To finish specifying the end of a range, apply or cancel the range.

Setting start- and end-range values

Instead of using separate calls to SetRangeStart and SetRangeEnd to specify range boundaries, you can call the SetRange procedure to put the dataset into dsSetKey state and set the starting and ending values for a range with a single call.

SetRange takes two constant array parameters: a set of starting values, and a set of ending values. For example, the following statement establishes a range based on a two-column index:
Copy Code

SetRange([Edit1.Text, Edit2.Text], [Edit3.Text, Edit4.Text]);

Copy Code

TVarRec StartVals[2]; TVarRec EndVals[2]; StartVals[0] = Edit1->Text; StartVals[1] = Edit2->Text; EndVals[0] = Edit3->Text; EndVals[1] = Edit4->Text; Table1->SetRange(StartVals, 1, EndVals, 1);

For a multi-column index, you can specify starting and ending values for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. To omit a value for the first field in an index, and specify values for successive fields, pass a null value for the omitted field.

Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, the dataset assumes the ending value of the range is a null value. A range with null ending values is always empty because the starting range is greater than or equal to the ending range.

Specifying a range based on partial keys

If a key is composed of one or more string fields, the SetRange methods support partial keys. For example, if an index is based on the LastName and FirstName columns, the following range specifications are valid:
Copy Code

Contacts.SetRangeStart; Contacts['LastName'] := 'Smith'; Contacts.SetRangeEnd; Contacts['LastName'] := 'Zzzzzz'; Contacts.ApplyRange;

Copy Code

Contacts->SetRangeStart(); Contacts->FieldValues["LastName"] = "Smith"; Contacts->SetRangeEnd(); Contacts->FieldValues["LastName"] = "Zzzzzz"; Contacts->ApplyRange();

This code includes all records in a range where LastName is greater than or equal to "Smith." The value specification could also be:
Copy Code

Contacts['LastName'] := 'Sm';

Copy Code

Contacts->FieldValues["LastName"] = "Sm";

This statement includes records that have LastName greater than or equal to "Sm."

Including or excluding records that match boundary values

By default, a range includes all records that are greater than or equal to the specified starting range, and less than or equal to the specified ending range. This behavior is controlled by the KeyExclusive property. KeyExclusive is False by default.

If you prefer, you can set the KeyExclusive property for a dataset to True to exclude records equal to ending range. For example,
Copy Code

Contacts.KeyExclusive := True; Contacts.SetRangeStart; Contacts['LastName'] := 'Smith'; Contacts.SetRangeEnd; Contacts['LastName'] := 'Tyler'; Contacts.ApplyRange;

Copy Code

Contacts->SetRangeStart(); Contacts->KeyExclusive = true; Contacts->FieldValues["LastName"] = "Smith"; Contacts->SetRangeEnd(); Contacts->FieldValues["LastName"] = "Tyler"; Contacts->ApplyRange();

This code includes all records in a range where LastName is greater than or equal to "Smith" and less than "Tyler".

See Also

Understanding the Differences Between Ranges and Filters

Applying or Canceling a Range

Modifying a Range