Topic: ASP

End If
%>

OLE DB Connections
The newer, and preferred, method of connecting to a database is through OLE DB (Object Linking and Embedding, DataBase) technologies. These methods provide exposure to virtually any type of stored data, not just databases, and are the evolving technologies for universal data access. In the case of database access, OLE DB is an intermediate component which, in turn, interfaces with the appropriate ODBC driver.

The OLE DB connection string supplies the same two pieces of information as does the ODBC connection string: (1) the location of the database and (2) the type of database driver used to access it. In this case the Data Source parameter supplies the location and the Provider parameter supplies the driver:

ConnectionObject.Open "DATA SOURCE=path;PROVIDER=Microsoft.Jet.OLEDB.4.0"

The database path is the physical server path to the named database and "Microsoft.Jet.OLEDB.4.0" references the OLE DB component for linking to the ODBC driver for Access databases. You should probably get in the habit of using this newer format, although the examples in this tutorial use the older form of connection string.


Connection Strings for Other Data Sources

As a point of reference the following tables show the connection strings used to access other representative databases and data stores. In all cases the information in the data store is exposed to the script as a set of rows and columns.

Microsoft Access
Data Source=database path;Provider=Microsoft.Jet.OLEDB.4.0

Microsoft SQL Server
Data Source=database path;Provider=SQLOLEDB.1

Oracle
Data Source=database path;Provider=MSDAORA.1

Recordset
The ASP Recordset Object contains the properties and methods necessary to extract data from a database table and to make that set of records available to a script. Normally, you need to create as many Recordset Objects as there are tables being accessed. In the present example, we are accessing the single Accounts table from Database.mdb. Therefore, we need a single Recordset Object for our script.

Creating a Recordset Object

The general format for creating (instantiating) a Recordset Object is similar to the method used to create a Connection Object,

Set RecordsetObject = Server.CreateObject("ADODB.Recordset")

where, RecordsetObject is a name assigned to the object and by which it can be referenced in our script. The Server Object uses its CreateObject method to create an ADODB (Active Data Object DataBase) Recordset Object and assigns it to the name provided. For this example, we'll use RSObj as the name of our object and add the necessary Set statement to our script:

Adding data to database

Adding Data to the Guestbook Database
Now we've got the form, to input the data through, out of the way we can make the the page that does all the work, adding the data to the database.

This page contains no HTML so we can start writing the asp straight away, still don't forget the server side script tags, <% .... %>.

First we need to dimension the variables used in the script, so open your favourite text editor and enter the following code.

<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsAddComments 'Holds the recordset for the new record to be added
Dim strSQL 'Holds the SQL query to query the database

Next we need to create a database connection object on the server using the ADO Database connection object.

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

Now we need to open a connection to the database. There are a couple of ways of doing this either by using a system DSN or a DSN-less connection. First I am going to show you how to make a DSN-less connection as this is faster and simpler to set up than a DSN connection.

To create a DSN-less connection to an Access database we need tell the connection object we created above to open the database by telling the connection object to use the 'Microsoft Access Driver' to open the database 'guestbook.mdb'.

You'll notice the ASP method 'Server.MapPath' in font of the name of the database. This is used as we need to get the physical path to the database. Server.MapPath returns the physical path to the script, e.g. 'c:\website\', as long as the database is in the same folder as the script it now has the physical path to the database and the database name.

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook.mdb")

If on the other hand you want to use a slower DSN connection to the database then you will need to replace the line above with the one below. Also if you don't know how to setup a system DSN you will need to read my tutorial on, Setting up a System DSN.

'Set an active connection to the Connection object using DSN connection
adoCon.Open "DSN=guestbook"

Next create an ADO recordset object which will hold the records from the database and the new record to be added to the database.

'Create an ADO recordset object
Set rsAddComments = Server.CreateObject("ADODB.Recordset")

To query a database we need to use SQL (Structured Query Language). In the next line we initialise the variable 'strSQL' with an SQL query to read in the fields 'Name' and 'Comments' form the 'tblComments' table.

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT tblComments.Name, tblComments.Comments FROM tblComments;"

Set the cursor type we are using to adLockOptomistic so we can move through the recrord set. The integer value for this is 2.

'Set the cursor type we are using so we can navigate through the recordset
rsAddComments.CursorType = 2

Because we are going to be saving an updated recordset back to the database we need to set the LockType of the recordset to 'adoLockOptimistic' so that the recordset is locked, but only when it is updated. The integer value for this lock type is 3.

'Set the lock type so that the record is locked by ADO when it is updated
rsAddComments.LockType = 3

Now we can open the recordset and run the SQL query on the database returning the results of the query to the recordset.
'Open the recordset with the SQL query
rsAddComments.Open strSQL, adoCon

Once the recordset is open we can add a new record onto the end of the recordset. In the next line we let the recordset know we are adding a new record to it.

'Tell the recordset we are adding a new record to it
rsAddComments.AddNew

Now we can add a new record to the recordset. The details taken from the form we created at the start of this tutorial are entered into there relevant fields in the recordset. To get the data entered by the user from the form we use the 'Form' method of the ASP 'Request' object to request the data entered into the text boxes, 'name' and 'comments'.

'Add a new record to the recordset
rsAddComments.Fields("Name") = Request.Form("name")
rsAddComments.Fields("Comments") = Request.Form("comments")

The data has been entered into the recordset we can save the recordset to the database using the 'Update' method of the recordse object.

'Write the updated recordset to the database
rsAddComments.Update

We have finished using the database in this script so we can now close the recordset and reset the server objects.

'Reset server objects
rsAddComments.Close
Set rsAddComments = Nothing
Set adoCon = Nothing

Now that the database is updated we are going to use the 'Redirect' method of the ASP response object to redirect to the 'guestbook.asp' page we created in the first database tutorial, Connecting to an Access Database, so we can display the updated database. Note that if you are going to use the 'Response.Redirect' method you must remember to redirect before any HTML is written.

'Redirect to the guestbook.asp page
Response.Redirect "guestbook.asp"
%>

Now call the file 'add_to_guestbook.asp' and save it to the same directory as the database and the 'guestbook.asp' page, don't forget the '.asp' extension.