Specifying Ranges

From RAD Studio
Jump to: navigation, search

Go Up to Limiting Records with 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.

Setting the beginning of a range

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:

with Customers do
begin
  SetRangeStart;
  FieldByName('CustNo').AsString := StartVal.Text;
  SetRangeEnd;
  if (Length(EndVal.Text) > 0) then
    FieldByName('CustNo').AsString := EndVal.Text;
  ApplyRange;
end;
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, because 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 multicolumn 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 the start (or end) of the range is not specified, then the inferior (or superior) limit of the domain is used. 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 Data.DB.TDataSet.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 Data.DB.TDataSet.FieldByName method. For example,

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

As with specifying the 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-range 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:

SetRange([Edit1.Text, Edit2.Text], [Edit3.Text, Edit4.Text]);
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 multicolumn 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 to 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:

Contacts.SetRangeStart;
Contacts['LastName'] := 'Smith';
Contacts.SetRangeEnd;
Contacts['LastName'] := 'Zzzzzz';
Contacts.ApplyRange;
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:

Contacts['LastName'] := 'Sm';
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 the ending range. For example,

Contacts.KeyExclusive := True;
Contacts.SetRangeStart;
Contacts['LastName'] := 'Smith';
Contacts.SetRangeEnd;
Contacts['LastName'] := 'Tyler';
Contacts.ApplyRange;
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