ADO for the Beginner

So I was speaking to my two friends the other day, Don Pedro and Balthasar, and they were rambling on about something:

Don Pedro: Nay Pray thee come, or if thou wilt hold longer argument, do it in notes.

Balthasar: Note this before my notes: there's not a note of mine that's worth the noting.

Don Pedro: Why, these are very crotchets that he speaks -- note notes forsooth, and nothing!

Me: Uhh what?

Yeah those guys know how to party. And while they are all a part of the whole Ado scene, it isn't quite the one I am writing about here today.

In this tutorial, I will discussing the vainglorious usage of ADO or ActiveX Data Objects.

What is ADO

ADO, as shown above stands for ActiveX Data Objects. They are a set of objects used to access data sources. It is a successor to RDO (Remote Data Objects) and DAO (Data Access Objects).

Before we begin learning ADO, I am going to assume a few things:

• You know HTML

• You are familiar with ASP

• You know some SQL

You don't have to be a professor in those subjects, just have a working knowledge or a basic understanding of them.

Now that all of that is out of the way, let's learn how to work with this bad mama jama.

Connecting to a Database

Before you can work with the data inside of a database you must first connect to a database. There several ways to do this:

How to Create a DSN-free Connection

Of the two ways to connect to a database, the DNS-free way is probably the easiest. It can be used to connect to any Microsoft Access database. For our example let's say you have a database named ChuckNorrisVictims.mdb that is located on a web directory such as "  c:/webinfo/  ". To connect to the ChuckNorrisVictims database, you could use the following code:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/webinfo/ChuckNorrisVictims.mdb  "

%>

As you can see from the above code, you must include the database driver (Microsoft.Jet) and the path to your database.

Connect to a Database with ODBC

Using our friend the ODBC database connection, you can connect to any database that is located on your network (providing of course that there is an ODBC connection to use). Here is how that would look in ASP:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Open "chucknorrisvicims"

$>

Connect to a Microsoft Access Database Using ODBC

So you want to connect to a Microsoft Access database? Well you can do that with ODBC. First, you must make sure to configure your ODBC connection on the same computer that holds your web site. In other words, if your web site is located on a remote server, your web host will need to be contacted to do this for you. If you are using Personal Web Server or Internet Information Server on a computer you have access to, the following will allow you to set up the connection yourself:

• Double Click the ODBC icon (it should be located in your Control Panel).

• Find the System DSN tab and click on it.

• Click the Add button under the System DSN tab.

• Click on the Microsoft Access Driver and click the Finish button.

• When the next screen appears, click the Select button to locate your database.

• Choose a DSN (Data Source Name).

• Click the OK button.

Working with Table Recordsets

Now that we have created a database connection, we can move on to creating a table recordset.

If we use our ChuckNorrisVictims database as an example, we can access a table in it using the following method. Let's say there is a table called Victims, holding a list of everyone Chuck Norris has brutalized yet allowed to live.

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "

set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Victims", conn

%>

How to Create an Add SQL Recordset

Another method of accessing our Victims table is to use SQL, like so:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "

set rs=Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Victims", conn

%>

In the above code sample we use a SQL statement to retrieve a list of data from our Victims table in the ChuckNorrisVictims database.

How to Retrieve Data from a Recordset

Let's say we want to extract some data from our Victims table. Now that we have opened the table, we can do so:

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "

set rs.Server.CreateObject(ADODB.recordset")

rs.Open "Select * from Victims", conn

for each x ni rs.fields

response.write(x.name)

response.write(" = ")

response.write(x.value)

next

%>

This will return all of the data in the Victims table.

Now that have retrieved the data, let's display it. We can do this in several different ways.

How to Display Data

To display your data, do the following (remember to use the asp extension when naming your file):

<html>

<body><%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/website/chucknorrisvictims.mdb  "

set rs =Server.CreateObject("ADODB.recordset")

rs.Open "SELECT * FROM Victims", conn

do until rs.EOF

for each x in rs.Fields

Response.Write(x.name)

Response.Write(" = ")

Response.Write(x.value & "<br />")

next

Response.Write("<br />")

rs.MoveNext

loop

rs.close

conn.close

%>

</body>

</html>

The above code creates a connection to the database, opens the chucknorrisvictims.mdb, and selects all of the data from it. It then creates a loop that runs through the file, writing all of the data inside and inserting a page break in between each set of data. It continues looping until it reaches the end of the data file (EOF). Finally it closes the connection to the database. Here is a sample of what you would see (using theoretical data; I don't really have a list of Chuck Norris's victims. There is no database that could contain such a massive amount of data).

VictimName: Ralph Macchio

VictimInjury: Broken foot

HowItHappened: Ralph Macchio tried his crane kick on Chuck. When it connected to Chuck's powerful chest hairs, his foot shattered.

VictimName: Jackie Chan

VictimInjury: Forced to work with Chris Tucker the rest of his life

HowItHappened: Chuck Norris punched Jackie Chan so hard he went from an A-list actor to a B-list actor

VictimName: Death

VictimInjury: Granted eternal life

HowItHappened: Death came for one of Chuck's victims before he was finished with him, so Chuck did a roundhouse kick and hit Death so hard not only did he become a living being, but he gained immortality as well.

And so forth.

As you can see it isn't the prettiest data in the world. To give it more uniformity and make it easier on the eyes we could always place the data into an HTML table instead, like so:

<html>

<body><%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "  c:/webdata/chucknorrisvictims.mdb  "

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT VictimName, VictimInjury, HowItHappened FROM Victims", conn

%>

<table border="1" width="100%">

<%do until rs.EOF%>

<tr>

<%for each x in rs.Fields%>

<td><%Response.Write(x.value)%></td>

<%next

rs.MoveNext%>

</tr>

<%loop

rs.close

conn.close

%>

</table>

</body>

</html>

This works the same way our previous sample did, except now it places the data in a table (it also selects three columns from the table and not technically the whole table).

Much better, but you will notice that there are no column headers. We are going to remedy that with the following code:

<html>

<body>

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

sql="SELECT VictimName, VictimInjury, HowItHappened FROM Victims"

rs.Open sql, conn

%>

<table border="1" width="100%" bgcolor="BLUE">

<tr>

<%for each x in rs.Fields

response.write("<th align='left' bgcolor='Yellow'>" & x.name & "</th>")

next%>

</tr>

<%do until rs.EOF%>

<tr>

<%for each x in rs.Fields%>

<td><%Response.Write(x.value)%></td>

<%next

rs.MoveNext%>

</tr>

<%loop

rs.close

conn.close

%>

</table>

</body>

</html>

Our table will now appear this way:

As you can see we also added color to the table. Viola!

That's it for this tutorial. In our next article we will discuss working with queries in ADO.

Till then...