Here you are, the long awaited tutorial on how to use databases in Active server pages using ADO. First of all this tutorial assumes you already have some working knowledge of Microsoft Access and VBScript. If you want a database primer, click here.

Now, firstly, lets go over the objects. In ASP people use ADO (ActiveX database objects to get data from database. ADODB is comprised of 3 main objects: Connection,RecordSet,Command. This tutorial will discuss the first two.

The ADODB.Connection object opens up an ODBC or OLEDB connection to a database through database drivers so you can do something with the database. You use it by first creating an instance of the object:

<%
Set objConn = Server.CreateObject("ADODB.Connection")
%>

When you are finished up with the connection it is a good idea to clean up:

<%
objConn.close
set objConn = Nothing
%>

Now objconn is just a reference to the connection, so really it can be called anything, but the ususal name is conn or objConn. The next step is to open up a database. You can do this by either using a Data Source Name(DSN) or a connection string. Most commonly developors use a connection string because a DSN is requires setup by the administrator. A DSN is really just a shortcut name for a connection string. But with a connection string, you can move your database around without having to change the DSN.

There are two connection strings. One uses ODBC the other uses OLEDB. We will use OLEDB since it is faster an simpler. An example:

<%
Dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
              Server.MapPath("mydata.mdb")
%>

Pretty simple huh, all is says is the driver to use and then the path to the mydata.mdb database. It uses the Server.MapPath() function to get the physical path. (i.e. c:\w3svc\mypage\mydata.mdb\). When using a password protected database, do this:

<%
Dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
              Server.MapPath("mydata.mdb") & "PWD=yourpass"
%>

If you want to use a DSN, simply do this:

<%
Dim strConnect
strConnect = "DSN=mydsn"
'if you are using a username and password for it
'strConnect="DSN=mydsn;UID=username;PWD=password
'and place your username and password in their places
%>

And finally, you have to use the connection string to open up a connection to the database by using the open property:

<%
Dim strConnect
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="& _
              Server.MapPath("mydata.mdb") & "PWD=yourpass"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open strConnect
%>

To retrieve information from a database without using RecordSet's, you can use the connection member object execute(). Execute() executes a SQL(said "sequel") statement. If you don't know how to form a SQL statement, go here. Now here's and example of using the execute() function.

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open strConnect
Set rs = objConn.execute("SELECT * FROM myTbl;")
%>

Now you can use a WHILE loop to go though the records and print them out.

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open strConnect
Set rs = objConn.execute("SELECT * FROM myTbl;")
DO WHILE NOT rs.EOF
    Response.Write "NAME:" & rs(name) & "<BR>"
    Response.Write "PHONE: & rs(phone) & "<BR>"
LOOP
%>

Which woulld produce something like this:

Name: John Doe
Phone: 215-555-1212
Name: Jane Doe
Phone: 610-555-1212
...
...

Or if just want to delete,insert,or modify records you don't neet to create rs. You can just do this

<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open strConnect
'delete all records where the is = 1
objConn.execute("DELETE * FROM myTbl WHERE ((id=1));")
%>

Lets move on to the RecordSet object >>