|
Introduction
This application is for the beginners who wonder how to build their own web database system on their own computers. I will use Microsoft FrontPage98 and Microsoft Access in the demonstration. The reader should be familiar with the RDBMS concept and know how to use Microsoft Access tool to build a local database system. They should also have the Northwind.mdb file, which is included in Microsoft Office 97.
Web Database System Application
- Importing a Database File
- Fire up FrontPage Explorer and open the FrontPage Web that is to contain the Northwind database.
- On the File menu, choose Import to display the import File to FrontPage Web dialog box.
- Click the Add File button, then point to the folder where the Northwind.mdb file is located, and then click the Open button to add a database file to the import list.
- Click OK to import a database file to your FrontPage Web.
- Check out whether the database file is on the file list or not.
- ODBC Connection
- Double click the My Computer icon, which is on your window screen, to open your computer’s Control Panel.
- Double click the Control Panel icon to open the Control Panel screen.
- Once the Control Panel screen is opened, double-click on the 32bit ODBC icon.
- ODBC Data Source Administrator will pop up on your computer screen. Then click on the System DSN tab to open the System DSN screen.
5. Click on the Add button to put Northwind.mdb file on the list of System Data Sources.
- Select “Microsoft Access Driver” from the driver list and then click on the Finish button.
- Type the Data Source Name (DSN) (use whatever name you like) in text box along with a short description, and then click on the Select button.
- The Select Database screen pops up on your computer screen. Find the location of database file by scrolling through the list of Directories. After finding the database file, click on it. Then click on the OK button.
- The computer screen returns to procedure Step7, but this time the Setup box also provides the database’s location. Click the OK button.
- Now the Data Source Name (DSN), Northwind, shows up on the source list. Click the OK button. Finally, ODBC is set up completely for Web database
connection.
- Generate SQL Query in MS Access
- Launch MS Access and open the Northwind.mdb file.
- In the Northwind database dialog box, click the Queries tab.
- Double-click the "Sales by Category" query to display the query results.
- The query result with field names (Category ID, Category Name, Product Name, and Product Sales) appears in the database screen.
- On the MS Access View menu, and choose Design View to modify the query.
- Deselect the check box below the CategoryID entry in the table.
- On the View menu, choose Datasheet View. Check that the CategoryID field has been successfully omitted.
- On the View menu, choose SQL View. Now, the actual SQL query is generated to create the search results.
- Copy this query to Notepad for later use.
- Format the Active Server Page
- Open the FrontPage Explorer, and click the FrontPage Editor icon, which is on the FrontPage Explorer's Tools menu. It displays the FrontPage Editor with a new page automatically.
- Position the cursor where you want the query results table to be displayed.
- On the FrontPage Editor's Insert menu, point to Database, then click Database Region Wizard.
- When the Database Region Wizard is displayed, type the name of the Data Source Name(DSN). The name you enter here should match exactly the DSN that was specified on the Web server. (In this example, we use "Northwind" as the DSN. This is the DSN we already specified through ODBC connection earlier.)
- Leave the Username and Password unchecked, and click the Next button
- Open the Notepad file that has a copy of the Sales by Category SQL. Copy all the SQL to the clipboard.
- Switch back to the Database Region Wizard on the FrontPage Editor screen and click the "Paste SQL From Clipboard" button to put the query in the input field.
- Click the Next button
- Click the Add field button When the Add field button displays, enter an actual database field name without spaces. To see the actual field names, switch to the Design View in Microsoft Access.
- Type the "CategoryName" without spaces between Category and Name, then click OK.
- Repeat steps 9 and 10 to insert the "ProductName" and "ProductSales."
- Click Finish to create the database region.
- Saving the Active Server Page
- After clicking the Finish button from the database region wizard, the "save file" message pops up in the FrontPage Editor screen.
- Click the OK button.
- On the FrontPage Editor's File menu, choose Save As.
- Type the file name for this page with an ".asp" extension.
- Click OK to save this page in the Web server file folder.
- Open the FrontPage Explorer window, and select the Folders View. Then check out whether the saved file is there or not.
- Preparing for Criteria
- Go back to the FrontPage Editor screen. Right-click the database region, and choose Database Region Properties.
- The Database Region Wizard pops up in the FrontPage Editor.
- Modify part of the SQL query.
Where (((Orders.OrderDate) Between #1/1/94# And #12/31/94#))
?
Where ((Categories.CategoryName = '%%CategoryName%%') And ((Orders.OrderDate) Between #1/1/94# And #12/31/94#))
- Click the Finish button to apply the changes.
- Formatting the Search Form
- Open the new FrontPage Editor.
- On the FrontPage Editor' s Insert menu, point to Form Field. Then click on One-Line Text Box.
- Double-clicking on the One-Line Text Box opens the Text Box Properties dialog box.
- Type the "CategoryName" in the Name field, and click the OK button.
- Click on the HTML view tab, which is at the bottom of FrontPage Editor screen.
- From the HTML View, reset the "action=directory name/file name."
Note: Please remove all WebBot tags to insure that your HTML view looks like the above.
- Save the file as search.htm.
- Go back to the FrontPage Explorer screen and make sure all files are in the same folder.
- Open Internet Explorer or Netscape Navigator, and type the URL in the text field.
- Type the Product Category you want inside the browser's text box, and then click the Submit button.Let's try "Beverages."
- Here we go!
About the Author
Joung-ho is a senior MBA student at WSU with EE/MIS background. Currently, he works
as part time Systems Analyst/Application Developer. He has developed many quality
websites to help internal and external business operation (Intranet and Internet)
based on an intensive analysis of Web Design Strategy including ASP, ISAP, PERL
programming. He will be startign his Ph.D. program in MIS in autumn 1999. He can be reached at: joung-ho@wsunix.wsu.edu.
|
