Data Solutions with Microsoft Office FrontPage 2003: The Database Interface Wizard and the Data View Web Part

  • Article

 

Microsoft Corporation

November 2003

Applies to:
    Microsoft® Office FrontPage® 2003
    Microsoft Office Access 2003
    Microsoft Office SharePoint™ Portal Server 2003
    Microsoft Windows® SharePoint Services

Summary: Walk through how to build custom database solutions using the Database Interface Wizard and integrate them with your Web site using Microsoft Office FrontPage 2003. Learn how to edit ASP and ASP.NET files using FrontPage. Learn how to connect your database to your Web site including search forms and search results. Also learn how to create data views on SharePoint sites using FrontPage.

If you have Microsoft FrontPage 2003 and a Web site hosted on a Windows server with FrontPage 2000 Server Extensions from Microsoft or later, or Microsoft SharePoint Products and Technologies installed, you already have all the tools you need to create your own custom database solution. (12 printed pages)

Contents

Introduction
Databases
FrontPage and Databases
Creating Complete Database Solutions with the Database Interface Wizard
Building Custom Database Pages for Your Site
Using SharePoint Web Sites
Conclusion

Introduction

Microsoft® Office FrontPage® 2003 enables you to integrate databases into your Web site quickly and easily, whether you're a professional developer or someone who knows next to nothing about how databases and Web applications work. FrontPage provides many of the most commonly used database applications for a Web site, such as storing form input in a database, providing an interface for editing and adding records, displaying the results of a search query, generating reports, and more.

For the professional developer, FrontPage provides tools that you can leverage for rapid application development. Not only does FrontPage have excellent integration with Microsoft ASP.NET, it also maintains the ability to integrate with active server pages (ASP) if you prefer. FrontPage ASP.NET support in particular provides the professional developer with an environment that won't interfere with coding while providing the latitude to customize FrontPage-generated output as much as you like.

For the more casual Web designer, FrontPage can do almost everything for you, if that's what you want. For example, the Database Integration Wizard can create an entire database-interactive ASP.NET or ASP Web site in just a few moments. With just a few clicks of a mouse, the Database Results Wizard can enable you to create a display page for selected database results or a form to collect data from your Web site's users and store it in a database. And if you want to do a little customization without having to know how to write code, FrontPage helps you with that too.

FrontPage 2003 also integrates with Microsoft SharePoint™ Products and Technologies, allowing you to create a SharePoint site that can display Data Views through pluggable Web Parts.

Note   The information in this article that applies to Microsoft Windows SharePoint Services also applies to Microsoft Office SharePoint Portal Server 2003, which is built on the Windows SharePoint Services platform.

In short, if you need a Web site that can collect and store user input, display any kind of data in a variety of ways, or provide a complete database interface quickly and easily, Microsoft Office FrontPage 2003 is the complete Web development toolkit for you.

Databases

A database is simply a container for storing data persistently. Typically, a database is constructed of tables that contain related data in records (or "rows") consisting of various columns (or "fields") of data. These tables are stored in the file system of the computer's hard drive. The database management system provides functionality for indexing, organizing, searching, and manipulating the data stored in these tables.

A database Web interface is a collection of Web pages that interact with the database management system to display the data in a meaningful way to the user. It can also contain interface elements for interacting with the database management system, such as forms for customized searches and data input.

Microsoft Office Access 2003 is the database solution included with the Microsoft Office System, and it integrates with FrontPage very well. However, FrontPage can integrate with almost any database. You don't need a previously created database solution to work with an Access database in FrontPage. FrontPage can create one for you, and a Web interface to go with it.

FrontPage and Databases

If you have Microsoft FrontPage 2003 and a Web site hosted on a server running Microsoft Windows Server® 2003 with FrontPage 2000 Server Extensions from Microsoft or later, or Microsoft SharePoint Products and Technologies installed, you already have all the tools you need to create your own custom database solution. You don't even need to own a database program. Once your Web site is published to a Web server, you can have your database solution up and running in minutes. Your database-connected Web pages can be viewed in any Web browser, with no additional client software needed.

Unlike previous versions of FrontPage, FrontPage 2003 can create database-connected Web pages using any of three different technologies:

Active Server Pages (ASP)—Created in 1997, ASP is one of the most widely used active Web technologies in the world today. To implement ASP, you need a Microsoft Windows NT® operating system version 4.0 or later server running Microsoft Internet Information Services (IIS) version 4.0 or later, with FrontPage 2003 server extensions for publishing. Essentially, ASP pages are simply Web pages containing HTML and server scripting code that produce standard HTML code for the client browser, making it completely cross-browser compatible.

ASP.NET—ASP.NET is part of the Microsoft .NET Framework. It is the next generation of ASP and as such is far more powerful. While ASP is procedural in nature and uses Microsoft Visual Basic® development system scripting edition, ASP.NET is object-oriented, event-driven, and uses the more powerful .NET-compatible languages. So you can use scripting or any combination of scripting and custom-built dynamic link libraries (DLLs) to further customize the FrontPage-generated Web interface for your database (note that the FrontPage database wizards use scripted components for ASP.NET only, which makes editing the classes possible without a compiler). ASP.NET requires a server running Microsoft Windows 2000 or later and IIS 5.0 or later, with the .NET Framework and ASP.NET installed on the Web server and FrontPage 2003 server extensions for publishing.

Microsoft Windows SharePoint Services—FrontPage enables you to build SharePoint Web sites, which can use Windows SharePoint Services to provide functionality not available to typical Web sites. One technology provided is Web Parts. A Web Part is a modular unit of information that consists of a title bar, a frame, and content. Web Parts are the basic building blocks of a Web Part Page. A Web Part is the combination of a Web Part Description file (.dwp) and a Web Part assembly file (.dll). All Web Parts are based on Web Custom Controls. Among the Web Parts available is the Extensible Stylesheet Language (XSL) data view Web Part. A Data View is a formatted set of records from an XML file, Web service, or table within a database. The data view Web Part allows you to add this view to a SharePoint site.

Ask your Web hosting service which of these technologies they support. For a list of Web presence providers that support ASP and Microsoft Windows SharePoint Services, see Locate a Web Presence Provider.

It is important to note that if your site is a SharePoint site, you cannot use the FrontPage ASP and ASP.NET database wizards to create database-connected ASP and ASP.NET pages; you use the Data View Web Part. If your site is not a SharePoint site, you cannot use the Data View Web Part; you use the FrontPage ASP and ASP.NET database wizards. If you have not yet selected a hosting service, these contrasting distinctions are important to remember.

Again, it isn't necessary to understand the underlying technologies in order to use FrontPage to create your Web application, but it is helpful to be somewhat familiar with them. With that in mind, let's review a few critical features of these technologies, and how FrontPage works with them.

The Global.asa File

If your Web site connects to one or more databases using ASP or ASP.NET, FrontPage adds a text file named Global.asa to the root folder of your Web site. This file contains a number of scripted functions and is used by ASP to obtain global settings, including the connection string to your database(s). ASP and ASP.NET use technologies that establish a connection to a database through a text string containing all the information needed to identify and establish communications with a data source.

Interestingly, ASP.NET does not use a Global.asa file. It can use a Global.asax file, but it can also use a Web.config file (see next section). However, FrontPage uses the Global.asa file to store and retrieve database connection information at design time for both technologies. For this reason, it is recommended that you avoid modifying the script in this file.

The Web.config File

One of the advantages of ASP.NET is its use of cross-platform compatible international standards, such as Extensible Markup Language (XML). When you create an ASP.NET connection to a database, the connection information is stored in a file named Web.config in the root folder of your Web site. This is an XML file, which is a text file containing markup that looks similar to HTML. Because XML is an extensible language, the Web.config file is an extensible configuration file for ASP.NET. You can add as many custom configuration elements to it as you like. Unlike the Global.asa file, there is no executable scripting in a Web.config file. There is only configuration data.

You can safely make changes to the settings in this file, as well as add any other data that may pertain to your own custom application in your Web site.

ASP

FrontPage, while fully enabled to take advantage of ASP.NET, can also working with ASP, and you can select ASP as your technology of choice when creating a database-connected Web page or an entire Web site. However, unless ASP.NET is not supported on the server or you have existing ASP pages that you need to continue to work with in FrontPage, ASP.NET is the recommended solution for your application. ASP.NET surpasses ASP because ASP lacks the power, extensibility, and modularity of ASP.NET. Finally, ASP provides you with less flexibility than ASP.NET when making changes to a database page.

If you insert database regions into an existing HTML page, be sure to rename it with an ASP extension. The Web server uses the file extension to know how to properly handle the file. If it doesn't have an ASP extension, the file is not processed by the ASP Internet Server Application Programming Interface (ISAPI), or DLL on the server.

Editing ASP Files

The database connectivity components for ASP in FrontPage use FrontPage server extensions to generate the code. In addition, due to the nature of ASP, WEBBOT code, code used by FrontPage to prevent changes to certain Web components created with FrontPage server extensions, in a FrontPage-generated ASP page cannot be modified. For these reasons, you can modify properties of the HTML tables in which your database results reside, as well as some other layout properties of the page, and you can add HTML to the page outside of the database regions—however, you cannot modify any code inside a database region.

Again, this is another excellent reason for using ASP.NET with FrontPage.

ASP.NET

ASP.NET is the latest Microsoft solution for creating interactive dynamic Web applications. Simply put, input is received from a client browser, either through a page request or form submission. Executable code in an ASP.NET page is processed on the Web server, and a normal HTML page is returned to the browser. While this article discusses database connectivity, it should be noted that ASP and ASP.NET can do quite a bit more. In fact, ASP.NET uses a different technology called ActiveX® Data Objects.NET (ADO.NET) to communicate with databases.

Unlike ASP, ASP.NET is fully object-oriented and is as powerful as any executable program you use on your Web server.

In addition to providing a selection of easy-to-use productivity tools for creating database-connected Web pages, FrontPage provides excellent overall support for developing in ASP.NET.

Editing ASP.NET Files

ASP.NET database integration in FrontPage works without WEBBOT code. In fact, FrontPage 2003 is the first version of FrontPage to perform database connectivity without server extensions. This is particularly significant if, for any reason, you want to make changes to ASP.NET files created by the Database Interface Wizard or Database Results Wizard in FrontPage.

FrontPage uses ASP.NET to its maximum potential. When FrontPage creates a database-interactive ASP.NET Web page, it creates a Web form template that includes ASP.NET server controls. Based on templates, server controls include an HTML (text) component and a server-side class (object) component. The Web form template also uses scripted user controls, which FrontPage generates for you. The ASP.NET data grid Web control, for example, which FrontPage uses to display database results, is almost entirely ASP.NET template code, similar to HTML, which you can edit as you would any other HTML code. FrontPage also provides several graphic user interface (GUI) tools for editing data grids without writing any code.

Behind the Web form template, FrontPage generates scripted user control classes that perform the bulk of the work. You can find these in a hidden folder of your site called "_fpclass." To view this, on the Tools menu, click Site Settings, click Advanced, and then check Show hidden files and folders. The .ascx files are ASP.NET user controls, and the fpdbnet.cs file contains the business classes. You can modify all of this code as much as necessary. In fact, FrontPage uses the data grid template to modify the results in the Database Results Wizard—even after you modify the code in the page.

So, whether you never coded in ASP.NET or are a seasoned professional, FrontPage provides all the tools you need to create any kind of ASP.NET application.

Creating Complete Database Solutions with the Database Interface Wizard

As mentioned previously, if you need a complete database solution for your Web site, the Database Interface Wizard can create en entire database interface—giving you the ability to add and edit new records, manage existing records, and search and display data—all in a matter of minutes. The following example illustrates how to create an entire ASP.NET database interface Web site using the Database Interface Wizard.

The first thing you need is a Web site hosted on a IIS 5.0 or later Web server, with the Microsoft .NET Framework and ASP.NET installed.

Walk-Through: Creating an ASP.NET database interface Web site using the Database Interface Wizard

  1. On the File menu, click New.
  2. In the New Web Site section, click More Web Site Templates.
  3. Under the General tab, click Database Interface Wizard.
  4. In the Database Interface Wizard dialog box, point to ASP.NET, then point to Create a new Microsoft Access database within your Web site.
  5. On the next page, specify a name for your database connection. This is not the name of the database, but the name by which to identify the connection to the database. This name is not significant. For this demonstration, simply use the default, Database1.
  6. Now, define the columns (fields) in your database table. It is important to be as thorough as possible at this stage, as this is the last time you can use this interface for making changes to the table structure. If necessary, you can make changes in the database structure later using Microsoft Office Access 2003. For more information about databases in Access, see Microsoft Office Access 2003 on Office Online.
  7. After the Database Interface Wizard creates your Web site and database, select a table with which to work. Since you just created a database with one table, you only have one choice. Note that this wizard can also work with an existing database, in which case you may choose an alternate table. This screen also asks you to specify a folder location in the Web site for your ASPX pages.
  8. Next, supply the types of database pages to include in the Web site. There are three choices:
    • Database Results Page. A page that extracts its contents from your database.
    • Submission Form. A form that inserts results into your database.
    • Database Editor. A set of Web pages that allow you to view, add, delete, and update records in your database using a Web browser.
  9. If you select either the Submission Form or the Database Editor, the wizard prompts you to supply a user name and password to access these pages, however you may choose not to include this information. This feature is also available for ASP pages.

Once you complete the Database Interface Wizard, you can use the new pages to perform your basic database maintenance tasks: The database results page allows you to browse records, you can use the submission form to add new records, and the database editor allows you to browse, edit, add new records, and delete existing records.

You may want to build custom database-connective pages for various purposes, such as a search form to locate records using a query. The following sections describe some of these variations.

Building Custom Database Pages for Your Site

The basic database interface elements described earlier may not be all that you need for your Web site. You may need other types of database-connected pages, such as search forms for locating records or specialized views for displaying filtered sets of results. You may also want to display special kinds of data, such as pictures or hyperlinks.

FrontPage includes tools that can simplify the creation of such elements in your Web site. The next section describes how to create a search form using the Database Results Wizard and ASP.NET.

Walk-Through: Creating a Database Form within Your Web Site using the Database Results Wizard

Frequently, databases require an interface for searching through a table for records that match specific criteria, such as a table that contains product information for a retail seller. Users may want to find products within a certain price range, or by name, and so on.

The Database Results Wizard can simplify the development process of such an interface. Not only can it create a page with data from your database, it can also create a search form for you as well.

Walk-Through: Connecting Your Search Form to an External Database

FrontPage can create an Access database for you, as we discussed earlier. However, if your Web site gets moderate to heavy traffic, a file-based database such as Access may soon be pushed beyond its limits with regards to concurrent connections and performance. In a scenario such as this, you may want to use a more powerful database server such as Microsoft SQL Server.

A database server is much like a Web server in that it monitors a network port for requests and responds to them. However, as a Web server hosts Web sites and Web pages for client applications, so a database server hosts databases and data to client database applications.

To demonstrate how to use the Database Results Wizard to create a search form, we must connect with a local instance of SQL Server, specifically to the built-in Northwind database. Then, we can create simple search form and database results pages.

  1. In FrontPage, on the Insert menu, click Database, then click Results.

  2. In the Database Results Wizard dialog box, click ASP.NET and then click Connect to an external database.

  3. In the Select database driver list, click SQL Server. In the Server box, type the name of a SQL server in the format of a TCP/IP address (192.168.0.0) or a domain name (domain_name). In the Database name box, type the name of the database; in this example, Northwind. In the User name and Password boxes, type the user name and password with permission to select records from the database.

    Figure 1. The Database Results Wizard dialog box

    Note   ADO.NET includes a variety of classes for connecting to nearly every kind of database. ADO.NET uses a connection string to hold connection-specific information, and the Database Results Wizard builds the connection string according to the information you provide.

  4. Click the Products table. The wizard prompts you to select the fields to display. Select several fields typically used to display product information to potential customers.

  5. Next, the wizard prompts you to identify the criteria to use to select records in the table.

    It is here that you can build a custom query for your search form—for example, to filter records in a product table by products with a price lower than a specified amount. Or, you can build a query that searches a personnel table by department, or by the first letter of employee last name. The wizard provides a very easy-to-use interface for building a query without even knowing what a query is. The wizard uses the criteria that you select in this section to create the search form.

  6. Next, the wizard prompts you to choose whether to create a search form for your Database Results page. Leave the checkbox checked and click Finish, thus creating a Database Results page which includes a search form.

You can edit the resulting ASPX page and all the associated .NET scripts for the various server controls and classes. For example, the search form and database results are placed in the same page by default. You can easily modify the page to hide the search form when the database results are displayed and vice versa. Or, you can split the page into two separate pages, one for the search form and the other for the results.

Another option is to create a database results page with a custom SQL query, into which you plug values from a search form you already created. Then, you can modify the form properties to post to your database results page.

Walk-Through: Sending Form Results to an Existing Database

You may have an existing database that you want to use in your Web site. FrontPage can easily create a connection to an existing database as well. If it is an Access 2003 database, you can simply import the database into your Web site, and FrontPage recognizes it and offers to create a connection to it. All you have to do is provide a name for the connection that you can easily remember to associate with the specified database.

You can also add connections to existing databases that may not be in your Web site. To do so, on the Tools menu, click Site Settings. On the Database tab, view the list of existing database connections in your Web site. From here, you can verify the connections by testing them to make sure they are working correctly, or you can add new database connections such as to the following:

  • File or folder in your current Web site
  • System data source on the Web server (System DSN)
  • Network connection to a database server
  • Custom definition

Click on Advanced to write your own custom connection string. Once you create the connection, you can use all of the FrontPage database tools to create database pages as described earlier in this article.

You now have all the information you need to use FrontPage to connect to different databases, and to create database-connected pages. You can combine the FrontPage wizards and techniques outlined in this article in many ways to create different database interfaces.

Using SharePoint Web Sites

SharePoint sites are Web sites based on Microsoft SharePoint Products and Technologies. These sites provide team-oriented Web sites to share information and foster collaboration with other users on documents.

You can use FrontPage to customize SharePoint sites and Web Part Pages. A Web Part Page is a special type of Web page that contains one or more Web Parts. A Web Part is a modular unit of information that consists of a title bar, a frame, and content. Web Parts are the basic building blocks of a Web Part Page. A Web Part is the combination of a Web Part Description file (.dwp) and a Web Part assembly file (.dll). All Web Parts are based on Web Custom Controls. You can use a Web Part Page to consolidate data, such as lists and charts, and Web content, such as text and images, into a dynamic information portal built around a common task or special interest. You can develop your own custom Web Parts or use default Web Parts and Web Parts developed by third-parties. For more information about Web Parts and Web Part Pages, see A Developer's Introduction to Web Parts. For more information about Microsoft SharePoint Products and Technologies, see the Microsoft SharePoint Products and Technologies 2003 Software Development Kit.

One of the default Web Parts is called a Data View Web Part. In a database, a data view is a filtered subset of a table or combination of tables. A Data View Web Part displays a tabular representation of a filtered subset of a table on a Web Part Page. You can create data views from a large variety of data sources including databases, XML files, SharePoint lists, and so on.

Walk-Through: Creating a Data View of Data from a Database

This section describes how to create a data view from your database.

  1. In FrontPage, on the Insert menu, click Database, and then click Data View. This opens the Data Source Catalog task pane.

    Note   You can also add a new database connection from the Data Source Catalog task pane. Alternatively, you can click Data. . .Insert Data View to launch the Data Source Catalog as well

  2. Configure the connection. For example, you can add a filter, specify sort order, identify fields to show or hide.

  3. Now, you can create a connection to the database. If you already have a connection, it appears in the Data Source Catalog list. Otherwise, in the Database Connections section, click Add to Catalog and add a new connection.

  4. Once you add the data source to the Data Source Catalog list, drag and drop the Data Source onto the page wherever you want the data view to appear. In addition, the FrontPage Design View even shows the data in the Data View Web Part while you edit the page.

Click here to see larger image

Figure 2. The Data View Details task pane (click picture to view larger image)

Conclusion

FrontPage provides attractive features for anyone, from the casual user to the seasoned Web programmer. In addition to the support for ASP database pages, Microsoft Office FrontPage 2003 introduces excellent support for both ASP.NET and Microsoft SharePoint Products and Technologies including enhanced functionality from that available in FrontPage 2002. FrontPage helps even novice Web editors and developers to create a complete database interface solution in minutes and to add customized database-interactive pages through a user-friendly wizard interface. If you are a professional developer, FrontPage provides you with productivity tools that enable you to quickly create a framework on which to build. Integration with ASP.NET enables you to provide fully customizable solutions.

The database tools provided by FrontPage are modular, and you can combine them in a variety of ways. Now that you know the basic steps to connecting your Web site with a data source using the Database Interface Wizard and the Database Results Wizard and working with data views and Web Parts, you can create all kinds of database-connected solutions for your Web site. FrontPage allows you to create a product catalog, collect and store individual user information and preferences, or even customize a SharePoint site to provide data views for your organization.