Visual c ++ 6.0 ADO access

The ADO library contains three basic interfaces: _ connectionptr, _ commandptr, and _ recordsetptr.
_ Connectionptr interface returns a record set or a null pointer.
It is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure.
Using the _ connectionptr interface to return a record set is not a good method.
Generally, like cdatabase, you can use it to create a data connection and use other objects to perform data input/output operations.
The _ commandptr interface 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.
If you only perform one or several data access operations, the latter is a good choice.
However, if you want to frequently access the database and return many record sets, you should use the global _ connectionptr interface to create a data connection,
Use the _ commandptr interface to execute stored procedures and SQL statements.
_ 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.
. Like the _ commandptr interface, it does not have to use a created data connection,
You can use a connection string to replace the connection pointer assigned to the connection Member variable of _ recordsetptr, so that it can create its own data connection.
If you want to use multiple record sets, the best way is to use the global _ connectionptr interface that has created a data connection like the command object.
And then use _ recordsetptr to execute stored procedures and SQL statements.

(1) initialize the com library and introduce the ADO library definition file.
(2) connect to the database using the connection object
(3) Use the established connection to execute SQL commands through the connection and command objects, or use the recordset object to get results
Query and process the set.
(4) Close the connection and release the object after use.

We can use afxoleinit () to initialize the com library, which is usually completed in the overload function of cwinapp: initinstance ().
See the following code:
Bool cadotest1app: initinstance ()
{
Afxoleinit ();
......
}

We add the following statement to stdafx. h:
# Import "C:/program files/common files/system/ADO/msado15.dll" no_namespace Rename ("EOF", "adoeof ")
The final function is similar to # include, which we are familiar with. during compilation, the system will generate msado15.tlh and ado15.tli C ++ header files to define the ADO library.

First, we need to add a pointer to the connection object:
_ Connectionptr m_pconnection;
Bool cadotest1dlg: oninitdialog ()
{
Cdialog: oninitdialog ();
Try
{
Hresult hR = m_pconnection.createinstance ("ADODB. Connection"); // create a connection object
If (succeeded (HR ))
{
HR = m_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = test. mdb", "", ", admodeunknown); // connect to the database
// In the above sentence, the provider in the connection string is for the Access2000 environment. For Access97, you need to change it to provider = Microsoft. Jet. oledb.3.51 ;}
}
}
Catch (_ com_error e) // get the com error. When the com function is executed, if an error occurs, the _ com_error exception can be caught.
{
Cstring strcomerror;
Strcomerror. Format ("error no.: % 08lx/n error message: % s/n Error Source: % s/n error Description: % s ",
E. Error (), // error number
E. errormessage (), // error message
(Lpcstr) E. Source (), // error source
(Lpcstr) E. Description (); // error description
: MessageBox (null, strcomerror, "error", mb_iconexclamation );
}
}
You can also use the udl file for connection.
Try
{
M_pconnection.createinstance (_ uuidof (connection ));
M_pconnection-> connectionstring = "file name = E. udl ";
M_pconnection-> open ("", null );
}
Catch (_ com_error E)
{....}
◆ In this code, we connect to the database through the open method of the connection object. The following is a prototype of this method.
Hresult connection15: open (_ bstr_t connectionstring, _ bstr_t userid, _ bstr_t password, long options)
Connectionstring is the connection string,
Userid is the user name,
Password is the login password,
Options is the connection option, used to specify the permission of the connection object to update data,
Options can be the following constants:
Admodeunknown: default. The current permission is not set
Admoderead: Read-Only
Admodewrite: Write only
Admodereadwrite: read/write
Admodesharedenyread: blocks other connection objects and opens the connection with the read permission.
Admodesharedenywrite: blocks other connection objects and opens the connection with write permission.
Admodemo-exclusive: prevents other connection objects from opening connections.
Admodesharedenynone: allows other programs or objects to establish connections with any permissions.
◆ Common Database Connection Methods:
(1) connection to the Access2000 Database through the Jet Database Engine
M_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = C: // test. mdb", "", "", admodeunknown );
(2) Use the DSN data source to connect to any database that supports ODBC:
M_pconnection-> open ("Data Source = adotest; uid = sa; Pwd =;", "", "", admodeunknown );
// M_pconnection-> open ("DSN = test;", "", "", 0); // connect to the ODBC data source named Test
(3) do not use DSN to connect to the SQL Server database:
M_pconnection-> open ("driver = {SQL Server}; server = 127.0.0.1; database = vckbase; uid = sa; Pwd = 139", "", "", admodeunknown );
The server is the name of the SQL Server, and the database is the name of the database.
◆ First, we will introduce two useful connectiontimeout and State attributes in the connection object.
Connectiontimeout is used to set the connection timeout time, which must be called before open, for example:
M_pconnection-> connectiontimeout = 5; // set the timeout value to 5 seconds.
M_pconnection-> open ("Data Source = adotest;", "", "", admodeunknown );
The state attribute specifies the status of the current connection object. 0 indicates that the connection object is closed, and 1 indicates that the connection object has been opened.
Read this attribute for processing. For example:
If (m_pconnection-> state)
M_pconnection-> close (); // if the connection has been enabled, close it.
4]
To obtain the result record set, we define a pointer to the recordset object:
_ Recordsetptr m_precordset;
And create an instance of the recordset object for it:
M_precordset.createinstance ("ADODB. recordset ");
SQL commands can be executed in multiple forms. The following describes how to execute these commands.
◆ (1) Execute SQL commands 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)
Where
Commandtext is a command string, usually an SQL command.
Recordsaffected is the number of rows affected after the operation is completed,
Options indicates the content type in commandtext. options can be one of the following values:
Adshorttext: indicates that commandtext is a text command.
Adcmdtable: indicates that commandtext is a table name.
Ad1_proc: indicates that commandtext is a stored procedure.
Adcmdunknown: Unknown
After Execute is executed, a pointer pointing to the record set is returned. The specific code is provided and described below.
Try
{
_ Variant_t RA;
M_pconnection-> execute ("create table student (SNO integer, sname text, ssex text, sage integer, sdept text)", & RA, ad1_text );
M_pconnection-> execute ("insert into student information (student ID, name, age, birthday) values (112105, 'cheng Hongxiu ', 22, '2017-08-16 ')", & RA, adshorttext); // Add records to the table
M_precordset = m_pconnection-> execute ("select count (*) from student information", & RA, ad1_text); // run the SQL statistics command to obtain the record set containing the number of records
_ Variant_t vcount = m_precordset-> getcollect (_ variant_t) (long) (0); // get the value of the first field and put it in the vcount variable
M_precordset-> close ();
Cstring message;
Message. Format ("% d records in total", vcount. lval );
Afxmessagebox (Message );
}
Catch (_ com_error E)
{...}
◆ (2) use the command object to execute SQL commands
Try
{
_ Commandptr m_pcommand;
M_pcommand.createinstance ("ADODB. Command ");
M_pcommand-> activeconnection = m_pconnection; // key sentence, assign the established connection to it
M_pcommand-> commandtext = "insert into student information (student ID, name, age, birthday) values (112105, 'cheng Hongxiu ', 22, '2017-08-16 ')";
M_pcommand-> execute (null, null, ad1_text );
M_pcommand-> commandtext = "select count (*) from student information ";
M_precordset = m_pcommand-> execute (null, null, ad1_text );
_ Variant_t vcount = m_precordset-> getcollect (_ variant_t) (long) 0); // obtain the value of the first field
Cstring STR;
Str. Format ("% d records in total", vcount. lval );
Afxmessagebox (STR );
M_precordset-> close ();
}
Catch (_ com_error e ){...}
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 stored procedures. We will introduce it in detail next time.
◆ (3) directly use the recordset object for query to obtain the record set
For example
M_precordset-> open ("select * from student information", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );
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)
Where:
① Source is a 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 this 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, neither bookmark, recordcount, absoluteposition, or absolutepage can be used.
Adopenkeyset = 1, // the record set with this cursor cannot see the new or delete operations of other users, but you can see the operations for updating the original records.
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, // not specified
Adlockreadonly = 1, // read-only record set
Adlockpessimistic = 2, // pessimistic locking mode. Lock all other actions when data is updated. This is the most secure locking mechanism.
Adlockoptimistic = 3, // Optimistic Locking mode. The record is locked only when you call the update method. Before that, you can update, insert, or delete data.
Adlockbatchoptimistic = 4, // optimistic batch update. The record is not locked during editing. Modification, insertion, and deletion are completed in batch mode.
};
⑤ For options, refer to the introduction to the execute method of the connection object in this article.

According to the student information table created by executing the SQL command just now, it contains four fields: Student ID, name, age, and birthday
The following code enables you to open a record set, traverse all records, delete the first record, add three records, and move the cursor to the second record.
Record, change its age, and save it to the database.
Try
{
_ Variant_t vusername, vbirthday, vid, vold;
_ Recordsetptr m_precordset;
M_precordset.createinstance ("ADODB. recordset ");
M_precordset-> open ("select * from student information", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );
While (! M_precordset-> adoeof)
{
Vid = m_precordset-> getcollect (_ variant_t (long) 0); // obtain the value of the 1st column and count it from 0. You can also give the column name directly, the following line
Vusername = m_precordset-> getcollect ("name"); // obtain the name field value
Vold = m_precordset-> getcollect ("Age ");
Vbirthday = m_precordset-> getcollect ("Birthday ");
Trace ("ID: % d, name: % s, age: % d, birthday: % S/R/N ",
Vid. lval,
(Lpctstr) (_ bstr_t) vusername,
Vold. lval,
(Lpctstr) (_ bstr_t) vbirthday); // records in the output window output record set in debug mode
M_precordset-> movenext (); // move to the next record
}
M_precordset-> movefirst (); // move to the first record
M_precordset-> Delete (adaffectcurrent); // Delete the current record
For (INT I = 0; I <3; I ++) // Add three new records and assign values
{
M_precordset-> addnew (); // Add a new record
M_precordset-> putcollect ("student ID", _ variant_t (long) (I + 10 )));
M_precordset-> putcollect ("name", _ variant_t ("Wang binnian "));
M_precordset-> putcollect ("Age", _ variant_t (long) 21 ));
M_precordset-> putcollect ("Birthday", _ variant_t ("1930-3-15 "));
}
M_precordset-> move (1, _ variant_t (long) adbookmarkfirst); // move a record from the first record to the second record.
M_precordset-> putcollect (_ variant_t ("Age"), _ variant_t (long) 45); // modify the age
M_precordset-> Update (); // save it to the database.
} Catch (_ com_error e ){}

You can close Record Sets or connections using the close method.
M_precordset-> close (); // close the record set
M_pconnection-> close (); // close the connection
Macro definition in stdafx. h:
# If! Defined catch_error
# Define catch_error/
{/
Cstring strcomerror ;/
Strcomerror. Format ("error no.: % 08lx/n error message: % s/n Error Source: % s/n error Description: % s ",/
E. Error (),/
E. errormessage (),/
(Lpcstr) E. Source (),/
(Lpcstr) E. Description ());/
: MessageBox (null, strcomerror, "error", mb_iconexclamation );/
}
# Endif
Usage:
Try
{...}
Catch (_ com_error E)
{
Catch_error;
}