A simple database application

It's very easy to connect to a database and get, edit, add and delete records in a table of the database using MFC classes, CDatabase and CRecordset. In this article, you will learn how to use the Visual Studio application wizard to create a simple database application which connect to a Access *.mdb database.

Because CDatabase and CRecordset uses ODBC interface to connect to work with a database, we first have to create an ODBC data source. Here are the steps for Windows XP.

1. Go to Control Pannel and open Administrative Tools->Data Sources (ODBC). You will see a window which lists available ODBC data sources in your computer.
2. Click on "Add.." button to add a new data source. You will get a window where you have to select the database you are going to use.
3. Select "Driver to Microsoft Access (*.mdb)" because we are going to use an Access database in this simple application. Click "Finish".
4. In the next window you are getting, type testDB in Data Source Name field. Then, in Database section, click "Select..".
5. You will get a window where you need to select the database file from the disk. Select the Access database file and click ok You can download the database file used in example application here. Click ok on other open windows also.

Now you have created the ODBC data source in your computer.

Lets now create our simple application which connects to our newly created data source.

1. Open Visual Studio and select File->New->Project.
2. Select MFC application as the application type. Enter a suitable name for the project. I have used "db access"
3. In the application wizard, select "Single Document" and "Use MFC in a Static Library" options.
4. In the "database support" window, select "Database view with file support" and ODBC as "client type". Click on "Data Source button.
5. You will get a window to select the data source. In "Machine Data Source" tab, you will see the data source you just created, "testDB". Select it and click OK.
6. You will get a dialog asking the username and password to connect to the database. Because our database do not have a password set, leave these fields blank and click ok.
7. Then you will get a dialog showing the available tables in the database. In this example project, I have used a database which contains just one table. Select the table "users" and click OK.
8. Leave the other settings of the Application wizard on default values.

The application you get will have a dialog form created for you. You can locate it from the resource view. Add controls to this form and make it similar to this one.

It is better if you use some meaningful names to the control ids of the edit boxes. I have used IDC_USERNAME, IDC_FIRSTNAME, IDC_LASTNAME, IDC_EMAIL, IDC_COUNTRY. You can change the control id of edit box from the properties of the edit box.

Now, go to view class of your application and locate DoDataExchange() function. In this sample application, it is located in the file "db accessView.cpp".

In this function, you can "bind" the fields of our form to the fields in the table of our database. A CRecordset variable (m_pSet) is created for us by the application wizard. This variable corresponds to the table in our database and it has a mamber variable for each field in the table. Here is the DoDataExchange function.

void CdbaccessView::DoDataExchange(CDataExchange* pDX)
{
CRecordView::DoDataExchange(pDX);
DDX_FieldText(pDX, IDC_USERNAME, m_pSet->m_username, m_pSet);
DDX_FieldText(pDX, IDC_FIRSTNAME, m_pSet->m_firstname, m_pSet);
DDX_FieldText(pDX, IDC_LASTNAME, m_pSet->m_lastname, m_pSet);
DDX_FieldText(pDX, IDC_EMAIL, m_pSet->m_email, m_pSet);
DDX_FieldText(pDX, IDC_COUNTRY, m_pSet->m_country, m_pSet);}

All the fields in our databse table are strings. So we have use DDX_FieldText function for bind the variable to the text boxes in our form. There are other functions such as DDX_FieldCBIndex to synchronize with other data types.

You will have to include "resource.h" header to the cpp file with above code to compile the project. Now, if you compile the application, you will get an error. If you go to the location of error, you will see #error preprocessor directive telling that the passwords may be in the connection string . just comment that line. Now you should be able to compile the project.

Now, we have created a simple application so easily which can read and edit the data in a database table. You will get a toolbar to go through the records in the table.

Now, lets see how to we can add a new record to the database table. You can see a button on our form to add a new record to the database table. here is the handler of that button.

void CdbaccessView::OnBnClickedButton1()
{
adduserdlg dlg;
if(dlg.DoModal()==IDOK)
{
UpdateData(FALSE);
m_pSet->AddNew();
m_pSet->m_username=dlg.username;
m_pSet->m_firstname=dlg.firstname;
m_pSet->m_lastname=dlg.lastname;
m_pSet->m_email=dlg.email;
m_pSet->m_country=dlg.country;
m_pSet->Update();
m_pSet->Requery();
UpdateData(FALSE);} }

I have created a dialog to display when that button is clicked. If you do not know how to create a dialog, see creating custom dialogs in MFC section. The class of this dialog is "adduserdlg". I have created a variable form that class and have called DoModal function to display the dialog. If user fills the data and clicks OK in that dialog, we add the new record to the database table. To add a new record to the database table, we first call the AddNew() function of the CRecordset variable corresponds to that table. Then we assign values to the each field in the table. After that, we call Update() and Requery() fucntions to save the data to the database.

You can download the source code of the sample application here.
Download the Access database file here.

For the example application to work properly, you have to copy database.mdb to C: drive and create an ODBC data source with name 'test' from that file.

Some useful functions in CRecordset

MoveNext()
This function moves to the next record of the table.

MoveLast()
This function moves to the last record of the table.