Working With TDBGrid - Selecting Records
To simulate user actions over the Borland TDBGrid control or to obtain the data of the grid cells, you will have to locate the row that contains the data that you are going to work with. In this article I'd like to describe approaches that can you can use to locate records in the TDBGrid control:
Searching by Using the Underlying Dataset
Searching by Iterating Through Grid Rows
At the end of the article I'll also compare these approaches.
Note that both approaches use internal methods and properties of the TDBGrid object. In order for TestComplete to be able to access these methods and properties, the tested application must be compiled as an Open Application with debug information. Searching by Using the Underlying Dataset
A TDBGrid control displays data stored in the dataset (
TDataset object), to which the control is connected. When the tested application activates another record in the dataset, the TDBGrid control activates the appropriate row. To choose a row in the grid, you can use the methods of the dataset object that search for the appropriate record. When you activate a record in the dataset, the grid control will highlight the appropriate row.
To find a record in the dataset, you can use methods of the
TDataset object. Most often you will use the
Locate method. This method locates a record, in which fields contain the specified values. The method has the following syntax:
DatasetObj.Locate(Fields, Values, Options)
The method parameters specify the following information:
- Fields - The name of the dataset field (or fields), in which the method will search. Multiple field names should be separated with semicolons.
- Values - One or several sought-for values. If you search in several fields, the parameter should specify the array holding the search values.
- Options - A combination of the two constants:
DB.loPartialKey. These constants defines the search options. If
DB.loCaseInsensitive is specified, the method ignores the case when comparing string values. If
DB.loPartialKey is specified, the search value can include only part of the matching field, for instance, New will match both Newfoundland and New York. The
DB.loPartialKey constants are analogues to the
loPartialKey constants defined by the VCL library.
If the method finds the record, it returns True, else - False.
For complete information on the
Locate method, see Delphi or C++Builder documentation.
The first parameter of the method should specify the dataset’s field name (or names) and these names may differ from the column names. So, you have to write script code that obtains the field name by column name (see sample below).
Also, since the
Locate method searches in dataset fields, the data type of the search values must match the data type of dataset fields. Else, an error will occur during the method execution. For instance, if you search in a text field and specify an integer value for the search, you will get an error message informing you about incompatible data types.
The following code demonstrates how you can use the Searching by Iterating Through Grid Rows
Locate method to find a record by a field value:
Quite often, users search for records by iterating through grid records and examining cell values. You can create a script that will emulate these user actions. Below is a sample script that demonstrates this approach.
The sample contains the
ActivateRow - This routine selects the row specified by its index in the grid (the index is zero-based, that is, the topmost data row has index 0). To select the row, we choose the appropriate record in the dataset connected to the TDBGrid control. To change the record in the dataset, we call the dataset’s
MoveBy methods. The
First method activates the first record in the dataset. The
MoveBy method changes the active record to the record, whose position is specified by the method parameter. The parameter specifies the position relative to the current active record. A positive value of the parameter means the forward moving and a negative value means the backward moving. (See description of the
TDataset.First methods in Borland Delphi or C++Builder documentation).
Using these two methods ensures the correct positioning in both filtered and non-filtered datasets.
GetCell - Returns the cell’s contents by the row and column indexes of the cell. Both indexes are zero-based, that is, we consider the top-left data cell in the grid as having indexes (0, 0). At the beginning, the
GetCell function uses the internal properties of the TDBGrid object to obtain the name of the field that corresponds to the specified column. Then,
GetCell calls the
ActivateRow routine to activate the desired row in the dataset. Finally,
GetCell uses the
FieldByName property of the grid’s dataset to obtain the field object (
TField) and then calls the
AsString property of this object to get the field’s text.
SearchCell - Activates the grid’s row, which contains a cell storing the specified value. The cell is specified by its column index (zero-based). The routine goes through all the grid rows (from the top of the grid) and for each record it compares the value of the specified cell with the sought-for value.
Note that the routine performs the search within the cell’s text, so the sought-for values should always be a string. For instance, if a cell displays a numerical value, say 123, the sought-for value should be a string 123, not an integer value.
Main - The “main” routine of the sample. It obtains the scripting object that corresponds to the TDBGrid control, then calls the
SearchCell function and posts the results to the test log.
As you can see, to activate a row, we called two methods of the grid’s dataset:
MoveBy. That is, locating a record requires two steps: we choose the first record and then move the selection to the specified distance. Using this technique guarantees that the selection procedure will work in both filtered and non-filtered datasets.
If the dataset is not filtered, you can activate grid rows by using the grid’s vertical scroll bar. When displaying the data stored in a non-filtered dataset, the TDBGid control sets the minimum position of the vertical scroll bar to 1 and the maximum position to the number of dataset records. By specifying the scroll box position you can activate the desired row by its index.
To get a scripting interface to the grid’s vertical scroll bar, use the
VScroll property of the scripting object that corresponds to the TDBGrid control. This property is provided by TestComplete. It returns the
ScrollBar object corresponding to the vertical scroll bar of the control. To set the scroll bar’s position, use the
Pos property of the
ScrollBar object. The following code demonstrates how you can use this property:
Note that since the minimum scroll bar position is 0 and the row index is zero-based, we increased the RowIndex parameter by 1 when assigning it to the
I would like to note once again that this approach only works if the TDBGrid displays data of a non-filtered dataset. Which Approach to Choose
You read about two variants of the searching procedure. One of these variants uses the
Locate method and another one iterates through the rows of the grid to find the desired row.
In general, the approach that uses the
Locate method provides a faster search. If the dataset has indexes, the
Locate method will search using these indexes. However, the method analyzes data stored in the database fields and this may cause problems, which can affect your decision on using the method.
Locate method checks data stored in the database fields, you have to be aware of the field data type and specify the appropriate data as a search value. Else, an error will occur. For instance, if the field holds an integer value and you specify a string, you will get an error message during the script execution.
Note that the fields’s data may differ from the data you see in the grid cells: VCL includes special events, which let programmers change the field text to be displayed in visual controls. So, the
TDBGrid control may display the modified text rather than the data stored in the field. For instance, a field may store an integer value (say, 3), but the grid may display the string other than “3”, because the event handler can change the displayed text to something like “3 book(s)” or “3 user(s)”. Please remember this when specifying the sought-for values for the
The second variant of the search procedure iterates through the grid records and analyzes the field’s text, so it does not depend on the field type and on the
TField object’s events that change the displayed text. However, the iteration approach functions a lot slower than the
Locate method. So, you can use it when you need to search for data within the field’s text.
Note that developers change the cell text using events of the
TDBGrid objects. The iteration approach does not depend on the
TField event handlers. However, it will not help if developers modify the cell values using events of the TDBGrid control. To search for records in this case, you will have to use the
Locate method of the