CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
[This assumes the user has installed the necessary DAO files,
included on the Visual C++ CD, and that you are using VC++
version 5.0 or later]
Overview:
Occasionally on the discussion board there are requests for
examples of how to access database information. One common method
is a dialog-based retrieval of data from a record, based on
search information provided by the user.
This is a sample program I wrote that allows you to access an
employee’s record based on their SSN. I created a small test
database (db1.mdb) using Microsoft Access 2.0 that has one table
called "Employees". Social security numbers are used as
the primary key. There are 3 records in this database:
123-45-6789 (John Doe)
987-65-4321 (Jane Smith)
456-78-9123 (Jack Jones)
Data fields include:
Name [text field]
Address [memo field]
Title [text field]
Hire Date [date/time field]
Social Security Number (SSN) [number field (double), primary
key].
The Demo Program:
The test program is called "Database". It will open as
a centered, small, single-document view, based on the CFormView
class. The user opens the desired database (in this case,
db1.mdb) using the standard file open dialog. After the database
is successfully opened, the user enters the employee SSN in the
edit box provided in the formview window (DO NOT include the
dashes). Click on the "Find Record" button and a dialog
will be displayed with the matching employee’s data. Changes can
be made to the data and will be saved to the database if the user
clicks the OK button. Clicking the Cancel button will not save
any changes to the database. See the
Update section at the bottom of this page for a list of new
features added to the original sample program.
I chose the Data Access Object (DAO) architecture to open the
database and create the recordset. I’ve found that DAO seems to
work better when interfacing exclusively with Microsoft Access
(*.mdb) databases. You should, however, be able to use almost
identical code/techniques applying the ODBC method. For example,
a recordset class using the DAO system would be based on the CDaoRecordset
class. A recordset using ODBC would be based on the CRecordset
class. When you generate a DAO class without using the
"New Project" wizard (as is demonstrated in this
tutorial), be sure to include the DAO header file
"afxdao.h" in your stdafx.h file.
Creating the Database Program:
The next several sections will take you through the method I used
to create the sample program. First, create a new workspace using
the "MFC AppWizard (exe)". I named my program
"Database". Scroll through the wizard, choose single
document interface, "none" for database support (that’s
right, you don’t need the wizard to do this!), whatever other
features you want, then at step 6, select your view class, press
the down arrow of the "Base class" combobox, and choose
"CFormView". Then click OK and let Developer Studio do
the rest. Go ahead and compile your program to make sure
everything is working (so far).
Center the Main Program Window:
In the InitInstance() function of your App class, add m_pMainWnd->CenterWindow(); just
before the calls to m_pMainWnd->ShowWindow(SW_SHOW)
and m_pMainWnd->UpdateWindow();
This will center your main window each time the
program is run. If you want the window size maximized each time
the program is run, change SW_SHOW to SW_SHOWMAXIMIZED in the
ShowWindow() function. If you want to specify exactly how large
your main program window should be, add the following code to
your program’s CMainFrame class, in the PreCreateWindow()
function before the return call:
cs.cy = 300; // Main window height
cs.cx = 300; // Main window width (Substitute
whatever values you need).
Create the Database Recordset Class:
Right click on the classes header in the Workspace window of
Developer Studio (the area where your classes are displayed in a
tree-control view). This is the top listing in bold font (in the
sample program it’s called "Database classes"). Choose
"New Class" and the new class dialog box appears. Class
type defaults to "MFC Class" which is what we want.
Under Class information, type in the name of your new class in
the "Name:" field. In the sample program I typed in: CDaoRecordsetEmployees
. Now click the down arrow of the "Base class:"
combobox, scroll down and select "CDaoRecordset". Check
your spelling, then click the OK button to create your new class.
A series of dialog boxes will follow, asking you to locate the
database. Click the " … " box to the right of
the DAO Datasource field, locate your database file (in the
sample program this is the "db1.mdb" file), click the
"Open" button, accept the defaults of
"Dynaset", "Detect dirty columns" and
"Bind all columns", and click the OK button. Choose
your database table (or tables) and click OK. Your new recordset
should now appear in the Workspace window on your program’s class
tree. Later, you will link this recordset with the dialog you
create in order to display and edit the database data.
Create the Dialog-based Display:
I won’t go into too much detail here. You need to create a new
dialog resource using the resource editor. Design this dialog box
to display the database information in whatever form you need.
The sample program is very simplistic: the data are displayed in
edit boxes with static text labels. You can certainly display the
data using other methods (perhaps fill a listbox, combobox or
tree-view).
When we created our program, we set up the base View class as
a CFormView class. This makes it easy to add dialog components to
the main program’s view window. In the sample program, I modified
the formview the same way you edit your dialog: using the
resource editor. I added an edit box for the user to type in the
employee’s SSN, and a button to perform the "Find"
function. Using Class Wizard, I associated a member variable
called m_ssn with the edit box. Finally, I created a Windows
message member function in the view class based on the "Find
Record" button, called OnButtonFind(), that will be used to
create the dialog object, link the dialog and database, and find
records that match the user supplied SSN’s (described in more
detail below). In the updated program
two more buttons were added (New Record and Delete Record) with
similar Windows messaging functions — see below.
Associating Database Data with Dialog Controls and
Variables:
Linking the database data to their respective dialog box
components can be accomplished in a couple of ways: you can read,
buffer, and write the data yourself, using SetFieldValue()
functions, or you can use the dialog data exchange mechanism
(DDX) provided by MFC. I chose the "easy" approach and
used DDX. However, you need to set up a couple of things after
creating your dialog class to link it with your database file
(just to clarify, we are "linking" our database file
and our dialog box using the DaoRecordset class that we created
earlier).
After you create your dialog box using the resource editor and
have arranged all of its controls where you want them, leave your
dialog box open in the resource editor.
Select — View | Class Wizard — from the menu bar. Class wizard
recognizes the dialog as a new potential class and asks if you
want to create a new class based on this dialog. Select OK and
you will now be in the new class window. Give your new class a
name — in the sample program I chose CDialogData . The
base class should have defaulted to CDialog (which is what we
want) and the dialog ID should have defaulted to your new
dialog’s ID (usually something like IDD_DIALOG1, if you didn’t
specify an ID name yourself in the dialog’s
"properties" box). Click OK to create your new dialog
class.
Now open the class wizard again (if it closed, select — View |
Class Wizard — again) and click on the "Class Info"
tab on the far right side. In the "Class Name" list box
at the top, scroll down and select the name of the new dialog
class you just created (again, in the sample program this was
named CDialogData). At the bottom is another listbox named
"Foreign variable". Choose this, scroll down, and
select the name of your recordset class that was created earlier
(remember, in the sample program this is called
CDaoRecordsetEmployees). Type in a variable name in the
"Foreign variable" box. I usually use the same variable
name created by the AppWizard: m_pSet . (This is actually
a pointer variable ; eventually it will point to the
address of your recordset class, after that class has been
created and initialized with data from the database file).
With Class Wizard still open, click on the "Member
Variables" tab. You will see all of the control ID’s listed
for your dialog box on the left side of the display window. Now
when you want to add member variables for these controls, you
will be able to select any of the fields available in your
recordset (i.e., in your database). These steps are necessary in
order to link your dialog’s variables with the recordset
(database) fields and use DDX to handle transferring and saving
the data from the database. When you have finished assigning your
dialog control variables, click the OK button to close the Class
Wizard and save your changes. Later, you will need to assign the
foreign variable pointer (m_pSet) to the address of your database
recordset.
Opening the Database File and Recordset:
We need to open the database file and pass its address to our
recordset class so that we can access its data using our dialog
box. This is accomplished within the OnOpenDocument() function of
the Doc class in the sample program. We will also need a pointer
to the recordset object. This is created in the Doc header file,
so don’t forget to include your recordset’s header file in the
document class’s header file. In the sample program, look in the
DatabaseDoc.h file under // Custom member functions and
variables — you’ll see a protected pointer variable CDaoRecordsetEmployees
* m_pSet , and a public member function that can be used to
retrieve it called GetRecSet() [trying to be strictly
object-oriented here]. We will use this function later in the
View class to obtain the address of the recordset and assign it
to our dialog’s foreign m_pSet variable (see the Linking the
Dialog and Database: section). Here’s the OnOpenDocument()
function:
BOOL CDatabaseDoc::OnOpenDocument(LPCTSTR lpszPathName)
{
if (!CDocument::OnOpenDocument(lpszPathName))
return FALSE;
// If user selected a file then try to open it as a database
m_pDB = new CDaoDatabase;
ASSERT(m_pDB != NULL);
try
{
m_pDB->Open(lpszPathName);
}
catch (CDaoException* e)
{
delete m_pDB;
m_pDB = NULL;
TCHAR szCause[255];
CString strFormatted = _T("The data file could not be opened because of this error: n");
e->GetErrorMessage(szCause, 255);
strFormatted += szCause;
AfxMessageBox(strFormatted, MB_OK | MB_ICONEXCLAMATION);
e->Delete();
m_bFileOpen = FALSE;
return FALSE;
}
// If database successfully opened then open EMPLOYEES recordset
m_pSet = new CDaoRecordsetEmployees(m_pDB);
ASSERT(m_pSet != NULL);
try
{
m_pSet->Open();
}
catch (CDaoException* e)
{
delete m_pSet;
m_pSet = NULL;
TCHAR szCause[255];
CString strFormatted = _T("The data file could not be opened because of this error: n");
e->GetErrorMessage(szCause, 255);
strFormatted += szCause;
AfxMessageBox(strFormatted, MB_OK | MB_ICONEXCLAMATION);
e->Delete();
m_bFileOpen = FALSE;
return FALSE;
}
// boolean variable to indicate database is open
m_bFileOpen = TRUE;
return TRUE;
}
[NOTE: the catch statements which display DAO exceptions were
modified from a sample database application included on the
Visual C++ CD]
The user first sees the common "File Open" dialog to
locate the correct database file (db1.mdb). If the user has
selected a file, we create a new CDaoDatabase object and assign
our member pointer variable to it. We then try to open this
object as a CDaoDatabase file. If this is successful, we create a
new CDaoRecordsetEmployees object and assign our member pointer
variable to it. We then try to open our recordset, passing it the
pointer to the database. If this is successful, we update the
boolean "fileopen" variable (used to keep track of the
open recordset in the View class, but could be done using the
IsOpen() function as well) and allow the OnOpenDocument()
function to return. Since we created our database and recordset
objects using the "new" function, we need to add
appropriate "delete" functions as well. Take a look
around the DatabaseDoc.cpp file and note where the
"delete" functions are used. (This is to prevent those
pesky memory leaks!)
Linking the Dialog and Database:
Look at the OnButtonFind() function in the CDatabaseView.cpp
file:
void CDatabaseView::OnButtonFind()
{
CDatabaseDoc* pDoc = GetDocument();
ASSERT_VALID(pDoc);
// record ssn user entered
UpdateData(TRUE);
// create the data dialog object
CDialogData dlg;
ASSERT(&dlg != NULL);
// set the ptr to the open database set
dlg.m_pSet = pDoc->GetRecSet();
// create search string
dlg.m_pSet->m_strFilter.Format("[SSN] = %9.0f", m_ssn);
// requery the database
dlg.m_pSet->Requery();
// allow editing of database fields
dlg.m_pSet->Edit();
// if user edited data, update the database
if (dlg.DoModal() == IDOK)
dlg.m_pSet->Update();
}
If the user has clicked the Find Record button (or chose Find
Record from the Database menu command), we save the SSN the user
entered, create the DialogData dialog box and set its m_pSet
variable equal to the recordset’s pointer using the following
statement: dlg.m_pSet =
pDoc->GetRecSet(); Since we create our dialog
object in the view class, don’t forget to include the dialog
class’s header file at the top of the view class source file
(somethingView.cpp). For the purists
out there, the Requery() and Update() commands should really be
used with try/catch
statements.
Searching the Database:
We then format our search string (a.k.a.: find the record that
has the same SSN as that entered by the user), requery the
database and display the dialog box, hopefully filled with all
the correct data. A call to the Edit() function is made so that
if the user changes the data displayed in one of the dialog
controls and clicks the OK button, the database data can be
updated (using the Update() function) with the new changes.
(You’ll get an error message if you try to use Update() without
calling Edit() or New() first). To search through the database
and find the employee record that matches the SSN entered by the
user, we use the m_strFilter function and Requery() the database
file. m_strFilter , however, is just as it appears — a
string value. Therefore, we have to convert our
"double" SSN value into a "string" SSN value
using the following function: dlg.m_pSet->m_strFilter.Format("[SSN]
= %9.0f", m_ssn); This sets the value of the
m_strFilter variable to a complete string value of "[SSN] =
123456789" when the user enters 123456789 as the employee’s
SSN.
When I created the test database (db1.mdb), I set up the SSN
field as a number field of type "double" (i.e: double
precision floating point). It could have just as easily been a
text field or long integer. I wanted a number value so that I
could designate it as the primary key (see the MS Access help if
you don’t know about primary keys). Anyway, when Visual C++
interprets the *.mdb file during creation of the DaoRecordset
class, it assigns type "double" to the SSN field. When
I created the SSN edit box in the CFormView window (the edit box
the user enters the employee’s SSN number into before pressing
the "Find Record" button), I created a member variable
(called m_ssn) using class wizard and assigned it to a type of
"double" as well.
By the way, "search" is probably the wrong term to
use here. What we are really doing is "filtering" the
recordset/database to return only those files that match the
m_strFilter variable. Since we are basing this filter on a unique
database element, the SSN, we know that the Requery() function
should only return one record. It is possible, therefore, to
create a filter that can return more than one record. You would
then need to set up your data viewing window so that it contains
some method for displaying each matching record.
Conclusion:
These are some of the major areas to address when creating your
own database application. Hopefully this has provided some basic
steps to creating at least one type of database application. I
hope you find the test program helpful as a template in designing
your own custom interface program. It has room for infinite
modifications (such as adding new records, deleting old ones [see below], other types of
searches/filters, etc.). Although I used a simple database with
only one table, multiple related tables can be used just as
easily. I recommend creating a new recordset for each table in
the database, however, then perform your searches/filters using
unique keys or on each recordset separately. Believe it or not,
most of this information can be found using the online help and
documentation (that’s how I’ve found most of it, along with trial
and error – I am NOT a professional programmer!). The problem is
finding it and, unfortunately, that’s not very straightforward.
Most of the VC++ review books seem to spend very little time on
database programming. Developer
Central has a tutorial on database programming that I found
helpful. Look at the sample database programs as well (under
Samples | MFC | Database) on the Visual C++ CD. They provide some
good code to dissect and learn from. Good luck!
UPDATE:
The following additions have been made to the original
program:
- Add new records to the database file (via a menu command,
toolbar button, or form button) - Delete records based on a given SSN (via a menu command,
toolbar button, or form button) - Enable/Disable the Find Record, New Record, and Delete
Record commands based on whether a database file is open
Add new records to the database file:
Look at the OnButtonNew() function in the
CDatabaseView.cpp file.
void CDatabaseView::OnButtonNew()
{
CDatabaseDoc* pDoc = GetDocument();
ASSERT_VALID(pDoc);
// record ssn user entered
UpdateData(TRUE);
// create the data dialog object
CDialogData dlg;
ASSERT(&dlg != NULL);
// set the ptr to the open database set
dlg.m_pSet = pDoc->GetRecSet();
// Verify adding new record and SSN
CString str;
str.Format("%.0f", m_ssn);
if ( (AfxMessageBox("Add a new record using the following SSN: " + str, MB_YESNO | MB_ICONQUESTION)) == IDYES )
{
// add a new record to the database with user-supplied SSN
dlg.m_pSet->AddNew();
dlg.m_pSet->m_SSN = m_ssn;
dlg.m_pSet->Update();
// create search string
dlg.m_pSet->m_strFilter.Format("[SSN] = %9.0f", m_ssn);
// requery the database
dlg.m_pSet->Requery();
// allow editing of database fields
dlg.m_pSet->Edit();
// if user clicks OK button, update the database
if (dlg.DoModal() == IDOK)
dlg.m_pSet->Update();
}
}
If the user has clicked the New Record button (or chose New
Record from the Database menu command), we save the SSN the user
entered, create the DialogData dialog box and set its m_pSet
variable equal to the recordset’s pointer just as was done in the
OnButtonFind() function described above. We then verify from the
user that they want to add a new record to the database using the
SSN provided in the edit box (this also helps to remind the user
that they need to provide a new SSN, since this serves as the
unique key value). If the user clicks the Yes button, a new
record is added with the user-supplied SSN. A nice feature of the
Access (*.mdb) file is that it will automatically let us know if
this particular SSN can be added to the database, since it serves
as the key and duplicates are not allowed. New data can be added
to the edit boxes and will be saved to the database if the user
clicks the OK button. Clicking the Cancel button will not save
any changes to this record, although the new record has been
created (i.e: the fields will be blank).
Delete records based on a given SSN:
Look at the OnButtonDelete() function in the
CDatabaseView.cpp file.
void CDatabaseView::OnButtonDelete()
{
CDatabaseDoc* pDoc = GetDocument();
ASSERT_VALID(pDoc);
// record ssn user entered
UpdateData(TRUE);
// create the data dialog object
CDialogData dlg;
ASSERT(&dlg != NULL);
// set the ptr to the open database set
dlg.m_pSet = pDoc->GetRecSet();
// Verify deleting record with current SSN
CString str;
str.Format("%.0f", m_ssn);
if ( (AfxMessageBox("Delete the record with the following SSN: " + str, MB_YESNO | MB_ICONQUESTION | MB_DEFBUTTON2)) == IDYES )
{
// create search string
dlg.m_pSet->m_strFilter.Format("[SSN] = %9.0f", m_ssn);
// requery the database
dlg.m_pSet->Requery();
// delete the returned record
dlg.m_pSet->Delete();
}
}
If the user has clicked the Delete Record button (or chose
Delete Record from the Database menu command), we save the SSN
the user entered, create the DialogData dialog box and set its
m_pSet variable equal to the recordset’s pointer just as
previously described. We then verify from the user that they want
to delete the record with the SSN provided in the edit box. If
the user clicks the Yes button, that record is deleted.
Enable/Disable the Find Record, New Record, and Delete
Record commands based on whether a database file is open:
The following code (in red) was added to the
CDatabaseView.h file (manually, the class wizard won’t do this
for you):
// Generated message map functions
protected:
//{{AFX_MSG(CDatabaseView)
afx_msg void OnButtonFind();
afx_msg void OnButtonDelete();
afx_msg void OnButtonNew();
afx_msg void OnUpdateButtonFind(CCmdUI* pCmdUI);
afx_msg void OnUpdateButtonNew(CCmdUI* pCmdUI);
afx_msg void OnUpdateButtonDelete(CCmdUI* pCmdUI);
//}}AFX_MSG
DECLARE_MESSAGE_MAP()
The following code (in red) was added to the CDatabaseView.cpp
file (manually, the class wizard won’t do this for you):
BEGIN_MESSAGE_MAP(CDatabaseView, CFormView)
//{{AFX_MSG_MAP(CDatabaseView)
ON_BN_CLICKED(IDC_BUTTON_FIND, OnButtonFind)
ON_BN_CLICKED(IDC_BUTTON_DELETE, OnButtonDelete)
ON_BN_CLICKED(IDC_BUTTON_NEW, OnButtonNew)
ON_UPDATE_COMMAND_UI(IDC_BUTTON_FIND, OnUpdateButtonFind)
ON_UPDATE_COMMAND_UI(IDC_BUTTON_NEW, OnUpdateButtonNew)
ON_UPDATE_COMMAND_UI(IDC_BUTTON_DELETE, OnUpdateButtonDelete)
//}}AFX_MSG_MAP
END_MESSAGE_MAP()
void CDatabaseView::OnUpdateButtonFind(CCmdUI* pCmdUI)
{
// Enable Find Record button if database file has been opened
CDatabaseDoc* pDoc = GetDocument();
ASSERT_VALID(pDoc);
// Toolbar & Menu
pCmdUI->Enable(pDoc->IsFileOpen());
// Form view
if (pDoc->IsFileOpen())
GetDlgItem(IDC_BUTTON_FIND)->ModifyStyle(WS_DISABLED,NULL,0);
else
GetDlgItem(IDC_BUTTON_FIND)->ModifyStyle(NULL,WS_DISABLED,0);
}
(repeated for the OnUpdateButtonNew() and OnUpdateButtonDelete() functions, substituting the appropriate control ID's)
The ON_UPDATE_COMMAND_UI macro allows you to easily
enable/disable the toolbar and menu commands. I used the
ModifyStyle() function to enable and disable the button controls
on the form view (there are, of course, other ways to do this).
These commands/controls are enabled when the IsFileOpen()
function returns TRUE.
Downloads
Download demo application – 163 KB
Download demo project – 40 KB
