Vckbasec ++ calls ado

Programming with ADO in Visual C ++


Organized by wnchg

1. generate an applicationProgramFramework and initialize the OLE/COM library Environment Create a Standard MFC Appwizard (exe) application, and then initialize the OLE/COM Library (because the ADO library is a com dll library) in the initinstance function of the ADO database ). In this example:

Bool cadotestdlg: oninitdialog () {: coinitialize (null); // initialize the OLE/COM library environment}

The program must call: couninitialize (); // release the com resources occupied by the program. In addition:

M_precordset-> close (); note !!! Do not close it multiple times !!!!!!!!!!!! M_pconnection-> close (); m_precordset = NULL; m_pconnection = NULL;

2. Introduce the ADO Library File Before using ADO, you must use the import symbol # import to introduce the ADO library file at the end of the stdafx. h file of the project so that the compiler can compile it correctly.CodeAs follows: # Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll" no_namespace Rename ("EOF", "adoeof ") Ado class is defined as a kind of resource stored in ado dll (msado15.dll), which is internally called a Type Library. The Type Library describes the autonomous interface and the com vtable interface used by C ++. When using the # import command, Visual C ++ needs to read this type of library from ado dll at runtime and create a set of C ++ header files. These header files have the. TLI and. tlh extensions, which can be found in the project directory. The ADO class called in the C ++ program code should be defined in these files. The third line of the program indicates that the ADO object does not use namespaces. In some applications, namespace is necessary because the objects in the application may conflict with those in ADO. If you want to use a namespace, you can change the third-line program to rename_namespace ("adons "). The fourth line of code changes the EOF In ADO to adoeof to avoid conflicts with other libraries that define their own EOF. 3. database operations using smart pointers Define two ADO smart pointer instances in the caboutdlg header file, and add a listctrl in the dialog box.

Class cadotestdlg: Public cdialog {_ connectionptr m_pconnection; _ recordsetptr m_precordset; clistctrl m_list ;......}

the ADO library contains three smart pointers: _ connectionptr, _ commandptr, and _ recordsetptr. _ connectionptr is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure. _ commandptr returns a record set. It provides a simple method to execute stored procedures and SQL statements that return record sets. When using the _ commandptr interface, you can use the global _ connectionptr interface, or you can directly use the connection string in the _ commandptr interface. _ Recordsetptr is a record set object. Compared with the above two types of objects, it provides more control functions for the record set, such as record lock and cursor control. Add the following code to onbutton1 In the event response using the ADO program:

Void cadotestdlg: onbutton1 () {m_list.resetcontent (); m_pconnection.createinstance (_ uuidof (connection); // initialize connection pointer m_precordset.createinstance (_ uuidof (recordset )); // initialize the recordset pointer try {m_pconnection-> open ("DSN = adotest", "", "", 0); // connect the ODBC data source called adotest // note: this is an open function that does not require a user ID or password to connect. // otherwise, the form is-> open ("DSN = test; uid = sa; Pwd = 123 ;","","", 0); // execute the SQL statement to obtain a record set and assign its pointer to m_precordsetcstring strsql = "select * from m Iddle "; BSTR bstrsql = strsql. allocsysstring (); m_precordset-> open (bstrsql, (idispatch *) m_pconnection, adopendynamic, adlockoptimistic, ad1_text); // adopendynamic: Dynamic adlockoptimistic optimistic blocking ad1_text: text query statement while (! M_precordset-> adoeof) // traverses all records {// one of the methods for getting record Field Values _ variant_t thevalue; // variant data type thevalue = m_precordset-> getcollect ("big_name "); // obtain the value of big_name if (thevalue. VT! = Vt_null) m_list.addstring (char *) _ bstr_t (thevalue )); // Add this value to the list control. // method 2 for getting the record field value // _ bstr_t thevalue1 = m_precordset-> fields-> getitem ("big_name")-> value; // cstring temp = thevalue1.copy (); // m_list.addstring (temp); // data type conversion _ variant_t vusername, vbirthday, vid, vold; trace ("ID: % d, name: % s, age: % d, birthday: % s \ r \ n ", vid. lval, (lpctstr) (_ bstr_t) vusername, vold. lval, (lpctstr) (_ bstr_t) vbirthday); m_precordset-> movenext (); // transfers to the next record} M_precordset-> close (); m_pconnection-> close ();} catch (_ com_error e) // Exception Handling {afxmessagebox (E. errormessage ();} m_precordset-> close (); // note !!! Do not close it multiple times !!!! Otherwise m_pconnection-> close (); m_precordset = NULL; m_pconnection = NULL ;}

the program converts data of the COM Object and C ++ type through _ variant_t and _ bstr_t. The _ variant_t class encapsulates the OLE autonomous variant data type. Using the _ variant_t class in C ++ is much easier than directly using the variant data type. after compilation, the program can run, but remember to create an ODBC data source named adotest before running. This program will display the big_name field value in the table middle in the list control. 4. execute the SQL command and obtain the result record set to obtain the result record set, we define a pointer to the recordset object: _ recordsetptr m_precordset; and create a recordset object for the instance: m_precordset.createinstance ("ADODB. recordset "); SQL commands can be executed in multiple forms. The following describes how to execute SQL commands. (1) execute the SQL command using the execute method of the connection object the prototype of the execute method is as follows:

_ Recordsetptr connection15: Execute (_ bstr_t commandtext, variant * recordsaffected, Long Options) commandtext is a command string, usually an SQL command. The recordsaffected parameter indicates the number of rows affected by the operation. The options parameter indicates the content type in commandtext. The options parameter can be set to one of the following values: ad1_text: indicates that commandtext is the text command adcmdtable: it indicates that commandtext is a table name ad1_proc: It indicates that commandtext is a stored procedure adcmdunknown: After an unknown execute is executed, a pointer pointing to the record set is returned. The following describes the specific code. _ Variant_t recordsaffected; // run the SQL command: Create Table to create users. Users contains four fields: integer ID, string username, and integer old, date type birthday m_pconnection-> execute ("create table users (ID integer, username text, old integer, birthday datetime)", & recordsaffected, ad1_text ); /// Add the record m_pconnection-> execute ("insert into users (ID, username, old, birthday) values (1, '''washington '''', 25, ''' 1970/1/1') ", & recordsaffected, ad1_text ); /// Add the values of all record old fields to m_pconnection-> execute ("Update users set old = old + 1", & recordsaffected, ad1_text ); /// execute the SQL statistics command to obtain the set of records containing the number of records m_precordset = m_pconnection-> execute ("select count (*) from users", & recordsaffected, ad1_text ); _ variant_t vindex = (long) 0; _ variant_t vcount = m_precordset-> getcollect (vindex ); /// put the value of the first field into the vcount variable and write-_ variant_t vcount = m_precordset-> getcollect (_ variant_t) (long) 0 )); m_precordset-> close (); // close the record set cstring message; message. format ("% d records in total", vcount. lval); afxmessagebox (Message); // display the number of current records

(2) Execute SQL commands using command objects

_ Commandptr m_pcommand; m_pcommand.createinstance ("ADODB. command "); _ variant_t vnull; vnull. vt = vt_error; vnull. scode = disp_e_paramnotfound; // It is defined as a very critical sentence without the parameter m_pcommand-> activeconnection = m_pconnection, assign the established connection m_pcommand-> commandtext = "select * from users"; // command string m_precordset = m_pcommand-> execute (& vnull, & vnull, ad1_text ); /// execute the command to obtain the record set

In this Code, we only use the command object to execute the SELECT query statement. The command object can truly reflect its role in calling the stored procedure. We will introduce it in detail next time. (3) directly use the recordset object for query to obtain the record set Instance --

Void cgmsadlg: ondbselect () {// todo: add your control notification handler code here _ recordsetptr RS1; // define the recordset object _ bstr_t connect ("DSN = GMS; uid = sa; Pwd =; "); // defines the connection string _ bstr_t source (" select count (*) from BUAA. mdb010 "); // The SQL statement to be executed: coinitialize (null); // initialize the RS1 object hresul hR = rs1.createinstance (_ uuidof (recordset )); // The RS1-> open (source, connect, adopenforwardonly, adlockreadonl Y,-1); _ variant_t temp = RS1-> getcollect (_ variant_t (long) 0); cstring strtemp = (char *) (_ bstr_t) temp; messageBox ("OK! "+ Strtemp);} For example m_precordset-> open (" select * from users ", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, ad1_text ); the prototype of the open method is as follows: hresult recordset15: open (const _ variant_t & source, const _ variant_t & activeconnection, Enum cursortypeenum cursortype, Enum locktypeenum locktype, long options: ① source is the data query string ② activeconnection is a established connection (we need to use the connection object pointer to construct a _ variant_t Object) ③ cursortype the cursor type. It can be one of the following values. Please refer to the enumeration structure: Enum cursortypeenum {adopenunspecified =-1, // do not specify adopenforwardonly = 0, /// roll the static cursor forward. The cursor can only browse the record set forward. For example, if movenext is used to scroll forward, this method can increase the browsing speed. However, record sets such as bookmark, recordcount, absoluteposition, and absolutepage cannot use adopenkeyset = 1. // The records set with the cursor cannot be added or deleted by other users, however, the operation to update the original record is visible to you. Adopendynamic = 2, // dynamic cursor. All database operations are immediately reflected in each user record set. Adopenstatic = 3 // static cursor. It generates a static backup for your record set, but the addition, deletion, and update operations of other users are invisible to your record set .}; ④ Locktype: it can be one of the following values. See the following enumeration structure: Enum locktypeenum {adlockunspecified =-1, // adlockreadonly = 1 is not specified, /// read-only record set adlockpessimistic = 2, pessimistic locking mode. When data is updated, all other actions are locked. This is the most secure locking mechanism adlockoptimistic = 3, Optimistic Locking method. The record is locked only when you call the update method. Before that, data can still be updated, inserted, deleted, and other actions. adlockbatchoptimistic = 4, optimistic batch update. The record is not locked during editing. Modification, insertion, and deletion are completed in batch mode .}; ⑤ Options can take one of the following values: ad1_text: It indicates that commandtext is a text command adcmdtable: It indicates that commandtext is a table name ad1_proc: It indicates that commandtext is a stored procedure adcmdunknown: Unknown

5. record set traversal and update according to the users table created by executing the SQL command, it contains four fields: ID, username, old, birthday code implementation below: Open the record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, save to database.

_ Variant_t vusername, vbirthday, vid, vold; _ recordsetptr m_precordset; m_precordset.createinstance ("ADODB. recordset "); m_precordset-> open (" select * from users ", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, ad1_text); While (! M_precordset-> adoeof) {vid = m_precordset-> getcollect (_ variant_t (long) 0); // obtain the value of the 1st column, counting from 0, /// you can also directly give the column name, as shown in the following line: vusername = m_precordset-> getcollect ("username "); /// obtain the username field value vold = m_precordset-> getcollect ("old"); vbirthday = m_precordset-> getcollect ("Birthday "); /// if (vid. VT! = Vt_null & vusername. VT! = Vt_null & vold. VT! = Vt_null & vbirthday. VT! = Vt_null) trace ("ID: % d, name: % s, age: % d, birthday: % s \ r \ n", vid. lval, (lpctstr) (_ bstr_t) vusername, vold. lval, (lpctstr) (_ bstr_t) vbirthday); m_precordset-> movenext (); // move to the next record} m_precordset-> movefirst (); /// move to the first record m_precordset-> Delete (adaffectcurrent); // Delete the current record // Add three new records and assign the value for (INT I = 0; I <3; I ++) {m_precordset-> addnew (); // Add the new record m_precordset-> putcollect ("ID", _ variant_t (long) (I + 10 ))); m_precordset-> putcollect ("username", _ variant_t ("Silverlight"); m_precordset-> putcollect ("old", _ variant_t (long) 71 )); m_precordset-> putcollect ("Birthday", _ variant_t ("1930-3-15");} m_precordset-> move (1, _ variant_t (long) adbookmarkfirst )); /// move one record down from the first record, that is, to the second record m_precordset-> putcollect (_ variant_t ("old"), _ variant_t (long) 45 )); /// modify the age m_precordset-> Update (); // save it to the database.

Note: multiple queries can make the query process into a function executesql that allows m_precordset to obtain the query result of the connection pointer m_pconnection.

Void executesql (_ connectionptr m_pconnection, _ recordsetptr m_precordset, cstring strsql) {// execute the SELECT statement BSTR bstrsql = strsql. allocsysstring (); try {m_precordset-> open (bstrsql, (idispatch *) m_pconnection, adopendynamic, adlockoptimistic, adshorttext); // adopendynamic: Dynamic adlockoptimistic optimistic blocking adshorttext: text query statement} catch (_ com_error error) {cstring errormessage; errormessage. format ("% s", (lptstr) error. description (); afxmessagebox (errormessage) ;}// error handling: 3127 -- the target table 3092 is not found -- the target table already exists, for example, catch (const _ com_error E) {afxmessagebox (E. description (); long errorcode = E. wcode (); If (3127 = errorcode) afxmessagebox ("table does not exist"); If (3092 = errorcode) afxmessagebox ("table already exists"); Return false ;}

Latest comments[Comment] [Article Contribution]

View All comments Recommended to friends Print

When I use VC to connect to a database using ADO, this problem occurs when I modify or add or delete the database: the SQL server does not exist or the access is denied connectionopen (connect ()), but it can be queried. I use DataGrid and ADO together! Thank you for your advice! (Ljguobing was published on 18:06:00) I would like to use ADO to connect to the Oracle 9i Database. The code is: _ Connectionptr m_pconnection; M_pconnection.createinstance (_ uuidof (connection )); Try { M_pconnection-> open ("provider = oraoledb; database = xx", "uid = xxx", "Pwd = xxx", 0 ); .......... } Catch (_ com_error & E ){ : MessageBox (null, E. Description (), "", mb_ OK | mb_iconwarning ); } M_pconnection-> close (); After running, an error prompt box is displayed, indicating that the user name or password is invalid. But the database username and password are correct. How can I solve this problem? Thank you. . (James1918 was published on 12:44:00)   Welcome to the China Micro software development center. The website is under construction... Tailor-made small software for you, Http://minisoft.91x.net/ Contact info: Email: ldh5118@sohu.com QQ: 36201365 (rbird5118 was published on 16:02:00)   Why cannot I display my data? While (! M_precordset-> adoeof) // traverses all records { // One of the methods for getting record Field Values _ Variant_t thevalue; // variant Data Type Thevalue = m_precordset-> getcollect ("big_name"); // obtain the value of the big_name field. If (thevalue. VT! = Vt_null) M_list.addstring (char *) _ bstr_t (thevalue )); // Add the value to the List Control // Method 2 for getting the record Field Value // _ Bstr_t thevalue1 = m_precordset-> fields-> getitem ("big_name")-> value; // Cstring temp = thevalue1.copy (); // M_list.addstring (temp ); // Data type conversion _ Variant_t vusername, vbirthday, vid, vold; Trace ("ID: % d, name: % s, age: % d, birthday: % s \ r \ n ", Vid. lval, (lpctstr) (_ bstr_t) vusername, vold. lval, (lpctstr) (_ bstr_t) vbirthday ); M_precordset-> movenext (); // transfers to the next record } (Thomos was published on 16:25:00)   Thanks wenyuansunrise's reply If it is not added to DLL (stdafx. h) # Import "C: \... \ ADO \ msado15.dll" no_namespace Rename ("EOF", "adoeof") means no error will occur in the executable application .? (Rabbitkn was published on 18:50:00)   Reply to this question: Rabbitkn@sina.com: Add in DLL (stdafx. h) # Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll" no_namespace Rename ("EOF", "adoeof ") An error occurs during compilation. Ompiling... Stdafx. cpp F: \ test \ database \ ncsimu \ ncsimudll \ debug \ msado15.tlh (169): Error c2011: 'locktypenum': 'enum' c2011: 'parameterdireoneonenum': 'enum' type redefinition Error executing cl.exe. Ncsimu.exe-6 error (s), 1 warning (s) Why ??? High finger points (Posted on 10:28:00) // ------------------------ when you use the MFC wizard, you have selected support for header files, which leads to repeated definitions. invincible 2:31:00 am (wenyuansunrise was published on) when using the SQL Server database, if the data in the "License Plate" record is null rather than ", an error occurs, indicating that no data can be read: _ variant_t varcar; varcar = m_pcomrecordset-> getcollect ("license plate"); or _ bstr_t m_ballbstr; m_ballbstr = m_pcomrecordset-> fields-> getitem ("license plate")-> value; m_strcarnb = m_ballbstr.copy (); change to try { m_ballbstr = M_pcomrecordset-> fields-> getitem ("license plate")-> value; m_strcarnb = m_ballbstr.copy (); }< br> catch (_ com_error E) {< br> m_strcarnb = ""; }< br> This is my experience (xxq57 was published on 11:20:00) How can I read the database field name, like the VB method: Rs. fields (1 ). name is the name of the second field in the RS of the read record set. urgent !! send an example to my mailbox: scienceMode@163.com In addition, which of the following books or materials about netmeeting SDK usage ?? (Sciencemode was published on 16:42:00) for the solution to the rabbitkn@sina.com problem, see: http://support.microsoft.com/default.aspx? SCID = KB; en-US; q169496 (ztliu01 published on 9:17:00) Please introduce some books on VC ++ ADO programming for urgent use ..... liufusheng@konka.com, thank you first (liufusheng published on 13:45:00) .................................. ..................... more...