The average surfer today demands personalized, customized, interactive Web pages that change content on user request. Learn how to fill that demand by using ADO to connect to any ODBC compliant database.
The advent of the internet has transformed the world into a global market. With more and more people hooking on to the internet the expectations of speed and efficiency are far increasing. Gone are the days when surfers browsed through static HTML pages. The average surfer today demands personalized web pages i.e. customized and interactive web pages that change content depending on user request. This requires the storage of non pertinent information which includes information on users and information required by them in databases and its delivery in customized modes. Dynamic content can be offered to users in numerous ways such as CGI with Perl, ASP, Servlets, ColdFusion, JSP, Livewire etc.
ActiveX Data Objects (ADO) with their
proprietary scripting language VBScript to offer a simple
yet powerful solution to the problem. ADO can be used to
connect to any ODBC compliant database. This is a positive
feature as most of the popular databases are ODBC compliant
so all we need is a ODBC driver for the corresponding Database.
ADO can also be used to connect and retrieve data from
spreadsheet files like Excel and text or other plain format
data files. ASP-ADO is a perfect solution for small and medium
sized web sites owing to its cost effectiveness and minimum
development cycle time.
ADO resides on top of the OLE DB which takes care of the medium
through which the database is to be connected. This leaves the
developer to just bother about how to use the ADO. Issues such
as multiple connections, reusable connections and performance
bottlenecks are automatically taken care of when ADO 2.0 is
used in conjunction with IIS 4.0.
(Click for full size image)
The ADO Interface
ADO is built upon another layer called the OLE-DB. ODBC is one
popular OLE-DB provider. ADO capabilities can vary depending
upon the OLE-DB provider. Ado Capabilities can also vary widely
depending upon the ODBC driver used. For example most ODBC
drivers do not support all the cursor types, (cursor types are
Exploring the ADO
The ADO Object model consists of seven objects namely:
The Connection, Command and Recordset objects each have a
collection of properties. The Connection object also maintains
a collection of Error objects detailing the errors we encounter
while making database connections. Each member of this collection
is a Field object which in turn contains information about
individual fields in the Recordset.
However the Connection, RecordSet and Command objects are the
most commonly used objects for interaction with databases. The
different objects are used depending on the complexity of the
The Connection object is used to establish a connection to a
database and can be used for simple database queries like select,
insert, delete, update etc. It can be also used for other complex
manipulations like stored procedures, but they are better done by
The RecordSet object is used for updating records, querying
multiple tables and fields. Unlike the Connection object, the
RecordSet gives more information on the transaction like the
number of records updated, records deleted etc.
The Command object is capable of running stored procedures
and SQL statements with multiple and complex parameters.
The Field object represents an individual field in the
RecordSet. It is generally used in conjunction with the
RecordSet object for more flexibility in programming.
The Parameter object represents a parameter in a SQL stored
The Error object represents the ADO errors.
The Property object provides properties of different data
providers like MS-Access, SQL Server etc.
Establishing a Connection
To make a connection to a database a data source (DSN)
corresponding to the database used is needed. The connection
object then uses this data source to connect to the database
and retrieve data. There are three types of data sources: User
DSN, System DSN and File DSN. The DSN provides connectivity to
the database through a specified ODBC driver. (An ODBC driver
corresponding to the database used is to be installed on the
File DSN is preferred over the other two as it stores the
data source information in a separate file which can be ported
to a different machine easily.
To open a connection to the database an instance of the
Connection object is required, then subsequent manipulations
can be done using the Connection object.
Click here for code example 1.
The first statement gets an instance of the Connection
object. The open method of the Connection object is used to
call the DSN. The location of the DSN could be any directory
on the server provided the full path is specified. If the
database requires authentication then the username and password
need to be supplied.
Click here for code example 2.
The File DSN may alternatively be placed in a session
variable so that a change in the path of the DSN is reflected
in all the pages without changing the code.
conn.Execute statement executes a SQL query; if the
query were a select statement that returned records then it
is assigned to a RecordSet object and subsequent manipulations
are done on it.
Click here for code example 3.
conn.Close closes the connection to the database. It
is a good practice to close database connections after use.
The Execute method has two optional parameters. The
RecordsAffected parameter returns the number of records
affected by the SQL query. The
Options parameter indicates
the type of SQL statement. The constant used with the
parameter helps the ADO in interpreting the database call and
executing it efficiently. The constants used are
specify that it is a textual command,
adCMDTable to indicate a
adCMDStoredProc to indicate its a stored procedure and
adCMDUnknown if the type is unknown.
AdCMDUnknown is taken as
default if this parameter is not specified.
Connection Object methods and Properties:
||Opens a new connection|
||Closes the connection|
||Executes a SQL query or a stored procedure|
||Begins a new transactions|
||Saves any changes made and ends the transaction|
||Cancels any changes made and ends the transaction|
||Provides information on database schema like tables, rows, columns etc.|
||Controls whether to begin a new transaction when a existing one ends|
||Time to wait when executing a command before terminating the attempt and returning the error|
||Information used to create a connection to the data source|
||Time to wait when making a connection before terminating the attempt.|
||Sets or returns the name of the provider.|
In the earlier example the data returned from a SQL query was
assigned to a RecordSet even though the RecordSet object was
not created. The RecordSet object was explicitly created when
the conn.Execute statement was executed. Alternatively a
RecordSet object can be explicitly initialized and the
additional flexibility of the RecordSet used.
A RecordSet contains a collection of records with the first
record as the current record.
Click for code example 4.
The RecordSet object has a few extra optional parameters to
help the programmer get more control over how the data is
accessible to different users and how data in the RecordSet
can be manipulated. The first parameter is the type of cursor
used. The cursor defines the types of manipulations that can
be done on an open RecordSet.
There are four types of cursors:
adOpenForwardOnly: This allows only forward movement in the RecordSet
adOpenKeyset: This allows both forward and backward movement in the RecordSet. It also reflects delete and update operations made on the records by other users
adOpenDynamic: This allows both forward and backward movement in the RecordSet. It reflects the operations done on the records by other users.
AdOpenStatic: This allows both forward and backward movement in the RecordSet. It does not reflect any operations done on the records by other users.
If omitted the
adOpenForwardOnly is taken as default cursor.
The other optional parameter is the locking type. The
locking type tells the database how to deal with situations
where more than one user tries to manipulate a record.
The options are:
adLockReadOnly: The records are read only.
adLockPessimistic: The records are locked immediately upon editing.
adLockOptimistic: The record is locked when the RecordSet's Update method is called.
adLockBatchOptimistic: The records are batch updated.
adLockReadOnly is taken as the default locking type.
The RecordSet object has a number of methods that help navigate through the RecordSet.
||Moves the specified number of records forward or backward|
||Moves to the first record|
||Moves to the next record|
||Moves to the previous record|
||Moves to the last record|
||Closes the recordset|
||Opens the cursor on the recordset|
BOF - Indicates that the current position is before the
EOF - Indicates that the position is after the last
Apart from the above methods and parameters the
RecordSet has many additional methods like AddNew to add
a new record, Update to save changes to a record, UpdateBatch
to save changes to all the records when the RecordSet is in the
batch-update mode and so on. Most of the other methods are for
Displaying the results:
The RecordSet object has a Fields Collection that
can be used to manipulate the columns of a table. E.g.
RS.Fields.Count gives the number of columns in the table.
The data in a particular column in a record can be accessed
Set RS =Server.CreateObject("ADODB.RecordSet")
conn.Open "FILEDSN= C:\myAspDir\testDsn.dsn","username","password"
RS.Open "SELECT * FROM MYTABLE WHERE MYRECORDNO>0", conn
<% FOR i=0 to RS.Fields.Count -1 %>
<TH><% = RS(i).Name%></TH>
<% WHILE NOT RS.EOF %>
<% FOR i=0 TO RS.Fields.Count - 1%>
<TD><% = RS(i) %></TD>
The Command Object
The Command object represents a command. The ResultSet
and the Connection objects execute SQL commands which are
command strings. Instead of the Command string a Command
object can be used. The Command string can be anything from
a simple SQL command to a complex stored procedure. But this
does not make the Command object anything special as the RecordSet
can do all this too. The Command object is best used for
complex stored procedures with in parameters and out parameters.
The Command object can be used to execute bulk operations and
manipulate the structure of the database.
Click here for code example 5.
Command object methods and properties:
The first step is to specify the ActiveConnection we want to
use as specified in line 3. Once we have established the active
connection we can use the Command object in the same way as the
Connection object. We don't need to explicitly open the data
source or close it after use, we can use the execute method
directly as in line 6.
The different properties of the Command object help us
specify some parameters outside the Execute method to get more
control over the operation; like in line 4 we have specified
the Command text and in line 5 we have indicated the Command
Type as a stored Procedure. We can also specify the
CommandTimeout property. This determines how long ADO has
to wait for the command to execute. The default value is 30.
If the value is set to 0 ADO will wait indefinitely until the
The command object has methods like CreateParameter which
creates a new parameter object in the Parameters collection and
an Execute method that runs the SQL statement or stored
procedure specified in the CommandText.
If a SQL query requires parameters then it can be supplied
by adding them to the Command objects' parameters collection or
by creating them on the fly as we execute the SQL query.
myCommand.Execute RecordsAffected, Parameters
An array of parameters can be passed to the Parameters object
while the query is being executed.
myCommand.Execute Array (parameter1, parameter2 etc...)
Instead of passing parameters on the fly we can hold the
parameters in the parameters collection. For this the parameter
collection has methods like Append, Delete, Refresh, and
properties like Count, Item etc. To add a new parameter an
instance of the Parameter is made and its property values are
set and finally the Append method of the Command object is
called to append the parameters to it like it is done in line
The parameters are not limited to stored procedures, they
can also be used in SQL statements in Access where parameters
are indicated by being placed in square brackets within the
statement. This can be used as an alternate for SQL statements
in which the 'where' clause changes dynamically.
We need to specify Type and Size for the parameters used in
the Parameter object. For String values it is the size and for
numeric values the precision needs to be specified.
There are two kinds of properties: Built-in properties
and Dynamic properties. The Built-in properties are those
defined by ADO for objects as they are created. These are part
of the object itself and can be used to set or get some property
to the object. These are available irrespective of the provider
The Dynamic properties are additional properties specifically
provided by the provider.
The Errors collection holds all of the Error objects for a
specific Connection. Generally, if an ADO operation encounters
errors, one or more Error objects are automatically added to
the Errors collection. More detailed error messages can be
obtained from these error objects instead of vague Runtime
errors. The Errors collection has a clear method to clear all
the errors. It also has two properties. The count property
returns the number of error objects in the collection and the
item property is used to retrieve the contents of the error
objects in the collection. Two types of error objects are
thrown depending on the type of error. Errors are returned
by the data source whereas Err objects are created at compile
time or runtime by VBScript or by an automation object like
Apart from critical errors that stop execution some properties
and methods return warnings to the Errors collection . Warnings
don't stop execution, but that could be due to small problems
like data conversion, which could affect the precision of the
The Error object has properties like Description for detailed
description , Source for the source of the error, Number for
the ADO error code number etc.
Click here for code example 6.
Ado is the easiest way to provide dynamic content on
the Internet. It is best suited for small and medium
sized web sites as the development time and costs are
minimum compared to other languages. Moreover its
default support on Windows NT makes it an ideal choice
for companies intending to do business on the net. ASP
and ADO seamlessly combine with other Microsoft tools
like the Siteserver, MTS and Commerce server making it
scaleable and robust.