A Beginner's Guide to Data Access Using ADO and ASP
The primary aim of this tutorial is to demonstrate how to execute an SQL command within an Active Server Page and display the results in an HTML table. This will involve using ActiveX Data Objects (ADO) to establish a connection to a data source (Connection) and to manipulate the data contained within it (Recordset).
It is assumed that the reader has some familiarity with creating ASP pages, as well as with constructing SQL statements.
To complete this tutorial you will need the example database. This is a very simple Access database containing a single 'Company' table.
The zip file below contains this database in versions for Access 97 and Access 2000. It also contains a copy of this tutorial and a ready-to-run ASP page that shows the finished working example.
(Note: If you are viewing this tutorial from a downloaded zip file, this link will not work. The example databases are already included in the zip file.)
Download ADO tutorial zip file
Before we can access our database from an ASP page we need to set up an ODBC data source. To do this we need to know the location of the database on your machine. For the purposes of this tutorial, we'll assume that it is located in the root of your 'c:' drive. Go to 'Start/Settings/Control Panel' and select 'ODBC Data Sources' and then the 'System DSN' tab. Click the add button and you will then be prompted to choose a driver type.
Pick 'Microsoft Access Driver' and then click 'Finish'. You'll now be asked to name your data source and to select the database that you want to use.
As you can see, we have named the Data Source 'dbADOtut' which is also the name of the database file itself. Click select and find the database on your machine ('c:/dbADOtut.mdb' in our example) and then click 'OK'. You will now see your newly created System Data Source listed in the 'ODBC Data Source Administrator' window.
Click 'OK' and we're ready to start adding database functionality to your ASP page.
We'll accomplish this in four easy steps.
Step 1: Open a connection to the data source.
First, we need to create a Connection object and use that object to establish a connection to our data source. To create the Connection object, we use the CreateObject method of the Server object:
<% Set cnnDB = Server.CreateObject("ADODB.Connection") %>
Then we simply invoke the Open method of our Connection object, passing the name we chose for the connection when we set up the ODBC resource:
<% cnnDB.Open "dbADOtut" %>
If the Data Source requires username/password authentication (our example does not), you can pass that information as additional parameters to the Open method:
<% cnnDB.Open "dbADOtut", "user_name", "password" %>
Step 2: Execute an SQL command.
Next, we specify our SQL statement and execute it. We assign our statement to a variable as shown:
<% strQuery = "SELECT * FROM Company" %>
If the query is a "SELECT", the Execute method returns a Recordset object that contains the data, so we'll need to capture that Recordset:
<% Set rsInfo = cnnDB.Execute(strQuery) %>
Otherwise (if we're doing an UPDATE, INSERT, or DELETE), we just run the Execute method:
<% cnnDB.Execute("DELETE FROM Company WHERE " &_ "Company.CEO=
'Bill Gates'") %>
It should be noted that this technique (using the Execute method of the Connection object) returns a read-only, forward-only Recordset. In most instances, this is all you'll need (and it's the efficient way to do so).
Building on our example, let's use our "Company" table that contains the fields "Name", "Address", and "CEO". To retrieve all the records, we would have:
<%
strQuery = "SELECT * FROM Company ORDER BY Name"
Set rsInfo = cnnDB.Execute(strQuery)
%>
If we simply needed to remove a company by name, we would have:
<%
strQuery = "DELETE FROM Company WHERE " &_
"Company.Name='Netscape'"
cnnDB.Execute(strQuery)
%>
Step 3: Retrieve the information, if applicable.
If our SQL command is a SELECT statement, then we'll need to access the data we've selected. The Execute method provides a forward-only Recordset object, so we can use a while-loop based on the EOF property.
The EOF property can be thought of as a blank record that is automatically appended to the end of every recordset. It contains no fields or data; it's simply used to inform your code that it has reached the end of the records contained in the recordset.
<%
Do While Not rsInfo.EOF
'do something with the current record.
Response.Write(rsInfo("Name"))
rsInfo.MoveNext
Loop
%>
Initially, the Recordset object references the first row of data that resulted from our SQL command. To move to the next row, we call the MoveNext method. When the MoveNext method advances past the last row of data, the EOF condition becomes True, which breaks us out of the Do-While loop.
Continuing our example, we could tabulate the data from the "Company" table:
<TABLE>
<TR>
<TH>
Name
</TH>
<TH>
Address
</TH>
<TH>
CEO
</TH>
</TR>
<% Do While Not rsInfo.EOF %>
<TR>
<TD>
<% =rsInfo("Name") %>
</TD>
<TD>
<% =rsInfo("Address") %>
</TD>
<TD>
<% =rsInfo("CEO") %>
</TD>
</TR>
<%
rsInfo.MoveNext
Loop
%>
</TABLE>
You will need to type the url "http://localhost/adoTut.asp" into the browser, and not just double-click the asp file to view it. If you do double-click the asp file icon it will not be executed, but instead the system will attempt to open it in edit mode.
The data will be displayed in the browser window as shown below.
Step 4: Close the objects.
Finally, being conscientious programmers, we invoke the Close method on both objects and nullify any references to them:
<%
rsInfo.Close
Set rsInfo = Nothing
cnnDB.Close
Set cnnDB = Nothing
%>
That's it! Although this is a simple example it should give you a taste of what is possible using these technologies. Welcome to the world of database-enabled web applications. Experiment, have fun and, above all, check out devguru.com regularly for more hot hints, tips and tutorials.