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.

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.

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.

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.

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.

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'

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.

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.

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

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

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

Here's the code in Full.

Site developed by Michael Wall - Web Design Belfast N.Ireland.
Copyright © 2000-2008. All rights reserved.