In this brief article I'm going to describe you 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 Student and Dept with different fields of different types and some sample data (You're able to download it from the above link). One more think 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 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 snipped 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 UserName and Password for logging on to the database. Our sample database doesn't have any users defined so they can be empty strings, and 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 is different ways to represent an SQL statement in ADO. The most regular way is using a
Command object. 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 represent 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".
As our 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 is two ways to execute a command in ADO, first is through the Command objects
Execute member function and the other is using
Open member function of
Recordset object. Here we do it via Recordset's
Open, as below:
pRecordset.CreateInstance (__uuidof (Recordset));
pRecordset->CursorLocation = adUseClient;
pRecordset->Open ( (IDispatch *) pCommand, vtMissing, adOpenStatic,
In the snipped code 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 let 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 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 its 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 students 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 its 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 injoy 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 recordset by 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 its invisible in the recordset.For reappearance of records we should remove the filter at the end, and its 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 recordset by
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 manipulation and update them. But there are really many more things in ADO which you must know about, one of the most important one among them is called stored procedures and parameterized commands.
Executing an Stored Procedure with input parameters
Calling an stored procedure is as easy as opening a new recordset such as the one we did in this article formerly. There is 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 shows you how to set the input parameters and execute the
Query1 stored procedure in our sample database using Parameter object.
_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. Forth 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 pParam1 and 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 its a simple query statement which will return a rowset (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 codes snipped in this article so far, I didn't care about the error handling because of code brerity.At this point, I'm going to mention one simple example of error handling that you can use in all over your program wherever you use some ADO's critical functions. Generally, becuase ADO made of COM objects so 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.
It was a brief explanation of some ADO features. There are really a 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 near future about this subject, discussing more interesting and unique features.
If you have any suggestions ,recommendation or questions please feel free to ask it. I would be glad to hear the article's weaknesses as well as usefulness. You can reach me using email at firstname.lastname@example.org.
I'm a VC++ programmer working in different fields varying from realtime telecommunication softwares to database applications and component based applications.