Although creating a simple ODBC database program is easy with Visual C++, there are a number of steps you must complete:

In the following sections, you will see how to perform these steps as you create the Employee application, which enables you to add, delete, update, sort, and view records in the Employees table of the sample Department Store database.

Before you can create a database application, you must register the database that you want to access as a data source that you can access through the ODBC driver. Follow these steps to accomplish this important task:

FIG. 12.4 Connecting a data source to your application starts with the ODBC Data Source Administrator.

FIG. 12.5 Creating a new data source is as simple as choosing Access from a list of drivers.

FIG. 12.6 Name your data source whatever you like.

FIG. 12.7 Browse your way to the .mdb file that holds your data.

Your system is now set up to access the DeptStore.mdb database file with the Microsoft Access ODBC driver.

Now that you have created and registered your data source, it’s time to create the basic Employee application. The steps that follow lead you through this process. After you complete these steps, you will have an application that can access and view the Employees table of the Department Store database:

FIG. 12.8 Arrange for a database view but no other file support.

FIG. 12.9 Choose the Department Store data source.

FIG. 12.11 After selecting the data source, the Step 2 dialog box looks like this.

FIG. 12.12 Turn off print support.

At this point, you can compile the application by clicking the Build button on Developer Studio’s toolbar, by selecting the Build, Build command from the menu bar, or by pressing F7 on your keyboard. After the program has compiled, select the Build, Execute command from the menu bar or press Ctrl+F5 to run the program. When you do, you see the window shown in Figure 12.14. You can use the database controls in the application’s toolbar to navigate from one record in the Employee table to another. However, nothing appears in the window because you’ve yet to associate controls with the fields in the table that you want to view. You will do that in the following section.

FIG. 12.13 The application summary mentions the data source as well as the usual information.

FIG. 12.14 The basic Employee application looks nice but doesn’t do much.

The next step in creating the Employee database application is to modify the form that displays data in the application’s window. Because this form is just a special type of dialog box, it’s easy to modify with Developer Studio’s resource editor, as you will discover while completing the following steps:

FIG. 12.15 Connect the IDC_EMPLOYEE_DEPT control with the m_DepartmentID member variable of the recordset.

You’ve now created a data display form for the Employee application. Build and execute the program again, and you will see the window shown in Figure 12.16. Now the application displays the contents of records in the Employee database table. Use the database controls in the application’s toolbar to navigate from one record in the Employee table to another.

FIG. 12.16 The Employee application now displays data in its window.

After you’ve examined the database, try updating a record. To do this, simply change one of the record’s fields (except the employee ID, which is the table’s primary key and can’t be edited). When you move to another record, the application automatically updates the modified record. The commands in the application’s Record menu also enable you to navigate through the records in the same manner as the toolbar buttons.

Notice that you’ve created a sophisticated database-access program without writing a single line of C++ code—an amazing feat. Still, the Employee application is limited. For example, it can’t add or delete records. As you may have guessed, that’s the next piece of the database puzzle, which you will add.

When you can add and delete records from a database table, you will have a full-featured program for manipulating a flat (that is, not a relational) database. In this case, the flat database is the Employees table of the Department Store relational database. Adding and deleting records in a database table is an easier process than you might believe, thanks to Visual C++’s CRecordView and CRecordSet classes, which provide all the member functions you need in order to accomplish these common database tasks. You will need to add some menu items to the application. Follow these steps to include add and delete commands in the Employee application:

Next, you will connect these commands to toolbar buttons, Follow these steps:

Now that you have added the menu items and the toolbar buttons, you need to arrange for code to catch the command message sent when the user clicks the button or chooses the menu item. Because it is the view that is connected to the database, the view will catch these messages. Follow these steps:

FIG. 12.17 Add a button and connect it to the menu item.

FIG. 12.18 Add a function to catch the message.

FIG. 12.19 Override the OnMove() function.

You’ve now modified the Employee application so that it can add and delete, as well as update, records. After compiling the application, run it by selecting the Build, Execute command from Developer Studio’s menu bar or by pressing Ctrl+F5. When you do, you see the Employee application’s main window, which doesn’t look any different than it did in the preceding section. Now, however, you can add new records by clicking the Add button on the toolbar (or by selecting the Record, Add Record command on the menu bar) and delete records by clicking the Delete button (or by clicking the Record, Delete Record command).

When you click the Add button, the application displays a blank record. Fill in the fields for the record; then when you move to another record, the application automatically updates the database with the new record. To delete a record, just click the Delete button. The current record (the one on the screen) vanishes and is replaced by the next record in the database.

You might be wondering how the C++ code you added to the application works. OnRecordAdd() starts with a call to the AddNew() member function of CEmployeeSet, the class derived from CRecordSet. This sets up a blank record for the user to fill in, but the new blank record doesn’t appear on the screen until the view window’s UpdateData() function is called. Before that happens, you have a few other things to tackle.

After the user has created a new record, the database will need to be updated. By setting a flag in this routine, the move routine will be able to determine whether the user is moving away from an ordinary database record or a newly added one. That’s why m_bAdding is set to TRUE here.

Now, because the user is entering a new record, it should be possible to change the contents of the Employee ID field, which is currently set to read-only. To change the read-only status of the control, the program first obtains a pointer to the control with GetDlgItem() and then calls the control’s SetReadOnly() member function to set the read-only attribute to FALSE.

Finally, the call to UpdateData() will display the new blank record.

Now that the user has a blank record on the screen, it’s a simple matter to fill in the edit controls with the necessary data. To add the new record to the database, the user must move to a new record, an action that forces a call to the view window’s OnMove() member function. Normally, OnMove() does nothing more than display the next record. Your override will save new records as well.

When OnMove() is called, the first thing the program does is check the Boolean variable m_bAdding to see whether the user is in the process of adding a new record. If m_bAdding is FALSE, the body of the if statement is skipped and the else clause is executed. In the else clause, the program calls the base class (CRecordView) version of OnMove(), which simply moves to the next record.

If m_bAdding is TRUE, the body of the if statement is executed. There, the program first resets the m_bAdding flag and then calls UpdateData() to transfer data out of the view window’s controls and into the recordset class. A call to the recordset’s CanUpdate() method determines whether it’s okay to update the data source, after which a call to the recordset’s Update() member function adds the new record to the data source.

To rebuild the recordset, the program must call the recordset’s Requery() member function, and then a call to the view window’s UpdateData() member function transfers new data to the window’s controls. Finally, the program sets the Employee ID field back to read-only, with another call to GetDlgItem() and SetReadOnly().

Deleting a record is simple. OnRecordDelete() just calls the recordset’s Delete() function. When the record is deleted, a call to the recordset’s MoveNext() arranges for the record that follows to be displayed.

A problem might arise, though, when the deleted record was in the last position or when the deleted record was the only record in the recordset. A call to the recordset’s IsEOF() function will determine whether the recordset was at the end. If the call to IsEOF() returns TRUE, the recordset needs to be repositioned on the last record. The recordset’s MoveLast() function takes care of this task.

When all records have been deleted from the recordset, the record pointer will be at the beginning of the set. The program can test for this situation by calling the recordset’s IsBOF() function. If this function returns TRUE, the program sets the current record’s fields to NULL.

Finally, the last task is to update the view window’s display with another call to UpdateData().

In many cases when you’re accessing a database, you want to change the order in which the records are presented, or you may even want to search for records that fit certain criteria. MFC’s ODBC database classes feature member functions that enable you to sort a set of records on any field. You can also call member functions to limit the records displayed to those whose fields contain given information, such as a specific name or ID. This latter operation is called filtering. In this section, you will add sorting and filtering to the Employee application. Just follow these steps:

FIG. 12.20 The Sort menu has four commands for sorting the database.

FIG. 12.21 After you add the four new functions, ClassWizard looks like this.

FIG. 12.22 The Filter menu has four commands.

FIG. 12.23 Create a filter dialog box.

Now that the menus and dialogs have been created and connected to skeleton functions, it’s time to add some code to those functions. Double-click OnSortDepartment() in ClassView and edit it to look like Listing 12.4.

Double-click OnSortID() in ClassView and edit it to look like Listing 12.5. Double-click OnSortName() in ClassView and edit it to look like Listing 12.6. Double-click OnSortRate() in ClassView and edit it to look like Listing 12.7.

At the top of EmployeeView.cpp, add the following line after the other #include directives:

Edit OnFilterDepartment(), OnFilterID(), OnFilterName(), and OnFilterRate(), using Listing 12.8.

All four functions call DoFilter(). You will write this function to filter the database records represented by the recordset class. Right-click CEmployeeView in ClassView and choose Add Member Function. The Function Type is void, and the declaration is DoFilter(CString col). It’s a protected member function because it’s called only from other member functions of CEmployeeView. Click OK to close the Add Member Function dialog box. Add the code from Listing 12.9.

You’ve now added the capability to sort and filter records in the employee database. Build the application and run it. When you do, the application’s main window appears, looking the same as before. Now, however, you can sort the records on any field, by selecting a field from the Sort menu. You can also filter the records by selecting a field from the Filter menu and then typing the filter string into the Filter dialog box that appears. You can tell how the records are sorted or filtered by moving through them one at a time. Try sorting by department or rate, for example. Then try filtering on one of the departments you saw scroll by.

All the sorting functions have the same structure. They close the recordset, set its m_strSort member variable, open it again, and then call UpdateData() to refresh the view with the values from the newly sorted recordset. You don’t see any calls to a member function with Sort in its name. Then when does the sort happen? When the recordset is reopened.

A CRecordset object (or any object of a class derived from CRecordset, such as this program’s CEmployeeSet object) uses a special string, called m_strSort, to determine how the records should be sorted. When the recordset is being created, the object checks this string and sorts the records accordingly.

Whenever the user selects a command from the Filter menu, the framework calls the appropriate member function, either OnFilterDept(), OnFilterID(), OnFilterName(), or OnFilterRate(). Each of these functions does nothing more than call the local member function DoFilter() with a string representing the field on which to filter.

DoFilter() displays the same dialog box, no matter which filter menu item was chosen, by creating an instance of the dialog box class and calling its DoModal() function.

If result doesn’t equal IDOK, the user must have clicked Cancel: The entire if statement is skipped, and the DoFilter() function does nothing but return.

Inside the if statement, the function first creates the string that will be used to filter the database. Just as you set a string to sort the database, so, too, do you set a string to filter the database. In this case, the string is called m_strFilter. The string you use to filter the database must be in a form like this:

The column ID was provided to DoFilter() as a CString parameter, and the value was provided by the user. If, for example, the user chooses to filter by department and types hardware in the filter value box, DoFilter() would set str to DeptID = ‘hardware’.

With the string constructed, the program is ready to filter the database. As with sorting, the recordset must first be closed; then DoFilter() sets the recordset’s filter string and reopens the recordset.

What happens when the given filter results in no records being selected? Good question. The DoFilter() function handles this by obtaining the number of records in the new recordset and comparing them to zero. If the recordset is empty, the program displays a message box telling the user of the problem. Then the program closes the recordset, resets the filter string to an empty string, and reopens the recordset. This restores the recordset to include all the records in the Employees table.

Finally, whether the filter resulted in a subset of records or the recordset had to be restored, the program must redisplay the data—by calling UpdateData(), as always.