ASP Guest Book - Connecting a Database

Now that the database is created and test data entered we can get on with creating the web page to display the data from the database.

First we need to start web page, open up your favorite text editor and type the following HTML.

<html>
<head>
<title>My First ASP Page</title>
</head>
<body bgcolor="white" text="black">

Next we can begin writing the ASP to connect to the database. First we need to create the variables that we are going to use in the script.

<%
' Dimension variables
Dim adoCon         'Holds the Database Connection Object
Dim rsGuestbook   'Holds the recordset for the records in the database
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 you'll see 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 front of the name of the database. This is used to get the physical path of 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 DSN connection to the database then you will need to replace the line above with the one below.

' 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.

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

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

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

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
rsGuestbook.Open strSQL, adoCon

Using a 'Do While' loop we can loop through the recordset returned by the database while the recordset is not at the end of file (EOF). The 'Response.Write' method is used to output the recordset to the web page. The 'MoveNext' method of the recordset object is used to move to the next record in the recordset before looping back round to display the next record. The lines in green are comments and have no effect on the code. To enter a comment in your code place a single quote ( ' ) in front of the comment.

' Loop through the recordset

Do While not rsGuestbook.EOF

     ' Write the HTML to display the current record in the recordset
     Response.Write ("<br>")
     Response.Write (rsGuestbook("Name"))
     Response.Write ("<br>")
     Response.Write (rsGuestbook("Comments"))
     Response.Write ("<br>")

' Move to the next record in the recordset
rsGuestbook.MoveNext

Loop

And finally we need to close the recordset, reset the server objects, close the server side scripting tag, and close the html tags.

' Reset server objects
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>

</body>
</html>

Now call the file you have created 'guestbook.asp' and save it in the same directory folder as the database. Don't forget the '.asp' extension.

Main Page | Hosting Plans | E-Commerce | Sign Up Now! | Site Map

asp web hosting by HyperlinkHost.com