Using Database in your MFC program - A beginner's experience

Download adofinal.zip - 324.91 KB

Introduction

Currently I'm a beginner of MFC programming. So I understand how puzzled and anxious a beginner will be when he begin to program using MFC. I don't mean that MFC is too difficult to understand by a beginner (though it is not easy).

No matter what programming environment you are using (vc6 or vc >6 (I use VC6)), you'll find that MFC is one thing that you must learn (sooner or later). And you'll find it a bit strange if you have been used to making programs running under console. You cannot find the main function, and puzzled with where to type your code, and problems of this kind. But, what I want to say is, there's just one step between console and graphical based programming! And what I want to do is to make this step easier.

Background

A month ago, one of my friends come to me and asked me to make a program for him and gave me 6 months. I consented to him without consideration. (He's one of my best friend you know)

And the fact is that, the program is graphical based and must be run with a databased connected. And by that time, I even don't know exactly what MFC is.

Under the help of the internet and, which is more important, another one of my best friends. Things becomes much better now, though the program hasn't been finished yet. What I have now is a dialog window with a few controls, and with a database connected. And I decide to put the problems and the solutions here. I hope it will be helpful to those who new to MFC.

Whenever new problems have been solved, I'll append it here. And I'll do this job (weekly because I'm still in school) until the completion of the program.

Questions:

1. How can I create a data source within the code?
2. What shall I do when I have created the data source?

The Data Source (*.mdb)

There are 3 things must be done before creating the data source.

1. Prepare a database (eg: test.mdb)
2. Import library "odbccp32.lib" (Project->Add to project->Files). Path of the file in my computer "C:\Program Files\Microsoft Visual Studio\VC98\Lib".
3. Include head file ("#include <odbcinst.h>")

By using:
SQLConfigDataSource(HWND hwndParent, UINT fRequest, LPCSTR lpszDriver, LPCSTR lpszAttributes);

one can create a data source within his code. (For more details about this function, please look into MSDN.)
The sample code is:

The forth parameter can be generated by the function below:

void getAdoStr( char * str, char dbfile[], char dbname[], char linkstr[] = "DSN=%s!DBQ=%s!DEFAULTDIR=%s!!") { //str = Result //dbfile[] = file name (eg:test.mdb) //dbname[] = data source name char szAtr [256]; CString szPath, szFile; int nPos, nlen, i; GetModuleFileName(NULL, szPath.GetBufferSetLength (MAX_PATH+1), MAX_PATH); szPath.ReleaseBuffer (); nPos=szPath.ReverseFind ('\\'); szPath=szPath.Left (nPos); szFile = szPath + "\\" + dbfile; sprintf (szAtr, linkstr, dbname, szFile, szPath); nlen = strlen (szAtr); for (i=0; i<nlen; i++){ if (szAtr [i] == '!'){ szAtr [i] = '\0'; } } for (i=0; i<nlen; i++){ str [i] = szAtr [i]; } }

CDatabase and CRecordset

Create data source is just the first step. The next step is to use the database.
CDatabase class represents a connection to a data source.
CRecordset class is usually used to select records from forms.

CDatabase m_database; CRecordset m_recordset; CString strSQL; CString username; CString password; SQLConfigDataSource( NULL, ODBC_ADD_DSN, "Microsoft Access Driver (*.mdb)\0", "DSN=DataSourceTest\0DBQ=X:\\dbpath\\test.mdb\0DEFAULTDIR=X:\\dbpath\0\0"); //DataSourceTest is a data source name m_database.Open("DataSourceTest"); m_recordset.m_pDatabase = &m_database; //tell CRecordset where to find the database //Insert a record into database strSQL.Format("insert into user(uname, upwd) values(%s, '%s')", "admin", "pwd"); m_database.ExecuteSQL(strSQL); //Select the record (uname='admin' and password='pwd') strSQL.Format("select * from user where uname='%s' and upwd='%s'", "admin", "pwd"); m_recordset.Open(CRecordset::forwardOnly, strSQL); //Get the number of the selected records if (m_recordset.GetRecordCount() == 0){ //no record found }else{ //Get value of field 'uname' m_recordset.GetFieldValue("uname", username); //Get value of field 'upwd' m_recordset.GetFieldValue("upwd", password); } m_recordset.Close(); m_database.Close();