Finance Developer (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

So, you now know your SQL, you've got an idea for a great data-driven ASP page, and you're all set to go. Now it's time to communicate with the database using ADO and ASP. (Remember, ADO stands for ActiveX Data Objects, and is the object that allows ASP to connect to a database.)

Let's say that we have a database with a Products table, which has the following columns: ProductID, Name, and Price. We want to write an ASP page that will hit the database, get information for all products under 10 dollars, and print out the name and price.

Any time you want to access a database, you need to follow these simple steps:

1.) Connect to the database using ADODB.Connection
2.) Talk to the database using ADODB.Recordset

If you are thoroughly confused, worry not, for here comes an explanation! ADODB stands for ADO DataBase, and is the name of the object needed in ASP to connect to a database. Let's examine subpoint one first.

First, you need to connect to a database. To do this, you need to create an instance of ADODB.Connection, which is done as follows:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

You then need to specify the information needed to connect to a database. In most cases, this is the name of the System DSN you created for this database. To set the information, you need to set the ConnectionString property, which can be accomplished like so:

objConn.ConnectionString = "DSN=System DSN Name"

Finally, you just need to open the connection to the database using the Open method.

'Open a connection
objConn.Open

That's all, you've now completed subpoint one! For more detailed information, such as how to create a System DSN, and information on DSN-less connections, be sure to read Connecting to a Database!

OK, now onto subpoint two, using ADODB.Recordset to talk to the database you've opened in subpoint one. To do this, we first need to create an instance of the ADODB.Recordset object:

Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

I think it's best to then create a variable named strSQL or something of the like. This will be the SQL string that we will send to the database (if you're unfamiliar with SQL, the language of databases, be sure to read the previous FAQ). Since we want to get the name and price of all products under 10 dollars, our SQL string will look like:

SELECT Name, Price FROM Products WHERE Price < 10

Now, let's create a variable named strSQL and set it equal to our SQL string needed:

Dim strSQL
strSQL = "SELECT Name, Price FROM Products WHERE Price < 10"

Simple enough, eh? Now that we have our SQL string ready, it's time to tell the database what we want it to do. To do this, we need to use the Recordset's Open method, telling the recordset the SQL string we want to execute and what database we are wanting to execute it on.

objRS.Open strSQL, objConn

That's all there is to it. You just have to let the recordset object know what you want the database to do (the SQL string) and on what database to do it on (the name of the connection object, in our example, objConn).

It's best to think of a recordset object as a matrix. Let's say that the Products table contained the following information:

Since we are retrieving the name and price of items under 10 dollars, the recordset can be thought of as the following matrix:

The recordset object concerns itself with rows. When you execute the Open method of the recordset object, the recordset object is pointing to the frist row in the dataset returned. So, right now, objRS is pointing to Plate/5. To access the information, you need only issue the following command:

objRS("Name")

This will get the value of the currently pointed to Name. Since objRS is pointing to Plate/5, executing:

Response.Write objRS("Name")

Would print out: Plate. (To obtain the price, you would issue Response.Write objRS("Price").) OK, so you now want to move to the next row. This is done using the MoveNext method of the recordset object. If we issue:

objRS.MoveNext

objRS now points to the row Pen Set/3. When we move past the last row, the EOF property is set to True. So, we can loop through an entire recordset, printing out the results, as follows:

'Loop until we've hit the EOF (end of file)
Do Until objRS.EOF = True
   Response.Write "Name = " & objRS("Name")
   Response.Write "<BR>Price = " & objRS("Price")
   Response.Write "<P><HR><P>"

   'Move to the next record (important!!)
   objRS.MoveNext
Loop

That's it, the above code will print out all the contents of the query we performed. As a final request, you should always explicitly close and delete your recordset and connection objects. At the bottom of your ASP page, add the following lines:

'Close the Recordset object
objRS.Close

'Delete the Recordset Object
Set objRS = Nothing


'Close the Connection object
objConn.Close

'Delete the Connection Object
Set objConn = Nothing

Happy Programming!

FAQ Table of Contents

SQL, the Language of Databases

Common Database Problems

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article