Using databases on websites with Front Page -01/14/2003

This is a constantly changing topic (thus the date). You are encouraged to try this on your own. If you run out of time or get frustrated - contact us.  mark@hightechministry.org

Topics

Quick Overview:

The Process and Details:

If you want to have a database for members, front page makes it easy. 
Step 1:  create an input form using your member application form you created above.  open the page up and click on form properties, then choose the database button, then choose options, then choose create database.  fp will create a dbase for you with the fields matching all your form fields. awesome no?  now you have an input form you can enter the information in yourself, or let them enter it and you simply activate it and clean up their record once you approve them and they pay.

Step 2:  search page or member display page:  Create a new page for display.  Choose Insert, Database, Results. Follow the wizard through. Of course tell it to use the newly created dbase (likely default). We can experiment with the results wizard to see which display format you like best. Note that the search won't work on your local computer unless you have fp extensions installed. You might have to publish it to test it.

 

Here are your instructions that we'll go through:

(follow steps verbatim)

1. Open website

a. on the file menu, click 'file, open web'

b. type in http://www.alphagraphics-sac.com et al - click open

c. answer the username/password -

d. when the site is loaded, click on the 5th icon from the left - 'folder list' and you will

be able to see the folders listed

2. Find database and Open it.

a. double click on folder containing dbase '_private'

b. double click on alpha.mdb - and it will open on your pc with Access - be patient

**caution** always good idea to make a copy occasionally. Highlight dbase, right

mouse click, choose copy, click _private folder, paste.

c. make changes to dbase. click the save icon.

d. Upload it back to website by EITHER hitting the enter key while in the dbase and moving to next dbase field, OR simply close access.

THEN click on front page and watch it upload.

**caution** If you keep the dbase open for a long time or if you get disconnected

from the net you may have data snyc problems from people entering data in while

you had the dbase checked out.

**to recover** Make a copy of the dbase before you upload the modified dbase and

you can save any recently entered data. If disconnected, Once you get back online

and you still have access open....click on File, Import, and you should see the dbase

listed ready to upload. Click ok. A temp copy of the dbase will be listed in your

c:\windows\temp\frontpagetemp folder as alpha2.mdb for example. This is a

Backup copy which might be current, or one modification out of date.

Using Databases with Microsoft FrontPage 2000
White Paper
Published: December 1998
For the latest information, please see http://www.microsoft.com/frontpage
Introduction
The Microsoft® FrontPage® 2000 Web site creation and management tool makes it easier than ever for you to collect and display data on your own Web pages. With new database features in FrontPage 2000, you can create new or connect to existing databases and incorporate live data directly into your Web pages. FrontPage automatically generates the scripts needed to display and add information to databases, so no programming is required. If you require custom functionality, you can edit the script files to build exactly the solutions you need.
New Database Features
You may be familiar with previous versions of Microsoft FrontPage that introduced easy-to-use form-handling features, such as sending form data to a text file in a FrontPage-based Web site, to an email address, or to a custom script for special handling.
FrontPage 2000 builds on these form capabilities by providing new features to help you collect, store, and retrieve information from databases. With just a few mouse clicks, you can send form information from a Web page to a new or existing database or generate pages that display live database information.
With database publishing in FrontPage 2000, you can create a form and then specify where the form data should go. For example:
§ You can create an HTML form in a Web page and have FrontPage generate a new Microsoft Access database to store the data entered in the form. FrontPage automatically produces the information needed to connect the form to the database.
§ You can create a form and have its data added to an existing database compliant with ODBC (open database connectivity). All you need to do is specify a database connection and the database tables and fields in which to add the data.
With the Database Results Wizard in FrontPage 2000, you can build Web pages with live data that updates each time the page loads. Using this wizard, you can specify which records display, as well as the format in which the data will appear on the Web pages. If you choose to use custom queries, you can enter SQL SELECT statements using the Database Results Wizard.
Database Support
Using FrontPage 2000, you can incorporate data into your Web pages from any ODBC-compliant database. The database can reside either on the Web server itself or on a remote database server.
Drivers are available for tab or comma separated text files and Microsoft Excel, and for these file-based databases:
· Microsoft Access
· Microsoft Visual FoxPro® database development system
· Inprise dBASE
· Corel Paradox
Drivers for server-based databases include those for Microsoft SQL Server™ and Oracle.
Database Connectivity
FrontPage 2000 makes use of Active Server Pages (ASP), a server-side scripting environment that you can use to create dynamic Web pages. To utilize a database from within an ASP-based application, you will need a database connection or a set of data-binding instructions.
FrontPage stores all database-connection information in a file called Global.asa. You can either create a database connection through the Database tab in the Web Settings dialog box, or you can have FrontPage create a database connection when you import (or drag and drop) a Microsoft Access file into a FrontPage-based Web. In either case, FrontPage automatically writes connection information to Global.asa for you.
Traditionally, a database connection takes the form of a data source name (DSN)-an ODBC resource that contains the information needed to connect to a database. FrontPage 2000 requires a DSN to create a connection only to a database that resides outside of the FrontPage-based Web.
Using the New Database Connection dialog box in FrontPage 2000, you can browse for databases in the FrontPage-based Web or for system DSNs that live on the Web server machine.


If you have the correct security permissions, you can find a system DSN for a remote database by connecting directly to the database server. If you don't have permissions, contact the server administrator and request a system DSN to connect to the remote database.
The final release of FrontPage 2000 will allow administrators to disable DSN browsing and the use of FrontPage database features on specific ports or a Web or sub-Web basis.
Database Security
It is extremely important to maintain the security of a database that resides on a Web server. Using the FrontPage Server Extensions, administrators can ensure that only users with administrative or authoring privileges for a Web will be able to access databases in that Web.
The recommended location for file-based databases is in the FPDB folder in the FrontPage-based Web. With the FrontPage Server Extensions installed on the Web server, FrontPage automatically marks this folder as not browsable, scriptable, or executable. This ensures that users will be able to access a database only through Active Server Pages created for that purpose-not by simply browsing to the database.
By default, FrontPage 2000 places a new Microsoft Access database in the FPDB folder. In addition, when a user imports an existing database to a Web, FrontPage creates the FPDB directory (if it does not already exist), and uploads the file to the FPDB directory or to one specified by the user. (Note: If you choose to put the database in a directory other than the FPDB directory, you will see an entry in the Component Errors report recommending that you move it to the FPDB directory for security reasons.)
It is important to note that FrontPage does not provide any database security beyond the security settings that already exist within the database. If update restrictions are not set within the database, any user with authoring or administrative rights to the Web will be able to access and change the contents of the database.
Another issue to note is that publishing ASP pages to a Web server that does not support ASP can expose sensitive information (such as database passwords) to anyone browsing those files. Be careful not to publish ASP files to a non-ASP server.
Security Tips for Administrators
§ Check the Component Errors report in FrontPage 2000 to see if any databases need to be moved to the FPDB directory.
§ Use the database's built-in security mechanisms to restrict who can update its content.
§ Check to see what database privileges are available to the account that Web authors are using with FrontPage. Generally, accounts should not need privileges beyond SELECT and UPDATE, which are used by FrontPage.
§ If a Web server does not support ASP, be sure to configure the Web and FrontPage Server Extensions in a way that users cannot add ASP pages to their Webs.
Creating Custom ASP Solutions
At times, you may need to modify the ASP generated by the database features in FrontPage 2000. For instance, you may want to create an order form in which the fields presented depend on the payment method the user selects. FrontPage does not generate ASP to handle this scenario, where the form fields and payment information are in one database region. However, you can edit files that FrontPage creates in order to implement custom solutions.
When you create pages that use the database features in FrontPage 2000, FrontPage creates one ASP file and four include files. The include files, which contain ASP scripts used by the pages, are stored in a hidden _fpclass folder in the FrontPage-based Web:
§ fpdblib.inc contains the library functions used by the database features.
§ fpdbrgn1.inc contains the ASP code that establishes the database connection, and constructs and executes the query, starting a loop through all database records.
§ fpdbrgn2.inc contains the ASP code that finishes the query loop started by fpdbrgn1.inc and closes the database connection.
§ fpdbform.inc contains the ASP code that implements the Save to Database form handler.
Authors can use FrontPage 2000 and its HTML editing features to modify the scripts in these files. [Note: To view hidden files in a FrontPage-based Web, select Tools, Web Settings, Advanced, and Show documents in hidden directories.] To undo any changes to these include files, you can always delete the files from the Web. FrontPage will create new versions of the deleted files the next time a database page is saved.
All database pages in a FrontPage-based Web point to these four include files in the _fpclass folder. Any changes made to these include files will affect all database pages in a Web. To make changes to include files that will affect only a single page or a subset of pages, follow these steps:
1. Create a new Web that contains database pages. If you plan to modify an ASP page that affects a group of pages, you should create all of these pages prior to the last step.
2. Save the pages. FrontPage will automatically create the _fpclass folder, generate the include files, and save them to _fpclass.
3. Create a new folder in the Web, giving it a unique name.
4. Move the include files out of the _fpclass folder and into the new folder.
FrontPage will update the database page(s) to point to the include files in the new folder. From this point forward, you can modify the source in any of the include files in the new folder, and only those pages created prior to moving the files to the new folder will be affected.
If you need to regenerate the original ASP code for these include files that you removed, delete the include files from the new folder and proceed through the Database Results Wizard for each page that requires revised include files. When you save the pages, they will point to newly generated copies of the include files in the _fpclass folder.
Programming Solutions
FrontPage 2000 was designed to make it easy for users without programming expertise to create pages that interface with databases. Since FrontPage 2000 now preserves your HTML source without modification, you can easily make edits to the ASP pages generated by FrontPage 2000 to create data-driven Web solutions.
To take advantage of enhanced database programming tools such as the Visual Studio® development system's Integrated Database Tools, developers can import their ASP scripts to the Microsoft Visual InterDevÔ 6.0 Web development system. For more information about this product, see http://msdn.microsoft.com/vinterdev/prodinfo/evaluation/eval2.asp.
System Requirements for the Database Features
On the client, only a browser is required to view pages created with the database features in FrontPage 2000. The following software is required on the Web server hosting the pages:
§ Web server software. For the Web server, you can either use the one built into Microsoft Windows NT® Server, Microsoft Internet Information Server (IIS) version 3.0 or 4.0, or Microsoft Personal Web Services (PWS) 3.0 or 4.0. The FrontPage-based client and server can reside on the same machine.
§ FrontPage 2000 Server Extensions. The server extensions are required to send form data to a text file or e-mail address, to implement FrontPage-based Web security, and to enable remote database authoring. The extensions are not required to send form data to a database or to create reports using the Database Results Wizard.
§ Support for ASP. Database features in FrontPage rely on Active Server Pages (ASP) to provide up-to-date information every time a Web page is loaded or refreshed. IIS running on the Windows NT Server operating system provides native support for ASP.
Summary
New database features in Microsoft FrontPage 2000 provide an easy way to create Web pages that incorporate useful information from a variety of data sources. Using FrontPage 2000, you can create Web pages that collect or display data dynamically-without extensive database or programming skills. Administrators will find that creating new data sources and connecting to databases in FrontPage 2000 require less administrative overhead, making data access and retrieval over intranets and the Internet easier than ever.
 

The basics of databases
Think of a database as a sophisticated version of an index card file. For example, an employee phone list consists of index cards filed in alphabetical order by the employees' last names. Each card contains an employee's ID number, last name, first name, and company telephone extension.

In database terminology, each employee's card is called a record. Together, all the cards in the index card file are called a table, or record source. Each item on a card is called a field. On a card in the employee phone list, the first name is considered one field; the last name, another field, and so on. In a database, the fields are broken into two parts: the field label, which identifies the field (for example, LastName) and the field value, which is the actual data in the field (for example, "Smith").
A database can have multiple tables. For example, in addition to the employee phone list card file, you might also have card files for customers and products. You can store them as additional tables in the same database.
A database can also have multiple views. A view has records and fields just like a table, but it can contain information from different tables. In Microsoft Access, a view is called a query, and it can accept parameters that change the information it displays.
In Microsoft FrontPage 2000, you can use the new Database Results Wizard to create pages that let site visitors search for and display specific information from your database - in the employee phone list, for example, they can look up a specific employee's telephone extension. You can also create forms to let visitors add information to a database - for example, to subscribe to your e-mail list or order a product.

Connect to a database
Before you can use the information in a database on your pages, you must create a database connection. A database connection specifies the name, location, and type of database you want to access, along with any other required parameters.

There are different types of database connections:
· A file-based connection to a database, such as Microsoft Access, in your web. When you import an Access database into your web, FrontPage creates a database connection automatically.
· A System Data Source Name (System DSN) on a Web server. The data source can be a file-based database or a database management system.
· A network connection to a database server. A database server (for example, Microsoft SQL Server) is a computer dedicated solely to managing and maintaining large databases.
You can also create a custom connection using a file or string that defines all the necessary information.
The Database Results Wizard lets you use an existing database connection or create a new one. You can also use a sample database, Northwind Traders, to help you understand database concepts and experiment without altering your own "live" database.

Select information from a database
After connecting to a database, the Database Results Wizard determines the names of the tables and views in that database. In the Northwind Traders database, for example, there are tables for Employees, Customers, Products, and so on.

After you select a table or view, the wizard determines the names of the fields it contains - in the Northwind Employees table, for example, there are fields for FirstName, LastName, and Extension. You can then select the fields that contain the information you want to display in the database results region.
When a site visitor views the page in a browser, the selected fields from each record in the table or view are displayed in the database results region. For example, for each employee in the Northwind Employees table, the database results include a field label (for example, LastName) and a field value (for example, "Smith").
If you're familiar with Structured Query Language (SQL) statements, you can create a custom query (instead of selecting a table or view) and have the wizard verify it.

Let site visitors add information
You can create forms that let site visitors add information to a table in a database. One common example is an order form.

A site visitor types his e-mail address, shipping address, credit card information, and so on. When he clicks Submit, the form sends the information (the field labels and values for each piece of information) to a database, where it's added to a table as a new record. You can add the information to an existing database, or have FrontPage create one for you. The Web server sends back a confirmation message or page letting the visitor know that the data has been accepted.

More Help Files

Use an Access database in a web
Microsoft FrontPage makes it easy to use information from a Microsoft Access database in your web. You can import an Access database into your web, or you can connect to an Access database defined as a System Data Source Name (System DSN) on another server. Using the Database Results Wizard, you can then display information from the Access database on the pages in your web.
· To import an Access database into your web, in Windows Explorer, copy the Access database file (.mdb) you want to import, and then paste the file into the Folder List in FrontPage. FrontPage asks if you want to create a new database connection, and then prompts you for a name for the new connection. To display information from that database on a page, run the Database Results Wizard (point to Database on the Insert menu, and then click Results).
· When you import an Access database into your web, FrontPage creates a folder named Fpdb with permissions specifying that the folder can't be viewed in a Web browser. To protect the privacy of your database, move it into the Fpdb folder.
· To use an Access database defined as a System DSN on the Web server, create a new database connection to a System DSN on a Web server.

Create a new database connection to a System DSN on a Web server
To display information from, or input information to, a database, FrontPage uses a database connection. The database connection specifies the name, location, and type of database you want to access, along with any other parameters it may require.
When you start the Database Results Wizard, you specify the database connection you want to use. You can connect to any System Data Source Name (System DSN) defined on the Web server. A System DSN can connect to an ODBC-compliant database (such as a Microsoft Access or Microsoft Excel database), or a database management system (such as Microsoft SQL Server).
Note Only a Web server administrator can create a System DSN on a Web server. If the Web server doesn't contain a System DSN for the database you want to access, ask the Web server administrator to define one using the ODBC Data Sources option in Control Panel on the Web server.
To create a new connection to a system data source on a Web server, do the following:
1. In Page view, open a page containing an existing database results region, or create a new page to contain the database results region, and then start the Database Results Wizard:
· If you haven't yet run the wizard to create a database results region on the page, point to Database on the Insert menu, and then click Results.
· If the page already contains a database results region, and you want to replace that data with data from a different database connection, right-click anywhere in the database results region, and then click Database Results Properties on the shortcut menu.
2. In step 1 of the wizard, click Use a new database connection, and then click Create.
3. Click Add, and then type a name for the new database connection in the Name box.
4. Click System data source on Web server, and then click Browse.
The System Data Sources on Web Server dialog box is displayed. The list shows the System DSNs defined on the Web server.
5. Select the System DSN for the database you want to access, and then click OK.
6. Click OK to close the Database Connection Properties dialog box, and then click OK to close the Web Settings dialog box.
Tips
· To create a new database connection for a web without running the Database Results Wizard, click Web Settings on the Tools menu, click the Database tab, and then follow this procedure starting at step 3.
· If the database is password-protected, click Advanced, and then type your user name and password in the boxes.
· To set timeouts or other name-value parameters for the database, click Advanced.

Create a database connection
To display information from, or input information to, a database, FrontPage uses a database connection. The database connection specifies the name, location, and type of database you want to access.
When you add information from a database to your pages using the Database Results Wizard, in step 1 of the wizard, you can select an existing database connection or create a new one.
You can also create or modify a database connection without using the Database Results Wizard as follows:
· On the Tools menu, click Web Settings, and then click the Database tab:
· To create a new database connection, click Add, and then follow the appropriate procedure to create a new database connection to a file or folder in the current web, create a new database connection to a System DSN on a Web server, create a new database connection to a database server on a network, or create a new database connection using a custom definition.
· To modify an existing database connection, select that connection in the list, click Modify, and then follow the procedure to modify an existing database connection.

Easy Database Integration
FrontPage users have requested ease and flexibility in incorporating database content in their Web sites. FrontPage 2000 gives you several options to make database integration easier than ever.

Create a New Form That Sends Results to New Access Database and the Database Results Wizard
In this exercise we will create a form on a page that sends user-inputted data to a new Microsoft Access database, and then we'll show the contents of that database on an HTML page.

Create a New Form That Sends Results to New Access Database Quick Steps More Detail
Start: FrontPage 2000 launched, sample Web site open. (Note: These database integration features require you to host your pages on a server with Active Server Pages capabilities, such as Microsoft Internet Information Server. You can use these capabilities with any ODBC-compliant database. In this exercise, we will assume that you are authoring against an ASP-capable server. If you are not, you can author on your hard drive or a network drive, and later publish to your ASP-capable Web server.)
· Page View (Normal mode)
· New Page Click on Page View, then New Page to create a new page.

· · Insert
· Form
· Form
· Enter two or three times Insert a form on your page by selecting Insert, then Form, and then Form. Press Enter several times to open up space to insert form fields.

· Quick Steps More Detail
· · Insert
· Form
· Check Box
· Double-click the check box and change Name from C1 to Choice1, and then click OK
· Type choice 1
· Repeat and add choice 2 and choice 3 Insert a check box (Insert, Form, Check Box), and type some text next to it, such as choice 1. Double-click the check box and change Name from C1 to Choice1, and then click OK.
Repeat and add choice 2 and choice 3, with Names choice2 and choice3 respectively.
· · Insert
· Form
· Scrolling Text Box
· Enter
· Type Your comments go here
· Double-click text box and change Name from S1 to Comments, and then click OK. Insert a scrolling text box (Insert, Form, Scrolling Text Box), press Enter, and then type some text underneath it, such as Your comments go here. Double-click text box and change Name from S1 to Comments, and then click OK.
· · File
· Save the page as dbform.htm Save the Page (File, Save). Save the page as dbform.htm.
· · Right-click on form field
· Form Properties
· Send to Database
· Options Right-click on one of the form fields you just entered, and then select Form Properties. Select the Send to Database option, then click the Options button.

· Quick Steps More Detail
· · Create Database
· OK
· OK
· OK
· OK Instruct FrontPage to create a new database to send the data to by clicking the Create Database button. FrontPage will automatically create the Access .MDB file and place it in the newly created fpdb directory in your Web. Since your page is called dbform.htm, it will name the database connection dbform. Press the OK button when the dialog confirms that fpdb/dbform.mdb has been created. Press the OK button in the Options for Saving Results to Database dialog, and again in the Form Properties dialog. You will next see a dialog that warns you that the page should be saved as ASP. Press OK.
· · File
· Save As
· OK
· Save as type: Active Server Pages
· File name: dbform
· Save You will need to save your page as .ASP so it can take advantage of these server-based database capabilities (File, Save As). When warned that you must save this page as ASP, click OK. For file name, type dbform, and for Save As Type, select Active Server Pages. Click Save.
· · File
· Preview in Browser
· Preview
· Enter three or four complete records in the form
· Go back to FrontPage Preview dbtest.htm in your browser (File, Preview in Browser, Preview). Enter three or four complete records into the form. This data is being saved to the Access .MDB that was automatically created for you when you created the form. Go back to FrontPage.
Create Page That Shows Data From Access Database · Page View (Normal mode)
· New Page Click Page View, then New Page.
· Insert
· Database
· Results Next you can use the Database Results Wizard to incorporate the data from the database you just set up onto a page. Note that this same process can be applied to any ODBC-compliant database, not just to Access databases. Click Insert, then Database, and then Results.

· Quick Steps More Detail
· Use an Existing Database Connection
· Select dbform
· Next
· Next
· Next
· Next
· Finish Select Use an Existing Database Connection, then select the database that you just created (dbform), and then click Next.
· File
· Save
· Save page as dbresults.asp Next you'll need to save your page. Since the data will be dynamically updated from the database each time a user enters the page for the first time or refreshes the page, you will need to save it as an .ASP page. Save this page as dbresults.asp. (Note: This requires an Active Server Pages-capable server, such as one running Microsoft Windows NT Server with Internet Information Service.)
· · File
· Preview in Brower
· Preview
· Return to FrontPage Preview dbresults.asp in the browser (File, Preview in Browser, Preview). You can experiment with this feature by entering more data in dbtest.htm, then refreshing dbresults.asp so you can see that the page is automatically updated with fresh database content.