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
2.) Talk to the database using
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
is done as follows:
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
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 a connection
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:
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:
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,
pointing to Plate/5. To access the information, you need only issue the
This will get the value of the currently pointed to Name. Since objRS is pointing to Plate/5, executing:
Would print out: Plate. (To obtain the price, you would issue
OK, so you now want to move to the next row. This is done using the
method of the recordset object. If we issue:
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")
'Move to the next record (important!!)
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
'Delete the Recordset Object
Set objRS = Nothing
'Close the Connection object
'Delete the Connection Object
Set objConn = Nothing