The Fields of a Dataset

The Fields of a Dataset

I mentioned earlier that a dataset has only one record that is current, or active. The record is stored in a buffer, and you can operate on it with some generic methods, but to access the data of the record you need to use the dataset's field objects. This explains why field components (technically, instances of a class derived from the TField class) play a fundamental role in every Delphi database application. Data-aware controls are directly connected to these field objects, which correspond to database fields.

By default, Delphi automatically creates the TField components at run time, each time the program opens a dataset component. This is done after reading the metadata associated with the table or the query the dataset refers to. These field components are stored in the dataset's Fields array property. You can access these values by number (accessing the array directly) or by name (using the FieldByName method). Each field can be used to read or modify the current record's data by using its Value property or type-specific properties such as AsDate, AsString, AsInteger, and so on:

  strName: string;
  strName := Cds.Fields[0].AsString
  strName := Cds.FieldByName('LastName').AsString

Value is a variant type property, so using the type-specific access properties is a little more efficient. The dataset component has also a shortcut property for accessing the variant-type value of a field: the default FieldValues property. A default property means you can omit it from the code by applying the square brackets directly to the dataset:

strName := Cds.FieldValues ['LastName'];
strName := Cds ['LastName'];

Creating the field components each time a dataset is opened is only a default behavior. As an alternative, you can create the field components at design time, using the Fields Editor (double-click a dataset to see the Fields Editor in action, or activate the dataset's shortcut menu or that of the Object TreeView and choose the Fields Editor command). After creating a field for the LastName column of a table, for example, you can refer to its value by applying one of the AsXxx methods to the proper field object:

strName := CdsLastName.AsString;

In addition to being used to access the value of a field, each field object has properties for controlling visualization and editing of its value, including range of values, edit masks, display format, constraints, and many others. These properties, of course, depend on the type of the field—that is, on the specific class of the field object. If you create persistent fields, you can set some properties at design time instead of writing code at run time (perhaps in the dataset's AfterOpen event).


Although the Fields Editor is similar to the editors of the collections used by Delphi, fields are not part of a collection. They are components created at design time, listed in the published section of the form class and available in the drop-down combo box at the top of the Object Inspector.

As you open the Fields Editor for a dataset, it appears empty. You have to activate the shortcut menu of this editor or of the Fields pseudonode in the Object TreeView to access its capabilities. The simplest operation you can do is to select the Add command, which allows you to add any other fields in the dataset to the list of fields. Figure 13.5 shows the Add Fields dialog box, which lists all the fields available in a table. These are the database table fields that are not already present in the list of fields in the editor.

Click To expand Figure 13.5:  The Fields Editor with the Add Fields dialog box

The Fields Editor's Define command lets you define a new calculated field, lookup field, or field with a modified type. In this dialog box, you can enter a descriptive field name, which might include blank spaces. Delphi generates an internal name—the name of the field component—which you can further customize. Next, select a data type for the field. If it is a calculated field or a lookup field, and not just a copy of a field redefined to use a new data type, select the proper radio button. You'll see how to define a calculated field and a lookup field in the sections, "Adding a Calculated Field" and "Lookup Fields."


A TField component has both a Name property and a FieldName property. The Name property is the usual component name. The FieldName property is either the name of the column in the database table or the name you define for the calculated field. It can be more descriptive than the Name, and it allows blank spaces. The FieldName property of the TField component is copied to the DisplayLabel property by default. You can change the field name to any suitable text. It is used, among other things, to search a field in the TDataSet class's FieldByName method and when using the array notation.

All the fields you add or define are included in the Fields Editor and can be used by data-aware controls or displayed in a database grid. If a field of the physical dataset is not in this list, it won't be accessible. When you use the Fields Editor, Delphi adds the declaration of the available fields to the form's class as new components (much as the Menu Designer adds TMenuItem components to the form). The components of the TField class (more specifically, its subclasses) are fields of the form, and you can refer to these components directly in your program code to change their properties at run time or to get or set their value.

In the Fields Editor, you can also drag the fields to change their order. Proper field ordering is particularly important when you define a grid, which arranges its columns using this order.


You can also drag the fields from the editor to the form to let the IDE create visual components for you. This is a handy feature that can save you a lot of time when you're creating database-related forms.

Using Field Objects

Before we look at an example, let's go over the use of the TField class. Don't underestimate the importance of this component: Although it is often used behind the scenes, its role in database applications is fundamental. As I already mentioned, even if you do not define specific objects of this kind, you can always access the fields of a table or a query using their Fields array property, the FieldValues indexed property, or the FieldByName method. Both the Fields property and the FieldByName function return an object of type TField, so you sometimes have to use the as operator to downcast their result to its type (like TFloatField or TDateField) before accessing specific properties of these subclasses.

The FieldAcc example has a form with three speed buttons in the toolbar panel, which access various field properties at run time. The first button changes the formatting of the grid's population column. To do this, you have to access the DisplayFormat property, which is a specific property of the TFloatField class:

procedure TForm2.SpeedButton1Click(Sender: TObject);
  (cds.FieldByName ('Population') as
    TFloatField).DisplayFormat := '###,###,###';

When you set field properties related to data input or output, the changes apply to every record in the table. When you set properties related to the value of the field, however, you always refer to the current record only. For example, you can output the population of the current country in a message box by writing the following:

procedure TForm2.SpeedButton2Click(Sender: TObject);
  ShowMessage (string (cds ['Name']) +': '+ string (cds ['Population']));

When you access the value of a field, you can use a series of As properties to handle the current field value using a specific data type (if this data type is available; otherwise, an exception is raised):

AsBoolean: Boolean;
AsDateTime: TDateTime;
AsFloat: Double;
AsInteger: LongInt;
AsString: string;
AsVariant: Variant;

These properties can be used to read or change the value of the field. Changing the value of a field is possible only if the dataset is in edit mode. As an alternative to the As properties, you can access the value of a field by using its Value property, which is defined as a variant.

Most of the other properties of the TField component, such as Alignment, DisplayLabel, DisplayWidth, and Visible, reflect elements of the field's user interface and are used by the various data-aware controls, particularly DBGrid. In the FieldAcc example, clicking the third speed button changes the Alignment of every field:

procedure TForm2.SpeedButton3Click(Sender: TObject);
  I: Integer;
  for I := 0 to cds.FieldCount - 1 do
    cds.Fields[I].Alignment := taCenter;

This change affects the output of the DBGrid and of the DBEdit control I added to the toolbar, which shows the name of the country. You can see this effect, along with the new display format, in Figure 13.6.

Click To expand
Figure 13.6: The output of the FieldAcc example after the Center and Format buttons have been clicked

A Hierarchy of Field Classes

The VCL includes a number of field class types. Delphi automatically uses one of them depending on the data definition in the database, when you open a table at run time or when you use the Fields Editor at design time. Table 13.2 shows the complete list of subclasses of the TField class.

Table 13.2: The Subclasses of TField


Base Class




An ADT (Abstract Data Type) field, corresponding to an object field in an object relational database.



Represents a maintained aggregate. It is used in the ClientDataSet component and is discussed in Chapter 14.



An array of objects in an object relational database.



A whole positive number connected with a Paradox table's auto-increment field (a special field automatically assigned a different value for each record). Note that Paradox AutoInc fields do not always work perfectly, as discussed in Chapter 14.



Real numbers with a fixed number of digits after the decimal point.



Generally not used directly. This is the base class of the next two classes.



Binary data with no size limit (BLOB stands for binary large object). The theoretical maximum limit is 2 GB.



A Boolean value.



Arbitrary data with a large (up to 64 KB characters) but fixed size.



Currency values with the same range as the Real data type.



An object corresponding to a separate table in an object relational database.



A date value.



A date and time value.



Floating-point numbers (8 byte).



(New field type in Delphi 6.) A true binary-coded decimal (BCD), as opposed to the existing TBCDField type, which converted BCD values to the Currency type. This field type is used automatically only by dbExpress datasets.



A graphic of arbitrary length.



A field representing a COM Globally Unique Identifier (part of the ADO support).



A field representing pointers to IDispatch COM interfaces (part of the ADO support).



Whole numbers in the range of long integers (32 bits).



Generally not used directly. This is the base class of fields that contain pointers to interfaces (IUnknown) as data.



Very large integers (64 bit).



Text of arbitrary length.



Generally not used directly. This is the base class of all the numeric field classes.



Generally not used directly. This is the base class for fields providing support for object relational databases.



A pointer to an object in an object relational database.



Whole numbers in the range of integers (16 bits).



(New field type in Delphi 6.) Supports the date/time representation used in dbExpress drivers.



Text data of a fixed length (up to 8192 bytes).



A time value.



Arbitrary data; up to 64 KB of characters. Very similar to the TBytesField base class.



A field representing a variant data type (part of the ADO support).



A field representing a Unicode (16 bits per character) string.



Whole positive numbers in the range of words or unsigned integers (16 bits).

The availability of any particular field type, and the correspondence with the data definition, depends on the database in use. This is particularly true for the field types that provide support for object relational databases.

Adding a Calculated Field

Now that you've been introduced to TField objects and have seen an example of their run time use,
I will build an example based on the declaration of field objects at design time using the Fields Editor and then add a calculated field. In the country.cds sample dataset, both the population and the area of each country are available; you can use this data to compute the population density.

To build the new example, named Calc, follow these steps:

  1. Add a ClientDataSet component to a form.

  2. Open the Fields Editor. In this editor, right-click, choose the Add Field command, and select some of the fields. (I included them all.)

  3. Select the New Field command and enter a proper name and data type (Float, for a TFloatField) for the new calculated field, as you can see in Figure 13.7.

    Click To expand
    Figure 13.7: The definition of a calculated field in the Calc example


Obviously, because you create some field components at design time using the Fields Editor, the fields you skip won't get a corresponding object. As I already mentioned, the fields you skip will not be available even at run time, with Fields or FieldByName. When a program opens a table at run time, if there are no design-time field components, Delphi creates field objects corresponding to the table definition. If there are some design-time fields, however, Delphi uses those fields without adding any extra field objects.

Of course, you also need to provide a way to calculate the new field. This is accomplished in the OnCalcFields event of the ClientDataSet component, which has the following code (at least in a first version):

procedure TForm2.cdsCalcFields(DataSet: TDataSet);
  cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value;

In general, calculated fields are computed for each record and recalculated each time the record is loaded in an internal buffer, invoking the OnCalcFields event over and over. For this reason, a handler of this event should be extremely fast to execute and cannot alter the status of the dataset by accessing different records. A more time-efficient (but less memory-efficient) version of a calculated field is provided by the ClientDataSet component with internally calculated fields: These fields are evaluated only once—when they are loaded—and the result is stored in memory for future requests.

Everything fine? Not at all! If you enter a new record and do not set the value of the population and area, or if you accidentally set the area to zero, the division will raise an exception, making it problematic to continue using the program. As an alternative, you could have handled every exception of the division expression and set the resulting value to zero:

    cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value;
    on Exception do
      cdsPopulationDensity.Value := 0;

However, you can do even better: You can check whether the value of the area is defined—if it is not null—and whether it is not zero. It is better to avoid using exceptions when you can anticipate possible error conditions:

  if not cdsArea.IsNull and (cdsArea.Value <> 0) then
    cdsPopulationDensity.Value := cdsPopulation.Value / cdsArea.Value
    cdsPopulationDensity.Value := 0;

The code for the cdsCalcFields method (in each of the three versions) accesses some fields directly. It can do so because you used the Fields Editor, and it automatically created the corresponding field declarations, as you can see in this excerpt of the form's interface declaration:

  TCalcForm = class(TForm)
    cds: TClientDataSet;
    cdsPopulationDensity: TFloatField;
    cdsArea: TFloatField;
    cdsPopulation: TFloatField;
    cdsName: TStringField;
    cdsCapital: TStringField;
    cdsContinent: TStringField;
    procedure cdsCalcFields(DataSet: TDataset);

Each time you add or remove fields in the Fields Editor, you can see the effect of your action immediately in the grid present in the form (unless the grid has its own column objects defined, in which case you often don't see any change). Of course, you won't see the values of a calculated field at design time; they are available only at run time, because they result from the execution of compiled Delphi language code.

Because you have defined components for the fields, you can use them to customize some of the grid's visual elements. For example, to set a display format that adds a comma to separate thousands, you can use the Object Inspector to change the DisplayFormat property of some field components to ###,###,###. This change has an immediate effect on the grid at design time.


The display format I just mentioned (and used in the previous example) uses the Windows International Settings to format the output. When Delphi translates the numeric value of this field to text, the comma in the format string is replaced by the proper ThousandSeparator character. For this reason, the output of the program will automatically adapt itself to different international settings. On computers that have the Italian configuration, for example, the comma is replaced by a period.

After working on the table components and the fields, I customized the DBGrid using its Columns property editor. I set the Population Density column to read-only and set its ButtonStyle property to cbsEllipsis to provide a custom editor. When you set this value, a small button with an ellipsis is displayed when the user tries to edit the grid cell. Clicking the button invokes the DBGrid's OnEditButtonClick event:

procedure TCalcForm.DBGrid1EditButtonClick(Sender: TObject);
  MessageDlg (Format (
    'The population density (%.2n)'#13 +
    'is the Population (%.0n)'#13 +
    'divided by the Area (%.0n).'#13#13 +
    'Edit these two fields to change it.',
    mtInformation, [mbOK], 0);

I haven't provided a real editor but rather a message describing the situation, as you can see in Figure 13.8, which shows the values of the calculated fields. To create an editor, you might build a secondary form to handle special data entries.

Click To expand
Figure 13.8: The output of the Calc example. Notice the Population Density calculated column and the ellipsis button displayed when you edit it.

Lookup Fields

As an alternative to placing a DBLookupComboBox component in a form (discussed earlier in this chapter in the section "Using Lookup Controls"), you can also define a lookup field, which can be displayed with a drop-down lookup list inside a DBGrid component. You've seen that to add a fixed selection to a DBGrid, you can edit the PickList subproperty of the Columns property. To customize the grid with a live lookup, however, you have to define a lookup field using the Fields Editor.

As an example, I built the FieldLookup program, which has a grid that displays orders; it includes a lookup field to display the name of the employee who took the order, instead of the employee's code number. To accomplish this functionality, I added to the data module a ClientDataSet component referring to the employee.cds dataset. Then I opened the Fields Editor for the orders dataset and added all the fields. I selected the EmpNo field and set its Visible property to False to remove it from the grid (you cannot remove it altogether, because it is used to build the cross-reference with the corresponding field of the employee dataset).

Now it is time to define the lookup field. If you followed the preceding steps, you can use the Fields Editor of the orders dataset and select the New Field command to open the New Field dialog box. The values you specify here will affect the properties of a new TField added to the table, as demonstrated by the DFM description of the field:

object cds2Employee: TStringField
  FieldKind = fkLookup
  FieldName = 'Employee'
  LookupDataSet = cds2
  LookupKeyFields = 'EmpNo'
  LookupResultField = 'LastName'
  KeyFields = 'EmpNo'
  Size = 30
  Lookup = True

This is all that is needed to make the drop-down list work (see Figure 13.9) and to also view the value of the cross-reference field at design time. Notice that you don't need to customize the Columns property of the grid because the drop-down button and the value of seven rows are used by default. However, this doesn't mean you cannot use this property to further customize these and other visual elements of the grid.

Click To expand
Figure 13.9:  The output of the FieldLookup example, with the drop-down list inside the grid displaying values taken from another database table

This program has another specific feature. The two ClientDataSet components and the two DataSource components have not been placed on a form but rather on a special container for nonvisual components called a data module (see the sidebar "A Data Module for Data-Access Components"). You can obtain a data module from Delphi's File ® New menu. After adding components to it, you can link them from controls on other forms with the File ® Use Unit command.

Handling Null Values with Field Events

In addition to a few interesting properties, field objects have a few key events. The OnValidate event can be used to provide extended validation of a field's value and should be used whenever you need a complex rule that the ranges and constraints provided by the field cannot express. This event is triggered before the data is written to the record buffer, whereas the OnChange event is fired soon after the data has been written.

Two other events—OnGetText and OnSetText—can be used to customize a field's output. These two events are extremely powerful: They allow you to use data-aware controls even when the representation of a field you want to display is different from the one Delphi will provide by default.

Handling null values provides an example of the use of these events. On SQL servers, storing an empty value for a field is a separate operation from storing a null value for a field. The latter tends to be more correct, but Delphi by default uses empty values and displays the same output for an empty or a null field. Although this behavior can be useful in general for strings and numbers, it becomes extremely important for dates, where it is hard to set a reasonable default value and where if the deletes the contents of the field, you might have invalid input.

The NullDates program displays specific text for dates that have a null value and clears the field (setting it to the null value) when the user uses an empty string in input. Here is the relevant code of the field's two event handlers:

procedure TForm1.cdsShipDateGetText(Sender: TField;
  var Text: String; DisplayText: Boolean);
  if Sender.IsNull then
    Text := '<undefined>'
    Text := Sender.AsString;
procedure TForm1.cdsShipDateSetText(Sender: TField; const Text: String);
  if Text = '' then
    Sender.AsString := Text;

Figure 13.10 shows an example of the program's output, with undefined (or null) values for some shipping dates.

Click To expand
Figure 13.10:  By handling the OnGetText and On-SetText events of a date field, the NullDates example displays specific output for null values.

The handling of null values in Delphi 6 and 7 can be affected by changes in the way null variants work. As discussed in Chapter 3, "The Run Time Library," in the section "The Variants and VarUtils Units," comparing a field with a null value with another field will have a different effect in the latest versions of Delphi than in the past. As discussed in that section, in Delphi 7 you can use global variables to fine-tune the effect of comparisons involving variants.

Part I: Foundations