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.

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.loCaseInsensitive and 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.loCaseInsensitive and DB.loPartialKey constants are analogues to the loCaseInsensitive and 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 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, GetCell, SearchCell and Main routines:

• 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 First and 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.MoveBy and 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: First and 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 Pos property.

I would like to note once again that this approach only works if the TDBGrid displays data of a non-filtered dataset.

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.

Since the 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 Locate method.

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 TField and 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 TDataset object.