Introduction

Microsoft ActiveX Data Objects (ADO) allow you to manipulate and store your data in a database server through an OLEDB provider, and as it provides dual interface you are able to use it from both scripting languages such as VBScript and JavaScript as well as C++. It has so many advantages such as high speed, low memory overhead and the most important one, ease of use.

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" \
no_namespace \
rename( "EOF", "adoEOF" )

If you mention above code you'll get that I renamed the EOF to 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

,

etc) -->

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):

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 :

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:

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, m_pConn. The 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:

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 _variant_t constant, 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 Recordset object.

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.

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 Query1stored procedure in our sample database using Parameter object.

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 :

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.

Comments

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 shokuie@hotmail.com.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

The application you have developed is fantastic and so it this posting with good explanation and examples.

As you use "Microsoft.Jet.OLEDB.4.0", I believe it will not work on 64 bit platforms. I believe jet databases are not supported on 64 bit platform. Can you tell me what changes would be required in order to make
this application run on 64 bit platfom.

Hi!
I use jet oledb 4.0 to access my db named as bla_bla.mdb.
but oledb command have an exception with this sql staring.
"SELECT SQRT(X1) FROM LINES"
namely do not run sqrt or square.
can anybody help me.

I'm using Visual C++ and can't get the database columns to bind to the DataGrid columns. It will show the correct number of rows but the cells are empty. Any suggestions or comments are greatly appreciated.

Hello,
Using the code you have provided above. I would like to merely add a column to the student table. I see how you change the data in student. I adapted the code so that I can also chang Dept, which was quite simple. But I am a bit of a beginner using ADO. Thanks

This message is a natural product made from recycled electrons. The slight variations in spelling and grammar enhance its individual character and
beauty and in no way are to be considered flaws or defects.

Just so you everyone knows. I would like to mention some of the things I learned. First, in the code I downloaded. I came across 3 errors.

1. "cannot open include file 'res\ADOTest.rc2'" I canged all the lines that read res\... and deleted the res\.

2. The was no ms5ado1.dll command. well first of all it should be msado15.dll. Next, it was not in the zip file that I downloaded. Just do a search for the file and you will find it. Once you find it just copy it to the file where you have everything stored.

As of right now I cannot insert anything into the database, I can only Query and update existing entries.

This message is a natural product made from recycled electrons. The slight variations in spelling and grammar enhance its individual character and
beauty and in no way are to be considered flaws or defects.

Hi!
I have the following problem:
Reading strings from my database is working when I use static_cast(_bstr_t(RS->Fields->GetItem(L"Description")->GetValue().bstrVal)) but reading a field with the data type "long integer" won't work

I tried (long)RS->Fields->GetItem(L"Beschreibung")->GetValue() but this always returns 0

I have a problem with my program locking up on access to the database on a quick second run of the program whenever I have updated the database in the first run. This behaviour is not present in the sample here, and I wonder what I am missing out on. I do the connection to the DB in exactly the same way as in the sample, and whenever I want to read or update the DB I use the the recordset Open from the OnQuery method above... It works fine to update the DB but on a quick second run the program hangs on accessing the DB. Wouldn't the locks be unlocked whenever I close the recordset and/or the connection. Please help, it's really annoying.

When I have quotes in one of my SQL statements (like INSERT INTO bla (v1,v2) VALUES("bla", "<html><body bgcolor="#ffffff">")) I get an error.
I've tried to do it the php-way (replace quotes thru \") but that didn't work either.

I'm trying to save a mp3 files in a table in access file.
SO I'm using ADO. I can save and read data in access file if it is of the tipes (Long , String, BOOL .) But I don't know how to Read & Write BIG array.
I think that I should use VARIANT but I did not know how!

Hi:
I'm also encounter this problem, i want to read data from oracle database, but data is using binary(BCD code) format stored. so when read it from database table,when the bit of the data is zero, then use ADO Recordset function such as GetString or GetItem, those function were truncation the got string.

See MSDN Library article "Implementing ADO with Various Development Languages"
This is a rather long article, but somewhere it says:

8
#import rename ( "EOF", "adoEOF" )

This technique is the more modern approach and requires that you already added the path "c:\program files\common files\system\ado" to one of your PATH, INCLUDE, or LIB environment variables (assuming the operating system has been installed on the C: drive). Or you can add the path to the options in Visual C++, as demonstrated in the section below, "Creating an ADO Project with #import."
-------------------------->8

Hello every body, I just compiled that tutorial but i have the following error!
Compiling...
StdAfx.cpp
c:\Alice\code\ado\adotest\stdafx.h(25) : fatal error C1083: Cannot open type library file: 'msado15.dll': No such file or directory
Error executing cl.exe.

My problem is that I get an error when I try to get data from an empty field in a record. In my table I have FirstName, LastName and PhoneNumber. It's not necessary to put a value into PhoneNumber. So when I try to get the data, I have "invalide data type" error. I supposed that all my data was CString type.

You can do it another way. Just check for NULL before you extract the information.
Here is an example
_variant_t TheValue;
CString tempString;
lpRecset is the recordset that you have got
if(!lpRecSet->adoEOF)
{
TheValue =lpRecSet->GetCollect("Phone_Number");
if(TheValue.vt!=VT_NULL)
{
tempString.Empty();
tempString = (char*)_bstr_t(TheValue);
}
}

if it is not null extract if null then u can set it to a default value.

I have the same problem. I have a stored procedure with only one output parameter. I tried all I can to try to get it's value. but all I got is com errs. it runs well if I use SQL query analyzer.

I tried to use Refresh() and also tried pCommand->CreateParameter(), but all the errors I got are:
1. parameter not supplied.
2. ADODB.Recordset, Operation is not allowed when the object is closed (actually, I closed the Recordset obj at the end of the function).
3. if I try pCommand->Parameters->Item[1L]->Value; I only got NULL value.

Hi!
Does anyone know how I can open and update the Windows Address Book database using C/C++ ? Do I have to use the WAB functions listed in the MSDN library or can I use the normal ADO commands?
Thanks!

however, i can only display one result, for example, in the above example, i want to get the duration>50 and duration<100 and service_id = 'email'
if i want to get the duration>50 and duration<100 and service_id = 'ecard' and display in the same row different column
the result is only have the ecard but no eamil
how can i solve this problem
thx