Microsoft Corporation
This article discusses how to use the data-bound controls that are shipped with Microsoft® Visual C++® version 4.2. This information is specific to Visual C++. Additional reference information for these controls can be found in the accompanying help files shipped with these controls. See the section titled "Inserting an RDO Control into Your Application" (below) for instructions on how to access online help within Visual C++ for any of the Remote Data Object (RDO) controls.
You can also use the RemoteData control to connect to any database for which you have an ODBC driver installed on your system. However, this RemoteData type has only been designed and tested to work with Microsoft® SQL Server ODBC driver.
You can use the RemoteData control to create simple database applications without writing any code at all. You can also use it together with Visual C++® code to create robust applications that give you a high degree of programming control over the behavior of your application's data. This section will first consider the simple "no-code" approach, and then examine more complex programming examples.
The RemoteData control can perform the following tasks without the use of code:
You add the RemoteData control to your forms just as you would any other Visual C++ control. You can have as many RemoteData controls on your form as you need. As a rule, you will use one RemoteData control for each database table that you need to manipulate.
Before a RemoteData control can use a data source, you must first create an ODBC data source driver for that data source.
To use the Microsoft Foundation Class Library (MFC) database classes for targeting a Win32® platform (such as Windows NT®), you must have the 32-bit ODBC driver for your data source. Some drivers are included with Visual C++; others can be obtained from Microsoft and other vendors. For more information, see the Visual C++ online topic titled "ODBC Driver List." Following are the steps to obtain a 32-bit ODBC driver:
You can now use this data source as input to a RemoteData control in a Visual C++ application.
When you use AppWizard to create an application that uses Remote Data Objects (RDO), you must enable OLE Controls. Also in the creation process, you may want to change the base class of the View-derived class to CFormView. CFormView gives you a blank dialog box on which you can place your OLE controls.
Before you can place an RDO control on a dialog box in your application, you must first insert the control into your project's Controls toolbar, as follows:
Your control will now appear in your project's Controls toolbar.
To create a simple database application, follow these steps:
You can now double-click the control and perform design-time operations.
The following procedure gives you a brief overview of how to use the RemoteData control in an application. In order to perform this procedure, you must first have followed the instructions in the section above, "Inserting an RDO Control into Your Application."
You now have a sample program that is ready to be run or tested (CTRL-T). No coding was involved.
Bound controls are the data-aware controls through which you access information in a database. When a control is bound to the RemoteData control, Visual C++ applies field values from the current database record to that control. In turn, the control displays data to you and accepts your changes. If you change data in a bound control, those changes can be automatically written to the database as you move to another record.
Visual C++ supports several built-in controls that you can bind to the RemoteData control. For a complete discussion of bound controls, see the sections "Using DBList and DBCombo" and "Using DBGrid" (below). This section uses the Masked Edit control to illustrate the basic principles of using bound controls.
The Masked Edit control is characterized by the data-aware properties DataField and DataSource.
| Property | Description |
| DataField | Specifies the name of a field in the result set created by the RemoteData control. |
| DataSource | Specifies the name of the RemoteData control to which the control is bound. |
The steps in adding bound controls to your application are as follows:
You can have more than one bound control for a particular field, but you do not need to provide a bound control for each field in the table. Neither the RemoteData control nor the bound controls need to be made visible, so you can incorporate data access capabilities into any form you design, manipulating the data control "behind the scenes" with code.
When you run your application, the RemoteData control works together with the database to give you access to the current set of records with which you are working. Using the arrow buttons on the RemoteData control, you can move from record to record, and using the bound controls, you can view or edit the data displayed from each field. Whenever you click a button on the RemoteData control, Visual C++ automatically updates any changes you've made to the record.
Adding a New Record
There are two methods of adding a new record to the database with the RemoteData control.
Using the EOFAction Property
Warning As of Visual C++ 4.2, using this property does not work as expected.
EOFAction and BOFAction are properties that determine what happens when you move past the beginning or end of the RemoteData control's result set. The possible settings for the EOFAction property are listed in the following table.
| Value | Action |
| 0 | A MoveLast method was used, positioning the current record at the last record in the result set and effectively preventing the user from scrolling past the end of the result set. |
| 1 | Positions the current record to the invalid (EOF) record and disables the MoveNext button on the RemoteData control. |
| 2 | Validates the last record and automatically invokes the AddNew method, then positions the RemoteData control on the new record. |
With the EOFAction property set to 2, when the user moves past the last record, the RemoteData control will automatically create a new record and allow the user to enter data. If new data is entered, moving off the current (new) record will automatically trigger an update and save the new record in the database. If the user moves off the new record without adding data, the new record is discarded.
This method provides a convenient way of entering many new records consecutively. Users simply move to the end of the database and then use the MoveNext button to move one record past the end of the file. They can then begin adding new records, moving the result set forward after each record is entered. The RemoteData control automatically handles the addition and updating of the records.
Using the AddNew Method
The other method of adding a new record requires the use of code, using the AddNew method of the RemoteData control's object. The syntax is:
RDC1->GetResultset( )->AddNew( );
When the AddNew method is used, Visual C++ adds a new record to the end of the result set. At this point, all bound controls are cleared, and you can fill in the new record values. After adding a new record, you will need to save the information, either by using the Update method or by clicking one of the RemoteData control arrow buttons to move to another record. The syntax of the Update method is:
RDC1->GetResultset( )->Update( );
Using the arrow buttons on the RemoteData control or one of the Move methods to move to another record will automatically invoke the Update method and save the new record.
Updating a Record
After you edit a record, save the changed information to the database by using the Update method on the result set:
RDC1->GetResultset( )->Update( );
You can also use the Update method after using the AddNew method, instead of clicking one of the arrow buttons on the RemoteData control. Your attempt to add or change a record may fail if:
Each of these conditions will generate an exception that your application must handle. For example, an error is triggered if the length of the text you supply in your bound text control is too long, or if you define a numeric field and the number is too large. By default, the Error event displays an error message to indicate the problem.
Unless you end your application or have a pending transaction, any operation that moves the current record pointer to another record will automatically save any pending changes. Transactions are discussed in the section titled "Controlling Transactions, Validation, and Updates" (below).
If you want to cancel the AddNew method, simply perform another AddNew method and move to another record. You can also perform the RemoteData control's UpdateControls method to restore the previous values of the bound controls.
Deleting a Record
You can delete a record by using the Delete method. To do this, you first move the RemoteData control to the record you want to delete, and then call the Delete method. There is no need to use the Update method after the Delete method. The Delete method deletes the current record from the database and makes the current record invalid. At this point, you must move to another record in your result set. Any attempt to change the contents of the deleted record will result in an exception. Thus, you would implement a Delete button with code that looks like this:
RDC1->GetResultset( )->Delete( );
RDC1->GetResultset( )->MoveNext( )
The RemoteData control offers a high degree of functionality that you can use without writing any code at all—simply set and manipulate its properties and incorporate data-bound controls to provide a user interface. There will be occasions, however, when you will want to extend the functionality of the RemoteData control in code that you write yourself. Visual C++ provides this flexibility by allowing you to manipulate the RemoteData control and the CrdoResultset objects it creates.
For example, if you want to write code to move to the last record in the result set, you can treat the result set as an object, and then apply the MoveLast method:
RDC1->GetResultset( )->MoveLast( );
Or, if you want to check the value of a specific field in the current record, you could write:
COleVariant MyString;
MyString = RDC1->GetResultset( )->GetRdoColumns(
)->GetItem(COleVariant("Titles"))->GetValues( );
The syntax used to control data access objects is the same syntax used to manipulate other kinds of objects in Visual C++.
Navigating refers to moving around or changing the current record in a result set. By simply clicking on the forward or back buttons of the RemoteData control, you can traverse through the records in your result set. This section discusses the CrdoResultset methods that you can use to carry out these same actions in code.
The following table shows which CrdoResultset methods are aligned with actions on the RemoteData control.
| To move the current record to: | Use this Move method: |
| The first record in the result set | RDC1->GetResultset( )->MoveFirst( ) |
| The previous record in the result set | RDC1->GetResultset( )->MovePrevious( ) |
| The next record in the result set | RDC1->GetResultset( )->MoveNext( ) |
| The last record in the result set | RDC1->GetResultset( )->MoveLast( ); |
The Current Record
The RemoteData control uses the concept of the current record to determine which record in the result set is currently accessible. At any given time only one record is the current record, and it is this record that is displayed in any controls that are bound to the RemoteData control. As you work with result sets in code, you will need to ensure that the current record is always a valid record. It's possible, for example, to position the current record on a deleted record or to position it beyond either end of the result set, thus making it invalid.
The BOFAction and EOFAction properties give you some control over this state, but you can also write code to check the BOF and EOF properties. The state of these two properties determines the position of the current record as shown in the following table. (See "Using the EOFAction Property," above, for important information on using this property.)
| Result Set BOF/EOF Property |
Meaning |
| BOF and EOF both False | The current record pointer is valid unless you have not moved after deleting the last record in the result set. |
| BOF = True | The current record is positioned ahead of first record of data. Current record pointer is invalid. |
| EOF = True | The current record is positioned behind the last record of data. Current record pointer is invalid. |
| BOF and EOF both True | There are no rows in the result set. Current record is invalid. |
Moving to the First or Last Record
It is often necessary to jump to the first or last record in a result set. For example, you may want to do a sequential search for a record containing a particular field value, and you need to start at one end or the other of the result set.
To move to the beginning of the result set, use the MoveFirst method:
RDC1->GetResultset( )->MoveFirst( );
To move to the end of the result set, use the MoveLast method:
RDC1->GetResultset( )->MoveLast( );
If the RemoteData control is positioned at either the first or last record of the result set, any further movement toward the beginning or end will set the BOF (Beginning of File) or EOF (End of File) flags to True. If the BOFAction and EOFAction properties are set to 1, you will not have a valid current record at this point, and the bound controls will be cleared. If you use a Move method that moves beyond BOF or EOF, Visual C++ generates an exception. Because of this design, you can code the following routine that safely traverses the database from any point to the end:
Do {
RDC1->GetResultset( )->MoveNext( )
// Insert your code to work with the current record...
} While (RDC1->GetResultset( )->EOF == False); //Assuming EOFAction = 1
At the end of this loop, the current record pointer is invalid, and you must use the MoveLast method to reposition it.
Moving to the Next Record
The MoveNext method makes the next record in the result set current. Generally, MoveNext is used to step through a result set's rows to extract data on a record-by-record basis.
For information about the DBGrid, DBCombo, and DBList, see "Using DBGrid" and "Using DBList and DBCombo."
Moving to the Previous Record
The MovePrevious method makes the previous record current. This method works like the MoveNext method, except that it moves the current record pointer toward the front of the result set.
Using a Bookmark to Move to a Specific Record
Bookmarks allow you to save a current record pointer and reposition directly to a specific record. The Bookmark property contains a pointer to a record you specify. You can jump to that record by setting the Bookmark equal to the value of that pointer. This value can be saved in a Variant or string variable. The following code repositions the current record to a previously saved Bookmark:
COleVariant MyBookMark;
MyBookMark = RDC1->GetResultset( )->GetBookmark( ); ' Save current record pointer
RDC1->GetResultset( )->MoveFirst( ); ' Move off the record
RDC1->GetResultset( )->SetBookmark( MyBookmark ); ' Move back to saved
location
If you don't know the physical order number or bookmark of the record you want to access, you can search for it by starting with the first record and loop through the result set, comparing data from each record with the item you want to find.
Caution If more than one user is accessing the database, the record order can change when you use the Refresh method to rebuild the result set. If you depend on the record number (counting from the top of the result set) to locate records, you will find that this number will not consistently bring you back to the same record. Bookmarks save a pointer to the record that can be used to retrieve a specific record as long as that record remains in the result set and you do not refresh the result set. If you or some other user deletes the record, the bookmark becomes invalid, and Visual C++ generates an exception.
Other Methods of Moving Through the Result Set
In addition to the Move methods outlined above, you can use the Move method with an integer argument to move a specific number of rows forward or backward from the current record. You can also use the AbsolutePosition and PercentPosition properties of the Resultset object to move through the result set.
For information about the Move methods and the AbsolutePosition and PercentPosition properties, search Help for the appropriate method or property. See "Inserting an RDO Control into Your Application" for instructions on how to access online help within Visual C++ for any of the RDO controls.
To determine whether the data in a result set can be changed, examine the Updatable property of the result set. If this property is TRUE, the result set will accept changes. For example, to see if a selected table is updatable, you could write the following code:
If (RDC1->GetResultset( )->Updatable( ) == True) {
...
}
To change the information in a database, you must use a database that is updatable. If you're not sure whether your database can be updated, you can check the following conditions:
The following code checks to see if a database can be updated:
if (RDC1->GetReadOnly( ) == TRUE ||
RDC1->GetResultset( )->GetUpdatable( ) == FALSE ||
RDC1->GetResultset( )->Updatable( ) == FALSE)
printf("\nThis data cannot be altered");
Adding a New Record
Once you have determined that the database and result set can accept changes, you are ready to add records.
To append new records to your result set:
The following code adds a new record to the Titles table of a bibliographic database.
RDC1->SetDataSourceName("BIBLIO.MDB");
RDC1->SetSql("Select * from Titles");
RDC1->Refresh( );
RDC1->GetResultset( )->AddNew( ); ' Create a new record.
CrdoColumns columns=RDC1->GetResultset( )->GetrdoColumns( );
columns->GetItem(COleVariant("Title"))->SetValue( COleVariant("The Data
Control")); ' Set the field values.
columns->GetItem(COleVariant("Year Published"))->SetValue( COleVariant("1993"));
columns->GetItem(COleVariant("AU_ID"))->SetValue( COleVariant(37));
columns->GetItem(COleVariant("ISBN"))->SetValue( COleVariant("2344456533"));
columns->GetItem(COleVariant("PubID"))->SetValue( COleVariant(43));
RDC1->GetResultset( )->Update( ); ' Append new record.
Note that if you are using a Microsoft Access database or a native Jet database, Visual C++ will validate the accuracy of "foreign" keys according to the restrictions set by the Relation object. Otherwise, you are responsible for verifying referential integrity yourself.
For example, in the preceding code example, the PubID field refers to the PubID field in the Publishers table. Your code must verify that this is a correct value for you to maintain database referential integrity. The best way to validate fields is by using the RemoteData control's Validation event. There you can check to see if the field values about to be written to the database are correct.
After using the AddNew method, you must update the result set before using the Close method. When using the RemoteData control, this is fairly easy, because any method that changes the current record pointer, including pressing any RemoteData control arrow button, will save the new (or changed) record before moving the current record pointer. In the preceding example, the following code adds the record to the result set:
RDC1->GetResultset( )->Update( );
Editing the Current Record
To change data in your database, you must first designate the record you want to edit to be the current record. You can use any of the Move methods or RemoteData control buttons to position the record pointer to the record you want to change. Then make any necessary changes in the bound controls. Remember, the same restrictions apply here as far as updatable fields are concerned—not all fields are updatable. To save your changes, simply move the current record pointer to another record, or use the Update method:
RDC1->GetResultset( )->Update( );
To edit one or more field values of the current record:
Alternatively, you can click one of the arrow buttons on the RemoteData control to save the changes and replace the existing field values.
The following code shows how to edit the value of the PubID field in the first record. Note that you can change the data in the result set or in the bound control itself.
RDC1->SetDataSourceName("BIBLIO.MDB");
RDC1->SetSql("Select * from Titles");
RDC1->Refresh( );// Open the database.
CrdoColumns columns=RDC1->GetResultset( )->GetrdoColumns( );
columns->GetItem(COleVariant("PubID"))->SetValue( COleVariant("12345"));
// Change the field values.
Text4->SetText( "New title") // Or the bound control.
RDC1->GetResultset( )->Update( ); // Save the changes.
Deleting a Record
To delete an entire record, position the current record pointer to the record you want to remove, and use the Delete method. For example, the following code will delete every record whose "Year Published" field is less than 1889 from a Titles table (in a hypothetical database):
RDC1->SetDataSourceName("BIBLIO.MDB");
RDC1->SetSql("Select * from Titles where [Year Published] < 1889");
RDC1->Refresh( );
Do {
// RDC1->GetResultset("Title")
TRACE( "Deleting title");
RDC1->GetResultset( )->Delete( );
RDC1->Resultset( )->MoveNext( );
} while (RDC1->GetResultset( )->GetEof( ) == False);
In this example, each iteration performs the MoveNext method. You need to use MoveNext to change the current record after a deletion, because a deleted record no longer contains valid data, and an attempt to access this data will result in an error.
Note that using the Delete and MoveNext methods in a loop, as in the example above, is less efficient than using a SQL DELETE query, such as:
"DELETE from Titles where [Year Published] < #1/1/1889#"
The Close method closes the result set and frees the resources allocated to it. Attempting to perform a method or access an element of a closed result set causes an exception. For example, the following code closes a result set:
RDC1->GetResultset( )->Close( );
Databases and their respective result sets are automatically closed when:
The Validate event is triggered when either the Close method is used or the dialog is closed. Validate is not triggered when the program ends. Last-minute cleanup operations can be performed in the Validate event.
A transaction is a recoverable series of changes you make to a result set. You use transactions when you want to verify any changes you make before committing the new information to the database. For example, if you are dealing with a lengthy series of financial transactions, you might want to cancel your changes if the final totals are out of balance.
You use code to explicitly begin a transaction. While a transaction is open, any changes you make to data can be undone, or "rolled back." When you decide that your work is complete, you can save, or "commit," the changes to the database.
When you first open a database and no transactions are pending, the transaction state is auto-commit, which means that all changes made to a result set are made immediately to the underlying table and are irreversible. For situations where you do not want changes to take effect automatically, you can use transactions to control when the changes will occur.
Visual C++ has three statements that support transaction processing: BeginTrans, CommitTrans, and Rollback. However, the database itself must also support transactions or these commands will be ignored. Make sure that the Transactions property of the database is set to True before using these statements.
Transactions span databases. That is, when you use one of the transaction statements, it applies to all databases in a workspace—even databases opened after the transaction was begun. When you use CommitTrans or Rollback, all pending transactions, regardless of the database, are either committed or rolled back.
The BeginTrans statement marks the beginning of a transaction and takes the next sequence of operations out of auto-commit mode. Once you begin a transaction, you must use CommitTrans or Rollback before you close the database or end the program. Uncommitted transactions are automatically rolled back when your program ends. If you attempt to close a database while a transaction is in progress, an error will occur.
CommitTrans saves all result set changes made since the transaction was opened with the BeginTrans command. When you execute CommitTrans, all changes are made permanent, the current transaction is ended, and the transaction state returns to auto-commit.
Rollback reverses, or undoes, all data changed in the current transaction. It also ends the transaction and returns the transaction state to auto-commit.
Transaction commands always apply to all result sets opened in the same workspace. As the preceding code example shows, this allows you to simply state the transaction command you want (such as BeginTrans) without reference to the RemoteData control. If you're working with multiple transactions, however, you'll need to pay attention to the sequence of the transaction commands you issue.
Once you issue a BeginTrans command, you have a transaction pending, and all further transaction commands apply to that pending transaction. If you then begin a second transaction, without concluding the first, you start to build a series of nested transactions. The model here is much like that of nested control structures, where you need to close the innermost statements (or transactions) before proceeding to the outermost.
The RemoteData control's Validate event allows you to check any changes made to the result set before new information is written to the database. It also allows you to specify which record will become current after the Validate event is concluded. Validate is triggered when the current row is changed. This means that Validate can be triggered regardless of whether you have changed data in the bound controls.
During a Validate event, you will not be able to invoke any method that triggers another Validate event. For example, you will not be able to call AddNew or Move methods.
The Validate event is invoked just before Visual C++ writes changes from the bound controls to the database and repositions the current record pointer to another row in the database. The following table summarizes the arguments you can use with the Validate event.
| Argument | Determines |
| Save | Whether or not the Update method will be performed to save the current changes. |
| Action | What action triggered the event; also lets you specify what operation takes place after the Validate event. |
The Save Argument
In the Validate event, you can determine if any of the bound controls have changed by examining the Save argument. Visual C++ automatically checks the Changed property of each bound control to see if its value has changed since it was set by the last database action. If any values have changed, Visual C++ sets the Save argument to True.
If the Save argument is True, Visual C++ will save any bound control changes to the database. If you do not want to save the changes, you can set the Save argument to False.
The Action Argument
The Action argument tells you what caused the Validate event to be triggered and allows you to reposition the current record pointer after the Validate event is completed. In the Validate event, Visual C++ sets the Action argument with a value that indicates which action initially triggered the event. The following table summarizes the Action argument values and the actions caused by the Validate event.
| Value | Description |
| 0 | Cancels the action that caused the event. |
| 1 | MoveFirst method. |
| 2 | MovePrevious method. |
| 3 | MoveNext method. |
| 4 | MoveLast method. |
| 5 | AddNew method. |
| 6 | Update method. |
| 7 | Delete method. |
| 8 | Find method. |
| 9 | The Bookmark property has been set. |
| 10 | Close method. |
| 11 | The form is unloaded. |
In some cases, you can set the Action argument to specify how Visual C++ will reposition the current row pointer after the event is completed. This is possible if the Validate event has been triggered by AddNew or one of the Move methods.
For example, suppose the Validate event was triggered because you clicked the MoveNext button on the RemoteData control. As Visual C++ then enters the Validate event, it sets the Action argument to 3, indicating MoveNext. After the validation is completed, reposition the current record pointer to the previous record instead of to the next record. To do this, set the Action argument to 2, indicating MovePrevious. The repositioning routine will then use the Action argument you specify to indicate which row to set as the current row after the transaction.
You can specify any one of the Move or AddNew methods to be performed in place of any other set of Move or AddNew methods. If you attempt to change any action except one of the Move methods or AddNew, Visual C++ ignores your attempt and proceeds with the originally intended operation.
Canceling the Action
If you do not want the Validate event to reposition the current record pointer to another record, you can set the Action argument to 0. Setting the Action argument to 0 has no impact on whether or not data is saved to the database—it merely cancels the repositioning operation and leaves the current record active. If no repositioning takes place, the values displayed in the bound controls and the current record pointer remain unchanged.
You have seen the Update method used to update the information in the result set, based on changes made through bound controls or through code. In this section, you'll learn about two additional methods that you can use to update information: UpdateControls and Refresh. The following table summarizes the actions of these methods.
| Method | Description |
| UpdateControls | Updates database (result set) changes to bound controls. |
| Refresh | Creates a new result set based on data control properties. |
The RemoteData control's UpdateControls method updates the bound controls using the values in the result set's current row. You use this method when the current row has changed, but the bound controls have not been automatically updated with data from the current row.
The RemoteData control provides a relational interface to database files. Basically, a relational database is one that stores data in tables made up of columns and rows of data. In Visual C++, columns are referred to as fields, and rows are referred to as records.
Tables
A table is a logical grouping of related information arranged in rows and columns, similar to a spreadsheet table. For example, a table might contain a list of information about authors, such as their names, dates of birth, addresses, and pictures.
Fields
Each column in a database table is called a field. Tables are defined by the fields they contain, with each field describing the data it is to hold. When creating a database, you assign a data type, maximum length, and other attributes to each field. Fields can contain characters, numbers, or even graphics. For example, the Authors table might have fields with the name and address as data type "character," the date of birth as data type "date," and the author's photograph as data type "graphic."
Records
Information about individual authors is kept in the rows of the table, called records. Generally, database table records are created such that no two rows are the same. That is, you would not have two authors by the same name at the same address and with the same birthday.
Indexes
To make access to the data faster, most databases use indexes. Database table indexes are sorted lists that are faster to search than the tables. Each index entry points back to the database row it references. If the database (which does all of the searching) can look through an index first when looking for records (performing a query), its job is made easier and your data is returned faster. When you use table-type result sets with the RemoteData control, the table's primary index is used to speed retrieval.
Structured Query Language (SQL)
Once the data is stored in the database, retrieving it is made easier by using an English-like language called Structured Query Language, or SQL. SQL has evolved into the most widely accepted means to "converse" with a database. Basically, the user asks questions in the SQL language; this is called a "query." The database engine "answers" by returning any database rows that meet the requirements of the query. The query usually contains the names of the tables to search, the names of the columns to return, and other information that sets the scope of the search. For example, an SQL query on our Authors table might look like this:
"Select Name, Picture from Authors where Date_of_Birth = #2/7/1947# "
This SQL query would return the name and picture of all authors whose birthday is February 7, 1947. If any rows are returned, you could use bound controls to display the values.
Accessing databases with the Data control shows how the RemoteData control lets you build database applications with little or no code by working with special data-aware controls called bound controls. This section provides an overview of how you can use bound controls to display, edit, and update records in the RemoteData control's database.
When a control is bound to the RemoteData control, Visual C++ displays field values from the current database record in that control. If you change data in a bound control, those changes can be automatically written to the database as you move to another record.
DBCombo and DBList controls can be automatically filled from a database field via the RemoteData control to which they are bound. In addition, they can optionally pass a selected field to a second RemoteData control, making it ideal for "lookup table" applications.
DBList and DBCombo boxes have five special properties that determine their data binding. These are listed in the following table.
| Property | Description |
| DataSource | The name of the RemoteData control to which the data-bound list box or data-bound list box control is bound. |
| DataField | The name of a field in the result set specified by the DataSource property. This field will be used to determine which element in the list will be highlighted. If a new selection is made, this field will be updated when you move to a new record. |
| RowSource | The name of the RemoteData control that will be used to fill the list. |
| BoundColumn | The name of a field in the result set specified by the RowSource property. This list must be of the same type as the DataField that will be used to update the DataField. |
| ListField | The name of a field in the result set specified by RowSource that will be used to fill the list. |
Note You can also use the data-bound list box and data-bound combo box controls with a single RemoteData control. To do this, set both the DataSource and RowSource properties to the same RemoteData control, and set the DataField and BoundColumn properties to the same field in the RemoteData control's result set. In this case, the list will be filled with ListField values from the same result set that is updated. If a ListField property is specified, but no BoundColumn property is set, BoundColumn will automatically be set to the ListField field.
A common use of these controls is to build a list of items based on a database query from which a user can select or enter a value or, in the case of data-bound combo box, to edit the selected field. When an item in the list is selected, its associated BoundColumn value is made available to the RemoteData control specified by the DataSource property. Once the RemoteData control moves to another record or is otherwise updated, the result set is updated with the selected or edited text.
The following example uses the DBCombo box control to create a data-entry screen for the Titles table of a sample database. It lets the user enter new titles and assign them to existing publishers by providing a lookup table of all publishers' names. When users get to the Publisher field in the entry form, they can choose a publisher from a list box. When they select a publisher, that publisher's PubID field is copied into the PubID field of the Titles table.
To create a lookup table with the data-bound DBCombo box control:
The DataField and the BoundColumn fields should be identical, but in different tables. That is, the BoundColumn field should be a foreign key into the DataField.
When you run this program, the DBCombo box control fills with a list of publishers' names. When you select one of the records from the Titles table using the first RemoteData control, you will notice that the name of that title's publisher is displayed in the text box portion of the DBCombo box. Next, change the name by making a new selection from the names in the DBCombo box control. Reposition the first RemoteData control to a different record, and then move back to the original record. Notice that the name displayed has been updated, indicating that the PubID field in the Titles table now contains the PubID field of the publisher selected from the DBCombo box control.
If you enter a name in the text box that is not in the list, the RemoteData control has no way of knowing which PubID value corresponds to the new name, so the PubID field will be saved as Null.
Some important additional properties of the DBList box and DBCombo box include:
For a complete list of properties and methods of these controls, search online Help for "DBList" and "DBCombo." See the section titled "Inserting an RDO Control into an Application" (above) for instructions on accessing online help.
If MatchEntry is set to False, the search argument is limited to one character.
The data-bound grid is a new spreadsheet-like bound control that displays a series of rows and columns representing records and fields from a CrdoResultset object. The intersection of a row and column is a cell.
When you set the DBGrid control's DataSource property to a RemoteData control, the control is automatically filled and its column headers are automatically set from the RemoteData control's result set. This gives you a great deal of "ready-made" functionality that allows your users to browse and edit complete database tables or query results.
The Row and Col properties specify the current cell in a grid. Each cell of a data-bound grid control can hold either text or picture values. You can specify the current cell in code, or the user can change it at run time using a mouse or the arrow keys. You can read and set the contents of each cell interactively or programmatically. A DBGrid control can have any number of rows, and as many columns as system resources can support.
Users can set the focus to individual cells and edit data directly in the cell. Users can also select entire rows, or cells on a per-column basis. Each column has its own font, border, word-wrap, color, and other attributes that can be set without regard to other columns. At design time, the column width and row height can be set and you can establish "hidden" columns. Users can be prevented from changing the formatting at run time.
Note You can search online Help for "DBGrid" to get a full reference listing of the DBGrid control's properties and methods, along with brief examples of usage. See "Inserting an RDO Control into an Application" (above) for instructions on accessing online help.
The DBGrid control can be highly customized and directly manipulated in code to give you great flexibility in creating custom database applications.
In many cases, however, you will want to use DBGrid to quickly and easily display and browse through all the records in a database table. This can be accomplished in minutes without writing code.
Before using the Apex Data Grid, you must add the control to the project. (See "Inserting an RDO Control into an Application.")
To use DBGrid to display and browse through an existing database table, follow these steps:
When your program runs, the database table you specified is displayed in the DBGrid, complete with column headers (from the RemoteData control's result set) and scroll bars. You can immediately scroll through all the records and fields of the table. In addition, you can edit any cell in the table, and it will automatically be updated when you move to a new row (assuming that the underlying result set is updatable and the DBGrid's AllowUpdate property is set to True). With the AllowAddNew and AllowDelete properties set to True, you can delete records by selecting and deleting the entire row.
When first created, the DBGrid control has a single column and a single row. It also contains an empty row—designated by the asterisk (*)—at run time, which is used to add new records. You can change any of the DBGrid's visible attributes through settings in the Properties window.
When you set the DataSource property, the DBGrid control binds itself to the result set created by the RemoteData control. It then sets the number of columns and other column properties from the corresponding properties of the RemoteData control's result set.
The Properties window displays the properties for the entire DBGrid control; the control can be moved and sized. To set properties for the individual Column objects, you must make the DBGrid control UI-active: Select the right mouse button, and click Edit on the control's pop-up menu. Use the pop-up menu to insert or delete columns, or to cut and paste columns to and from the Clipboard.
Each DBGrid control has a single property page, however, each Column object also has its own page. You can use these property pages to interactively set properties for the DBGrid control as a whole, or for individually selected columns.
The General property page shows properties that apply to the entire DBGrid control and can be assessed at design time.
The Columns property page shows properties for an individual column. If multiple columns are selected, the leftmost column in the columns property page is selected. Setting Column object properties doesn't affect overall DBGrid control properties.
The Colors property page allows selection of overall DBGrid control colors. Column object colors aren't affected by these settings.
The Fonts property page allows selection of overall DBGrid control and heading fonts. Column object fonts aren't affected by these settings.
The DBGrid control displays records from the underlying CrdoResultset object at run time. There is always a single current record in the DBGrid control, even if multiple rows in the control are selected. The current record corresponds to the RemoteData control's current record, and can be set or retrieved with the DBGrid control's Bookmark property.
As with other data-bound controls, when you change a record (or any field in a record) in the DBGrid, the underlying result set is updated when you move to a new record (row), assuming that it is updatable and the DBGrid's AllowUpdate property is set to True. Otherwise, an exception is raised.
If the RecordSelectors property is True, the user can select an entire row by clicking the record selector icon for that row.
The DBGrid control allows you to select ranges of cells, as in a spreadsheet. However, it does not provide any automatic functionality for working with ranges (such as copying, deleting, or moving entire "blocks" of fields or records). If you wish to implement such capability, you will have to do so in code.
Information about selected ranges is returned in two ways:
For example, to delete a selected range of records:
These steps are illustrated in the following code example:
void DeleteRows_Click( ) my attempt
{
while ( DBGrid1->GetSelBookmarks( )-> GetCount( ) != 0 )
{
RDC1->GetResultset( )->GetBookmark( ) = DBGrid1->SetBookmarks(COleVariant(0));
RDC1->GetResultset->Delete( );
}
}
It is possible to use DBGrid to display calculated fields; calculated fields do not actually appear in the database, but are calculated from the data in stored fields. An example of a calculated field would be displaying a "sales tax" column when the database contains only a "price" field. If the sales tax in your locality were 7 percent, the calculation for sales tax would be "price x .07."
To display a calculated field:
For more details on using the Apex Data Grid control, search online Help for "DBGrid".