C++ Builder Developer's Journal / DB dilemmas

December 1997

Detecting changing rows in a TDBGrid

by Jim Bailey

How can we detect the data cursor's movement-s from one row to another in a data grid? There's no direct event available to signal the change of a grid row--a little C++Builder problem that leaves me scratching my head and saying "Why did they do it that way?"

The OnColEnter event fires every time the column changes. When I began working with C++Builder, I was sure OnColEnter had a close cousin in the OnRowEnter category. I found no reference to such an event in the documentation, but there are many great features Borland's documentation didn't mention--surely the row-change event had to be one of them.

Well, after posting several forum messages and getting no response, I've come to believe the TDBGrid's row change doesn't exist, after all. However, I needed a workaround, and I did find one that had economical code and didn't require developing a new component. I later learned of a more elegant method, but I seldom use it. Let's look at both methods.

Method #1

On a form with a TDBGrid component, add a TDBEdit component and set its Visible property to False. Set the TDBEdit component's DataSource to point to the same TdataSource as the grid. Set the DataField property to the primary key of the data set feeding the grid. Next, set up the TDBEdit component's OnChange event, which fires every time the row changes--assuming the primary key is a single-field index. Applications with multiple-field primary keys can use the second method I'll describe, but the TDBEdit component's OnChange event is adequate in many cases. Plus, it offers another advantage. I often need to detect a change in a certain field while scrolling through a database. Detecting such changes requires considerable high-maintenance code like that shown in Figure A.

Figure A: Detecting a change in a database field requires some high-maintenance code.

bool FieldTextChanged()
{
  bool returnValue= false;
  static String ThisWas("v o i d");
  String ThisIs;

  //Get the target fields value.  
  ThisIs = TargetGrid->Columns->Items[1]->Field->AsString;

  // Track column movement or this won't work.
  if(ThisWas == "v o i d")      // first iteration
    ThisWas = ThisIs;
  else {
    if(ThisIs != ThisWas) {
      //Wake up Herman, the data changed!
      ThisWas = ThisIs;
      returnValue = true;
    }
    return returnValuea;
  }
}

There's no need to analyze this code, because it's completely unnecessary. You can track the entire process without a single line of code using the method we just described. How economical can you get!

Consider the grid shown in Figure B. You can quite easily determine when the MemberID changes. Simply add a TDBEdit component, set its DataSource property as described earlier, and set the DataField property to the MemberID field. The control's OnChange event will fire only when the text in the MemberID field changes.

Figure B: You can use our method to determine when the MemberID in this table changes.
[ Figure B ]

I used to think of Method #1 as a kludge, but I'm beginning to consider it a respected technique. Think of the code you can eliminate, and the flexibility you have at revision time. But as much as I like this technique, there are a few reasons not to use it--and some programmers won't like the non-standard use of the TDBEdit control. However, I'll take an invisible control with no code over a slick, high-tech, three-lines-of- code proper method almost every time.

Method #2

Now let's look at a second, somewhat more elegant technique for achieving our goal. First, create an OnDataChange event in the TDataSource referenced in the grid's DataSource property. According to the help file, the event operates as follows: "OnDataChange fires when the current record has been edited and the application moves from one field or record to another in a dataset associated with the data source component." The event does not fire when the application moves from one field to another unless the previous field was changed. It does fire when dataset changes state from dsBrowse to dsEdit or dsInsert. It also fires when the data cursor moves from one record to another. This technique requires at least a few lines of supporting code, as you can see in Figure C. If the TDataSource is located in a DataModule form, the supporting code for this method could be considerable, especially if the TDataSource is used by multiple components and/or forms.

Figure C: Our second method requires some supporting code.

void __fastcall TfrmRowChange::dsDataDataChange(
  TObject *Sender, 
  TField *Field)
{
  TDataSource *ds = dynamic_cast<TDataSource *>(Sender);

  //Get the dataset from Sender.
  if (ds) //The cast worked.
    if (ds->DataSet->State != dsBrowse)
      lblChangeNotice->Caption = tblDataOrderID->AsString;

      // If the state is Browse, the record 
      // can't be changing.
    else
      do nothing;  // The event fired because 
                   // the user was typing.

Searching a data set for a particular record

C++Builder database programmers often wonder how to locate a specific record. A database containing 15,000 records serves no purpose unless a user can go directly to the record she needs. Let's say you have a user who wants to access information about a company's office locations. The code shown in Listing A will serve that purpose.

Listing A: Locating the first record that contains a given value

TLocateOptions SearchOptions;

//Make the search case insensitive. 
SearchOptions << loCaseInsensitive;
bool locateSuccess = Query1->Locate("CustName", "A Bank",SearchOptions);

//Find 1st record where CustName equals "A Bank".

if(locateSuccess)
  ShowMessage("Located Record");
else
  ShowMessage("Did Not Locate Record.");

This code will move the data cursor to the first record containing A Bank in the CustName field. However, the poor user might then discover that A Bank has offices all over the world. Locating an office in a particular city requires a search for the contents of two or more fields. The code in Listing B illustrates a search on two fields.

Listing B: Searching a database on two fields

  #define COLUMNS_TO_MATCH 2

  //Two field names
  String columns ("CustName;CustCity");

  //Initialize string with two column names
  //separated by a semicolon.

  Variant matchText = 
    VarArrayCreate(OPENARRAY(int,(
    0,COLUMNS_TO_MATCH)),varVariant);

  //Declare a variant array to contain text to match in the search.

  //Array element zero= CustName field's match 
  matchText.PutElement("A Bank",0);

  //Array element one = CustCity field's match 
  matchText.PutElement("houston",1); 

  TLocateOptions SearchOptions;
  SearchOptions << loCaseInsensitive;

  bool locateSuccess = 
    Query1->Locate(columns,matchText,SearchOptions);

  //Locate the first record where the CustName 
  //field's text equals "A Bank" and the 
  //CustCity field's text equals "Houston".
 
  if(locateSuccess)
    ShowMessage("Located Record");
  else
    ShowMessage("Did Not Locate Record.");

The TlocateOptions variable brings powerful flexibility to the search criteria. The variable offers two options, loCaseInsensitive and loPartialKey. You can select both values using the following syntax:

SearchOptions << loCaseInsensitive << 
  loPartialKey ;

The loCaseInsensitive option locates matches without regard to case. The loPartialKey option finds the first match beginning with the target text. For example, the target text HAM would match both HAMMER and hamburger.

Formatting output from data-aware components

Formatting numeric and date output in grids and other data-aware components is one of the easiest tasks in C++Builder. Details are readily available--however, they can be hard to find. Some of the persistant data fields have a DisplayFormat property you can use, but accessing persistent fields is a little like a treasure hunt. Here are the clues. To begin, add a TTable object to a form or data module. Assign values to the DataBaseName and TableName properties as required. Now, double-click on the TTtable to open an empty field-editor window, as shown in Figure D. Right-click on the window, and a speed menu with two enabled items--Add Fields... and New Fields...--will appear.

Figure D: Double-click on the TTable component to open this empty editor window.
[ Figure D ]

Select the Add Fields... option to open an Add Fields dialog box like the one shown in Figure E, containing a list of every field in the table. (Seriously, isn't this like a treasure hunt?) Click OK to add all the fields to the TTable object as persistent fields.

Figure E: Click OK to add all these fields to your TTable object.
[ Figure E ]

I'm worn out--let's take a break. Such rote instructions are uncommon in RAD development environments. Mastering the process isn't difficult, but it isn't intuitive, either--unless you know how to do it. Okay, break's over.

At this point, every persistent field is selected in the field-editor window. Select one of the fields containing numeric or date data and press [F11]. When you do so, the properties for that persistent field will appear in the Object Inspector. One of those properties is the treasure: the long-sought DisplayFormat property.

The latest help files provide adequate information on formatting options.
Table A contains some examples for numeric fields.

Table A: Sample numeric data formats

Format Numeric 1 Numeric 2 Numeric 3 Numeric 4
Raw Data* 1276.234 9324587.235 .235 .2351
.000 1276.234 9324587.235 .235 .235
0.000 1276.234 9324587.235 0.235 0.235
0.00 1276.23 9324587.23 0.23 0.24
,.00 1,276.23 9,324,587.23 .23 .24
,0.00 1,276.23 9,324,587.23 0.23 0.24
$,0.00 $1,276.23 $9,324,587.23 $0.23 $0.24
$, 0.00 $ 1,276.23 $ 9,324,587.23 $ 0.23 $ 0.24

Format	Numeric 1	Numeric 2	Numeric 3	Numeric 4
Raw Data*	1276.234	9324587.235	.235	.2351
.000	1276.234	9324587.235	.235	.235
0.000	1276.234	9324587.235	0.235	0.235
0.00	1276.23	9324587.23	0.23	0.24
,.00	1,276.23	9,324,587.23	.23	.24
,0.00	1,276.23	9,324,587.23	0.23	0.24
$,0.00	$1,276.23	$9,324,587.23	$0.23	$0.24
$, 0.00	$ 1,276.23	$ 9,324,587.23	$ 0.23	$ 0.24

*Data as it appears without a DisplayFormat property setting

Note that C++Builder contains a rounding discrepancy. The value .235 should round to the hundredth's place as .24--but it rounds to .23 instead. Any value greater than .005 is rounded correctly in the hundredth's place. For example, the value .2351, correctly rounds to .24. This discrepancy doesn't repeat when rounding to the tenth's place--the values .450 and .451 both round to .5. It's amazing--a measly ten-thousandth of a cent could keep the bookkeeping department up all night trying to find the penny that shows up in Paradox and is lost in C++Builder (Paradox correctly rounds .2350 to .24). Just another example of life's hidden treasures. Table B illustrates several date formats, which won't provide as many thrills. Once you set a persistent field's DisplayFormat property, the formatting cascades to every data-aware component referencing the field.

Table B: Sample date formats

Format Date
Raw Date* 07/01/97
m-d-yy 7-1-97
mm/dd/yyyy 07/01/1997
yyyymmdd 19970701
mmm dd, yyyy Jul 01, 1997
mmmm d, yyyy July 1, 1997

Format	Date
Raw Date*	07/01/97
m-d-yy	7-1-97
mm/dd/yyyy	07/01/1997
yyyymmdd	19970701
mmm dd, yyyy	Jul 01, 1997
mmmm d, yyyy	July 1, 1997

*Data as it appears without a DisplayFormat property setting

Detecting selected rows in a TDBGrid control

Sometimes users need to select multiple rows in a TDBGrid and issue a command to process the selection(s). This task isn't difficult, but the documentation isn't replete with examples. OK, actually there aren't any examples--but the documentation is improving steadily. Let's look at a working example of our own. To begin, click on a grid object and open the Object Inspector by pressing [F11]. Locate the Options property and double-click on that line to expand the list of options.

Two option settings are critical for row selection: The dgRowSelect option must be True or the user can't select even one row, and dgMultiSelect must be True to allow multiple row selections. Both options are False by default. After the user selects one or more rows, run the code segment shown in Listing C to detect the selections.

Listing C: Detecting selected rows

TDataSet *ds = dgData->DataSource->DataSet;
//Get data set that feeds grid (dgData).

int selectedRows = dgData->SelectedRows->Count ;

//Determine how many rows are selected.

if(!selectedRows)
  ShowMessage("No Rows Are Selected.");
else
  for (int i = 0;i<selectedRows;i++)
  {
    ds->Bookmark = dgData->SelectedRows->Items[i];
    //Moves data cursor to selected row.

    ShowMessage("Wake up, Herman. I found another one!");
    //Perform your operations here.
  }