Datacheck Solutions :: ASP

Tutorials
Back

How to connect to a database using AD0

How to connect to a database using AD0
The question is how can an ASP file connect to a particular Microsoft Access database and retrieve the necessary records or edit or delete those records. Well the answer to our problem is Microsoft's ADO (ActiveX Data Objects) technology. ADO offers us two objects namely the Connection and the Recordset object that will allow us to do this.

To make a connection to our database we must first create an instance (create our own copy of the object) of the connection object and create a variable that will hold the object.

<% 
Dim Connection    'Here we declare our variable that will hold our new object
Set Connection=Server.CreateObject("ADODB.Connection")
%>

Our Variable 'Connection' above could be called anything. It's not uncommon to see this called oConnection or objConnection, objConn , with the 'o' and 'obj' being part of the programmer's naming convention.
Now that we have created our connection object we need to feed it more information i.e. the location/path of the database we wish to connect to and the driver that we intend to use. To feed this information we can either use a connection string or a DSN (Data Source Name). In this example and the majority of our examples we will work with a connection string rather than a DSN though we do have tutorials on connecting to a database with a DSN.

So now that you have decided on using a connection string we have another choice to either use an ODBC or an OLEDB connection string. (Don't worry about the terms) We'll choose OLEDB for our example as it's faster and more stable.

<% 
Dim sConnString  'Here we declare our variable that will hold the connection string
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("ourdatabase.mdb")
%>

NOTE: If aren't sure about the physical path to your database you can use the Server.MapPath Function which will map the path from the webserver's root folder to your database as we have done above. If we knew that the database was located C:\wwwroot\inetpub\myfolder\ourdatabase.mdb then the code below would work equally as well as the code below.

<% 
Dim sConnString  'Here we declare our variable that will hold the connection string
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\wwwroot\inetpub\myfolder\ourdatabase.mdb"
%>

What we have done in the code above is specified the database driver (OLEDB) and the location of the database and assigned it to a variable 'sConnString'.
Finally we have to open our connection open and use our connection string.

<% 
Connection.Open sConnString
%>

That's it, now we have an active connection to our database. Though it's not doing any thing exciting! Now it's the turn of the recordset object. The recordset object allows us to retrieve records.

<% 
Dim Recordset    'Here we declare our variable that will hold our new object
Set Recordset=Server.CreateObject("ADODB.Recordset")
%>

The code above created an instance of a recordset though it won't actually achieve anything, we need to feed it an SQL statement. So now let's create an SQL statement and assign it to a variable 'SQL'

<% 
Dim SQL    'Here we declare our variable that will hold the SQL statement
SQL="SELECT * FROM TABLENAME"
%>

Now we need to open the recordset object and tell it to execute our SQL statement and use the active connection to our database that we have created.

<% 
Recordset.Open SQL, Connection
%>

We have returned a recordset based on our SQL statement (ie all the records) so let's now do something with those records. The easiest thing to demonstrate is printing out those records.
For example sake lets say our database table name is called 'TableName' and has 2 fields 'Name' and 'LastName'. We could run a DO WHILE LOOP as below and print out all the records in the browser.

<% 
Do While NOT Recordset.Eof   'i.e. carry on looping through while there are records
Response.write Recordset("Name")
Response.write Recordset("LastName")
Response.write "<br>"    'include a line break
Recordset.MoveNext     'move on to the next record
Loop 
%>

That's it, now we just need to close the objects and free up resources on our server.

<% 
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>

To sum up here's the steps that we have followed to access a database from inside an ASP page:

We created an ADO connection to a database. 
We opened the database connection with a connection string. 
Next we created an ADO recordset and using our SQL statement and our active connection returned a recordset. 
We looped through those records as long as there were some and printed out the fields. 
Finally we closed the recordset and connection objects and freed up the server resources.

Here's the code in Full.

<% 
Dim Connection    'Here we declare our variable that will hold our new object
Set Connection=Server.CreateObject("ADODB.Connection")

Dim sConnString  'Here we declare our variable that will hold the connection string
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("ourdatabase.mdb")

Connection.Open sConnString

Dim Recordset    'Here we declare our variable that will hold our new object
Set Recordset=Server.CreateObject("ADODB.Recordset")

Dim SQL    'Here we declare our variable that will hold the SQL statement
SQL="SELECT * FROM TABLENAME"

Recordset.Open SQL, Connection

Do While NOT Recordset.Eof   'i.e. carry on looping through while there are records
Response.write Recordset("Name")
Response.write Recordset("LastName")
Response.write "<br>"    'include a line break
Recordset.MoveNext     'move on to the next record
Loop

Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>