ASP Lesson #5 - Using a Database

Creating an Access database

First we will create a database in Access called Employees.mdb. This will have a table called Employee and here is what it should look like:

Employee

EmpNo

FirstName

LastName

Insert the following values into the tables:

Employee

1	John	Smith
2	Mary	Jones
3	James	King

The Connection object

The first thing to do before you can use a database is create a Connection object.




Dim cn
Set cn = Server.CreateObject("ADODB.Connection")

Next set the ConnectionString property of the Connection and open the Connection.




cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("Employees.mdb")
cn.Open

The RecordSet object

We now need a RecordSet object to store the results of a query. You must create the RecordSet in a way similar to how you created a Connection.




Dim rs
Set rs = Server.CreateObject("ADODB.RecordSet")

Before you can go on with the next step you need to copy C:\Program Files\Common Files\System\ado\adovbs.inc to C:\Inetpub\wwwroot. Also add the following line after <%Option Explicit%>:




<!--#include file="adovbs.inc"-->

Now we can open the RecordSet like this:




rs.Open "SELECT * FROM Employee", cn, adOpenStatic, adLockOptimistic

The first parameter in quotes after rs.Open is a SQL statement that says all records from the Employee table must be retrieved. The second parameter is the connection. The Third parameter is the CursorType and the Fourth parameter is the LockType.

Reading from a RecordSet

Now that we have opened the RecordSet we can read values from it. We will use a loop to write the first name of each employee in the Employee table on the page.




While Not rs.EOF
   Response.Write rs("FirstName") & "<br>"
   rs.MoveNext
Wend

The Find command can be used to find a specific record.




rs.Find "LastName = 'King'"

Writing to a RecordSet

You can change the value of a field in the RecordSet by setting its value in the same way as you set the value of a variable. After you have set the value you have to use the Update command or the changes will not happen.




rs("FirstName") = "Mike"
rs.Update

Cleaning up

You must always close all Connections and RecordSets when you are finished using them. You must also set them equal to Nothing. If you do not then they will stay in the server's memory until it has not memory left.




rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing