Name 

Description 

Creating an Index with IndexFieldNames 

If there is no defined index that implements the sort order you want, you can create a pseudo-index using the IndexFieldNames property. The value of IndexFieldNames is a string. To specify a sort order, list each column name to use in the order it should be used, and delimit the names with semicolons. Sorting is by ascending order only. Case-sensitivity of the sort depends on the capabilities of your server. See your... more 

Supplying Parameters at Design Time 

At design time, you can specify parameter values using the parameter collection editor. To display the parameter collection editor, click on the ellipsis button for the Params or Parameters property in the Object Inspector. If the SQL statement does not contain any parameters, no objects are listed in the collection editor.

Supplying Parameters at Runtime 

To create parameters at runtime, you can use the

• ParamByName method to assign values to a parameter based on its name (not available for TADOQuery)
• Params or Parameters property to assign values to a parameter based on the parameter's ordinal position within the SQL statement.
• Params.ParamValues or Parameters.ParamValues property to assign values to one or more parameters in a single command line, based on the name of each parameter set.

The following code uses ParamByName to assign the text of an edit box to the :Capital parameter:  

Adding New Records 

A dataset must be in dsInsert mode before an application can add new records. In code, you can use the Insert or Append methods to put a dataset into dsInsert mode if the read-only CanModify property for the dataset is True.
When a dataset transitions to dsInsert mode, it first receives a BeforeInsert event. After the transition to insert mode is successfully completed, the dataset receives first an OnNewRecord event hand then an AfterInsert event. You can use these events, for example, to provide initial values to newly inserted records:  

Applying or Canceling a Range 

When you call SetRangeStart or EditRangeStart to specify the start of a range, or SetRangeEnd or EditRangeEnd to specify the end of a range, the dataset enters the dsSetKey state. It stays in that state until you apply or cancel the range. 

Calculating Fields 

Using the Fields editor, you can define calculated fields for your datasets. When a dataset contains calculated fields, you provide the code to calculate those field's values in an OnCalcFields event handler.
The AutoCalcFields property determines when OnCalcFields is called. If AutoCalcFields is True, OnCalcFields is called when

• A dataset is opened.
• The dataset enters edit mode.
• A record is retrieved from the database.
• Focus moves from one visual component to another, or from one column to another in a data-aware grid control and the current record has been modified.

If AutoCalcFields is False, then OnCalcFields is not... more 

Canceling Changes 

An application can undo changes made to the current record at any time, if it has not yet directly or indirectly called Post. For example, if a dataset is in dsEdit mode, and a user has changed the data in one or more fields, the application can return the record back to its original values by calling the Cancel method for the dataset. A call to Cancel always returns a dataset to dsBrowse state.
If the dataset was in dsEdit or dsInsert mode when your application called Cancel, it receives BeforeCancel and AfterCancel events before and after the current... more 

Controlling Read/Write Access to Tables 

By default when a table type dataset is opened, it requests read and write access for the underlying database table. Depending on the characteristics of the underlying database table, the requested write privilege may not be granted (for example, when you request write access to an SQL table on a remote server and the server restricts the table's access to read only).

Creating and Deleting Tables 

Some table type datasets let you create and delete the underlying tables at design time or at runtime. Typically, database tables are created and deleted by a database administrator. However, it can be handy during application development and testing to create and destroy database tables that your application can use. 

Creating Filters 

There are two ways to create a filter for a dataset:

• Set the Filter property. Filter is especially useful for creating and applying filters at runtime.
• Write an OnFilterRecord event handler for simple or complex filter conditions. With OnFilterRecord, you specify filter conditions at design time. Unlike the Filter property, which is restricted to a single string containing filter logic, an OnFilterRecord event can take advantage of branching and looping logic to create complex, multi-level filter conditions.

The main advantage to creating filters using the Filter property is that your application can create, change, and apply filters dynamically, (for... more 

Creating Master/detail Relationships 

Table type datasets can be linked into master/detail relationships. When you set up a master/detail relationship, you link two datasets so that all the records of one (the detail) always correspond to the single current record in the other (the master).
Table type datasets support master/detail relationships in two very distinct ways:

• All table type datasets can act as the detail of another dataset by linking cursors. This process is described in Making the table a detail of another dataset.
• TTable, TSQLTable, and all client datasets can act as the master in a master/detail relationship that uses... more 

Deleting Records 

Use the Delete method to delete the current record in an active dataset. When the Delete method is called,

• The dataset receives a BeforeDelete event.
• The dataset attempts to delete the current record.
• The dataset returns to the dsBrowse state.
• The dataset receives an AfterDelete event.

If want to prevent the deletion in the BeforeDelete event handler, you can call the global Abort procedure:  

Editing Records 

A dataset must be in dsEdit mode before an application can modify records. In your code you can use the Edit method to put a dataset into dsEdit mode if the read-only CanModify property for the dataset is True.
When a dataset transitions to dsEdit mode, it first receives a BeforeEdit event. After the transition to edit mode is successfully completed, the dataset receives an AfterEdit event. Typically, these events are used for updating the user interface to indicate the current state of the dataset. If the dataset can't be put into edit mode for some reason, an... more 

Emptying Tables 

Many table type datasets supply a single method that lets you delete all rows of data in the table.  

Enabling and Disabling Filtering 

 

Establishing Master/detail Relationships Using Parameters 

To set up a master/detail relationship where the detail set is a query-type dataset, you must specify a query that uses parameters. These parameters refer to current field values on the master dataset. Because the current field values on the master dataset change dynamically at runtime, you must rebind the detail set's parameters every time the master record changes. Although you could write code to do this using an event handler, all query-type datasets except TIBQuery provide an easier mechanism using the DataSource property.
If parameter values for a parameterized query are not bound at design time or specified at... more 

Executing a Search with Find Methods 

The Find methods do the same thing as the Goto methods, except that you do not need to explicitly put the dataset in dsSetKey state to specify the key field values on which to search. 

Executing a Search with Goto Methods 

 

Executing Queries That Don't Return a Result Set 

When a query returns a set of records (such as a SELECT query), you execute the query the same way you populate any dataset with records: by setting Active to True or calling the Open method.
However, often SQL commands do not return any records. Such commands include statements that use Data Definition Language (DDL) or Data Manipulation Language (DML) statements other than SELECT statements (For example, INSERT, DELETE, UPDATE, CREATE INDEX, and ALTER TABLE commands do not return any records).
For all query-type datasets, you can execute a query that does not return a result set by calling ExecSQL... more 

Executing Stored Procedures That Don't Return a Result Set 

When a stored procedure returns a cursor, you execute it the same way you populate any dataset with records: by setting Active to True or calling the Open method.
However, often stored procedures do not return any data, or only return results in output parameters. You can execute a stored procedure that does not return a result set by calling ExecProc. After executing the stored procedure, you can use the ParamByName method to read the value of the result parameter or of any output parameters:  

Fetching Multiple Result Sets 

Some stored procedures return multiple sets of records. The dataset only fetches the first set when you open it. If you are using TSQLStoredProc or TADOStoredProc, you can access the other sets of records by calling the NextRecordSet method:  

Limiting Records with Ranges 

You can temporarily view and edit a subset of data for any dataset by using filters. Some table type datasets support an additional way to access a subset of available records, called ranges.
Ranges only apply to TTable and to client datasets. Despite their similarities, ranges and filters have different uses. The following topics discuss the differences between ranges and filters and how to use ranges:

• Understanding the differences between ranges and filters
• Specifying ranges
• Modifying a range
• Applying or canceling a range

 

Making the Table a Detail of Another Dataset 

A table type dataset's MasterSource and MasterFields properties can be used to establish one-to-many relationships between two datasets.
The MasterSource property is used to specify a data source from which the table gets data from the master table. This data source can be linked to any type of dataset. For instance, by specifying a query's data source in this property, you can link a client dataset as the detail of the query, so that the client dataset tracks events occurring in the query.
The dataset is linked to the master table based on its current index. Before you specify the... more 

Marking and Returning to Records 

In addition to moving from record to record in a dataset (or moving from one record to another by a specific number of records), it is often also useful to mark a particular location in a dataset so that you can return to it quickly when desired. TDataSet introduces a bookmarking feature that consists of a Bookmark property and five bookmark methods.
TDataSet implements virtual bookmark methods. While these methods ensure that any dataset object derived from TDataSet returns a value if a bookmark method is called, the return values are merely defaults that do not keep track of the... more 

Modifying a Range 

Two functions enable you to modify the existing boundary conditions for a range: EditRangeStart, for changing the starting values for a range; and EditRangeEnd, for changing the ending values for the range. 

Modifying Entire Records 

On forms, all data-aware controls except for grids and the navigator provide access to a single field in a record.
In code, however, you can use the following methods that work with entire record structures provided that the structure of the database tables underlying the dataset is stable and does not change. The following table summarizes the methods available for working with entire records rather than individual fields in those records:
Methods that work with entire records  

Navigating Records in a Filtered Dataset 

There are four dataset methods that navigate among records in a filtered dataset. The following table lists these methods and describes what they do:
Filtered dataset navigational methods  

Obtaining Information About Indexes 

Your application can obtain information about server-defined indexes from all table type datasets. To obtain a list of available indexes for the dataset, call the GetIndexNames method. GetIndexNames fills a string list with valid index names. For example, the following code fills a listbox with the names of all indexes defined for the CustomersTable dataset:  

Opening and Closing Datasets 

To read or write data in a dataset, an application must first open it. You can open a dataset in two ways:  

Posting Data 

After you finish editing a record, you must call the Post method to write out your changes. The Post method behaves differently, depending on the dataset's state and on whether you are caching updates.

• If you are not caching updates, and the dataset is in the dsEdit or dsInsert state, Post writes the current record to the database and returns the dataset to the dsBrowse state.
• If you are caching updates, and the dataset is in the dsEdit or dsInsert state, Post writes the current record to an internal cache and returns the dataset to the dsBrowse state. The edits... more 

Preparing Queries 

Preparing a query is an optional step that precedes query execution. Preparing a query submits the SQL statement and its parameters, if any, to the data access layer and the database server for parsing, resource allocation, and optimization. In some datasets, the dataset may perform additional setup operations when preparing the query. These operations improve query performance, making your application faster, especially when working with updatable queries.
An application can prepare a query by setting the Prepared property to True. If you do not prepare a query before executing it, the dataset automatically prepares it for you each time... more 

Preparing Stored Procedures 

As with query-type datasets, stored procedure-type datasets must be prepared before they execute the stored procedure. Preparing a stored procedure tells the data access layer and the database server to allocate resources for the stored procedure and to bind parameters. These operations can improve performance.
If you attempt to execute a stored procedure before preparing it, the dataset automatically prepares it for you, and then unprepares it after it executes. If you plan to execute a stored procedure a number of times, it is more efficient to explicitly prepare it by setting the Prepared property to True.  

Searching On Partial Keys 

Each time you call SetKey or FindKey, the method clears any previous values in the Fields property. If you want to repeat a search using previously set fields, or you want to add to the fields used in a search, call EditKey in place of SetKey and FindKey.
For example, suppose you have already executed a search of the Employee table based on the City field of the "CityIndex" index. Suppose further that "CityIndex" includes both the City and Company fields. To find a record with a specified company name in a specified city, use the following code:... more 

Searching On Partial Keys 

If the dataset has more than one key column, and you want to search for values in a subset of that key, set KeyFieldCount to the number of columns on which you are searching. For example, if the dataset's current index has three columns, and you want to search for values using just the first column, set KeyFieldCount to 1.
For table type datasets with multiple-column keys, you can search only for values in contiguous columns, beginning with the first. For example, for a three-column key you can search for values in the first column, the first and second, or... more 

Setting Filter Options 

The FilterOptions property lets you specify whether a filter that compares string-based fields accepts records based on partial comparisons and whether string comparisons are case-sensitive. FilterOptions is a set property that can be an empty set (the default), or that can contain either or both of the following values:
FilterOptions values  

Setting the Filter Property 

To create a filter using the Filter property, set the value of the property to a string that contains the filter's test condition. For example, the following statement creates a filter that tests a dataset's State field to see if it contains a value for the state of California:  

Sorting Records with Indexes 

An index determines the display order of records in a table. Typically, records appear in ascending order based on a primary, or default, index. This default behavior does not require application intervention. If you want a different sort order, however, you must specify either

• An alternate index.
• A list of columns on which to sort (not available on servers that aren't SQL-based).

Indexes let you present the data from a table in different orders. On SQL-based tables, this sort order is implemented by using the index to generate an ORDER BY clause in a query that fetches the table's records.... more 

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.

 

Specifying the Current Record After a Successful Search 

By default, a successful search positions the cursor on the first record that matches the search criteria. If you prefer, you can set the KeyExclusive property to True to position the cursor on the next record after the first matching record.
By default, KeyExclusive is False, meaning that successful searches position the cursor on the first matching record. 

Specifying the Query 

For true query-type datasets, you use the SQL property to specify the SQL statement for the dataset to execute. Some datasets, such as TADODataSet, TSQLDataSet, and client datasets, use a CommandText property to accomplish the same thing.
Most queries that return records are SELECT commands. Typically, they define the fields to include, the tables from which to select those fields, conditions that limit what records to include, and the order of the resulting dataset. For example:  

Synchronizing Tables 

If you have two or more datasets that represent the same database table but do not share a data source component, then each dataset has its own view on the data and its own current record. As users access records through each datasets, the components' current records will differ.
If the datasets are all instances of TTable, or all instances of TIBTable, or all client datasets, you can force the current record for each of these datasets to be the same by calling the GotoCurrent method. GotoCurrent sets its own dataset's current record to the current record of... more 

Understanding the Differences Between Ranges and Filters 

Both ranges and filters restrict visible records to a subset of all available records, but the way they do so differs. A range is a set of contiguously indexed records that fall between specified boundary values. For example, in an employee database indexed on last name, you might apply a range to display all employees whose last names are greater than "Jones" and less than "Smith". Because ranges depend on indexes, you must set the current index to one that can be used to define the range. As with specifying an index to sort records, you can assign the... more 

Using Indexes to Search for Records 

You can search against any dataset using the Locate and Lookup methods of TDataSet. However, by explicitly using indexes, some table type datasets can improve over the searching performance provided by the Locate and Lookup methods.
ADO datasets all support the Seek method, which moves to a record based on a set of field values for fields in the current index. Seek lets you specify where to move the cursor relative to the first or last matching record.
TTable and all types of client dataset support similar indexed-based searches, but use a combination of related methods. The following table... more 

Using Nested Detail Tables 

A nested table is a detail dataset that is the value of a single dataset field in another (master) dataset. For datasets that represent server data, a nested detail dataset can only be used for a dataset field on the server. TClientDataSet components do not represent server data, but they can also contain dataset fields if you create a dataset for them that contains nested details, or if they receive data from a provider that is linked to the master table of a master/detail relationship.

Using Parameters in Queries 

A parameterized SQL statement contains parameters, or variables, the values of which can be varied at design time or runtime. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement. For example, in the following INSERT statement, values to insert are passed as parameters:  

Using Query-type Datasets 

 

Using Stored Procedure-type Datasets 

How your application uses a stored procedure depends on how the stored procedure was coded, whether and how it returns data, the specific database server used, or a combination of these factors. 

Using TDataSet Descendants 

TDataSet has several immediate descendants, each of which corresponds to a different data access mechanism. You do not work directly with any of these descendants. Rather, each descendant introduces the properties and methods for using a particular data access mechanism. These properties and methods are then exposed by descendant classes that are adapted to different types of server data. The immediate descendants of TDataSet include

• TBDEDataSet, which uses the Borland Database Engine (BDE) to communicate with the database server. The TBDEDataSet descendants you use are TTable, TQuery, TStoredProc, and TNestedTable. The unique features of... more 

Using Table Type Datasets 

 

Using the Eof and Bof Properties 

Two read-only, runtime properties, Eof (End-of-file) and Bof(Beginning-of-file), are useful when you want to iterate through all records in a dataset. 

Using the First and Last Methods 

The First method moves the cursor to the first row in a dataset and sets the BOF property to True. If the cursor is already at the first row in the dataset, First does nothing.
For example, the following code moves to the first record in CustTable:  

Using the MoveBy Method 

MoveBy lets you specify how many rows forward or back to move the cursor in a dataset. Movement is relative to the current record at the time that MoveBy is called. MoveBy also sets the BOF and EOF properties for the dataset as appropriate.
This function takes an integer parameter, the number of records to move. Positive integers indicate a forward move and negative integers indicate a backward move. MoveBy returns the number of rows it moves. If you attempt to move past the beginning or... more 

Using the Next and Prior Methods 

The Next method moves the cursor forward one row in the dataset and sets the BOF property to False if the dataset is not empty. If the cursor is already at the last row in the dataset when you call Next, nothing happens.
For example, the following code moves to the next record in CustTable:  

Using Unidirectional Result Sets 

When a query-type dataset returns a result set, it also receives a cursor, or pointer to the first record in that result set. The record pointed to by the cursor is the currently active record. The current record is the one whose field values are displayed in data-aware components associated with the result set's data source. Unless you are using dbExpress, this cursor is bi-directional by default. A bi-directional cursor can navigate both forward and backward through its records. Bi-directional cursor support requires some additional processing overhead, and can slow some queries.
If you do not need to be able... more 

Working with Stored Procedure Parameters 

There are four types of parameters that can be associated with stored procedures:

• Input parameters, used to pass values to a stored procedure for processing.
• Output parameters, used by a stored procedure to pass return values to an application.
• Input/output parameters, used to pass values to a stored procedure for processing, and used by the stored procedure to pass return values to the application.
• A result parameter, used by some stored procedures to return an error or status value to an application. A stored procedure can only return one result parameter.

Whether a stored procedure uses a... more 

Using Locate 

Locate moves the cursor to the first row matching a specified set of search criteria. In its simplest form, you pass Locate the name of a column to search, a field value to match, and an options flag specifying whether the search is case-insensitive or if it can use partial-key matching. (Partial-key matching is when the criterion string need only be a prefix of the field value.) For example, the following code moves the cursor to the first row in the CustTable where the value in the Company column is "Professional Divers, Ltd.":  

Using Lookup 

Lookup searches for the first row that matches specified search criteria. If it finds a matching row, it forces the recalculation of any calculated fields and lookup fields associated with the dataset, then returns one or more fields from the matching row. Lookup does not move the cursor to the matching row; it only returns values from it.
In its simplest form, you pass Lookup the name of field to search, the field value to match, and the field or fields to return. For example, the following code looks for the first record in the CustTable where the value of... more 

Modifying Data 

You can use the following dataset methods to insert, update, and delete data if the read-only CanModify property is True. CanModify is True unless the dataset is unidirectional, the database underlying the dataset does not permit read and write privileges, or some other factor intervenes. (Intervening factors include the ReadOnly property on some datasets or the RequestLive property on TQuery components.)
Dataset methods for inserting, updating, and deleting data  

Searching Datasets 

If a dataset is not unidirectional, you can search against it using the Locate and Lookup methods. These methods enable you to search on any type of columns in any dataset.
The following topics discuss Locate and Lookup in greater detail:

• Using Locate
• Using Lookup

 

Specifying an Index with IndexName 

Use the IndexName property to cause an index to be active. Once active, an index determines the order of records in the dataset. (It can also be used as the basis for a master-detail link, an index-based search, or index-based filtering.)
To activate an index, set the IndexName property to the name of the index. In some database systems, primary indexes do not have names. To activate one of these indexes, set IndexName to a blank string.
At design-time, you can select an index from a list of available indexes by clicking the property's ellipsis button in the Object Inspector... more 

Displaying and Editing a Subset of Data Using Filters 

An application is frequently interested in only a subset of records from a dataset. For example, you may be interested in retrieving or viewing only those records for companies based in California in your customer database, or you may want to find a record that contains a particular set of field values. In each case, you can use filters to restrict an application's access to a subset of all records in the dataset.
With unidirectional datasets, you can only limit the records in the dataset by using a query that restricts the records in the dataset. With other TDataSet descendants,... more 

Understanding Datasets: Overview 

The fundamental unit for accessing data is the dataset family of objects. Your application uses datasets for all database access. A dataset object represents a set of records from a database organized into a logical table. These records may be the records from a single database table, or they may represent the results of executing a query or stored procedure.
All dataset objects that you use in your database applications descend from TDataSet, and they inherit data fields, properties, events, and methods from this class.
TDataSet is a virtualized dataset, meaning that many of its properties and methods are virtual... more 

Determining Dataset States 

The state—or mode—of a dataset determines what can be done to its data. For example, when a dataset is closed, its state is dsInactive, meaning that nothing can be done to its data. At runtime, you can examine a dataset's read-only State property to determine its current state. The following table summarizes possible values for the State property and what they mean:
Values for the dataset State property  

Navigating Datasets 

Each active dataset has a cursor, or pointer, to the current row in the dataset. The current row in a dataset is the one whose field values currently show in single-field, data-aware controls on a form, such as TDBEdit, TDBLabel, and TDBMemo. If the dataset supports editing, the current record contains the values that can be manipulated by edit, insert, and delete methods.
You can change the current row by moving the cursor to point at a different row. The following table lists methods you can use in application code to move to different records:
Navigational methods of... more 

Writing an OnFilterRecord Event Handler 

You can write code to filter records using the OnFilterRecord events generated by the dataset for each record it retrieves. This event handler implements a test that determines if a record should be included in those that are visible to the application.
To indicate whether a record passes the filter condition, your OnFilterRecord handler sets its Accept parameter to True to include a record, or False to exclude it. For example, the following filter displays only those records with the State field set to "CA":  

Types of Datasets 

Using TDataSet descendants classifies TDataSet descendants by the method they use to access their data. Another useful way to classify TDataSet descendants is to consider the type of server data they represent. Viewed this way, there are three basic classes of datasets:
Table type datasets: Table type datasets represent a single table from the database server, including all of its rows and columns. Table type datasets include TTable, TADOTable, TSQLTable, and TIBTable.
Table type datasets let you take advantage of indexes defined on the server. Because there is a one-to-one correspondence between database table and dataset, you can use... more