Teach Yourself Visual C++ 6 in 21 Days

- 14 -
Retrieving Data from an ODBC Database

Database Access and ODBC
- The Open Database Connector (ODBC) Interface
- The CRecordset Class
Creating a Database Application Using ODBC
- Preparing the Database
- Creating the Application Shell
- Designing the Main Form
- Adding New Records
- Deleting Records
Summary
Q&A
Workshop
- Quiz
- Exercise
In Review

A large number of applications use a database. Everything from a personal organizer to a large, corporate personnel system uses a database to store and maintain all the records that the applications use and manipulate. Visual C++ provides you with four different technologies for using and accessing databases in your applications, Data Access Objects (DAO), ODBC, OLE DB, and ActiveX Data Objects (ADO). Today and tomorrow, you'll learn about two of these technologies, how they differ, and how you can use them in your own applications. Today, you will learn

How the ODBC interface allows you to use a consistent way to access a database.
How Visual C++ uses the CRecordset class to provide access to an ODBC data source.
How you can create a simple database application using the Wizards in Visual C++.
How you can add and delete records from an ODBC database in Visual C++.

Database Access and ODBC

Most business applications work with data. They maintain, manipulate, and access records of data that are stored in databases. If you build business applications, odds are that you will need to be able to access a database with your applications. The question is, which database?

There are a number of databases on the market. If you need to create a single-user application that is self-contained on a single computer, you can use any one of numerous PC-based databases, such as Microsoft's Access, FoxPro, or Borland's Paradox. If you are building applications that need to access large, shared databases, you are probably using an SQL-based (Structured Query Language) database such as SQL Server or Oracle. All of these databases provide the same basic functionality, maintaining records of data. Each will allow you to retrieve several records or a single record, depending on your needs. They'll all let you add, update, or delete records as needed. Any of these data- bases will be able to serve your application's needs, so you should be able to use any database for one application and then switch to another for the next application, based on the needs of the application and which database is most suited for the specific application needs (or your employer's whim).

NOTE: To be completely honest, there are numerous differences between the various databases that are available today. Each of these databases has specific strengths and weaknesses, making one more suitable for a specific situation than another. However, a discussion of the differences between any of these databases is beyond the scope of this book. For the discussions of databases today and tomorrow, you can assume that all of these databases are functionally equal and interchangeable.

The problem that you will encounter when you switch from one database to another is that each database requires you to use a different interface for accessing the database. Therefore, you have to learn and use a whole new set of programming techniques and functions for each database that you need to work with. This is the problem that the ODBC interface was designed to correct.

The Open Database Connector (ODBC) Interface

Microsoft saw the incompatibility between database interfaces as a problem. Each database had its own application development language that was well integrated with the database but didn't work with any other database. This presented a problem to any developer who needed to use one database for an application and then a different database for the next application. The developer had to learn the specific development language for each of the databases and couldn't use any languages that she already knew. For programmers to work with any database with the programming language of the developer's choice, they needed a standardized interface that works with every database.

The Open Database Connector (ODBC) interface is implemented as a standard, SQL-based interface that is an integral part of the Windows operating system. Behind this interface are plug-ins for each database that take the ODBC function calls and convert them into calls to the specific interface for that database. The ODBC interface also uses a central set of database connection configurations, with a standardized way of specifying and maintaining them. This setup allows programmers to learn and use a single database interface for all databases. This also allowed programming language vendors to add ODBC support into their languages and development tools to make database access all but transparent.

The CRecordset Class

In the Visual C++ development environment, most of the ODBC functionality has been encapsulated into two classes, CRecordset and CDatabase. The CDatabase class contains the database connection information and can be shared across an entire application. The CRecordset class encapsulates a set of records from the database. The CRecordset class allows you to specify a SQL query to be run, and the CRecordset class will run the query and maintain the set of records that are returned by the database. You can modify and update the records in the record set, and your changes will be passed back to the database. You can add or delete records from the record set, and those same actions can be passed back to the database.

Connecting to the Database

Before the CRecordset class can perform any other functions, it has to be connected to a database. This is accomplished through the use of the CDatabase class. You don't need to create or set the CDatabase instance; the first instance of the CRecordset class does this for you. When you create an application using the AppWizard and choose to include ODBC database support, the AppWizard includes the database connection information in the first CRecordset-derived class that it creates. When this CRecordset class is created without being passed a CDatabase object, it uses the default connection information, which was added by the AppWizard, to create its own database connection.

Opening and Closing the Record Set

Once the CRecordset object is created and connected to the database, you need to open the record set to retrieve the set of records from the database. Do this by calling the Open member function of the CRecordset object. You can call this function without any arguments if you want to take the default values for everything, including the SQL statement to be executed.

The first argument to the Open function is the record set type. The default value for this, AFX_DB_USE_DEFAULT_TYPE, is to open the record set as a snapshot set of records. Table 14.1 lists the four types of record set types. Only two of these record set types are available in the AppWizard when you are specifying the data source.

TABLE 14.1. RECORD SET TYPES.

Type	Description
CRecordset::dynaset	A set of records that can be refreshed by calling the Fetch function so that changes made to the record set by other users can be seen.
CRecordset::snapshot	A set of records that cannot be refreshed without closing and then reopening the record set.
CRecordset::dynamic	Very similar to the CRecordset::dynaset type, but it is not available in many ODBC drivers.
CRecordset::forwardOnly	A read-only set of records that can only be scrolled from the first to the last record.

The second argument to the Open function is the SQL statement that is to be executed to populate the record set. If a NULL is passed for this argument, the default SQL statement that was created by the AppWizard is executed.

The third argument is a set of flags that you can use to specify how the set of records is to be retrieved into the record set. Most of these flags require an in-depth understanding of the ODBC interface so you understand how the flags can and should be used in your applications. Because of this, I'll discuss only a few of these flags in Table 14.2.

TABLE 14.2. RECORD SET OPEN FLAGS.

Flag	Description
CRecordset::none	The default value for this argument; specifies that no options affect how the record set is opened and used.
CRecordset::appendOnly	This flag prevents the user from being able to edit or delete any of the existing records in the record set. The user will only be able to add new records to the set of records. You cannot use this option with the CRecordset::readOnly flag.
CRecordset::readOnly	This flag specifies that the record set is read-only and no changes can be made by the user. You cannot use this option with the CRecordset::appendOnly flag.

Once the user finishes working with the record set, you can call the Close function to close the record set and free any resources used by the record set. The Close function doesn't take any arguments.

Navigating the Record Set

Once you have a set of records retrieved from the database, you need to be able to navigate the set of records (unless the set has only one record). The CRecordset class provides several functions for navigating the record set, allowing you to move the user to any record. Table 14.3 lists the functions that you use to navigate the record set.

TABLE 14.3. RECORD SET NAVIGATION FUNCTIONS.

Function	Description
MoveFirst	Moves to the first record in the set.
MoveLast	Moves to the last record in the set.
MoveNext	Moves to the next record in the set.
MovePrev	Moves to the previous record in the set.
Move	Can be used to move a specific number of records from the current record or from the first record in the set.
SetAbsolutePosition	Moves to the specified record in the set.
IsBOF	Returns TRUE if the current record is the first record in the set.
IsEOF	Returns TRUE if the current record is the last record in the set.
GetRecordCount	Returns the number of records in the set.

Of all of these navigation and informational functions, only two, Move and SetAbsolutePosition, take any arguments. The SetAbsolutePosition function takes a single numeric argument to specify the row number of the record toward which to navigate. If you pass 0, it navigates to the beginning-of-file (BOF) position, whereas 1 takes you to the first record in the set. You can pass negative numbers to this function to cause it to count backward from the last record in the set. (For example, -1 takes you to the last record in the set, -2 to the next-to-last record, and so on.)

The Move function takes two arguments. The first argument is the number of rows to move. This can be a positive or negative number; a negative number indicates a backward navigation through the record set. The second argument specifies how you will move through the set of rows. The possible values for the second argument are listed in Table 14.4 with descriptions of how they affect the navigation.

TABLE 14.4. MOVE NAVIGATION TYPES.

Type	Description
SQL_FETCH_RELATIVE	Moves the specified number of rows from the current row.
SQL_FETCH_NEXT	Moves to the next row, ignoring the number of rows specified. The same as calling the MoveNext function.
SQL_FETCH_PRIOR	Moves to the previous row, ignoring the number of rows specified. The same as calling the MovePrev function.
SQL_FETCH_FIRST	Moves to the first row, ignoring the number of rows specified. The same as calling the MoveFirst function.
SQL_FETCH_LAST	Moves to the last row, ignoring the number of rows specified. The same as calling the MoveLast function.
SQL_FETCH_ABSOLUTE	Moves the specified number of rows from the start of the set of rows. The same as calling the SetAbsolutePosition function.

Adding, Deleting, and Updating Records

Navigating a set of records from a database is only part of what you need to be able to do. You also need to be able to add new records to the record set, edit and update existing records, and delete records. These actions are all possible through the various functions that the CRecordset class provides. The functions that you will use to provide this functionality to the user are listed in Table 14.5.

TABLE 14.5. RECORD SET EDITING FUNCTIONS.

Function	Description
AddNew	Adds a new record to the record set.
Delete	Deletes the current record from the record set.
Edit	Allows the current record to be edited.
Update	Saves the current changes to the database.
Requery	Reruns the current SQL query to refresh the record set.

None of these functions takes any arguments. However, some of them require following a few specific steps to get them to work correctly.

To add a new record to the database, you can call the AddNew function. The next thing that you need to do is set default values in any of the fields that require values, such as the key fields. Next, you must call the Update function to add the new record to the database. If you try to navigate to another record before calling the Update function, the new record will be lost. Once you save the new record, you need to call the Requery function to refresh the record set so that you can navigate to the new record and let the user edit it. This sequence of function calls typically looks like the following:

// Add a new record to the record set m_pSet.AddNew(); // Set the key field on the new record m_pSet.m_AddressID = m_lNewID; // Save the new record to the database m_pSet.Update(); // Refresh the record set m_pSet.Requery(); // Move to the new record m_pSet.MoveLast();

When you need to delete the current record, you can simply call the Delete function. Once you delete the current record, you need to navigate to another record so the user isn't still looking at the record that was just deleted. Once you delete the current record, there is no current record until you navigate to another one. You do not need to explicitly call the Update function because the navigation functions call it for you. This allows you to write the following code to delete the current record:

// Delete the current record m_pSet.Delete(); // Move to the previous record m_pSet.MovePrev();

Finally, to allow the user to edit the current record, you need to call the Edit function. This allows you to update the fields in the record with the new values entered by the user or calculated by your application. Once all changes are made to the current record, you need to call the Update function to save the changes:

// Allow the user to edit the current record m_pSet.Edit(); // Perform all data exchange, updating the fields in the recordset . . // Save the user's changes to the current record m_pSet.Update();

You might be wondering how you get to the fields in the records to update them. When the AppWizard creates the CRecordset-derived class for your application, it adds all the fields in the records that will be in the record set as member variables in order of the record set class. As a result, you can access the member variables in order to access and manipulate the data elements in the database records that are members of the record set.

Creating a Database Application Using ODBC

For the sample application that you will build today, you'll create an SDI application with ODBC database support. The application will retrieve records from an ODBC database, allowing the user to edit and update any of the records. You'll also add function- ality to enable the user to add new records to the database and to delete records from the database.

Preparing the Database

Before you can begin building an application that uses a database, you need a database to use with your application. Almost every database that you can purchase for your applications comes with tools for creating a new database. You'll need to use these tools to create your database and then use the ODBC administrator to configure an ODBC data source for your new database.

For the sample application in this chapter, I used Access 95 to create a new database. I used the Access Database Wizard to create the database, choosing the Address Book database template as the database to be created. When the Database Wizard started, I selected the default set of fields for including in the database and selected the option to include sample data, as shown in Figure 14.1. I then accepted the rest of the default settings offered in the Database Wizard.

FIGURE 14.1. Including sample data in the database.

Once you create the database, you need to configure an ODBC data source to point to the database you just created. To do this, run the ODBC Administrator, which is in the Control Panel on your computer.

Once in the ODBC Administrator, you'll add a new data source. You can do this by clicking the Add button, as shown in Figure 14.2. This opens another dialog, which allows you to select the database driver for the new data source, as shown in Figure 14.3. For the sample application that you will build today, because the database was created using Access, select the Microsoft Access Driver and click the Finish button.

FIGURE 14.2. The ODBC Data Source Administrator.

FIGURE 14.3. The Create New Data Source dialog.

In the ODBC Microsoft Access Setup dialog, shown in Figure 14.4, you'll provide a short, simple name for the data source. Your application will use this name to specify the ODBC data source configuration to use for the database connection, so it should reflect the function that the database will be serving, or it should be similar to the name of the application that will be using this database. For the purposes of the sample application database, name your data source TYVCDB (for Teach Yourself Visual C++ Database) and enter a description for the database in the next field.

Once you enter a name and description for the data source, you need to specify where the database is. Click the Select button and then specify the Access database that you created. Once you finish configuring the ODBC data source for your database, click the OK button to add the new data source to the ODBC Administrator. You can click the OK button to finish the task and close the ODBC Administrator because you are now ready to turn your attention to building your application.

FIGURE 14.4. The ODBC Microsoft Access 97 Setup dialog.

Creating the Application Shell

For the sample application that you will build today, you'll create a standard SDI-style application with database support. First, start a new project, selecting the AppWizard, and give your application a suitable name, such as DbOdbc.

On the first AppWizard form, specify that you want to build an SDI application. On the second AppWizard form, specify that you want to include Database view with file support. Click the Data Source button to specify which data source you will use in your application. In the Database Options dialog, specify that you are using an ODBC data source, and select the ODBC configuration from the list that you configured for your Access database, as shown in Figure 14.5. You can set the record set type to either Snapshot or Dynaset.

FIGURE 14.5. The Database Options dialog.

Once you click the OK button, another dialog opens, presenting you with the available tables in the database you selected. Select the Addresses table, as shown in Figure 14.6, and click the OK button to close this dialog and return to the AppWizard.

You can continue through the rest of the AppWizard, accepting all of the default settings. When you reach the final AppWizard step, you'll notice that the AppWizard is going to create an extra class. If you select this class, you'll see that it is derived from the CRecordset class, and it is the record set class for your application. You'll also notice that the view class is derived from the CRecordView class, which is a descendent of the CFormView class, with some added support for database functionality.

FIGURE 14.6. The Select Database Tables dialog.

Designing the Main Form

Once you create the application shell, you need to design the main form that will be used for viewing and editing the database records. You can design this form using the standard controls that are part of Visual C++, without adding any special ActiveX controls. For designing the main form in your sample application, lay out the main form as shown in Figure 14.7, and configure the controls with the properties specified in Table 14.6.

TIP: If you want to save a little time when building the example, you can leave out most of the controls and database fields from the application. The key fields that you'll need to include are ID, First and Last Names, Birthdate, and Send Card. If you want to leave out the other fields from the application, that's fine.

TABLE 14.6. CONTROL PROPERTY SETTINGS.

Object	Property	Setting
Static Text	ID	IDC_STATIC
	Caption	ID:
Edit Box	ID	IDC_EID
Static Text	ID	IDC_STATIC
	Caption	First Name:
Edit Box	ID	IDC_EFNAME
Static Text	ID	IDC_STATIC
	Caption	Last Name:
Edit Box	ID	IDC_ELNAME
Static Text	ID	IDC_STATIC
	Caption	Spouse Name:
Edit Box	ID	IDC_ESNAME
Static Text	ID	IDC_STATIC
	Caption	Address:
Edit Box	ID	IDC_EADDR
	Multiline	Checked
Static Text	ID	IDC_STATIC
	Caption	City:
Edit Box	ID	IDC_ECITY
Static Text	ID	IDC_STATIC
	Caption	State:
Edit Box	ID	IDC_ESTATE
Static Text	ID	IDC_STATIC
	Caption	Zip:
Edit Box	ID	IDC_EZIP
Static Text	ID	IDC_STATIC
	Caption	Country:
Edit Box	ID	IDC_ECOUNTRY
Static Text	ID	IDC_STATIC
	Caption	E-Mail:
Edit Box	ID	IDC_EEMAIL
Static Text	ID	IDC_STATIC
	Caption	Home Phone:
Edit Box	ID	IDC_EHPHONE
Static Text	ID	IDC_STATIC
	Caption	Work Phone:
Edit Box	ID	IDC_EWPHONE
Static Text	ID	IDC_STATIC
	Caption	Extension:
Edit Box	ID	IDC_EWEXT
Static Text	ID	IDC_STATIC
	Caption	Fax:
Edit Box	ID	IDC_EFAX
Static Text	ID	IDC_STATIC
	Caption	Birthdate:
Edit Box	ID	IDC_EDOB
Check Box	ID	IDC_CBCARD
	Caption	Send Card
Static Text	ID	IDC_STATIC
	Caption	Notes:
Edit Box	ID	IDC_ENOTES
	Multiline	Checked

FIGURE 14.7. The main form design.

Once you have added and configured all the controls on the main form for your application, you're ready to begin associating the controls with database fields. When you click the Member Variables tab of the Class Wizard and select a control to add a variable for, you'll notice that the Add Member Variable dialog has a drop-down combo box where you enter the variable name. If you click the arrow to drop down the list, you'll find that it's filled with the fields in the record set, as shown in Figure 14.8. This enables you to attach the database fields directly to the controls on the form. To attach the database fields to the controls on your application form, add the variables specified in Table 14.7.

FIGURE 14.8. The Add Member Variable dialog with record set fields.

TABLE 14.7. CONTROL VARIABLES.

Object	Name
IDC_CBCARD	m_pSet->m_SendCard
IDC_EADDR	m_pSet->m_Address
IDC_ECITY	m_pSet->m_City
IDC_ECOUNTRY	m_pSet->m_Country
IDC_EEMAIL	m_pSet->m_EmailAddress
IDC_EFAX	m_pSet->m_FaxNumber
IDC_EFNAME	m_pSet->m_FirstName
IDC_EHPHONE	m_pSet->m_HomePhone
IDC_EID	m_pSet->m_AddressID
IDC_ELNAME	m_pSet->m_LastName
IDC_ENOTES	m_pSet->m_Notes
IDC_ESNAME	m_pSet->m_SpouseName
IDC_ESTATE	m_pSet->m_StateOrProvince
IDC_EWEXT	m_pSet->m_WorkExtension
IDC_EWPHONE	m_pSet->m_WorkPhone
IDC_EZIP	m_pSet->m_PostalCode

You probably noticed when it was time to attach a database field to the birthdate control that the birthday field is missing from the list of database fields. If you look at the record set class in the class view and expand its tree, you'll notice that the birthdate field is included as one of the database fields, but it's not available in the list of available columns for use with the controls. Double-click on the birthdate field in the record set class to view its definition. You'll notice that the m_Birthdate variable is declared as a CTime variable. This is the reason that it's not available in the list of database fields that can be attached to controls. There isn't a macro or function you can call for exchanging data between a control and a CTime variable. This is also a problem because the CTime variable type cannot handle dates before December 31, 1969. To use this database field, you'll need to change its definition from a CTime to a COleDateTime variable type, as in line 17 in Listing 14.1. Once you change the variable type of this database field, you will be able to attach it to the IDC_EDOB control.

LISTING 14.1. THE DATABASE FIELD VARIABLE DECLARATIONS.

1:  // Field/Param Data 2:      //{{AFX_FIELD(CTestdb5Set, CRecordset) 3:      long    m_AddressID; 4:      CString    m_FirstName; 5:      CString    m_LastName; 6:      CString    m_SpouseName; 7:      CString    m_Address; 8:      CString    m_City; 9:      CString    m_StateOrProvince; 10:     CString    m_PostalCode; 11:     CString    m_Country; 12:     CString    m_EmailAddress; 13:     CString    m_HomePhone; 14:     CString    m_WorkPhone; 15:     CString    m_WorkExtension; 16:     CString    m_FaxNumber; 17:     COleDateTime    m_Birthdate; 18:     BOOL    m_SendCard; 19:     CString    m_Notes; 20: //}}AFX_FIELD

NOTE: Normally, you do not want to edit the portions of code in your applications that are created and maintained by the various wizards. The change I outline here is one of the few exceptions to this rule. This obstacle could possibly be considered a bug in the Visual C++ AppWizard, although it's technically not a bug. You can convert the date/time database field to sev- eral variable types when creating a class variable to represent that field. CTime is one of these variable types; COleDateTime is another. Because these are both equally valid choices, and the functions that populate this variable can work with either, making this change is possible without dire consequences.

Once you make the change to the variable type for the m_Birthdate variable in the record set class (CDbOdbcSet), and attach this database field to the Birthdate control on the form, you might think that you are ready to compile and run your application. Unfortunately, your application will not compile. You'll get a compiler error stating that the DDX_FieldText cannot convert the COleDateTime variable type. What you need to do is add the code to perform this conversion yourself. Return to the Class Wizard and delete the variable that you added to the IDC_EDOB control. Add a new variable to this control. Specify that the variable is type COleDateTime, and give the variable a name such as m_oledtDOB. Pull up the DoDataExchange function in the view class, CDbOdbcView, into the editor, and add lines 4 through 6 and lines 26 through 28 to the function, as shown in Listing 14.2.

LISTING 14.2. THE CDbOdbcView DoDataExchange FUNCTION.

1:  void CDbOdbcView::DoDataExchange(CDataExchange* pDX) 2:  { 3:      CRecordView::DoDataExchange(pDX); 4:      // Copy the DOB from the record set to the view variable 5:      if (pDX->m_bSaveAndValidate == FALSE) 6:          m_oledtDOB = m_pSet->m_Birthdate; 7:      //{{AFX_DATA_MAP(CTestdb5View) 8:      DDX_FieldText(pDX, IDC_EID, m_pSet->m_AddressID, m_pSet); 9:      DDX_FieldText(pDX, IDC_EFNAME, m_pSet->m_FirstName, m_pSet); 10:     DDX_FieldText(pDX, IDC_ELNAME, m_pSet->m_LastName, m_pSet); 11:     DDX_FieldText(pDX, IDC_ESNAME, m_pSet->m_SpouseName, m_pSet); 12:     DDX_FieldText(pDX, IDC_ESTATE, m_pSet->m_StateOrProvince, m_pSet); 13:     DDX_FieldText(pDX, IDC_ECITY, m_pSet->m_City, m_pSet); 14:     DDX_FieldText(pDX, IDC_EADDR, m_pSet->m_Address, m_pSet); 15:     DDX_FieldCheck(pDX, IDC_CBCARD, m_pSet->m_SendCard, m_pSet); 16:     DDX_FieldText(pDX, IDC_ECOUNTRY, m_pSet->m_Country, m_pSet); 17:     DDX_FieldText(pDX, IDC_EEMAIL, m_pSet->m_EmailAddress, m_pSet); 18:     DDX_FieldText(pDX, IDC_EFAX, m_pSet->m_FaxNumber, m_pSet); 19:     DDX_FieldText(pDX, IDC_EHPHONE, m_pSet->m_HomePhone, m_pSet); 20:     DDX_FieldText(pDX, IDC_ENOTES, m_pSet->m_Notes, m_pSet); 21:     DDX_FieldText(pDX, IDC_EWEXT, m_pSet->m_WorkExtension, m_pSet); 22:     DDX_FieldText(pDX, IDC_EWPHONE, m_pSet->m_WorkPhone, m_pSet); 23:     DDX_FieldText(pDX, IDC_EZIP, m_pSet->m_PostalCode, m_pSet); 24:     DDX_Text(pDX, IDC_EDOB, m_oledtDOB); 25:     //}}AFX_DATA_MAP 26:     // Copy the DOB variable back from the view variable to the record         Âset 27:     if (pDX->m_bSaveAndValidate == TRUE) 28:         m_pSet->m_Birthdate = m_oledtDOB; 29: }

In addition to the above change, you have to remove the initialization of the m_Birthdate variable in the set class. This is also code that was added by the AppWizard, and once again you have to break the rules by modifying the code that you are never supposed to touch. To make this change, you can take the simple approach by commenting out the initialization of this variable in the set class constructor, in line 19 of Listing 14.3.

LISTING 14.3. THE CDbOdbcSet CONSTRUCTOR.

1: CDbOdbcSet::CDbOdbcSet(CDatabase* pdb) 2:      : CRecordset(pdb) 3:  { 4:      //{{AFX_FIELD_INIT(CTestdb5Set) 5:      m_AddressID = 0; 6:      m_FirstName = _T(""); 7:      m_LastName = _T(""); 8:      m_SpouseName = _T(""); 9:      m_Address = _T(""); 10:     m_City = _T(""); 11:     m_StateOrProvince = _T(""); 12:     m_PostalCode = _T(""); 13:     m_Country = _T(""); 14:     m_EmailAddress = _T(""); 15:     m_HomePhone = _T(""); 16:     m_WorkPhone = _T(""); 17:     m_WorkExtension = _T(""); 18:     m_FaxNumber = _T(""); 19:     //m_Birthdate = 0; 20:     m_SendCard = FALSE; 21:     m_Notes = _T(""); 22:     m_nFields = 17; 23:     //}}AFX_FIELD_INIT 24:     m_nDefaultType = dynaset; 25: }

Now compile and run your application once again. You'll find that you have a fully functioning database application that retrieves a set of records from the database and allows you to scroll through them and make changes to the data, as shown in Figure 14.9.

Adding New Records

You've already created a fully functioning database application without writing a single line of code. However, a few functions are missing. Most database applications let the user add new records to the database. To add a new record to the database, you'll want to figure out what the next ID number should be, so you'll scroll to the last record in the set to get the ID and then increment it by one. Next, you'll call the AddNew function to add a new record, set the ID field to the new ID you calculated, and then call the Update function to save the new record. Finally, you'll call the Requery function to refresh the set of records and then scroll to the last record in the set to let the user enter data into the new record.

FIGURE 14.9. The running appli- cation.

TIP: Because the ID field in the database in defined as an AutoIncrement field, you do not normally specify your own ID for the field. However, because the record set is creating a new record with the ID field, you need to assign a valid ID to the record or you won't be able to add it to the database. The method used in this application will not work with any multiuser database because each person would generate the same IDs for new records. In this situation, a centralized method for generating new IDs, such as a counter field in the database, is a better solution. The other option is to create a SQL statement to insert a new record into the database that was missing the ID field. This allows the auto-increment functionality to work correctly.

To add this functionality to your application, start by adding a function to your record set class to determine the next ID number to be used. Add a member function to the record set class, CDbOdbcSet. Specify the function type as long, the function declaration as GetMaxID, and the access as public. Edit the function, adding the code in Listing 14.4.

LISTING 14.4. THE CDbOdbcSet GetMaxID FUNCTION.

1: long CDbOdbcSet::GetMaxID() 2: { 3:     // Move to the last record 4:     MoveLast(); 5:     // return the ID of this record 6:     return m_AddressID; 7: }

Next, you'll need a menu entry that the user can select to add a new record to the database. Add a new menu entry to the Record menu. Configure the new menu entry with the properties in Table 14.8.

TABLE 14.8. MENU PROPERTY SETTINGS.

Object	Property	Setting
Menu Entry	ID	IDM_RECORD_NEW
	Caption	N&ew Record
	Prompt	Add a new record\nNew Record

Using the Class Wizard, add an event-handler function for the COMMAND event message for this menu to the view class, CDbOdbcView. Edit this function, adding the code in Listing 14.5.

LISTING 14.5. THE CDbOdbcView OnRecordNew FUNCTION.

1:  void CDbOdbcView::OnRecordNew()  2:  { 3:      // TODO: Add your command handler code here 4:      // Get a pointer to the record set 5:      CRecordset* pSet = OnGetRecordset(); 6:      // Make sure that any changes to the current record 7:      // have been saved 8:      if (pSet->CanUpdate() && !pSet->IsDeleted()) 9:      { 10:         pSet->Edit(); 11:         if (!UpdateData()) 12:             return; 13: 14:         pSet->Update(); 15:     } 16:     // Get the ID for the new record 17:     long m_lNewID = m_pSet->GetMaxID() + 1; 18:     // Add the new record 19:     m_pSet->AddNew(); 20:     // Set the ID in the new record 21:     m_pSet->m_AddressID = m_lNewID; 22:     // Save the new record 23:     m_pSet->Update(); 24:     // Refresh the record set 25:     m_pSet->Requery(); 26:     // Move to the new record 27:     m_pSet->MoveLast(); 28:     // Update the form 29:     UpdateData(FALSE); 30: }

Add a new toolbar button for the New Record menu, and then compile and run your application. You should be able to add new records to the database, entering the data you want into the records.

Deleting Records

The only functionality remaining is the ability to delete the current record from the database. You'll need to add another menu entry to trigger this action. Once the action is triggered, you'll verify that the user really does want to delete the current record and then call the Delete function to remove the record. Once the record has been deleted, you'll call the MovePrev function to navigate to the previous record in the set.

To add this functionality to your application, you'll need a menu entry that the user can select to delete the current record from the database. Add a new menu entry to the Record menu. Configure the new menu entry with the properties in Table 14.9.

TABLE 14.9. MENU PROPERTY SETTINGS.

Object	Property	Setting
Menu Entry	ID	IDM_RECORD_DELETE
	Caption	&Delete Record
	Prompt	Delete the current record\nDelete Record

Using the Class Wizard, add an event-handler function for the COMMAND event message for this menu to the view class, CDbOdbcView. Edit this function, adding the code in Listing 14.6.

LISTING 14.6. THE CDbOdbcView OnRecordDelete FUNCTION.

1:  void CTestdb5View::OnRecordDelete()  2:  { 3:      // TODO: Add your command handler code here 4:      // Make sure the user wants to delete this record 5:      if (MessageBox("Are you sure you want to delete this record?", 6:              "Delete this record?", MB_YESNO | MB_ICONQUESTION) ==                   ÂIDYES) 7:      { 8:          // Delete the record 9:          m_pSet->Delete(); 10:         // Move to the previous record 11:         m_pSet->MovePrev(); 12:         // Update the form 13:         UpdateData(FALSE); 14:     } 15: }

Add another button to the toolbar and associate it with the IDM_RECORD_DELETE menu ID so that the user can delete the current record without having to go to the menu. If you compile and run your application at this point, you'll have a full-function database application in which you can add, edit, and delete records, as shown in Figure 14.10.

FIGURE 14.10. The completed application.

Summary

Today, you learned how you can use the ODBC interface to build database applications that can be easily run against any database you might need to use. You saw how the CRecordset class provides you with a substantial amount of functionality so that you can provide database functionality in your applications. You also saw how the AppWizard provides you with a large amount of database functionality without your typing a single line of code.

Tomorrow, you will learn about Microsoft's newest database access technology, ActiveX Data Objects, and how this can be combined with the ODBC interface to make your database access even easier.

Q&A

: Q Why would I want to use the ODBC interface instead of the Data Access Objects?
: A The Data Access Objects (DAO) use the Microsoft Jet database engine to perform all of the database access. This adds at least a megabyte of overhead to your application, and if you're using a SQL-based database, the database is already doing all of the work that the Jet engine is doing for you. What's more, the Jet database engine uses the ODBC interface to access any SQL-based databases. As a result, unless you are using PC-based databases, such as Access, FoxPro, or Paradox, you get better performance from going directly to the ODBC interface yourself.
: Q How can I add different record sets in an MDI application?
: A You can add additional CRecordset-derived classes through the New Class Wizard in an MDI application project. You need to specify that the new class is an MFC class and that its base class is the CRecordset class. The New Class Wizard will have you specify the data source, just as the AppWizard had you do when creating the shell for today's application. Once you create the record set class, you can create a new view class the same way, specifying the base class as CRecordView. Once you click the OK button, the New Class Wizard asks you to specify which of the record set classes to use with the new record view class.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered and exercises to provide you with experience in using what you've learned. The answers to the quiz questions and exercises are provided in Appendix B, "Answers."

Quiz

: 1. What does ODBC stand for?
: 2. What functions can you use to navigate the record set in a CRecordset object?
: 3. What view class should you use with an ODBC application?
: 4. What sequence of functions do you need to call to add a new record to a record set?
: 5. What function do you need to call before the fields in the CRecordset object can be updated with any changes?

Exercise

Add a menu entry and dialog to let the user indicate the record number to move to, and then move to that record.

In Review

Now that you've finished the second week, you should be getting very comfortable working with Visual C++. You should be beginning to understand how you can use the MFC class hierarchy to provide a substantial amount of existing functionality in your applications. You should also be starting to understand how much supporting infrastructure your applications start with when you use the Visual C++ wizards to construct as much of your application as you can.

This is a good time to take a little break and try some of the things that you've learned on your own. Build an MDI application, using a custom document type that you've come up with yourself. See how you can save and restore the document, as well as maintain it. Practicing on your own is key to cementing your understanding of what you've learned in this book. This will help you identify any areas that you might need to go back and read again, as well as those areas where you feel comfortable enough to not review.

By this time, you should have a good understanding of the Document/View architecture and how it can be used to maintain the separation of the data from the representation of the data that is displayed for the user. You've used this model for both Single Document Interface (SDI) and Multiple Document Interface (MDI) style applications, and you've used it for reading and writing files to the disk drive. This model is one of the main building blocks of MFC applications built with Visual C++. You should know where to place any initialization information for a new set of data and where to clean up when closing a set of data.

You should also have a good understanding of how the SDI and MDI application styles are alike and how they differ from each other and from the dialog application style. You should have a good idea of when an application you are building should use one of these styles and when it should use a different style. You should be able to create your own SDI and MDI applications, as you need to, without any significant problems. If you've got any questions about either of these areas, you might want to take another look at Days 10 and 11 to review how the Document/View architecture works in both SDI and MDI style applications.

You should understand how, in SDI and MDI style applications, you can save and restore complex data structures in files on the system hard drive. You should be able to create mixed-type objects that you create and maintain in the document object in your applications, be able to use the Serialize function with the CArchive object to write the objects to a file, and then be able to restore the objects at a later time. If you are having any trouble understanding how this works or are running into any problems trying to implement this functionality in your own applications, review Day 13.

Along with reading and writing files, you also have learned how you can design and build toolbars for use in your SDI and MDI applications. At this point, you should be completely comfortable with designing and creating your own toolbars and using them in your applications. You should understand the importance of matching the toolbar button ID to the ID of the menu for which the toolbar will be used as a substitute. You should also have a basic understanding of creating and using your own customized status bar elements in SDI and MDI applications. You should understand how you can use the UPDATE_COMMAND_UI event message to evaluate and alter the status of menu, toolbar, and status bar elements, relieving you of all the work of setting each of these elements, and how to maintain their appearance and status yourself. If you aren't clear on how you can do any of these things, you might want to go back over Day 12 one more time.

You've seen how you can build a simple database application, pulling data from a database through the ODBC interface. You should have a basic understanding of how you can build database applications using this approach, how to maintain the data, how to add new records, and how to delete records. You should know how all the database interaction is directed through the record set class and how you can directly control the data through this object. If you're not sure of some of this, you might want to look back at Day 14 for a quick refresher.

You learned how easy it is to add ActiveX controls to your projects and how Visual C++ builds C++ classes around the control, enabling you to interact with the control as if it were just another C++ object. You should have a good grasp of how to add any ActiveX control (armed with the documentation for the control) to your application and interact with it in a seamless manner. You should be able to declare a variable for the control, set the control's properties, call its methods, and react to its events just as if it were a standard part of the Visual C++ development environment. If you aren't sure how you can do some of this, you might want to go back and reread Day 9.

Finally, you started this week by learning how to draw graphics on the windows of your applications. You learned how to draw lines, circles, and squares, using a variety of pens and brushes. You even learned how you can make a customized brush from a bitmap. You learned how you can load a bitmap image from a file and display it for the user to see. But most importantly, you learned about the device context and how it is used to draw all these features on the windows of your applications. You should be able to use these and other figure drawing device context methods to draw any image you might want to draw on the window for the user to see and interact with. If you are unsure about how you can do this, you probably want to look back at Day 8 once more.

By this time, you have built up quite a set of programming skills with Visual C++. You are probably ready to tackle most of the smaller programming tasks you might encounter--and maybe even a few not-so-small tasks. At this point, you are well on your way to becoming an accomplished Visual C++ programmer. That said--now is not the time to stop because there's still more to be learned. There's only one more week to go, so tallyho!

pPage