create database on frontpage

Introduction

It is relatively simple to create a form, but where do you store the input? If you use a database, data can be retrieved for later calculation, tracking, and presentation as required interactively. Microsoft® FrontPage® offers multiple options to integrate a database in a Web site, including Microsoft ActiveX® Data Objects and the Database Interface Wizard. This article will provide intermediate ASP options you can use in a FrontPage Web site for easy database integration.

Dynamic Web pages let you retrieve current information, store data, or access people online—and they do so through the use of databases. Databases collect and store so many different types of information on the Web that the value they offer is often taken for granted. But how do you learn to use a database with a Web page?

Through the development of Active Server Pages (ASP) and the easy-to-use database wizards in Microsoft Access, Microsoft offers a set of tools that developers can quickly pick up to start creating their own dynamic data pages. Using the FrontPage 2002 Web site creation and management tool, an ASP developer can create an entirely new database from within FrontPage, use an existing database connection, or experiment with a sample database—all from a page template called the Database Interface Wizard. Although the Database Interface Wizard functionality will not be discussed in this article, see Resource Links at the end of the article for information about how to use the wizard.

This article will discuss ASP database integration options using Microsoft Visual Basic® Scripting Edition (VBScript), ActiveX Data Objects (ADO) methods to pull information from a database, and an example of how to display records from a database in Hypertext Markup Language (HTML) format. If you are working in the HTML view of FrontPage, using code from this article also will allow you to connect to a database and display data from it in your FrontPage ASP template.

A Little ASP, Logic, and SQL

Using Visual Basic Scripting Edition to handle your data means that the Web page does not have to be static. When you use VBScript logic to make a database connection, retrieve data, and display it in the Web page, this processing or load is transferred to the Web server. Because you are reducing load time and not using the Web browser, you gain interactivity while not sacrificing Web page speed.

When you are dealing with data in an ASP page, the logic behind VBScript saves data in a transient format, so that data manipulation may be performed (if necessary), before data is saved to a database. One of the easiest ways to connect to a database can now be done with an OLE DB connection. OLE DB sits between the Open Database Connectivity Layer (ODBC) layer and the application. With your ASP pages, ADO is the "application" that sits above OLE DB.

Note   The ADO concept, and its available methods, are discussed further when we are ready to write our database connection code. If any data manipulation needs to occur before data is stored in the database, VBScript logic can change the data accordingly before it reaches the database. For example, if someone submits a phone number through a form with dashes in it and phone numbers in your database do not contain dashes, VBScript logic can remove phone number dashes before saving that data to the database.
In an ASP environment, VBScript is not the only component used to get your data into the database. You also must use a simple database language called Structured Query Language (SQL). SQL is widely accepted by databases to understand how a developer wants to insert data or select data from a database. Here is an example of a SQL command (or statement) used to retrieve data from a Contacts table:

["SELECT * FROM Contacts"]

In ASP, SQL statements are formed a little differently. They are called after the ASP page has set up variables for the database connection and after the correct OLE DB drivers are in place for connecting to a database. Here is the same SQL example from above, as you would need to write it in VBScript in your ASP Web page:

[strSQL = "SELECT * FROM Contacts"]

As you can see, the SQL statement has been referenced with an ASP variable, instead of merely stated by itself. Using variables in VBScript allows a developer to reuse the values of prior commands to produce further results and allow programming code to be processed faster. Variables like the example above are considered objects, and using these and other objects makes ASP programming very powerful.

ActiveX Data Objects offer consistent, high-performance access to data you want to display in a Web page. ADO is now the only data interface tool you need to know for creating data access in your Web. Not only can you use ADO with VBScript, but it also works with other languages like Visual Basic, C++, and Microsoft Visual J++®.

This article will use two ADO method examples: Open and Close.

Setting Up a Template Layout

Before we begin coding, it's important to set up a template layout for data access. You can copy and reuse this layout as needed for other applications. Our template contains places to notate code version, author, date last modified and comments about the purpose and intent of the template itself (for example, "This template is to access data and display it in a tabular format"). Using a template layout for each ASP page you create is a good way to stay organized.

In FrontPage, create a new Web page and name it Index.asp. If you want to cut and paste the layout code below, make sure you are in the HTML view of your new Web page, in order to preserve page comments and database integration features.

Here is a template layout example:

<%@ LANGUAGE="VBScript" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<%
'Option Explicit
'--------------- WEB SITE: YOUR WEB SITE NAME ----------------
' File Name: index.asp
'
' Purpose: Demonstrate database access and Record Set Display
'
' Accesses Company Information in a table layout
'
' Arguments:
' Comments:
'
' Author: Mary Burk
'     Mess Enterprises
'     http://www.mess.net
'     Internet: mary@mess.net
'
' Date Created:
' 02/09/03
'
' Modification History:
'
'----------------------------------------------------------------------
'
'----------------------------------------------------------------------
' All your ASP preprocessing code goes here
'----------------------------------------------------------------------
'
'----------------------------------------------------------------------
<html>
<body>
</body>
</html>
'----------------------------------------------------------------------
' End HTML output
'----------------------------------------------------------------------
'
'----------------------------------------------------------------------
' All ASP post-processing code goes here, as well as sub routines and
  ' functions, if any are required.
'
'----------------------------------------------------------------------

Now we are ready to explore ADO methods and OLE DB examples to connect to our database.

Accessing Data Objects

Inside our Index.asp template, we need to declare a few variables, choose the proper OLE provider for the database we are connecting to, and then provide the path to where the database resides on our server. An OLE DB connection is also known as a "DSN-less" connection. This new code provides developers a way to specify the database they want to connect to without having to define a specific Data Source Name (this used to be required from a .dsn file that specified Data Source Names) It is no longer necessary to specify a Data Source Name or DSN. Instead, developers can simply specify the correct database program provider and the path to the database with an OLE DB connection string.

Here is what you need to declare as variables:

dim objConn     ' Our Connection Object
dim objRS       ' Our Recordset Object
dim strSQL      ' Our SQL string to access the database
dim strConnection    ' Our Connection Object string to access the database
dim i            ' Our counter variable to count the rows of the Recordset

Here is what the Connection Object string and Recordset Object string should look like:

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

Here is what a Connection Object string looks like to connect through OLE DB to an Access database. It uses the Open method (objConn.Open):

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" "Data Source="&
Server.MapPath("g:\Mess.mdb")

If you need to connect to a Microsoft SQL Server™ database, that connection string would look something like this:

objConn.ConnectionString = "Provider=SQLDB; Data Source=DatabaseSource;
  Initial Catalog=DatabaseName; User ID=UserID; Password=Password"
The Open method was demonstrated above. This Execute method is necessary to execute the SQL query we are making to the Access database:

<%
strSQL = "SELECT * FROM Contacts"
set objRS = objConn.Execute (strSQL)
if (objRS.BOF and objRS.EOF) then
   response.write "No records found"
   response.end
end if
%>

Other methods—such as AddNew for adding records to a database, Delete to delete database records, and Clear for removing Error objects—are discussed in further detail in documentation exploring OLE DB providers and the ADO API Reference set. This collection of examples and data access methods provides more examples for data access in additional languages. The reader is encouraged to try all the ADO connection method examples on this site and continue studying data manipulation methods available through ASP.

Displaying Results and Saving Data

Once we have established our data connection to the database, and checked to make sure our code will not return errors if no data is found, the last thing we need to do is write our data to a Web page using Visual Basic Scripting and HTML. The main snippet of HTML and VBScript we need to code into our FrontPage index.asp page is the loop structure. A loop will query the database repeatedly, until every record that satisfies our query is returned to the ASP page and displayed in our HTML table in successive table rows.

The trick to coding a Loop structure in a table is to write your HTML by rows. Your dynamic loop must run from <tr> to </tr>. If you write your loop code statement to only create new cells in your table (and not new rows), that is, if you only have your loop create new <td></td> tags, you will produce a loop in only one row. This construction of your loop could possibly grow the width of your table quite long, and make your table appear improperly formatted.

Here is how to structure your loop row to receive back all rows of data from the Access Contacts table:

<table border="1" cellpadding="2" cellspacing="1" width="400">
<%
response.write "<TR BGCOLOR='#CCCCCC'>"
For i = 0 to objRS.Fields.Count - 1
response.write "<TD>" & objRS.Fields(i).Name & "</TD>"
Next
response.write "</TR>"
strSQL = "SELECT * FROM Contacts"
set objRS = objConn.Execute (strSQL)
objRS.MoveFirst
Do While Not objRS.EOF
response.write "<TR>"
For i = 0 to objRS.Fields.Count - 1
response.write "<TD>" & objRS.Fields(i) & "</TD>"
Next
response.write "</TR>"
objRS.MoveNext
Loop
%>
</table>

Remember to query your database recordset, and close your database connection using the ADO Close method. Why is that important? One reason a good developer wants to do this is to save computer resources. Database connections that are not closed when they finish executing can disrupt other page loads in your site. There also can be a negative effect on the database if other programs are not able to access the database because it is still open somewhere else. Open database connections can even crash your entire Web site, if left open and running too long.

At this point in the index.asp page example, it is important to query the record set again and invoke the ADO Close method object. Here is how to do it:

<%
strConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
  Source=G:\Mess.mdb"
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open strConnectionString
objConn.Close
set objConn = Nothing
objRS.Close
set objRS = Nothing
%>

Beyond closing out html and body tags, that's it. If you have continued to work in the HTML view in FrontPage and copied this example into your editor, you now will be able to query an Access Contacts database on your computer or Web server.

This code connects to the Mess Company database when run on the Mess Enterprises ASP-enabled Web server, loops through all company contacts, and displays them in a looped table layout. Every page request rechecks the database for added or deleted entries, and only displays current information from the database in the ASP page.

Looking Ahead

This article has provided one example of data interactivity in ASP. There are numerous ways to make your Web site more interactive using a database. And there are powerful database tools you can use to gain performance time and reduce the lines of code you have to write when creating Web database pages and applications. Two advanced examples of database power are stored procedures and triggers. Let's have a quick preview.

Stored procedures are frequently used to keep a set of complicated SQL queries in one place. Developers have found that maintaining their VBScript code separately from their SQL code, which does not change as often, allows for faster code changes during development. Stored procedures are stored in the database and accessed through ASP with @ symbols. The @ symbol is the programmatic way to pass parameters to a stored procedure when the set SQL contained in a stored procedure is not all you want to execute.

Triggers are database events that happen only when certain events are executed in a database, and they are an easy way to track changes made to a database. For example, you might want to use triggers to see what hour of the day people visit the site and modify personal information. You can also use triggers to evaluate what time of day records are added or deleted from a database as well. Triggers usually track their information in separate tables from changed data fields, so that a full version history of changes made to data in the main database is available and clear. Triggers also reside in the database and are created through complex SQL statements made against the database itself.