ODBC and Access
This is a dynamically created data source, database (default Test.mdb), table and its query, insert, modification, and delete.
Generally speaking, the ODBC database is divided into the following steps:
Create a database object -> Connect Database -> Create Records -> Open Record -> Operation Record Set -> Close and Destroy Recording Set -> Close and destroy the database object.
The following details: 1. Open Visual C 6.0 (and determine the Access 2000), in order to easily create a new dialog-based engineering (Dialog), new-> engineering -> MFC AppWizard (exe) > Enter: DYODBC (Project Name) -> Optional Dialog - Based on Dialog Box -> Other default points are completed
2. Open DYODBCDLG.H in the FileView tab (1) Open DYODBCDLG.H Add code: #include
(2) Open stdafx.h Add code: #include
3. Click on the Dialog folder of the ResourceView tab to edit the dialog interface to edit the dialog interface, add the following controls to the dialog box (first delete: Todo: Set the dialog control here):
6 button control ID CAPTION
IDC_CREATEDSN Create Data Source IDC_CREATEDATABASE Creating Database IDC_CREATETABLE Creating Table IDC_DELETEDSN Delete Data Source IDC_DELETEDATABASE Delete Database IDC_DELETABLE Delete Table IDC_ShowDataInfo Display Data Sheet
Add to Dialog: Select ResourceView's DYODBC Resources directory DIALOG album insert Dialog, select IDD_DIALOG1 Right-click Properties to change ID to: IDD_SHOWDATADLG and join the following control: 1 list control
ID STYLES IDC_LISTCONTROL View: Report Add a class, click Right-click on the dialog -> ClassWizard-> ClassWizard Popked dialog (Add a class) Select Create A New Class (Default Option, this is prompting us to Just the second dialog box plus a new class), click OK, fill in the class name: showdatadlg in the pop-up New class dialog, other defaults, click OK; to the list control, click Right click on the list control Double-click the dialog box in the pop-up MEMBER VARIABLE NAME: Enter m_showlist other default, click OK;
4. Add the corresponding code (1) Double-click the button "Create Data Source" On the pop-up dialog box (Add Member Function) default name oncreatedSN, click OK; // Todo: add your control notification handler code here // Add the following code here: cstring strpath = getCurrentPath (); // Get the path of the application CString strdsn; strdsn.format ("% s", dsnname); // strdsn.format ("% s% s% s" , dsnName, strPath, mdbName); if (SQLConfigDataSource (NULL, ODBC_ADD_SYS_DSN, sDriver, strDSN)) // create a data source MessageBox ( "create DSN is OK !!", "create", MB_OK); else MessageBox ( "create DSN ISN't OK !! "," Create ", MB_OK;
(2) Double-click the button "Create Database" in the pop-up dialog box (Add Member Function) default name oncreatedatabase, click OK // Todo: add your control notification handler code here / / Add to code: cstring strpath = GetCurrentPath (); CString strMDB = "CREATE_DB =" strPath mdbName; MessageBox (strMDB, "!!!", MB_OK); if (SQLConfigDataSource (NULL, ODBC_ADD_SYS_DSN, sDriver, strMDB)) MessageBox ( "Create Database is OK! "," Create ", MB_OK; Else MessageBox (" Create Database ISN't OK, May Be Test! "," Create ", MB_OK;
(3) Double-click the button "Create Table" on the pop-up dialog (Add MEMBER FUNCTION) The default name oncreateTable, click OK // Todo: Add Your Control Notification Handler Code Here / / Add to the following code: cstring strsqlexe [ ] = {_ T ("Create Table Test (IID INT, TMJOIN DATETIME, SZNAME VARCHAR (40), FTALL FLOAT);"), _ T ("Create Table Student (Sno Varchar (10), Sname Varchar (12), AGE INT ); "), _ T (" INSERT INTO STUDENT (SNA, SNAME, AGE) VALUES ('JKX0013001', 'XKL', 22); "), _ T (" INSERT INTO TEST VALUES (1111, '2002-1-3 13:25 ',' User_3 ', 1.76); "), _ T (" INSERT INTO TEST VALUES (2222,' 2002-1-1 15:25 ',' User_1 ', 1.56); "), _ T (" Insert INTO TEST VALUES (3333, '2002-1-2 12:25', 'User_2', 1.53); "), _ T (" INSERT INTO TEST VALUES (4444, '2002-1-3 13:25 ",' USER_3 ', 1.76); "),}; try {cstring strpath = getCurrentPath (); pdb.openex (DSNNAME STRPATH MDBNAME); //, cdatabase :: openreadonly; // Open if (! Pdb.isopen () ) // Judgment whether to open MessageBox ("crete", MB_OK); else {pdb.executesql (strsqlexe [0]); pdb.executesql (strsqlexe [1]); pdb.executesql (strsqlexe [2]); pdb.executesql (strsqlexe [3]); P Db.executesql (strsqlexe [4]); pdb.executesql (strsqlexe [5]); pdb.executesql (strsqlexe [6]);}} catch (cdbException, e) {// the error code is in e-> m_nretcode PDB.Close (); cstring strtip = _t ("Database Operate Fail! / N"); Strtip = _ T ("Because: / N") E-> M_STRERROR; AFXMESSAGEBOX (STRTIP);} end_catch pdb.close () ;
(4) Double-click the button "Delete Data Source" In the pop-up dialog (Add Member Function) default name ONDLETEDSN, click OK // Todo: Add Your Control Notification Handler Code Here / / Add to Code here: IF SqlconfigDataSource (NULL, ODBC_REMOVE_SYS_DSN, SDRIVER, DSNNAME)) MessageBox ("Delete DSN IS OK !!", "Delete", MB_OK; Else MessageBox ("Delete DSN Isn't OK, May Be the DSN ISN't Exist !! "," Delete ", MB_OK; (5) Double-click the button" Delete Database "in the pop-up dialog (Add Member Function) default name OndeeledateBase, click OK // Todo: add your control notification handler code here // The following code is added here: if (deletefile (getCurrentPath () mdbname))) MessageBox ("Delete", MB_OK); Else MessageBox ("Delete Database Fail!", "Delete", MB_OK;
(6) Double-click the button "Remove Table" In the pop-up dialog (Add Member Function) Default name OnDeleTable, click OK // Todo: Add Your Control Notification Handler Code Here / / Add to the following code: Try {CString StrPath = getCurrentPath (); pdb.openex (DSNNAME STRPATH MDBNAME); //, cdatabase :: OpenreadOnly; // Open if (! pdb.isopen ()) // Decision Open {MessageBox ("Open Database IS Failed !!! "," create ", mb_ok);} else {pdb.executesql (" delete Table from test; "); // delete table pdb.executesql (" delete from student "); // delete Table PDB. Close ();}} catch (cdbException, e) {// the error code is in e-> m_nretcode pdb.close (); cstract strtip = _t ("Database Operate Fail! / N"); startip = _ t (" Because: / n ") E-> M_STRERROR; AFXMESSAGEBOX (STRTIP);} end_catch pdb.close ();
(7) Double-click the button "Display Data Table" in the pop-up dialog (Add MEMBER FUNCTION) The default name OnShowDataInfo, click OK // Todo: Add Your Control Notification Handler Code Here / / Add to the following code: ShowDataDLG DLG DLG.domodal (); (8) Add #include "globals.h" // custom variable #include "showdatadlg.h" 5. Add display features to showDATADLG.
(1) At the beginning of the showdatadlg.cpp file #include "dyodbcdlg.h" Extern CDatabase PDB; Extern CString DSNName; Extern CString MdbName; (2) Type SHOWDATADLG to select Add WINDOW Message Handler ... item in ClassView, Popked dialog box, New Windows Messages / Events: Find WM_INITDIALOG Double-click, click Add Handler button, add the following code: // Todo: add extra initialization here // Operation of record set CRecordset * dbset = new crecordset & PDB); // cdyodbcdlg DLG; CString strpath = DLG.GetCurrentPath (); // Get Application pathTry {pdb.openex (DSNNAME STRPATH MDBNAME, CDATABASE :: NoodbcDialog); // Open if (! pdb.isopen () AFXMessageBox ("Open DSNNAME ERROR."); // m_databaseInfo; Else {CSTRING STRSQL = _T ("Select * from test"); // cstring strsql = _t ("select * from student"); // cstring strsql = _T ("Select sname from student where agn = 33;"); // cstring strsql = _t ("SELECT IID, TMJOIN from Test where IID = 1111;");
DBSET-> Open (CRecordset :: Dynaset, strsql, crecordset :: readonly); // Statistics Table Information Short FieldCount = dbset-> getodbcfield (); do {dbset-> moext ();} while (! dbset-> ISEOF ()); Long recordcount = dbset-> getRecordcount ();
CSTRING TITLE [MAX_PATH] = {""}; CODBCFIELDINFO FIELDINFO; CSTRINGARRAY STRCOLTILTETLE; if (! Dbset-> isbof ()) dBset-> MoveFirst (); if (strcoltitle.getsize ()) strcoltitle.removeAll ();
// Display column header (field name) for (int i = 0; i
// Display record dbset-> movefirst (); for (i = 0; i
PDB.Close ();}} catch (cdbException, e) {// the error code is in e-> m_nretcode dbset-> close (); delete dbset;
PDB.Close (); cstring strtip = _t ("Database Operate Fail! / N"); Strtip = _ T ("Because: / N") E-> M_STRERROR; AFXMESSAGEBOX (STRTIP);} end_catch
6. Add global variables for the project, click New -> File C / C Header File name: globals.h cdatabase pdb; cstract dsnname = _t ("DSN = presentation data source; dbq ="); // Data source cstring SDRIVER = _T ("Microsoft Access Driver (* .mdb)"); // Drive CString MDBNAME = _T ("// Test.mdb"); // Database Name
7. Add the member function getCurrentPath () to Dyodbcdlg.cpp, select the cdyodbcdlg point button, select the Add Member Function ...; in the pop-up dialog box: function type: cstring function declaration: getcurrentpath () Access: PublicPath () Access: public after determining the point of adding the following code: // get the current working directory CString sPath; CString strDBFile; GetModuleFileName (NULL, sPath.GetBufferSetLength (MAX_PATH 1), MAX_PATH); sPath.ReleaseBuffer (); int nPos = sPath.ReverseFind ( ' // '); spath = spath.left (npos); strdbfile = spath; return_t (strdbfile); 8. Connection, run. . . // This is my study notes, there is something wrong, please contact me //qingbo8@163.com I hope to get your teachings // Thank you for your browsing (End)
