Access the ACCESS database using MFC to establish and execute the storage process

Access the ACCESS database using MFC to establish and execute the storage process

When I learned how to access the database through VC ado, I felt disgusted every time I encountered a query or storage process with parameters, because it seems that SQL supports these operations, therefore, questions about the stored procedure and query with parameters in access are collected everywhere on the Internet. However, many posts and authors on the Internet feel that they are not clear enough, or simply say that access does not support the storage process, or that the process is too complicated. I learned 《Directly passAfter reading some posts and reading the ADO Access Database (author Xu Jingzhou ),
2003 the storage process is successfully established and VC can be used for convenient operations. Here, I will share my learning content with students who want to get started quickly.

Create a table's data structure and initialized data in Access 2003:

 

 

 

Create the code for the stored procedure and execute the stored procedure in access:

 

Code in VC:

Note that I am using the control correlation variable, which is described as follows:

Clistbox m_accesslist; // control type of the list box
Cstring m_strclass; // student's class string type
Cstring m_strname; // Student name string type

Cstring m_strsno; // student's student ID string type

Int m_nage; // The age integer of the student

Public:
_ Recordsetptr m_precordset;
_ Connectionptr m_pconnection;

Ceditrecord is a dialog box for editing records (class ceditrecord: Public cdialog) as follows:

Some of these functions are not listed here to establish message ing. I believe you should have this foundation for connecting to the database through VC.

Here is the implementation file of class cadodlg.

// Adodlg. CPP: implementation file // # include "stdafx. H "# include" ADO. H "# include" adodlg. H "# include" editrecord. H "// cadodlg dialogcadodlg: cadodlg (cwnd * pparent/* = NULL */): cdialog (cadodlg: IDD, pparent) {// {afx_data_init (cadodlg) m_strclass = _ T (""); m_strname = _ T (""); m_strsno = _ T (""); m_nage = 0; //} afx_data_init // note that loadicon does not require a subsequent destroyicon in Win32 m_hi Con = afxgetapp ()-> loadicon (idr_mainframe); // initialize COM, create ADO connections, and other operations afxoleinit (); m_pconnection.createinstance (_ uuidof (connection )); m_precordset.createinstance (_ uuidof (recordset ));} //////////////////////////////////////// /// // cadodlg message handlersbool cadodlg:: oninitdialog () {// no changes to the previous Code are automatically generated by the VC system. This is not listed here. // todo: add extra initialization here initdata (); // initial list Box data return true; // return true unless you set the focus to a control} // Insert the new record void cadodlg: onbtninsert () {// todo: add your control notification handler code here try {// write each field value m_precordset-> addnew (); updatedata (); // remember to update the operation, obtain data from the control cstring strage; strage. format ("% d", m_nage); m_precordset-> putcollect ("student ID", _ variant_t (m_strsno); m_precordset-> putcollect ("name ", _ variant_t (m_strname); m_precor Dset-> putcollect ("Age", _ variant_t (strage); m_precordset-> putcollect ("class", _ variant_t (m_strclass); m_precordset-> Update (); loaddata (m_precordset);} catch (_ com_error * E) {afxmessagebox (e-> errormessage ();} // execute the storage process of the update record void cadodlg :: onbtnmod () {// todo: add your control notification handler code here _ commandptr m_pcommand; // smart pointer m_pco MmAnd. createinstance (_ uuidof (command); // instance // use the _ parameterptr smart pointer to create the parameter _ parameterptr m_pparam; m_pparam.createinstance (_ uuidof (parameter )); // set the parameter try {updatedata (); m_pparam = m_pcommand-> createparameter ("Sno", advarchar, adparaminput, 15, (_ variant_t) m_strsno ); // set the parameter attributes m_pcommand-> parameters-> append (m_pparam ); // Add the student ID to the parameter set attribute of the command object m_pparam = m_pcommand-> createparameter ("sname", advarchar, ADP Araminput, 20, (_ variant_t) m_strname); m_pcommand-> parameters-> append (m_pparam ); // Add the name to the parameter set attribute of the command object m_pparam = m_pcommand-> createparameter ("Sage", adinteger, adparaminput,-1, (_ variant_t) (long) m_nage); m_pcommand-> parameters-> append (m_pparam); // m_pparam = m_pcommand-> createparameter ("sclass", advarchar, adparaminput, 5, (_ variant_t) m_strclass); m_pcommand-> parameters-> append (M _ Pparam); // The class is added to the parameter set attribute of the command object m_pcommand-> commandtext = "proc_updatename"; m_pcommand-> activeconnection = m_pconnection; m_pcommand-> execute (null, null, adcmdstoredproc); // executes the update data storage process. Note that the record set m_accesslist.resetcontent () is not returned at this time. If (! M_precordset-> adoeof) m_precordset-> movefirst (); While (! M_precordset-> adoeof) {loaddata (m_precordset); m_precordset-> movenext () ;}} catch (_ com_error & E) {cstring error; long I, CNT; CNT = m_pconnection-> errors-> count; error + = E. errormessage (); for (I = 0; I <CNT; I ++) {error + = (lpcstr) (m_pconnection-> errors-> getitem (_ variant_t (I )) -> description) ;}afxmessagebox (error) ;}// the initial list data int cadodlg: initdata () {If (m_pconnection-> isolationlevel) try {// open the local access library M_pconnection-> open ("provider = Microsoft. jet. oledb.4.0; Data Source = stumgr. mdb "," "," ", admodeunknown);} catch (_ com_error e) {afxmessagebox (" database connection failed, check whether the database is in the current path! ", Mb_ OK | mb_iconhand); Return false;} // try... catch () to capture the error message, // because it sometimes produces unexpected errors. Try {m_precordset-> open ("select * from Stu", // query all fields in the stu table m_pconnection.getinterfaceptr (), // obtain the idispatch pointer adopendynamic, adlockoptimistic, ad1_text);} catch (_ com_error * E) {afxmessagebox (e-> errormessage ();} _ variant_t var; cstring strsno, strname, strclass; try {If (! M_precordset-> BOF) m_precordset-> movefirst (); else {afxmessagebox ("empty table data", mb_ OK | mb_iconhand); Return false ;} // read the fields in the database and add them to the list box while (! M_precordset-> adoeof) {loaddata (m_precordset); m_precordset-> movenext ();} // the default list points to the first item, and moves the record pointer and displays m_accesslist.setcursel (0 );} catch (_ com_error * E) {afxmessagebox (e-> errormessage ();} return true ;}// release the database resource void cadodlg: onbtnclose () When closing the dialog box () {// todo: add your control notification handler code here cadodlg: onclose ();} void cadodlg: onclose () {// todo: add your message handler code here and/or Cal L default if (idok = MessageBox ("shut down the database and launch it? "," Student Management ", mb_okcancel | mb_iconexclamation) {try {If (m_pconnection-> state) m_pconnection-> close (); m_pconnection = NULL; If (m_precordset-> state) m_precordset-> close (); m_precordset = NULL;} catch (_ com_error * E) {afxmessagebox (e-> errormessage ();} cdialog: oncancel ();}} // query by age with parameters void cadodlg: onbtnquery () {// todo: add your control notification handler code here cstring strsno, strname, strclass, St Rage; int Nage; // _ recordsetptr precodrset (_ uuidof (recordset); _ commandptr m_pcommand; // or intelligent pointer m_pcommand.createinstance (_ uuidof (command )); // instance // use the _ parameterptr smart pointer in VC to create a parameter, as follows: _ parameterptr m_pparam; // m_pparam.createinstance ("ADODB. parameter "); // The parameter ceditrecord DLG; DLG. m_strtip = "input age:"; if (idok = DLG. domodal () {Nage = atoi (DLG. m_strinput); // Method 1: Specify one time // m_pparam = m_pcommand-> Createparameter ("Age", adinteger, adparaminput,-1, (_ variant_t) (long) Nage ); // set each attribute for the parameter // method 2/* _ variant_t var; var. vt = vt_int; // specify the type as integer var first. intval = Nage; // value m_pparam = m_pcommand-> createparameter ("Age", adinteger, adparaminput,-1, VAR ); // set attributes for parameters * // method 3 m_pparam = m_pcommand-> createparameter ("Age", adinteger, adparaminput,-1 ); // set m_pparam-> value = (_ variant_t) (long) Nage) for each attribute of the parameter; // specify m_pcommand-> par subsequently Ameters-> append (m_pparam); // Add it to the parameter set attribute of the command object m_pcommand-> commandtext = (_ bstr_t) ("select * from Stu where age =? "); M_pcommand-> activeconnection = m_pconnection; try {m_precordset = m_pcommand-> execute (null, null, ad1_text); // execute an SQL statement and return m_accesslist.resetcontent (); while (! M_precordset-> adoeof) {loaddata (m_precordset); m_precordset-> movenext () ;}} catch (_ com_error & E) {cstring error; long I, CNT; CNT = m_pconnection-> errors-> count; error + = E. errormessage (); for (I = 0; I <CNT; I ++) {error + = (lpcstr) (m_pconnection-> errors-> getitem (_ variant_t (I )) -> description) ;}afxmessagebox (error) ;}}// load data from the record set to the list box and edit the control void cadodlg: loaddata (_ recordsetptr precordset) {int Nage; cstring Strsno, strname, strclass, strage; If (! Precordset-> adoeof) {_ variant_t var; Var = m_precordset-> getcollect ("student ID"); If (var. VT! = Vt_null) strsno = (lpcstr) _ bstr_t (VAR); Var = m_precordset-> getcollect ("name"); If (var. VT! = Vt_null) strname = (lpcstr) _ bstr_t (VAR); Var = m_precordset-> getcollect ("Age"); If (var. VT! = Vt_null) Nage = atoi (_ bstr_t (VAR); strage. format ("% d", Nage); Var = m_precordset-> getcollect ("class"); If (var. VT! = Vt_null) strclass = (lpcstr) _ bstr_t (VAR); m_accesslist.addstring (strsno + "-->" + strname + "-->" + strage + "-->" + strclass ); getdlgitem (dependencies)-> setwindowtext (strsno); getdlgitem (idc_edit_name)-> setwindowtext (strname); getdlgitem (idc_edit_age)-> setwindowtext (strage); getdlgitem (idc_edit_class) -> setwindowtext (strclass) ;}/// query the void cadodlg: onbtnprocedure () {// todo: add your control noti Fication handler code here cstring strsno, strname, strclass, strage; int Nage; // _ recordsetptr precodrset (_ uuidof (recordset); _ commandptr m_pcommand; // intelligent pointer m_pcommand.createinstance (_ uuidof (command); // instance // use the _ parameterptr smart pointer to create the parameter _ parameterptr m_pparam; // The parameter ceditrecord DLG is set; DLG. m_strtip = "input age:"; try {If (idok = DLG. domodal () {Nage = atoi (DLG. m_strinput); m_pparam = m_pcommand-> createparamet Er ("Age", adinteger, adparaminput,-1); // set m_pparam-> value = (_ variant_t) (long) Nage) for each parameter ); m_pcommand-> parameters-> append (m_pparam); // m_pcommand-> commandtext = "proc_queryage" in the parameter set attribute of the command object; m_pcommand-> activeconnection = m_pconnection; m_precordset = m_pcommand-> execute (null, null, ad1_storedproc); // execute the SQL statement and return the record set m_accesslist.resetcontent (); While (! M_precordset-> adoeof) {loaddata (m_precordset); m_precordset-> movenext () ;}} catch (_ com_error & E) {cstring error; long I, CNT; CNT = m_pconnection-> errors-> count; error + = E. errormessage (); for (I = 0; I <CNT; I ++) {error + = (lpcstr) (m_pconnection-> errors-> getitem (_ variant_t (I )) -> description) ;}afxmessagebox (error );}}

 

In the experiment, the results of querying by age SQL with parameters are the same as those of querying by age stored procedures. Example:

 

Of course, this article only provides a simple example of executing the query and storage process with parameters. The specific application still needs to be further explored. The Code has passed the test. In case of problems, modify the path of your program database, the database data type, and the dialog box structure.