ADO is Our Friend

Chris Assenza

October 16, 2000

Microsoft's ActiveX Data Objects

Microsoft was both kind enough and wise enough to include a means of talking to various data sources by including ActiveX Data Objects (aka ADO) with ASP. ADO will allow the developer to connect to an ODBC data source (what you set up on the last page), and, using special methods specific to ADO, send SQL commands to the data source. We will not be taking an exhaustive look at ADO for this article, because that would take a few articles in and of itself, we are just going to use it so we can get the job done - which is all most of us probably care about. If you are the type of person with an insatiable hunger to be a technical mastermind, check out the links at the bottom of this page for all the untold details.

The ADO Object Model^*

There are a number of objects within the ADO model, they are:

Connection
Error
Command
Parameter
Recordset
Field

Each of these objects has a number of properties and methods - most of which we will not even take a look at. As a matter of fact, forget all about Error, Command, Parameter and Field - we will not need those for what we are going to do. Instead, let's start with Connection, the object needed to get our web page talking to the database.

Connection does exactly what you think it would do, establishes a connection to the database, via something called a Connection String. To start, as with any other object, you must invoke an instance of it. This act is a simple one and requires that you declare a variable and then create the object on the server, like so (assuming conn is the name of the declared variable): set conn = Server.CreateObject("ADODB.Connection"). Once you have invoked the object, it is important to set the necessary properties in order to successfully talk to your database. The most important one at this point is the Connection String.

A typical Connection String will consist of the DSN for the database, and possibly the username and password to the database (if you assigned them). The syntax is: ConnectionName.ConnectionString = "dsn=DSN You Assigned[;user=Username;password=Database Password]" (Note: Code inside brackets is optional, the brackets themselves are not part of the syntax).

The following block of code is what a completed Connection would look like for our example registration system. I will explain all the code line-by-line below: (Note: This structure is not exactly how it appears in the final version of this particular project - the declarations and instance creation are done earlier on at the top of the page - I have illustrated what I mean on a subsequent page).

dim conn, strSQL, rst
set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = "dsn=wdvl_test"
strSQL = "SELECT * FROM tbl_users;"
	set rst = Server.CreateObject("ADODB.Recordset")
	rst.Open strSQL, conn, 3, 3

Not bad, right? dim conn, strSQL, rst is code you should be familiar with by now, this line declares variables called conn, strSQL and rst as type Variant. conn will be used for our Connection object, strSQL will be used for a line of SQL (Structured Query Language), and rst is going to be used for a Recordset, which I will explain momentarily.

set conn = Server.CreateObject("ADODB.Connection") invokes an instance of the ADODB.Connection on the server, allowing the developer to establish a connection to the database, as outlined in a preceding paragraph.

conn.ConnectionString = "dsn=wdvl_test" sets the ConnectionString property equal to the DSN we setup for the database on the previous page. Since we did not add any security features to the database, the optional user and password parameters were dropped.

On the next page we will discuss the next few lines of code, starting with a very brief look at SQL.

ADO References
LearnASP.com: ADO Table of Contents
ASP101: ADO 2.0 Reference
15 Seconds: A Practical Introduction to ADO
MSDN: The ADO Object Model

Print:
QUE: Using ASP Special Edition * This book helped me with this article! Definitely worth a look! (And no, I do not work for QUE).
WROX: Beginning ASP Databases