Microsoft dbWeb Tutorials

# Tutorial C: Customizing Schemas

You might need to present your data more intuitively by rearranging entry fields, by using HTML tables and nested report banding, or by personalizing queries and forms by adding your corporate logo. Using various fonts and applying display effects to your text can increase your forms’ visual appeal and effectiveness. You can make your forms more interesting by adding graphics, crisp pictures, and even image maps.

Microsoft dbWeb comes integrated with the facilities needed to alter the appearance of all the query and presentation forms using custom database format (.DBX) files written in HTML.

# Creating a Multi-Record .DBX file

The dbWeb Administrator supports custom format file creation for both multi-record and single-record forms in your schema. Multi-record forms display several records or query result rows at a time. Microsoft dbWeb’s tabular form is an example of a multi-record form. Single record forms display only one record or query result row at a time. Microsoft dbWeb’s Freeform, QBE form, and Update form fall under this category.

The multi-record form utilizes report banding, a unique Microsoft dbWeb feature. If you have used the report designers included with many of the desktop database and ad-hoc query software packages available, you are probably familiar with the concept of banding. Banding allows you to designate an area of the form that will repeat for each row in the query result set. \tbon and \tboff are the delimiters used to mark repeating regions in the form.

Many of the popular report designers allow you to nest banded regions. Microsoft dbWeb is no exception. The Category_Products example schema uses a multi-record .DBX file that nests banded regions. Feel free to examine the schema and the accompanying .DBX file to learn more about how to use this powerful feature.

Most of the examples in this chapter assume that you will be using Microsoft dbWeb’s .DBX file editor. You can launch the editor from the Schema window’s DBX tab panel.

Note: Microsoft dbWeb does not require that you use its built-in .DBX file editor to create custom format files. You are free to use the HTML editor of your choice, since Microsoft dbWeb treats custom format files as plain ASCII text files. You may find it more convenient to use the .DBX file editor, however, because of its ability to generate and insert the tags into your HTML code that are needed to reference form controls.

To create the Order Summary .DBX file

1. In the Schema window, open the Order_Summary schema and click the DBX tab.

2. Click Editor.

The columns you see listed on the left are compiled from the columns you included in your tabular form. You can begin typing HTML code in the edit box on the right. If you need to reference a column in your custom form, simply choose a column listed on the left and double-click it. Microsoft dbWeb automatically inserts the column’s corresponding HTML tag at the current insertion point marked by the blinking cursor. Because Microsoft dbWeb consults the schema repository when wrapping Microsoft dbWeb results with your HTML code, it respects all properties that you have set for that particular column.

3. Type the HTML code shown under the ORDSUMM.HTM heading below for the Northwind Order Summary Tabular form.

–or–

From the File menu, select Close, and in the Multi-record result output custom format file box, type the full path to the Ordsumm.htm file in the dbWeb\nwind subfolder, or click Browse to locate the file.

Figure 25. DBX editor for multi- record result output form

The Ordsum.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDSUMM.HTM

<HTML>

<HEAD><TITLE>Order Summary</TITLE></HEAD>

<BODY>

<TABLE>

<TR>

<TH ALIGN=LEFT VALIGN=BOTTOM><H1><I>Northwind Traders</I></H1></TH>

</TR>

</TABLE>

<HR>

<TR>

<TH ALIGN=LEFT>Company Name:</TH>

<TH ALIGN=LEFT>Order ID:</TH>

<TH ALIGN=LEFT>Order Date:</TH>

<TH ALIGN=LEFT>Required Date:</TH>

<TH ALIGN=LEFT>Shipped Date</TH>

<TH ALIGN=LEFT>Details:</TH>

</TR>

</TR>

\tbon\tmatch\tOrders\tOrder ID\t

<TR>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tCompany Name\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tOrder ID\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tOrder Date\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tRequired Date\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShipped Date\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tComputed\tcol\tC154435\t</I></TD>

</TR>

</TR>

\tboff\t

</TABLE>

<HR>

</BODY>

</HTML>

When you create a computed column, dbWeb generates a unique name that it assigns to the computed column. Because the Ordsumm.htm file has already been created for you, the internal name for the Detail computed column in this DBX file will differ from the one dbWeb assigned to the Detail computed column you created earlier in Tutorial B. You will need to synchronize the DBX file in order for it to work properly.

To synchronize the Order Summary DBX file

1. Open the Schema window for Order_Summary.

2. Click the DBX tab.

3. Click the Editor button beside the Multi record result output custom format file box to launch the DBX editor for the Ordsumm.htm file.

4. Search for the tag \tobj\tComputed\tcol\tC154435\t in Ordsumm.htm.

5. Select the entire tag and double-click the Detail computed column listed in the Tabular data columns. This replaces the selected tag with the correct internal name for the Detail computed column.

Now you can customize the Tabular form of the Order_Detail schema using Orddetm.htm, (the example multi-record .DBX file made especially for the Order_Detail schema), located in the dbweb\nwind subfolder of your Web server’s root folder for HTML documents.

To customize the Order Detail form

1. Open the Schema window for Order_Detail.

2. Click the DBX tab.

3. In the Multi record result output custom format file box, type the full path to the Orddetm.htm file in the dbWeb\nwind subfolder or click Browse to locate the file.

The Orddetm.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDDETM.HTM

<HTML>

<HEAD><TITLE>Order Detail</TITLE></HEAD>

<TABLE>

<TR>

<TH ALIGN=LEFT VALIGN=BOTTOM><H1><I>Northwind Traders</I></H1></TH>

</TR>

</TABLE>

<HR>

<TR>

<TH ALIGN=LEFT VALIGN=BOTTOM>Order ID:</TH>

<TH ALIGN=LEFT VALIGN=BOTTOM>Product ID:</TH>

<TH ALIGN=LEFT VALIGN=BOTTOM>English Name:</TH>

<TH ALIGN=LEFT VALIGN=BOTTOM>Unit Price:</TH>

<TH ALIGN=LEFT VALIGN=BOTTOM>Quantity:</TH>

<TH ALIGN=LEFT VALIGN=BOTTOM>Discount:</TH>

</TR>

</TR>

\tbon\tmatch\tOrder Details\tOrder ID\t

<TR>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tOrder Details\tcol\tOrder ID\t</I></TD>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tOrder Details\tcol\tProduct ID\t</I></TD>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tProducts\tcol\tEnglish Name\t</I></TD>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tOrder Details\tcol\tUnit Price\t</I></TD>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tOrder Details\tcol\tQuantity\t</I></TD>

<TD ALIGN=LEFT VALIGN=BOTTOM><I>\tobj\tComputed\tcol\tC141447\t</I></TD>

</TR>

</TR>

\tboff\t

</TABLE>

<HR>

</BODY>

</HTML>

When you create a computed column, dbWeb generates a unique name that it assigns to the computed column. Because the Orddetm.htm file has already been created for you, the internal name for the [Order Details].Discount * 100 computed column in this DBX file will differ from the one dbWeb assigned to the [Order Details].Discount * 100 computed column you created earlier in Tutorial B. You will need to synchronize the DBX file in order for it to work properly.

To synchronize the Order Detail DBX file

1. Open the Schema window for Order_Detail.

2. Click the DBX tab.

3. Click the Editor button beside the Multi record result output custom format file box to launch the DBX editor for the Orddetm.htm file.

4. Search for the tag \tobj\tComputed\tcol\tC141447\t in Orddetm.htm.

5. Select the entire tag and double-click on the [Order Details].Discount * 100 computed column listed in Tabular data columns. This replaces the selected tag with the correct internal name for the [Order Details].Discount * 100 computed column.

# Creating a Single-Record .DBX file

Recall from creating a multi-record .DBX file that Microsoft dbWeb also supports custom format files for single-record forms like its native freeform. The method for creating single-record .DBX files is very similar to custom format file creation for multi-record forms like Microsoft dbWeb’s native Tabular form.

To create a single-record .DBX file

1. Select the Order_Summary schema and click Modify.

2. Click the DBX tab.

3. Click Editor for Single record result output form.

Figure 26. DBX Editor for Single record result output form

This time, Microsoft dbWeb compiles the list of columns on the left from the columns you included in the Freeform. If you wish to edit this list (for example, adding new columns or deleting unwanted ones), you should do so from the Freeform tab panel of the Schema window.

You can begin typing your HTML code in the edit box on the right. If you need to reference a column in your custom form, simply choose a column listed on the left and double-click it. Microsoft dbWeb automatically inserts the column’s corresponding HTML tag at the current insertion point marked by the blinking cursor. Because Microsoft dbWeb consults the schema repository when wrapping dbWeb results with your HTML code, it respects all properties that you have set for that particular column.

4. Type the HTML code shown below for the Northwind Order Summary Freeform.

–or–

From the File menu, select Close, and in the Single record result output custom format file box, type the full path to the Ordsums.htm file in the dbWeb\nwind subfolder or click Browse to locate the file.

The Ordsums.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDSUMS.HTM

<HTML>

<HEAD><TITLE>Order Summary</TITLE></HEAD>

<BODY>

<BR>

<H2>Northwind Traders</H2>

<H2>Order Summary</H2>

</CENTER>

<HR>

<BR>

<TR>

<TH ALIGN=LEFT>Customer ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tCustomer ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Company Name:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tCompany Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Contact Name:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tContact Name\t</I></TD>

<TH ALIGN=LEFT>Contact Title:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tContact Title\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Address:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tAddress\t</I></TD>

</TR>

<TR>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tCity\t</I></TD>

<TH ALIGN=LEFT>Region:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tRegion\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Postal Code:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tPostal Code\t</I></TD>

<TH ALIGN=LEFT>Country:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tCountry\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Phone:</TH>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tPhone\t</I></TD>

<TD ALIGN=LEFT><I>\tobj\tCustomers\tcol\tFax\t</I></TD>

</TR>

<TR><TH ALIGN=LEFT>Order ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tOrder ID\t</I></TD>

<TH ALIGN=LEFT>Employee ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tEmployee ID\t</I></TD>

</TR>

<TR>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Ship Address:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Address\t</I></TD>

</TR>

<TR>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip City\t</I></TD>

<TH ALIGN=LEFT>Ship Region:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Region\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Ship Postal Code:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Postal Code\t</I></TD>

<TH ALIGN=LEFT>Ship Country:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Country\t</I></TD>

</TR>

<TR>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShip Via\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Order Date:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tOrder Date\t</I></TD>

<TH ALIGN=LEFT>Required Date:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tRequired Date\t</I></TD>

<TH ALIGN=LEFT>Shipped Date:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tShipped Date\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Freight:</TH>

<TD ALIGN=LEFT><I>\tobj\tOrders\tcol\tFreight\t</I></TD>

</TR>

</TABLE>

</CENTER>

<HR>

</BODY>

</HTML>

Now you can customize the Freeform of the Order_Detail schema using ORDDETS.HTM, (the example single-record .DBX file made especially for the Order_Detail schema), located in the /dbwebsamples/nwind subfolder of your Web server’s root folder for HTML documents.

To customize the Order Detail form

1. Open the Schema window for Order_Detail.

2. Click the DBX tab.

3. In the Single record result output custom format file box, type the full path to the Orddetm.htm file in the \dbWeb\samples\nwind subfolder or click Browse to locate the file.

The Orddets.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDDETS.HTM

<HTML>

<HEAD><TITLE>Order Detail</TITLE></HEAD>

<FORM>

<BR>

<H2>Northwind Traders</H2>

<H2>Order Detail</H2>

</CENTER>

<HR>

<BR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Order ID:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tOrder Details\tcol\tOrder ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Product ID:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tOrder Details\tcol\tProduct ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Supplier ID:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tSupplier ID\t</I></TD>

<TH ALIGN=LEFT VALIGN=TOP>Category ID:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tCategory ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Product Name:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tProduct Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>English Name:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tEnglish Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Quantity:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tOrder Details\tcol\tQuantity\t</I></TD>

<TH ALIGN=LEFT VALIGN=TOP>Unit Price:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tOrder Details\tcol\tUnit Price\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Quantity/Unit:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tQuantity Per Unit\t</I></TD>

<TH ALIGN=LEFT VALIGN=TOP>Units Stocked:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tUnits In Stock\t</I></TD></TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Units on Order:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tUnits On Order\t</I></TD>

<TH ALIGN=LEFT VALIGN=TOP>Reorder Level:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tReorder Level\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT VALIGN=TOP>Discontinued:</TH>

<TD ALIGN=LEFT VALIGN=TOP><I>\tobj\tProducts\tcol\tDiscontinued\t</I></TD>

</TR>

</TABLE>

</CENTER>

</FORM>

<HR>

</BODY>

</HTML>

# Creating a Custom Format File

One of Microsoft dbWeb’s best features is its ability to automatically generate the Hypertext Markup Language (HTML) code needed to create its default query and presentation forms. You can use this feature to your advantage by deriving your custom format files from Microsoft dbWeb’s generated HTML code. Most popular Web browsers contain a feature that allows you to view the source HTML code behind a Web page. You can then copy this source code to the system clipboard and paste it to your HTML editor of choice.

So far, you’ve already created a custom Tabular form and Freeform for the Northwind Order System. Customization of Microsoft dbWeb’s native presentation forms is not limited to just the Tabular form and Freeform. You can also change the look of the QBE form and the Insert form by using the copy-and-paste method.

Customizing the QBE Form

You can change the look of the QBE forms you created with the Order_Summary and Order_Details schemas, even without employing any .DBX file techniques, by setting certain properties pertaining to the columns involved in the query as well as to the entire form itself. The "Row sequence" and "Col sequence" properties, for instance, change the placement of the column labels and their corresponding entry controls on the form. The "Control type" property presents you with several types of entry controls supported by HTML. You are not limited to text boxes. With Microsoft dbWeb, you can automatically populate your selection controls (for example, combo and listboxes) with data from your database by specifying the #autofill# marker (or #autofill+# which instructs the selection control to populate itself and display a blank value indicating all rows by default) in the "Data value" property. You can assist browsers by specifying comparison operators through the "Data operator" property, such as "begins with", "contains", and "is less than", all of which they can use to build effective queries. With the "QBE form header" property, you can write HTML code that changes the background color and pattern of the QBE form. Microsoft dbWeb inserts this code in the header section of the HTML source it generates for the QBE form. Many of the example schemas that ship with Microsoft dbWeb, in fact, feature QBE forms that have been modified through properties and without the use of custom format files. You may find it useful to study the property settings of these QBE forms. After all, the appearance you wish to achieve for your QBE form (or Tabular and Freeform for that matter) may be accomplished solely through these properties, sparing you the time and effort of composing custom format files.

The QBE form is largely static because it is not necessary to populate the controls on this form at display time with data from the data source. Unless you specify #autofill# as the "Data value" property for a combo or listbox selection control on the QBE form, Microsoft dbWeb does not issue any queries to the data source before generating the HTML code that displays the form on the browser window. It is possible, therefore, to use a static HTML file based on the original QBE form instead of the getqbe dbWeb method. Like Microsoft dbWeb’s native QBE form, its static HTML counterpart contains all the necessary fields to construct the query and, most importantly, a Submit Query button that executes a getxresults DBWEB method. If the query yields more than one row in the result, getxresults displays the customized Tabular form; otherwise, it displays the customized Freeform.

To customize the Order Summary QBE form

1. Using a Web browser that allows you to view the source HTML for the currently loaded Web page, display the Order_Summary schema’s QBE form.

2. Save the HTML source code for the QBE form. If your browser does not provide you with the option to save a Web page as HTML but allows you to view the source in a separate window, choose that option now and copy the entire QBE form HTML source code to the system clipboard.

3. Open (or paste) the file in your favorite HTML editor or in Microsoft dbWeb’s .DBX file editor for a Single record result output custom format file, launched from the DBX tab panel of the Order_Summary Schema window.

4. Edit the file so that it approximately matches Ordsumq.htm, listed below and available in the dbWeb\nwind folder.

The Ordsumq.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDSUMQ.HTM

<HTML>

<HEAD><TITLE>Northwind Customer Orders</TITLE></HEAD>

<BODY>

<TABLE>

<TR>

<TH VALIGN=BOTTOM><H1><I>Northwind Traders</I></H1></TH>

</TR>

</TABLE>

<HR>

Please enter values for the fields below and press the <B>'Submit Query'</B> button

<B>

<BR>

<TR>

<TH ALIGN=LEFT>Company Name:</TH>

</TR>

<TR>

</TR>

<TR>

<TH ALIGN=LEFT>Region:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Postal Code:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Country:</TH>

</TR>

</TABLE>

<INPUT TYPE="checkbox" NAME="8,all,rows"> Use full-screen output even if more than one row is returned.

<BR>

Return no more than <INPUT VALUE="100" SIZE="4" NAME="9,9,9"> rows. [max allowed = 100]

<INPUT TYPE="SUBMIT" VALUE="Submit Query"> <INPUT TYPE="RESET" VALUE="Reset Defaults"> <A HREF="PageHelp.htm"><IMG ALT="[Help]" SRC="/dbweb/help.gif"BORDER=0></A> <A HREF="/"><IMG ALT="[Home]" SRC="/dbweb/home.gif"BORDER=0></A><HR> <A HREF="about.htm">About <I>dbWeb</I></A> <I>Send comments to: </I><A HREF="mailto:mhammet@northwind.com">mhammet@northwind.com</A><BR><HR>

</FORM>

</BODY>

</HTML>

Customizing the Insert Form

Northwind’s distributors have expressed their desire to add new products, update existing ones, and delete discontinued products. You will have to modify the Northwind Order System using Microsoft dbWeb to support these actions on the Products table.

The Order_Summary and Order_Detail schemas that you built in the previous chapter are solely for data presentation. The feature joins related tables in order to present data meaningfully. Although Microsoft dbWeb does not prevent you from using the Order_Detail schema to handle inserts, updates, and deletes on the Products table, it is good practice to delegate each of these tasks to different schemas or static HTML documents, especially if you decide to customize the default query and presentation forms.

The Northwind Order System channels all edit actions on the Products table through the Order_Products schema listed under the dbnwind dbWeb data source. By itself, Order_Products is sufficient to execute all insert, update, and delete requests as long as you continue to use the default query and presentation forms Microsoft dbWeb generates. Customization of these forms requires the creation of supporting schemas and static HTML documents that Order_Products calls to perform the requested action.

While Order_Products already comes as part of the dbWeb example schemas, you will need to specify the freeform custom database format file it will use.

To specify the freeform custom database format file

1. Open the Schema window for the Order_Products schema.

2. Click the DBX tab.

3. Click Browse for single-record .DBX files and search for the Ordprds.htm file. This file is located in the /dbwebsamples/nwind subfolder beneath your Web server’s root folder for HTML documents.

The Ordprds.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDPRDS.HTM

<HTML>

<HEAD><TITLE>Northwind Order System</TITLE></HEAD>

<BR>

<H2>Northwind Traders</H2>

<H2>Products</H2>

</CENTER>

<HR>

<BR>

<TR>

<TH ALIGN=LEFT>Product ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tProduct ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Supplier ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tSupplier ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Category ID:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tCategory ID\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Product Name:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tProduct Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>English Name: </TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tEnglish Name\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Quantity Per Unit:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tQuantity Per Unit\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Unit Price:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tUnit Price\t</I></TD>

</FORM>

</FORM></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Units In Stock:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tUnits In Stock\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Units On Order:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tUnits On Order\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Reorder Level:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tReorder Level\t</I></TD>

</TR>

<TR>

<TH ALIGN=LEFT>Discontinued:</TH>

<TD ALIGN=LEFT><I>\tobj\tProducts\tcol\tDiscontinued\t</I></TD>

</TR>

</TABLE>

<BR>

</CENTER>

<HR>

</BODY>

<BR> <A HREF="about.htm">About <I>dbWeb</I></A>

<I>Send comments to: </I>mhammet@northwind.com<BR><HR>

</HTML>

You must now link the Order_Products schema to the rest of the Northwind Order System. Recall that the Order_Detail schema references the Products table. Rather than building a Drilldown Automatic Link on the Product ID column, you can use a schema link so that Product ID joins both the Order_Detail and the Order_Products schemas.

To link the Order Products schema

1. Open the Schema window for the Order_Detail schema.

2. Click the Freeform tab.

3. Open the properties sheet for the Product_ID column.

4. Change the Automatic Link Type to Schema link, specifying Order_Products as the schema to jump to and [Order_Details]Products.Product ID=[Order_Products]Products.Product ID as the criteria.

The single-record .DBX file (Ordprds.htm) you specified in Order_Products wraps the schema’s freeform and features buttons to insert a new row and update or delete the currently displayed row. The insert button is linked to the Ordprdi.htm static HTML document. Clicking on this button would cause the insert form to display inyour Web browser window.

The Ordprdi.htm file (located in the dbWeb\samples\nwind folder) is a static HTML document that prompts for input from the user and posts the data to the database by calling dbWeb’s insert method. Examination of Ordprdi.htm reveals a structure similar to Order_Summary’s QBE .DBX file. In fact, it is based on Order_Summary’s default QBE form and was created using the copy and paste method described in the previous section.

The Ordprdi.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDPRDI.HTM

<HTML>

<HEAD><TITLE>Northwind Order System</TITLE></HEAD>

<BR>

<H2>Northwind Traders</H2>

<H2>Products</H2>

</CENTER>

Please enter values for the fields below and press the <B>'Submit Insert'</B> button

<HR><BR>

<TR>

<TH ALIGN=LEFT>Supplier ID:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Category ID:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Product Name:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>English Name:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Quantity Per Unit:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Unit Price:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Units In Stock:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Units On Order:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Reorder Level:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Discontinued:</TH>

</TR>

</TABLE>

<A HREF="about.htm">About <I>dbWeb</I></A>

<I>Send comments to: </I>mhammet@northwind.com<BR><HR>

</FORM>

</BODY>

</HTML>

Customizing the Delete Form

In the Order Products customized Freeform, if you click Delete, you run Microsoft dbWeb’s delete method. This method does not display any special form. Instead, it uses the key value of the currently displayed row in the SQL DELETE query it issues to the database and informs the user whether or not the delete action completed successfully.

# Creating an Update Form .DBX file

In the previous section, you customized the QBE and Insert forms for the Northwind Order System using static HTML documents created though the copy and paste method. While creation of the update form .DBX file employs similar techniques, the approach to customization is slightly different. Unlike the QBE and insert forms, the update form needs to be populated with data at display time; therefore, it cannot exist as a static HTML document. It is, however, a single-record form and can be wrapped using a single-record .DBX file within another schema.

This schema, named Order_Update, already exists among the sample schemas that shipped with Microsoft dbWeb. Upon comparing Order_Update and Order_Products, you will notice that their QBE forms, Tabular forms, Ins/Upd/del Forms, and Freeforms are similarly structured. Order_Products was duplicated to produce the Order_Update schema. This redundancy stems from the fact that both the Update and Freeform custom database format files fall under the single-record .DBX file category. Recall, however, that a schema can use only one single-record .DBX file, making it necessary to duplicate Order_Products.

The custom database format file for the update form, Ordprdu.htm, exists in the \dbWeb\samples\nwind subfolder beneath your Web server’s root folder for HTML documents.

To specify the Order Update custom database format file

1. Open the Schema window for Order_Update.

2. Click the DBX tab.

3. Click the Browse button for single-record .DBX files and search for Ordprdu.htm located in the dbweb\nwind subfolder beneath your Web server’s root folder for HTML documents.

The single-record .DBX file (Ordprds.htm) you specified in Order_Products wraps the schema’s Freeform and features buttons to insert a new row and update or delete the currently displayed row. The Update button executes Microsoft dbWeb’s getxfree dbWeb method for Order_Update. getxfree displays the wrapped single-record form for the specified schema.

The Ordprdu.htm file is listed below. It uses HTML tables, a feature that some Web browsers may not support. The sample code below also includes paths to resources, executable or otherwise, that may differ in your case, depending on the type of Web server you use and the destination folders you chose during Microsoft dbWeb’s installation. Double-check the code to ensure that these paths accurately reflect the location of these resources and change them accordingly.

ORDPRDU.HTM

<HTML><HEAD><TITLE>Northwind Order System</TITLE></HEAD>

<BR>

<H2>Northwind Traders</H2>

<H2>Products</H2>

</CENTER>

Please enter values for the fields below and press the <B>'Submit Update'</B> button

<HR><BR>

<TR>

<TH ALIGN=LEFT>Supplier ID:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Category ID:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Product Name:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>English Name:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Quantity Per Unit:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Unit Price:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Units In Stock:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Units On Order:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Reorder Level:</TH>

</TR>

<TR>

<TH ALIGN=LEFT>Discontinued:</TH>

</TR>

</TABLE>

<BR>

<A HREF="about.htm">About <I>dbWeb</I></A>

<I>Send comments to: </I>mhammet@northwind.com<BR><HR>

</FORM>

</BODY>

</HTML>

# Testing Customized Schemas

Congratulations! You have just customized the look of Northwind's ordering system. You have also added insert, update, and delete functionality to Northwind’s Products table. Now you are ready to test the new system by using your favorite Web browser.

To test your schemas

1. Type in the URL to display the customized QBE form. Remember that this form is merely a static HTML page; you do not need to use any of the dbWeb methods. For example, if your computer’s IP address is www.vectorlink.com and you followed the default installation of dbWeb, the URL would be http://www.vectorlink.com/dbWebsamples/nwind/ordsumq.htm

2. Type USA in the Country box and click Submit Query to show the customized multi-record form with the results of the query. Recall from Tutorial B that the action behind the Submit Query button in the customized QBE form is the dbWeb getxresults method. Getxresults is the DBX counterpart of getresults.

3. Click the Detail link in the first row, where Order # is shown as 10528. The resulting multi-record form is the Order_Detail schema's customized Tabular form.

4. Click the Product ID for Cabrales Cheese. The resulting single-record form is the Order_Products schema's customized Freeform.

Click the Insert button to display the customized Insert form (Ordprdi.htm). Like the customized QBE form for Order_Summary, this insert form is static. The action behind the Insert button does not use any dbWeb methods. The customized insert form itself, however, uses the dbWeb insert method to actually add a new record to the Products table. You can experiment with adding new records to the Products table on your own. Note that you can also customize the messages that appear after you attempt to add a new record through the Ins/Upd/Del property sheet of the Order_Products schema.

Click the Delete button to delete the current record (in this case, the product record for Cabrales Cheese) from the Products table. It does not involve any forms but instead runs the dbWeb delete method. You can experiment with deleting other existing records from the Products table on your own.

Click the Update button to display the customized Update form (Ordprdu.htm), which is really the Order_Update schema's single-record DBX. The Update button executes the dbWeb getxfree method, the DBX counterpart to the getfree method. Getfree forces dbWeb to display a schema's Freeform. The customized update form itself, however, uses the dbWeb update method to actually change an existing record in the Products table.

Note that the <FORM…> tag for the Update button in Ordprds.htm (see code listing) also submits the data for the record. This way, dbWeb can populate the fields in the customized Update form with the data that needs to be changed. This is typically the way parameters are passed between customized forms in dbWeb.

Let's practice updating a record with dbWeb now.

To update a record

1. In the customized single-record form for Cabrales Cheese, click the Update button to display the customized Update form.

2. Change the Unit Price to 2.50.

3. Click the Submit Update button. You can customize the success or fail messages that appear after clicking on this button through the Ins/Upd/Del property sheet of the Order_Products schema.

Once you have successfully updated the record, you may want to revisit it to see the changes that dbWeb made to the Products table.