In this brief article, I'm going to describe how to start working with ADO, for instance, how to open a recordset, query the database or execute a stored procedure.
First of all, let me inform you about the requirements. In this article, I used ADOTestDB.MDB as a sample database in conjunction with VC++ 6 (SP3) and ADO 2.1.In the database file, I implemented two simple tables, named
Dept with different fields of different types and some sample data (You're able to download it from the above link). One more thing that I should mention here is that I'm using
import directive and gain features of smart pointers in this article.
By typing the following lines in your stdafx.h, you simply can do the same too.
#import "msado15.dll" \
rename( "EOF", "adoEOF" )
If you mention the above code, you'll get that I renamed the
adoEOF, doing so will prevent your application from some nasty conflicts in future which will waste your time to find out the reason.
After doing so, you need to initialize the COM before doing anything. In order to do that, call the
CoInitialize(NULL) function somewhere at the beginning of your program and call
CoUninitialize() after doing your job with ADO.
Now, we are going to the main part of using ADO.
Making a Connection to the Database Server
The first step to access a data source in ADO is connecting to the data source. In order to connect to a data source, you can use ADO's
Connection object. The main way ADO establishes a connection is through the Connections Open member function. Take a look at the following code (Error handling omitted because of code brevity):
_bstr_t ( "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source = ADOTestDB.MDB" ),
_bstr_t ( "" ),
_bstr_t ( "" ),
That is a sample code snippet to establish a connection to the ADOTestDB.MDB via
Microsoft.Jet.OLEDB.4 provider. First of all, you have to define an object of type
_ConnectionPtr (ADO Connection), then you should instantiate it and last, issue the
Open function. The first parameter of
Open member function is connection string. This parameter specifies the data source you are trying to connect as well as the OLEDB provider for it. Next two parameters specify the
Password for logging on to the database. Our sample database doesn't have any users defined so they can be empty
strings, and the last parameter is
Options which determines if the open must work as a synchronous or asynchronous function. You can simply type
adModeUnknown if you don't know much about this feature as I did.
It's not bad to mention that you can also use ODBC drivers to connect to a data source, the following code will show you how to do it:
m_pConn->Open (_bstr_t ("DSN=pubs;uid=sa;pwd=;"),
Representing an SQL Statement
There are different ways to represent an SQL statement in ADO. The most regular way is using a
Command is an instruction understood by data provider to modify, manage and manipulate data source which is typically written in SQL. Now we are going to query our sample data source for all students in the
Student table. The code looks like the following:
pCommand.CreateInstance (__uuidof (Command));
pCommand->ActiveConnection = m_pConn;
pCommand->CommandText = "Select * From Student";
As you see above, at first, we declared a
Command object, then instantiated it. As a next step, we set the
ActiveConnection property of the command object by previously opened connection object,
CommandText property of command object represents the query, SQL statement or stored procedure name, which will be issued against the provider, in our case, it's the simple query statement, "
Select * From Student".
command text is a query, by issuing, it would return a set of rows which we should store somewhere. For this purpose, we will define a
Recordset object which will store the returned rows and in addition will let you manipulate these rows.
Now, we are going to execute this command, but let me mention something as before. There are two ways to execute a command in ADO, first is through the
Execute member function and the other is using
Open member function of
Recordset object. Here, we do it via
Open, as below:
pRecordset.CreateInstance (__uuidof (Recordset));
pRecordset->CursorLocation = adUseClient;
pRecordset->Open ( (IDispatch *) pCommand, vtMissing, adOpenStatic,
In the code snippet, we first defined an object of type
Recordset and then instantiated it.
Recordset object in ADO has a dozen of valuable properties for different purposes, the one we used here is
CursorLocation, which lets you specify the cursor's location usually between client side and server side one. After doing these, you can call the
Open function in order to issue the command against the data source. First parameter of
Open member function specifies a variant evaluated to a
Command object, an SQL statement, a Table name, a Stored procedure call or a URL known as a
Source. Second parameter is an active connection and is optional. As we specified, our active connection is in
Command object, it doesn't require to specify it again, just simply miss the parameter (In VC++ whenever you need to specify a missing parameter whose type is
Variant, specify a
_variant_t with a value of
DISP_E_PARAMNOTFOUND and a type of
VT_ERROR. Alternatively, specify the equivalent
vtMissing, which is supplied by the
#import directive). Third and forth parameters are cursor type and lock type. The lock type of
adLockBatchOptimistic is specified because of batch processing we'll use here, and because this processing requires cursor services, we specified the cursor location before. The last parameter indicates how the provider should evaluate the
Source argument if it's something other than a
Command object, but our source is exactly a command object here, so simply type
adCmdUnknown as the last parameter. Now, after issuing the
Open function, you'll have all students specification in your
Manipulating the Data
At this point, we are going to make some changes to the data. One of the fields in
Student table is
SocialSecNo which shows each
student's social security number, consider by some problem the government faced, it has to change the social security numbers starting with '
45' to something like '
77'. So, we have to change all the
SocialSecNo starting with '
45' to '
77'. In order to do this, we filter the current recordset for all
SocialSecNo starting with '
45'. Moreover, we'll set
StudentNo field as an
index in the
recordset so as to increase the sorting and filtering performance.
Here, you might think that it's not really efficient to do something such as the following, and it's somehow correct depending on the situation but my most important purpose here is, introducing different capabilities of ADO, so don't think about efficiency and enjoy using ADO's features.
The code for manipulating data looks like this:
GetItem ("Optimize")->Value = VARIANT_TRUE;
pRecordset->Sort = "Name";
pRecordset->Filter = "SocialSecNo LIKE '45*'";
CString str = (char *) (_bstr_t) pRecordset->Fields->
str = "77" + str.Right(str.GetLength() - 2);
pRecordset->Filter = (long) adFilterNone;
In the above code, at first, we set the
Optimize property of
StudentNo field to make it an index in the
recordset, then we've sorted the
Name filed and filtered it for records which
SocialSecNo starts with '
45'. In the
while loop, we just simply changed the
SocialSecNo value to its new one and moved the current position to next record in the
recordset. As you change the
SocialSecNo to its new value, it no longer matches the filter criteria so it's invisible in the
recordset. For reappearance of records, we should remove the filter at the end, and it's precisely what I did at the last line of the code.
Updating the Data
There are generally two ways of updating the data in ADO. The first method is immediate update, it does mean that you make changes directly to the
recordset and therefore data source as soon as you issue the
Update function. But the second method is known as the Batch mode update. If you open your
adLockBatchOptimistic lock type, ADO lets you update your changes in Batch mode. In Batch mode, every navigation over a record or call to
Update function on the current record saves changes on a copy buffer and it's just after calling
BatchUpdate function that changes propagates to the data source.
In our example, we're using Batch mode so you can propagate changes with the following simple line of code:
pRecordset->BatchUpdate (adAffectAll );
Now you learnt how to open and query for some data and then manipulate and update them. But there are really many more things in ADO which you must know about, one of the most important among them is called stored procedures and parameterized commands.
Executing a Stored Procedure with Input Parameters
Calling a stored procedure is as easy as opening a new recordset such as the one we did in this article formerly. There are just some minor additions for creating input parameters and assigning their values, which we're going to discuss in the following lines. There are two general ways of passing parameters in ADO, first one is through
Parameter object and the second one is via
Refresh method, but I always prefer the first method because of the performance privileges it has over the second one.
Here, I'll describe first methods for you. The following lines of code show you how to set the input parameters and execute the
Query1 stored procedure in our sample database using
_ParameterPtr pParam1, pParam2;
pCommand->ActiveConnection = m_pConn;
pCommand->CommandText = "Query1";
pCommand->CommandType = adCmdStoredProc;
pParam1 = pCommand->CreateParameter ( _bstr_t ("DeptID"), adTinyInt,
adParamInput, sizeof (BYTE),
_variant_t ( long (11)));
pCommand->Parameters->Append ( pParam1);
pParam2 = pCommand->CreateParameter ( _bstr_t ("Name"), adVarChar,
adParamInput, str.GetLength (),
pCommand->Parameters->Append ( pParam2);
pRecordset = pCommand->Execute(NULL, NULL, adCmdStoredProc);
At the beginning, create a
Command object, then set the
ActiveConnection property to an already opened connection, next, specify your stored procedure name in
CommandText and assign the
adCmdStoredProc value to the
CommandType property of your
Command Object. As your stored procedure has two input parameters, so declare two
Parameter objects and then, create them by calling
CreateParameter method of
Command object. The first argument of
CreateParameter refers to an optional name for your parameter and the second specifies type of the parameter, the third one determines the direction of the parameter which is input in our case. The fourth argument stands for the length of the variable and the fifth one specifies the value of the parameter.
After specifying arguments, the created parameters respectively will be assigned to the
pParam2. The created parameters then should be added (
Appended) to the
Parameters collection of our
Command object, so as to do that we call the
Append method for each
Parameter object over
Parameters collection. Now, your command is ready for execution.
If you mention to the
Query1 stored procedure in our sample database, you'll get that it's a simple query statement which will return a
recordset), so after execution of the
command object, it'll probably return a
recordset that we can store in a
Recordset object easily, as we did above.
Error Handling Mechanism
In all code snippets in this article so far, I didn't care about the error handling because of code brevity. At this point, I'm going to mention one simple example of error handling that you can use all over your program wherever you use some ADO's critical functions. Generally, because ADO is made of COM objects, it produces exceptions of type
_com_error whenever an error occurs at runtime. You, by putting your ADO function calls in a
try - catch block are able to handle the generated exceptions. Take a look at the following code:
HRESULT hr = m_pConn.CreateInstance(__uuidof(Connection));
if (FAILED( hr ))
"Can't create an intance of ADO.Connection" );
if (FAILED( m_pConn->Open(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source =
ADOTestDB.MDB"), _bstr_t( "" ), _bstr_t( "" ),
AfxMessageBox( "Can't open datasource" );
catch( _com_error &e )
TRACE( "Exception thrown for classes generated by #import" );
TRACE( "\tCode = %08lx\n", e.Error());
TRACE( "\tCode meaning = %s\n", e.ErrorMessage());
TRACE( "\tSource = %s\n", (LPCTSTR) bstrSource);
TRACE( "\tDescription = %s\n", (LPCTSTR) bstrDescription);
TRACE ( "*** Unhandled Exception ***" );
Consider in the above code, the
Open function call can't find the specified mdb file in the folder, so it'll generate an exception determining the kind of error and error code, in addition to a brief description about the error.
This was a brief explanation of some ADO features. There are really plenty of other things in ADO which you should learn if you want to become an expert in this subject. Here, I tried to give you some clues to start learning ADO and developing useful applications using it, I'm also working to issue more articles in the near future about this subject, discussing more interesting and unique features.
If you have any suggestions, recommendations or questions, please feel free to ask. I would be glad to hear the article's weaknesses as well as usefulness. You can reach me using email at email@example.com.