Using SQL statements

In the previous article, we just connected to a database with a single table and took the whole rows in that table one by one. But, in usual database applications, we have to execute various custom SQL statements in a database with several tables and the resulting recordsets returned varies depending on the query we executed.

In this article, you can learn how to execute SQL queries on your database and retrieve results. We use the same database.mdb file with only a one table. Our application is going to retrieve only the email address when the username is given. This time, our application is a usual dialog based MFC application. Here is the main dialog of our application.

When we enter a valid username which is in our database and click 'get email', a message box is displayed with the email address of the specified username.

Here is the handler of the button 'get email'.

void CsqlDlg::OnBnClickedButton1()
{
UpdateData(TRUE);
CDatabase database;
CString connectionstring, sqlquery, email;

connectionstring=TEXT("ODBC;DRIVER={MICROSOFT ACCESS DRIVER (*.mdb)};DSN='';DBQ=database.mdb");

database.Open(NULL, FALSE, FALSE, connectionstring);
CRecordset set(&database);

sqlquery.Format(TEXT("SELECT email FROM users where username='%s'"), username);

set.Open(CRecordset::forwardOnly, sqlquery, NULL);
if(set.IsEOF())
MessageBox(TEXT("username not in table"));else
{
set.GetFieldValue(TEXT("email"), email);
MessageBox(email);}
database.Close();
}

The code should be easy to understand. We have used the same MFC classes, CDatabase and CRecordset, which we used in the previous article. But here, we manually opened the database using a connection string and created the CRecordset using that database. Then, we used a custom SQL statement to get the results to our CRecordset variable using the open() function.

You have to enter the header file "afxdb.h" yourself this time to use CDatabase and CRecordset classes which were automatically included by the wizard in our previous lesson. Also, notice that this time we are not creating an ODBC database but directly accessing the database file using the driver. That is specified in the connection string we used this time. If you are going to use an advanced database such as SQL Server, then you have to create the data source and use the appropriate connection string to connect to the data source.

The example project is in Visual Studio 2008 format.

Download source code

Download database file

You have to copy the source file to the dame folder as the exe file of the application because in the connection string, we have used only the database file name. If you want, you can place the database file elsewhere and use the correct path to the file in the connection string.