Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Web Database Developer's Guide with Visual Basic 5

World Wide Web Database Developer's Guide with Visual Basic 5





-19-

Database Results Presentation Using HTML and CGI

This chapter focuses on the presentation of database information. Assume that users have submitted requests to your server, and your server and CGI programs now must respond to the user requests for information by executing programs that access your database and applications and obtain results. After the CGI programs obtain these results, they must be returned to the users in a format in which they can be used effectively. HTML 3.2 provides a number of methods that enable Web browsers to effectively present information and output from a variety of applications--in ways that are not only meaningful to the user but visually appealing as well. But as you'll see, by throwing in a few ActiveX controls and VBScript, you can create some truly outstanding presentations without a great deal of work.



This chapter briefly discusses issues of style, and then it more fully explores how you can use preformatted text, HTML-formatted text, graphics, and some forms elements to effectively and efficiently display the results of a user request. You'll also see how to use HTML 3.2 elements--specifically, tables, colors, and fonts--to enhance the readability, layout, and visual quality of the presentation. Along the way, you'll see several examples illustrating these techniques.

This chapter also explores techniques that use CGI programs to render database output in a variety of ways. You'll see how to use CGI programs to create tables of database output and lists of hypertext links based on results of a database query, as well as how to dynamically create other HTML forms complete with input objects such as checkboxes, radio buttons, and selection lists. These forms will be created dynamically by using the results of an initial database query.

The final section of this chapter is dedicated to an example that ties together all the concepts presented in this chapter. This example will show you how to present database information graphically to enhance the readability and interpretation of datasets returned in response to users' queries. In particular, you'll see how you can create various controls (such as Charts and ComboBoxes) and VBScripts dynamically with CGI programs and use them to create interactive charts that graphically summarize query results for your users.

What Are Data and Information?

Data often is described as a collection of raw, unprocessed, singular entities of information, whereas information is described as processed data. Processed data implies that the original data has undergone some value-added processing in order to become information. From a systems point of view, data is what is put into the system and then undergoes processing within the system. Information is what the system outputs.

This is certainly the case with all the nuggets of data put into a database. After processing a user query, sets of data are selected from the database, manipulated in various ways, and output as information--presumably with some relevance or importance to the user performing the query.

Because one person's information often can be another person's data, this chapter doesn't strictly differentiate between data and information; instead, the two terms are used interchangeably. This chapter also adopts the convention that information is virtually anything of importance to users, whether it is needed to make important business decisions, develop an environmental impact statement, fuel a decision-support system, provide input and feedback for intelligent tutoring systems, determine whether a vendor has a product in the color you want, ascertain which of your employees has the appropriate skills and background to work on that next big proposal, or just something that satisfies the personal curiosity of a user. The list is virtually endless.

What Is Information Presentation?

Raw data is queried and filtered to generate a data set that provides specific information. Presentation is the display of this data that has been designated as meaningful to the user.

It certainly is possible (and easy) to respond to a user query for information by filling the screen with several lines of unformatted, incomprehensible text, garish colors, and a jumble of pictures. But information presentation implies more than that. It implies that the information is presented in a way that makes sense for the data types being displayed and that the output is formatted so that the user immediately can do something useful with it, regardless of whether that something is reading a book excerpt or review, evaluating the technical specifications of some vendor's piece of hardware, learning about the potentially devastating effects of the next volcanic eruption in the Pacific northwest (complete with maps predicting areas of damage), or performing a detailed analysis of financial information in tabular form and accompanying pie-chart graphics.

Information presentation also implies that the information is presented with some forethought about how a user will navigate through it. Returning 100 pages of financial data tables and graphs as a single, scrollable document wouldn't make sense when the data could be presented with an index and paging capabilities. It's easy for the message to get lost through misuse of the media. The way data is presented should enhance its use rather than obscure it.

Data Types and Formats

A staggering number of data types is associated with database systems and other development environments on the market: CHAR, DATE, LONG, RAW, binary large objects (BLOBs), NUMBER, VARCHAR, MONEY, VARBINARY, INT, BIT, FLOAT, REAL, and so on. For the purposes of data presentation, though, most of these boil down to the following handful of constituent data types:

The remainder of this chapter focuses on the presentation of information that is essentially textual and graphical.

Textual Data

Textual data is used to present varying combinations of alphabetic characters, punctuation, words, numbers, and symbols (such as those used in mathematical formulas). Textual data can be presented in several forms: free-form, preformatted, tabular, or all of these forms.

Graphical Data

Graphical information is data displayed as a pictorial presentation. Common types of graphical data include pictures, photographs, images, figures, graphs, graphics, drawings, and maps.

Graphical information can be presented in black-and-white or color choices ranging from 256 colors for 8-bit color; 65,536 (64KB) colors for 16-bit color; and up to 16 million colors with 24-bit color graphics.

Numerous graphics formats and methods are used to store and render graphical information. Many of these are open formats, although some are proprietary. Others implement lossy compression methods, in which some data is deliberately discarded to reduce the size of the compressed file or to store data at a reduced resolution to decrease the use of disk space and to speed rendering times. Many graphics are uncompressed and at full resolution.

For your purposes, the most useful of these formats are those the majority of graphical Web browsers understand, interpret, and render for the user. These formats and their file extensions follow:

In addition to these formats, several other graphics file formats are in widespread use and understood by several of the major Web browsers. Also, helper applications and plug-ins can be invoked to render formats that the browser itself cannot handle.

Anchor Elements

Anchor elements aren't so much a type of data as they are a pathway to other data. They're covered in this chapter because they commonly are used in information presentations, enabling users to access other information with a simple click of a mouse. Anchor elements, along with the HREF attribute, enable you to specify URLs to data, information, services, and applications. Generating these links to information as a result of a database query is a common practice.

One example of such an application is a user-interface form enabling the user to query for all corporate monthly financial reports in which sales for a specific suite of products rose a certain percentage during a specified time. The result generated by the server might be a list of anchors to specific reports--perhaps with a brief synopsis of the reports' contents. Clicking the URL would cause the full reports' contents, complete with any associated graphics, to be rendered for the user. In this example, the URLs might be links to static HTML documents or links to server programs that read the report data from a database and generate the HTML on the fly.

Static Data

Static data is data presented to the user that does not change during the time it is in use. Textual data, reports, static graphics, URLs, and tables of these types of data typically are not updated while in use by the user.

Dynamic Data

Dynamic data, on the other hand, is data presented to the user that is in motion--changing periodically or in response to some user action. ActiveX controls provide many exciting possibilities for enabling users to dynamically tailor presentations (such as the style and attributes of charts and graphics derived from database query results) in response to actions taken on the client browser.

Animating graphics components of the information presentation is an obvious example. Many methods exist for dynamically creating information presentations. These techniques include server push (currently only supported by Netscape browsers), ActiveX controls, animated GIF images, helper applications for rendering video clips and audio snippets, and plug-in applications supported by browsers (such as Netscape and Java programs).

Data Presentation Using HTML 2.0

HTML 2.0 provides several elements that enable you to present various data types in meaningful and visually appealing ways. The following sections explore some of the most common elements.

Using <PRE>

The <PRE> tag is handy when you need to retain the original formatting of the source document used to create an HTML document. Using <PRE> causes text to be displayed in a fixed-width font. Using the <PRE> tag also causes original whitespace, carriage returns, and line feeds in the source document to be retained.


TIP: Because the fixed-width font varies between types of browsers, it's a good practice to test the document on different types of browsers to verify that the text will format correctly.

Often it is desirable to maintain the original document formatting because you might want to make documents in different formats available to users via Web browsers without having to convert the document contents to HTML. You also might want to quickly present the results of a database query without reformatting the results in HTML. Suppose that you maintain a database of products for your online store. A user can fill out an online form to obtain pricing information about your econo-widget products. When submitted, the form sends a SQL query like the following to your database server:

select name, id, price
    from widget_table
    where type="econo_widget" and price < $250.00
    order by price desc

The result of this query might be the following:


name               id       price
-----------------  -------  ------
Super-econo Widget 12345    $199.99
Budget Widget      98765    $189.45
Goodluck Widget    47586    $103.27

(3 rows affected)

For a browser to present the database results formatted exactly as shown here, you must surround the results with the <PRE> tag pair. Listing 19.1 shows how you can use <PRE> to retain the formatting of the original database results.

Listing 19.1. Using <PRE> to Retain the Formatting of the Original Database Results.

<HTML>

<HEAD>
<TITLE>PRE Tag to Maintain Database Results Formatting</TITLE>
</HEAD>

<BODY>

<CENTER>
<H1>Product Query Search Results</H1>
</CENTER>
<HR>

<P>
<STRONG>Using &ltPRE&gt to Maintain Formatting</STRONG><BR>
<PRE>
<!-- these are the actual results from the query -->
name               id       price
-----------------  -------  ------
Super-econo Widget 12345    $199.99
Budget Widget      98765    $189.45
Goodluck Widget    47586    $103.27

(3 rows affected)

</PRE>
</P>

<P>
<STRONG>Without &ltPRE/&gt</STRONG><BR>
<!-- lack of PRE tag allows browser to "format" the results -->
name               id       price
-----------------  -------  ------
Super-econo Widget 12345    $199.99
Budget Widget      98765    $189.45
Goodluck Widget    47586    $103.27

(3 rows affected)

</P>

</BODY>
</HTML>

Figure 19.1 shows the results of using the HTML <PRE> tag to retain the original format of the source data.

FIGURE 19.1.Using the HTML <PRE> tag to retain formatting.

The advantages of using the <PRE> tag instead of letting the browser strip whitespace and impose formatting is obvious in this example. Even with the use of <PRE>, though, the presentation leaves much to be desired. Some of the methods for dressing up this output are discussed later in this chapter.

HTML-Formatted Text

In many cases, you'll simply want to present the user with a body of text in which formatting data fields is not a great concern. In these cases, the browser strips excessive whitespace, ignores carriage returns, and performs automatic word wraps (all of which were shown in Figure 19.1). To enforce some degree of formatting on the rendered document, you can use HTML elements such as <P> and <BR>.

Inline Graphics Using <IMG>

Using graphics in your presentations can serve two important purposes. Obviously, they can make your presentations more attractive. They often can communicate complicated concepts more easily and clearly than a textual explanation. A single graph or chart can help make sense of a table full of numbers, for example. In other cases, a well-designed graphic can make the same point as several lines of text. Think of all the times somebody tried to explain something to you in words but just couldn't sufficiently communicate his point. Then he drew you a picture, and you immediately understood what he was attempting to communicate. That is the power of graphics.

Many Web-based applications, such as database-access and electronic-commerce applications, are perfectly suited for handling graphical data. Imagine a database application that enables the user to obtain the high and low temperatures for various United States cities during a specified date range. This is an application that conceivably could return many numbers the user would need to analyze. If the data also was returned in graphical form as a time-series graph, however, the user could rapidly glean much of the desired information simply by looking at the graph.

How often are you willing to spend your money based solely on a vendor's description of a product? Now imagine an online store that enables you to leaf through the pages of an electronic catalog. Graphics and images, along with the descriptive text, enable potential customers to see what they're getting and increase the likelihood of a sale. Anchors that provide hypertext links to additional product specifications easily could be included as well.

The <IMG> Tag

Using the HTML <IMG> tag enables you to insert graphics inline on the page. The general syntax follows:

... other HTML ...
<IMG SRC="url to your image" ALIGN="your desired alignment"
ALT="alternate text to display">
... other HTML ...

You can insert an image virtually anywhere you can insert text in an HTML document. The image is inserted inline in the body of the HTML page as if it were a single character of text (except it's the size of your image). In the following code, for example, the image is rendered on the page directly next to the emphasized text:

<EM>Text aligned</EM> <IMG SRC="omniscient.gif">

Note that images cannot be registered to a specific location on the page; in other words, there are no attributes that enable you to specify a coordinate location at which to render the image. SRC The SRC attribute value is the image that is to be placed inline in your presentation. As shown in the general syntax for <IMG>, the value of this attribute is a URL that points to the image file location. The SRC attribute must be specified. Note that various browsers support inline rendering of different image formats. The major browsers such as Microsoft Internet Explorer, Netscape Navigator, and Mosaic, all support the most common types--specifically, GIF and JPEG. A JPEG image might be specified as the following:

<IMG SRC="caffrey_technologies.jpg">

ALT The ALT attribute specifies a string of text to be displayed by browsers incapable of displaying the specified SRC image. Some browsers such as Microsoft's Internet Explorer use the ALT text immediately and replace it with the actual image after the image is loaded. If you use the following HTML code, for example, the browser immediately renders the text WWidgets-Logo and then replaces it with the image specified by IMG SRC after it is loaded:

<IMG SRC="wirkkala_widgets.gif" ALT="WWidgets-Logo">

ALIGN The ALIGN attribute specifies how text adjacent to the inline image should be aligned with the image. The values of this attribute are limited to top, bottom, and middle. The following code shows how an image is rendered on the left-hand side of the text and aligned so that the top of the image is on the same line as the text string:

<B>Here's our corporate logo</B><IMG SRC="omniscient.gif"
ALT="OMNISCIENT" ALIGN=top>

Rendering an Inline Image

Listing 19.2 shows how you can use <IMG> and the ALIGN attribute to display graphics inline with other text. Figure 19.2 shows the corresponding output. This code renders the same GIF image in three ways. The first case shows how the top edge of an image is aligned with preceding text. The second case illustrates how the vertical center line of an image is aligned with text that precedes and follows the inline image. The third case demonstrates aligning the bottom edge of an image with text.

Listing 19.2. Using <IMG> and ALIGN to display graphics inline with other text.

<html>

<head>
<title>Use of IMG Tag for Inline Graphics/Images</title>
<!-- Example: Use of IMG Tag for Inline Graphics/Images -->
</head>

<body bgcolor="#FFFFFF">

<h2 align="center">
<font color="#0000FF">
Examples of In-line
Graphics/Images
</font></h2>

<hr>

<p><b>Text aligned with top of image</b><img src="omni.gif"
alt="OMNI" align="top" width="173" height="71"> </p>

<p><b>Text aligned</b> <img src="omni.gif" alt="OMNI"
align="middle" width="173" height="71"> <b>with the middle of
image</b> </p>

<p><b>Text aligned with bottom of image</b> <img src="omni.gif"
alt="OMNI" width="173" height="71"> </p>
</body>
</html>

FIGURE 19.2. Using the <IMG> tag to embed inline graphics and images.

The ALT attribute specifies that the text string "OMNI" should be displayed if the browser is unable to load the image specified in the SRC URL. Notice in Figure 19.2 that there is a character of whitespace between the image and adjacent text in the bottom two images. This keeps the browser from placing the image directly adjacent to the text, as it does in the first image. You may have surmised that the <IMG> tag's attributes limit your capability to neatly flow text around inline images. Later sections in this chapter present some extensions to <IMG> attributes.

Presentations Using <SELECT> and <OPTION>

Historically, the <SELECT> element and <OPTION> subelement have not seen a great deal of use for presentations of information, such as the results of a database query. After all, they're form elements and were designed for acquiring user input. These lists, however, can function as both presentation devices and input elements.

An earlier example in this chapter used <PRE> to retain the original formatting of results from a simple database SQL query. Suppose that the result of a user's initial query is a list of 20 products, and you want the user to be able to obtain more detailed information on one or more of the products. It would be nice to save some screen space by displaying the initial query results in a scrollable option list and to provide a second round of user input that would enable the user to obtain details on select products. The code in Listing 19.3 shows how you could accomplish this, and Figure 19.3 illustrates the form.

Listing 19.3. Using OPTION Lists for Presenting Data.


<html>
<head>
<title>Using an OPTION list for data presentation</title>
<!-- Example - Using an OPTION list for data presentation
</head>

<body bgcolor="#FFFFFF">

<h1 align="center"><font color="#0000FF">Product Query Search
Results</font></h1>

<hr>
<!-- Declare start of form  -->
<!-- action specifies a routine to process listinput and -->
<!-- display additional information on the product -->
<form action="URL%20for%20cgi%20routine" method="POST">
<!-- this scrolled-list allows a single choice -->    
<p><em><strong>There were 10 Product Listings Meeting Your Criteria<br>

    Please Select One to Display Detailed Pricing and Product
    Information </strong></em></p>
    <p><select name="options" size="6">
        <option value="opt1">Acme Wid-jet</option>
        <option value="opt2">Super-econo Widget</option>
        <option value="opt3">Budget Widget</option>
        <option value="opt4">Widgets-R-Us #32</option>
        <option value="opt5">Omni Pro-Widget</option>
        <option value="opt6">Goodluck Widget</option>
        <option value="opt7">Noname Gadget</option>
        <option value="opt8">Naselle Gadget #A1</option>
        <option value="opt9">Caffrey Model-X</option>
        <option value="opt10">Widget Solutions</option>
    </select><!-- other options here --> </p>
    <p><input type="submit" value="Display Product Information"> </p>
</form>
</body>
</html>

Listing 19.3 illustrates a manually intensive way of making a presentation. The HTML for this example was all hand-coded. Presenting the results of a database query in lists (or any other form element, for that matter) can easily be accomplished by using programs that generate the required HTML automatically. Later sections of this chapter explore methods for developing these programs.

FIGURE 19.3. Using a list for data presentation.

Presentations Using HTML 3.2 and Netscape Extensions

Although HTML 2.0 enables you to do many things, it has definite limitations. Fortunately, the major browser manufacturers and HTML standards folks realized this and have been adding functionality to their browsers and trying to agree on the content of the HTML 3.2 standard. Additionally, each browser manufacturer continues to add non-standard features in a continuing effort to stay ahead of the other guys. Changes are occurring at a staggering pace. Just keeping up with the latest browser functionality can be a full-time endeavor. This situation is complicated further by each vendor's desire to incorporate functions that its competitors have not yet implemented or are implementing in a different manner. The following sections introduce several features of HTML 3.2 and some common extensions.


RESOURCE: The following site maintains a current listing of browser functionality, as well as sample programs and a variety of interesting links to other sites:
http://www.browserwatch.com/



Presentations Using <TABLE> Elements

<TABLE> elements also are well-suited to information presentations. This is true especially for presenting information extracted from a database. Information stored in a relational database is by nature organized in row/column formats. Therefore, results of SQL queries are suited perfectly to the use of tables to nicely format and present this information to the user. This section illustrates how you can accomplish this task.

The following code uses <TABLE> elements to format the query results. This code creates a table that is three columns wide and uses three table data tags for each row of database results presented in the table:

<TR>
<TD>data_field1</TD><TD>data_field2</TD>
<TD>data_field3</TD>
</TR>

This code enables you to logically present information about each data record in a separate row in the table. Again, this code demonstrates how data that is essentially row/column (database record/fields) by nature is perfectly suited for presentation in a table format.

In the resulting presentation, notice that the BORDER and CELLPADDING attributes provide a nice delineation between cells and cell contents and ensure that cell contents don't touch the borders (see Listing 19.4). Using <TABLE> also enables you to provide a descriptive heading for each column. The ALIGN attribute enables you to horizontally align cell contents, much as you would in a spreadsheet. Finally, the presentation of the database results in this format is by far the most logical, intuitive, and easy to read of all examples presented so far. Figure 19.4 shows the results of the code in Listing 19.4.

Listing 19.4. Using the <TABLE> Feature for Presenting Data.

<HTML>
<HEAD>
<TITLE>TABLE for Database Results Formatting</TITLE>
</HEAD>

<BODY>

<CENTER>
<H2>Product Query Search Results</H2>
</CENTER>
<HR>

<P>
<STRONG><EM>
There Were 10 Product Listings Meeting Your Criteria
</EM></STRONG>
</P>

<P>
<!-- TABLE used to format query results.  Note use of More -->
<!-- descriptive headings -->
<TABLE BORDER="1" CELLPADDING="4">
<TR>
<TH>Product Name</TH>
<TH>Product ID</TH>
<TH>Unit Price</TH>
</TR>
<TR>
<TD>Acme Wid-jet</TD><TD ALIGN="right">92234</TD><TD ALIGN="right">$244.95</TD>
</TR>
<TR>
<TD>Super-econo Widget</TD><TD ALIGN="right">12345</TD>
<TD ALIGN="right">$199.99</TD>
</TR>
<TR>
<TD>Budget Widget</TD><TD ALIGN="right">98765</TD>
<TD ALIGN="right">$189.45</TD>
</TR>
<TR>
<TD>Widgets-R-Us #32</TD><TD ALIGN="right">BA642</TD>
<TD ALIGN="right">$177.33</TD>
</TR>
<TR>
<TD>Omni Pro-Widget</TD><TD ALIGN="right">33-12</TD>
<TD ALIGN="right">$137.89</TD>
</TR>
<TR>
<TD>Goodluck Widget</TD><TD ALIGN="right">47586</TD>
<TD ALIGN="right">$103.27</TD>
</TR>
<TR>
<TD>Noname Gadget</TD><TD ALIGN="right">QR722</TD>
<TD ALIGN="right">$99.17</TD>
</TR>
<TR>
<TD>Naselle Gadget #A1</TD><TD ALIGN="right">33987</TD>
<TD ALIGN="right">$88.25</TD>
</TR>
<TR>
<TD>Caffrey Model-X</TD><TD ALIGN="right">X3379</TD>
<TD ALIGN="right">$79.75</TD>
</TR>
<TR>
<TD>Widget Solutions</TD><TD ALIGN="right">T-129</TD>
<TD ALIGN="right">$74.32</TD>
</TR>
</TABLE>
</P>

</BODY>
</HTML>

NOTE: Using <TABLE> elements to format data has barely been touched on here. You can do many fancy things, including nesting tables within tables, using graphics and images in table cells, and using anchors with HREF URL links to other documents and programs. You can even use <TABLE> elements to impose page-layout designs that include text and graphics. You should experiment with this powerful data-presentation tool.

FIGURE 19.4. Using <TABLE> elements to format database query results.

Using Popular Netscape Extensions

The HTML specification has some serious limitations when it comes to dressing presentations of information for your users. Often, the appropriate use of color, font styles, or page backgrounds can enhance your overall presentation and emphasize key components of the information provided to your users. Fortunately, Netscape introduced extensions to the HTML 2.0 standard that enable you to better control the overall look of the information on your pages. This functionality is provided by the <FONT> tag and the COLOR attribute, as well as the BACKGROUND attribute used with the <BODY> tag. Most of the major vendors have incorporated these extensions into the latest versions of their products. Additionally, vendors such as Microsoft continue to provide additional capabilities for using a variety of fonts, table cell background, border colors, and so on. Font Size and Color Netscape introduced the capability to specify font size and color changes for specific sections of text within an HTML document. Other browsers, such as Microsoft Internet Explorer and Mosaic, also support these features and have added their own extensions as well. Judicious use of fonts and colors can make a data presentation not only more attractive, but it can help outline and format a page to make the overall presentation more effective. The following code shows how you can display various font sizes, styles (in this case, those supported by Internet Explorer), and colors using this general syntax:

<FONT SIZE="desired_size" COLOR="#hex_value_for desired_color"
face="desired_style">your_text</FONT><BR>

In this case, FONT SIZE takes on an integer value from 1 to 8. COLOR takes on a hexadecimal value of the format #RRGGBB, which specifies the red, green, and blue components of the desired color. For example, #FF0000 specifies red text. face specifies the font style (such as Braggadocio) to use. Listing 19.5 shows how several font styles and colors can be added to a Web page and Figure 19.5 shows the results of this code.

Listing 19.5. Adding Font Styles and Colors to a Web Page.

<html>
<head>
<title>Colors and Fonts</title>
<!-- Example: Use of Font sizes Styles and Color-->
</head>

<body bgcolor="#FFFFFF">

<h1 align="center">Various Font Sizes, Styles and Colors</h1>

<hr>

<p>
<font color="#FF0000" size="1" face="Optimum">
This text is font size 1 and color red.</font><br>
<font color="#00FF00" size="2">
This text is font size 2 and color green.</font><br>
<font color="#0000FF" size="3" face="Modern">
This text is font size 3 and color blue.</font><br>
<font color="#FF8040" size="4" face="Impact">
This text is font size 4 and color orange.</font><br>
<font color="#0000FF" size="5" face="Braggadocio">
This text is font size 5 and color blue.</font><br>
<font color="#FF0000" size="6" face="Matisse ITC">
This text is font size 6 and color red.</font><br>
<font color="#00FF00" size="7" face="Britannic Bold">
Font size 7 and color green.</font><br>
<font color="#808080" size="7" face="Comic Sans MS">
Font size 8 and color grey.</font><br>
</p>
</body>
</html>

Page Backgrounds The BACKGROUND attribute extension enables you to fill in page backgrounds by tiling a specified image--most commonly, a GIF or JPEG image. Browsers can do this efficiently by loading the image a single time from the server and then pulling it out of the local cache as necessary for the tiling process.

FIGURE 19.5. Using different font sizes, styles, and colors.

You can specify a page for your HTML document by adding the BACKGROUND attribute to the <BODY> tag and specifying a URL that references the image file you want to tile as the background. The following code shows how to do this, and Figure 19.6 shows the results:

<BODY BACKGROUND="omni.gif">

FIGURE 19.6. Creating a background of tiled images.


CAUTION: Use page backgrounds with caution and forethought as to the content and color of data and other text presented on the page. It's too easy to choose a background image that makes the information difficult to read.

Making Text Flow Around Inline Images The following attribute extensions to <IMG> enable you to flow text around inline images:

HSPACE and VSPACE

BORDER

HEIGHT and WIDTH

ALIGN

CLEAR

These attribute extensions have been broadly accepted by most of the major browser vendors, such as Microsoft Internet Explorer and Mosaic. HSPACE and VSPACE These attributes enable you to specify the amount of space that should exist between an image and the text that flows around it. Both values are specified in pixels. For example, the following code specifies that a horizontal separation of 8 pixels should exist between the right side of the image and the specified text:

<IMG SRC="omni.gif" ALT="OMNI" ALIGN=left HSPACE=8 VSPACE=2>
<FONT SIZE=5>Since 1991

Additionally, the text will be vertically aligned 2 pixels below the top edge of the image. BORDER The BORDER attribute enables you to embed the <IMG> tag in an anchor so that it responds as a hypertext link after you click it. The BORDER attribute controls the width of the border drawn around an image to indicate it is a hypertext link. The value is specified in pixels; a value of 0 suppresses the border from being rendered. The following code specifies an image as a hypertext link with a border 2 pixels wide:

<A HREF="your_url_here"><IMG SRC="your_image_here" BORDER=2></A>

TIP: This technique enables you to use images as buttons to link to other documents or to invoke routines specified by the HREF URL in your anchor. Several publicly available images resembling buttons exist, and you can specify them in <IMG> so that you can add button bars to your presentations.

HEIGHT and WIDTH

Use the HEIGHT and WIDTH attributes to specify the height and width of the image or graphic to be displayed. If the specified height and width do not match the source image dimensions, the browser attempts to scale the image to fit the specified sizes. These attribute values are specified in pixels. The following code, for example, specifies that the image should be rendered at a size of 250 pixels high by 350 pixels wide:

<IMG SRC="omin.gif" HEIGHT=250 WIDTH=350>

But what if the source image file were only 150x200 pixels? In that case, the browser would rescale the source image data to fit the specified height and width.


CAUTION: Scaling the specified image can result in a degradation of quality in the rendered image. This process may result in blockiness, poor spatial resolution (reduced detail), and changes in the aspect ratio. In some cases, rescaling the image may result in slower image-rendering speeds. Often it's better to just specify an image of the desired dimensions.

ALIGN Extensions You might remember that in HTML, the ALIGN attribute can take on the values of top, middle, or bottom. In these cases, the value specifies how the inline image should be aligned with the baseline of contiguous text. In effect, the image acted as though it were a character of text and was subject to word wrapping along with the rest of the text. Extensions to the ALIGN attribute modify this behavior. The extended ALIGN attribute now can take values of left or right, which means that the image is rendered to the left or right side of the text and acts as a container for that text instead of wrapping with the text. In effect, the image boundary now determines where lines of text will wrap to the next line. This enables you to create pages of text with inline images where the text flows around the boundaries of the image. The following code specifies a left-aligned image (the image appears to the left of the text), for example:

<IMG SRC="your_image" ALIGN="left">your_text

Text is bounded by the right side of this image until the left-hand margin is clear to the edge of the browser window, at which point it is bound by the browser and not the right side of the image. This gives the appearance that the text has wrapped around the right side of the image and along the bottom of the image. You can see the results of this technique in Figure 19.7, in the section "Using HTML Extensions: An Example." CLEAR Use the CLEAR attribute to specify the end of the <IMG> container (note that there is no </IMG> tag). Using CLEAR in an HTML element stops the flow of text around an image. The following code illustrates use of the CLEAR attribute:

<IMG SRC="your_image"
ALIGN="left">your_text<BR CLEAR=LEFT>

Note that, if the supplied text was not long enough to reach the bottom of the image, <BR CLEAR=LEFT> indicates that the browser should page down until it finds a clear left margin (beyond the bottom of the image) before rendering any subsequent HTML. Using HTML Extensions: An Example The code in Listing 19.6 illustrates how you can use the HTML extensions previously presented to format a page. In this example, a left-aligned image is rendered to show you how to make text flow around the image, in effect making the image a container for that text. Additionally, this listing demonstrates how you can use an <IMG> tag in an <A HREF> anchor to allow an image to be used as a hypertext link.

Listing 19.6. Formatting a Page by Using the <IMG> HTML Extension.

<html>
<head>
<title>Using IMG Extensions</title>
</head>

<body bgcolor="#FFFFFF">

<h1 align="center"><em>Welcome to Omniscient Technologies</em></h1>

<hr>

<p><img src="omni.gif" alt="omni" align="left" clear="left" hspace="8"
vspace="2" width="173" height="71"><font size="5"><em>Since 1991,
Omniscient Technologies has been actively involved in image and
signal processing, expert systems, fuzzy logic, and neural</em></font><em>
</em><font size="5"><em>networks routinely used in development of
solutions to image and signal processing problems. Omniscient
provides customers with advanced solutions to technically
challenging problems in Remote Sensing, GIS, Simulation Systems
and imaging technologies such as Feature Extraction, Sensor
Fusion and Automatic Target Recognition. Omniscient is also
actively involved in the WWW phenomenon and is currently heavily
involved in the development of WWW Database Applications for our
clients.</em> </font></p>

<hr>

<address>
    <a href="http://www.omniscient.com"><img src="omni.gif"
    alt="OMNI" width="80" height="40"></a> <a
    href="mailto:kittel@omniscient.com">E-Mail:
    kittel@omniscient.com</a>
</address>
</body>
</html>

The ALIGN=left extension specifies that the omni.gif source image should be placed to the left of the text. The CLEAR attribute within the <BR> tag ends the wrapping of text at the right image boundary and continues the next line of text at full-screen width. The HSPACE and VSPACE attribute extensions provide fine control over how close the text appears next to the image. They give the page a polished, formatted, newsletter quality--a nice touch when presenting information such as company financial reports.

The ADDRESS portion of the code also illustrates several of the Netscape extensions. The original omni.gif image is scaled down considerably using the HEIGHT and WIDTH attribute extensions. Also, the scaled image is embedded in an anchor, with an HREF URL pointing to the corporate home page address. The BORDER attribute gives a slightly bolder border to identify this explicitly as a pushbutton link.

Figure 19.7 shows the result of Listing 19.6.

FIGURE 19.7. Using <IMG> attribute extensions.

Information Presentation Style Guidelines

A simple credo applies to developing Web-based applications: If you build it, they will come. But if you build it well, they will come in large numbers, again and again. The data and information you present should take center stage, and the tools you use to present it should facilitate the communication of that information.

HTML and vendor extensions provide a rich set of tools, techniques, and tricks for presenting information to your users. There can be a downside to all this functionality, though: It's easy to get caught up in the available "gee whiz" features. By adding too many bells and whistles, you detract from the very thing that brought users to your site in the first place--the information and services they want.

Here are some simple HTML style guidelines for presenting information to your users:

When using graphics and images, the following guidelines apply:

Presenting Database Output Dynamically Using CGI

Chapter 18, "The Common Gateway Interface (CGI)," presents an example that uses the <PRE> tag to perform on-the-fly formatting of query results fetched from a database. Although you can use the <PRE> tag to adequately format results from very simple queries, the resulting presentations might be visually unappealing. <PRE> also is very limiting when you want to present more than just simple textual data. What if you also want to provide nicely formatted tables, generate form input objects based on initial query results, add graphics, or provide links to other data types such as graphics, audio, or video? This section presents a number of methods for accomplishing all these things.

The Simple pubs Database Query Revisited

Chapter 18 presents an example illustrating how form input can be used by a Windows CGI program to perform simple queries against the Microsoft SQL Server pubs database. The next few examples here revisit and build on the CGI program presented in that example. These examples show methods for presenting the query results in a variety of ways by generating the necessary HTML on-the-fly. To refresh your memory, Figure 19.8 shows the pubs simple query form, along with the actual input used for the following examples. You'll learn how to perform the following tasks:

FIGURE 19.8. The pubs database query form showing input used for examples.


For the examples in the following sections, you'll concentrate on the portion of the CGI program associated with generating the HTML to present results to the user.


TIP: When reviewing the following examples, keep in mind that although they are presented independently, you can combine these techniques to produce comprehensive, visually appealing, and effective presentations. Remember that virtually anything you can code by hand using HTML can be implemented in a CGI program. Additionally, CGI programs enable you to integrate external applications to process database results and incorporate some very interesting presentations. Integrating ActiveX controls designed for graphing and charting data is an especially effective way to enhance your presentations.

Generating Database Output Formatted with an HTML <TABLE> Relational databases are composed of tables of data conceptually laid out in rows and columns. This format provides a logical way of dealing with the data. You're probably familiar with database reports full of rows and columns of text and numbers. Wouldn't it be nice if there were an easy way to improve the appearance and readability of these reports? Well, HTML provides that capability quite nicely. Figure 19.9 illustrates how database results from the simple pubs query can be formatted on-the-fly and presented to the user in table format. This output is generated by the following ACTION attribute that was specified in the form:

<form
 action="http://dkittel.clark.net:8080/cgi-win/pubs_tables.exe"
 method="POST">

FIGURE 19.9. The pubs database query results formatted by using <TABLE>.

The formatting in Figure 19.9 was accomplished by using the HTML <TABLE> container tag and <TABLE> elements within the portion of the CGI program that renders HTML output. Listing 19.7 shows the portion of the pubs_tables.exe CGI routine responsible for generating this output.

Listing 19.7. VB Code Used to Generate the Table Output Shown in Figure 19.9.

Result = SqlResults(SqlConn)
        If Result = SUCCEED Then
            Send ("<center>")
            Call HTMLTableStart("80", "1", "#00000F", "")
            h_array(0) = "Title"
            h_array(1) = "Price"
            Call HTMLTableHeaders("center", 2, h_array)
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                d_array(0) = SqlData(SqlConn, 1)
                d_array(1) = SqlData(SqlConn, 2)
                Call HTMLTableDataRow("center", 2, d_array)
            Loop
            Call HTMLTableEnd

            Send ("</center>")
        End If

This code spools to the results package the HTML code required to create a table and specify the table headers. It then enters a loop that does the following:

This looping continues until database results are exhausted, at which time the closing HTML tags necessary to end the table and the HTML document are printed. Note that this code relies on a set of simple HTML subprocedures developed to help make the program more modular and readable. These procedures simplify the process of creating HTML by enabling you to call routines that accept input for table attributes (such as width and colors) and arrays of data used to populate table header and data cells. These procedures in turn use the CGI32.BAS framework procedure Send() to spool results to the results package. The code for these subroutines is in the HTML.BAS module on the CD-ROM that accompanies this book; it also is shown in List- ing 19.8.

Listing 19.8. HTML.BAS Subprocedures Used to Format Database Output into HTML Tables.

Sub HTMLTableStart(width, border, bordercolor, bgcolor)
    Send ("<table width=" + width + "%" + " border=" + border + " bordercolor="
         Â + bordercolor + " bgcolor=" + bgcolor + ">")
End Sub

Sub HTMLTableHeaders(alignment, nelements, header_array)
    ndx = 0
    Send ("<TR>")
    For ndx = 0 To (nelements - 1) Step 1
        Send ("<TH align=" + alignment + ">" + header_array(ndx) + "</TH>")
    Next
    Send ("</TR>")
End Sub

Sub HTMLTableDataRow(alignment, nelements, data_array)
    ndx = 0
    Send ("<TR>")
    For ndx = 0 To (nelements - 1) Step 1
        Send ("<TD align=" + alignment + ">" + data_array(ndx) + "</TD>")
    Next
    Send ("</TR>")
End Sub

Sub HTMLTableEnd()
    Send ("</table>")
End Sub

Even though this is a simple example, you can extend it easily to produce fancier, more comprehensive formatted output. Assuming that you, as a CGI developer, have a fundamental understanding of the types of results returned from queries posed to the database, you can modify the loop in this program to allow results of more complex queries with order by, group by, and compute by clauses to be appropriately formatted in a table (or tables) to be rendered on your users' browsers. Additionally, you can enhance the supplied routines to create nested tables and set additional table and cell properties. Generating Database Output Formatted as a <FORM> Selection List Sometimes, the results of a database query can be quite lengthy. Inserting all this data as rows in a table might force the user to scroll through numerous pages to view the results. Alternatively, you could code more complex mechanisms to enable users to page back and forth between pages of tables. This might be warranted for some applications; however, for simple applications, it's often sufficient to display the results in a scrollable selection list. Figure 19.10 illustrates how nicely you can format database results from the simple pubs query on-the-fly and present it to the user in scrollable list format. This output is generated by the following ACTION attribute that was specified in the form:

<form
 action="http://dkittel.clark.net:8080/cgi-win/pubs_optlist.exe"
 method="POST">

The formatting in Figure 19.10 was accomplished by using the HTML <SELECT> container tag and elements within the portion of the CGI program that renders HTML output. Listing 19.9 shows the portion of the pubs_optlist.exe CGI routine responsible for generating this output.

FIGURE 19.10. The pubs database query results formatted as a selection list.

Listing 19.9. VB Code Used to Generate the Selection List Shown in Figure 19.10.

Call HTMLFormStart("myForm", "POST",
Â"http://www.omniscient.com/cgi-win/book_review.exe")
        Call HTMLSelectStart("mySelectList", 12)
        Result = SqlResults(SqlConn)
        If Result = SUCCEED Then
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                title = SqlData(SqlConn, 1)
                title_id = SqlData(SqlConn, 2)
                price = SqlData(SqlConn, 3)
                Call HTMLSelectOption(title_id, Chr(34) + title + Chr(34) +
                                   Â " ........... " + price)
            Loop
            Call HTMLSelectEnd
            Send ("<P>")
            Call HTMLButton("SUBMIT", "Get Book Review", "htmBtn")
            Call HTMLFormEnd
        End If

This code spools to the results package the HTML code required to create a scrollable selection list. It then enters a loop that does the following:

This looping continues until database results are exhausted, at which time the closing HTML tags necessary to end the selection list and the HTML document are printed. Notice that the CGI program was required to supply <FORM> container tags bracketing the portion of the generated HTML that would render the selection-list object. This code also relies on a set of simple HTML subprocedures developed to help make the program more modular and readable. These procedures simplify the process of creating HTML by enabling you to call routines that accept arrays of data used to create list option entries. These procedures, in turn, use the CGI32.BAS framework procedure Send() to spool results to the results package. The code for these subroutines is in the HTML.BAS module on the CD-ROM that accompanies this book; it also is shown in Listing 19.10.

Listing 19.10. HTML.BAS subprocedures used to format database output into HTML scrollable selection lists.

Sub HTMLFormStart(fname, method, action)
    Send ("<FORM name=" + fname + " method=" + method + " action=" +
         ÂChr(34) + action + Chr(34) + ">")
End Sub

Sub HTMLFormEnd()
    Send ("</FORM>")
End Sub

Sub HTMLSelectStart(sname, Size)
    Send ("<SELECT name=" + sname + " size=" + CStr(Size) + ">")
End Sub

Sub HTMLSelectOption(Value, optiontext)
    Send ("<OPTION value=" + Value + ">" + optiontext + "</OPTION>")
End Sub

Sub HTMLSelectEnd()
    Send ("</SELECT>")
End Sub

Once again, this is a simple example, but it illustrates some key points. You can save screen real estate by presenting results in a selection list. You can save users some effort by not requiring them to scroll several pages. Additionally, you might save yourself work by not having to code paging applications. You can develop CGI routines that can be used to dynamically create secondary forms based on the results of your users' queries. This yields a type of dynamism to your applications that is impossible to achieve with hard-coded static forms. You easily can modify this example to perform additional manipulation of the results data, to create pop-up menus or multiple selection-list objects, and so on. Generating Database Output Formatted as Lists of Hypertext Links Sometimes it's only necessary to provide a minimal response to an initial database query with the idea that the user will narrow her search for information based on the results of that initial query. In this case, it might be sufficient to display database results as a list of hypertext links that the user can click to obtain more information about the subject listed. These links can be references to other HTML documents, data of a variety of MIME types, or a CGI routine that performs some desired action.

Figure 19.11 illustrates how you can format database results from the simple pubs query on-the-fly and present it to the user as a list of hypertext links. In this example, the user can click a link to obtain additional information about the book title of interest. This output is generated by the following ACTION attribute that was specified in the form:

<form
 action="http://dkittel.clark.net:8080/cgi-win/pubs_links.exe"
 method="POST">

FIGURE 19.11. The pubs database query results formatted as a list of hypertext links.

The formatting in Figure 19.11 was accomplished by using the HTML <A HREF> anchor container tag within the portion of the CGI program that renders HTML output. Listing 19.11 shows the portion of the pubs_links.exe CGI routine responsible for generating this output.

Listing 19.11. VB Code Used to Generate the Formatted List of Hypertext Links Seen in Figure 19.11.

Result = SqlResults(SqlConn)
        If Result = SUCCEED Then
            Send ("<center>")
            Call HTMLTableStart("80", "0", "", "")
            h_array(0) = "Title"
            h_array(1) = "Price"
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                title = SqlData(SqlConn, 1)
                title_id = SqlData(SqlConn, 2)
                price = "$" + SqlData(SqlConn, 3)
                d_array(0) = HTMLLink(base_url + book_review_cgi +
                                   Âtitle_id, title)
                d_array(1) = price
                Call HTMLTableDataRow("left", 2, d_array)
            Loop
            Call HTMLTableEnd
            Send ("</center>")
        End If

In this example, a loop is entered that does the following:

<TD align=left>
<A HREF="http://www.omniscient.com/cgi-win/book_review.exe?BU1032">
 The Busy Executive's Database Guide</A></TD>
<TD align=left>$19.99</TD>

This looping continues until database results are exhausted, at which time the closing HTML tags necessary to end the selection list and the HTML document are supplied. This code also relies on a simple HTML subprocedure developed to help make the program more modular and readable. This procedure simplifies the process of creating HTML by enabling you to call a routine that accepts data used to create hyperlinks. This procedure, in turn, uses the CGI32.BAS framework procedure Send() to spool results to the results package. The code for this subroutine is in the HTML.BAS module on the CD-ROM that accompanies this book and is shown in the following snippet:


Function HTMLLink(href, linktext)
  HTMLLink = "<A HREF=" + Chr(34) + href + Chr(34) + "> " + linktext + "</A>"
End Function

Generating Database Output Formatted as a Set of <FORM> Checkboxes Figure 19.12 illustrates how you can format database results from the simple pubs query on-the-fly and present it to the user as a form with a set of form checkbox objects. In this example, the user can click a box or boxes and then click the Submit button to see book reviews about the book titles of interest. This output is generated by the following ACTION attribute that was specified in the form:

<form
 action="http://dkittel.clark.net:8080/cgi-win/pubs_cbox.exe"
 method="POST">

FIGURE 19.12. The pubs database query results formatted as a set of checkboxes.

The formatting in Figure 19.12 was accomplished by using the HTML <INPUT> tag within the portion of the CGI program that renders HTML output. Listing 19.12 shows the portion of the pubs_cbox.exe CGI routine responsible for generating this output.

Listing 19.12. VB Code Used to Generate the Formatted Set of Checkboxes Shown in Figure 19.12.

Call HTMLFormStart("myForm", "POST",
Â"http://www.omniscient.com/cgi-win/book_review.exe")
        Result = SqlResults(SqlConn)
        If Result = SUCCEED Then
            Send ("<center>")
            Call HTMLTableStart("80", "0", "", "")
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                title = SqlData(SqlConn, 1)
                title_id = SqlData(SqlConn, 2)
                price = SqlData(SqlConn, 3)
                d_array(0) = HTMLCheckBox("cb" + title_id, title_id,
                                   Âtitle, " ")
                d_array(1) = price
                Call HTMLTableDataRow("left", 2, d_array)
            Loop
            Call HTMLTableEnd
            Send ("<P>")
            Call HTMLButton("SUBMIT", "Get Book Review", "htmSubmitBtn")
            Call HTMLButton("RESET", "Reset", "htmResetBtn")
            Send ("</center>")
        End If

In this example, a loop is entered that does the following:


<TD align=left><INPUT type=checkbox name=cbBU1032 value=BU1032> The Busy
 Executive's Database Guide</TD>

This looping continues until database results are exhausted, at which time the HTML tags necessary to render Submit and Reset buttons and to close the HTML document are supplied. The code for HTML subroutines used is available in the HTML.BAS module on the CD-ROM that accompanies this book.

Creating Graphical Database Output Presentations Dynamically

In this chapter, you have seen some fairly powerful methods for dynamically creating presentations of database results. Many times, however, simply giving users access to raw database information, no matter how nicely formatted, does not ensure that the reader will make sense of the overall content. Providing a graphical representation of that same data, however, certainly can make understanding it far easier for the reader. In addition, a visual representation of the information tends to hold the reader's interest far longer than plain text.

The old adage A picture is worth a thousand words holds true in many cases when trying to make sense of large, often complex, datasets. Consider the daily stock information in your local newspaper. Staring at that jumble of stock prices and trending information can cause the most steadfast individual to become disinterested in the data content. When you present that same data in a graphical form, such as a bar graph or a pie chart, however, the reader instantly gains a better understanding of the meaning of the data. This is the power of data visualization, and it's not difficult for you to tap into this power. In the remainder of this chapter, you'll explore a comprehensive example that illustrates how to significantly improve your presentations by adding ActiveX controls (in particular, the Chart and ComboBox controls) and VBScripts that use many of the same CGI techniques you've learned so far. Figure 19.13 shows a bar chart (one of the many styles possible) generated by using the Microsoft Chart control.

FIGURE 19.13. A simple bar chart generated by using the Microsoft ActiveX Chart control.

The Microsoft Chart Control

The Microsoft ActiveX Chart control enables you to present data in graphical formats such as pie charts, bar charts, area charts, and line graphs. You can add this control to your Web applications in the same manner as any other ActiveX control. The Microsoft Chart control enables you to present data by using the following chart styles:

Unlike many ActiveX controls, the Chart control has no events and only two methods. These methods follow:

Even though it has no events and only two methods, the Chart control has many properties that enable developers to control the chart's appearance onscreen. Table 19.1 summarizes the many properties you can set explicitly by using OBJECT parameters or VBScript code.

Table 19.1. Microsoft Chart Control Properties.

Property Function
BackStyle Sets the background according to the following values:
Transparent: 0
Opaque: 1
ChartType Specifies the type of chart you want. The supported chart styles and their corresponding property values follow:
Simple pie: 0
Pie with wedge out: 1
Simple point chart: 2
Stacked point chart: 3
Full point chart: 4
Simple line chart: 5
Stacked line chart: 6
Full line chart: 7
Simple area chart: 8
Stacked area chart: 9
Full area chart: 10
Simple column chart: 11
Stacked column chart: 12
Full column chart: 13
Simple bar chart: 14
Stacked bar chart: 15
Full bar chart: 16
HLC stock chart: 17
HLC stock chart WSJ: 18
OHLC stock chart: 19
OHLC stock chart WSJ: 20
ColorScheme Specifies which set of five predefined colors (used to fill regions ) you want to use. Possible values are 0, 1, 2, 3, and 4.
Columns Specifies the number of columns in the data series.
DataItem Specifies data values identified by the RowIndex and ColumnIndex properties. To set data values, the RowIndex and ColumnIndex properties first must be set to reference the data point. To specify a value of 7 for row 3, column 2, for example, set the RowIndex property to 3; the ColumnIndex property to 2; and the DataItem property value to 7.
DisplayLegend Determines whether a legend is displayed by specifying one of these values:
Hide legend: 0
Show legend: 1
GridPlacement Determines how grids are drawn according to these settings:
Gridlines in the background (under chart): 0
Gridlines in the foreground (over chart): 1
HorizontalGrid,
VerticalGrid
Specifies horizontal and vertical grids.
PARAM tags Specifies the data values for the chart. For example,
<PARAM NAME="Data[0][0]" VALUE="9">
<PARAM NAME="Data[0][1]" VALUE="10">
<PARAM NAME="Data[0][2]" VALUE="11">
<PARAM NAME="Data[1][0]" VALUE="7">
<PARAM NAME="Data[1][1]" VALUE="11">
<PARAM NAME="Data[1][2]" VALUE="12">
RowIndex, Specifies the row and column indexes; used
ColumnIndex with the DataItem property.
RowName, ColumnName Used with the RowIndex and ColumnIndex properties to specify a name for the row and column. These names are used in legends and labels.
Rows Specifies the number of rows in the data series.
Scale Determines the percentage scaling factor. The default is 100 percent scaling. Valid values are in the range of 1 to 100.
URL Specifies a URL to a data file used to populate the chart.


The use of most of these control properties is demonstrated in the example in later sections of this chapter, with the exception of reading data from a file by using the URL property. Additional information on this and other properties is available from the online developer's reference that accompanies VB 5.

You can place the Chart control into your HTML or HTML layouts simply by using the OBJECT tag. The example in Listing 19.13, generated by using the ActiveX Control Pad, illustrates how a control for a bar chart might look in your HTML code. You saw the results of this code in Figure 19.13.

Listing 19.13. Creating a Bar Chart.

<OBJECT ID="iechart1" WIDTH=412 HEIGHT=269
 CLASSID="CLSID:FC25B780-75BE-11CF-8B01-444553540000">
    <PARAM NAME="_ExtentX" VALUE="10901">
    <PARAM NAME="_ExtentY" VALUE="7117">
    <PARAM NAME="Rows" VALUE="4">
    <PARAM NAME="Columns" VALUE="3">
    <PARAM NAME="ChartType" VALUE="14">
    <PARAM NAME="Data[0][0]" VALUE="9">
    <PARAM NAME="Data[0][1]" VALUE="10">
    <PARAM NAME="Data[0][2]" VALUE="11">
    <PARAM NAME="Data[1][0]" VALUE="7">
    <PARAM NAME="Data[1][1]" VALUE="11">
    <PARAM NAME="Data[1][2]" VALUE="12">
    <PARAM NAME="Data[2][0]" VALUE="6">
    <PARAM NAME="Data[2][1]" VALUE="12">
    <PARAM NAME="Data[2][2]" VALUE="13">
    <PARAM NAME="Data[3][0]" VALUE="11">
    <PARAM NAME="Data[3][1]" VALUE="13">
    <PARAM NAME="Data[3][2]" VALUE="14">
    <PARAM NAME="HorizontalAxis" VALUE="0">
    <PARAM NAME="VerticalAxis" VALUE="0">
    <PARAM NAME="hgridStyle" VALUE="0">
    <PARAM NAME="vgridStyle" VALUE="0">
    <PARAM NAME="ColorScheme" VALUE="0">
    <PARAM NAME="BackStyle" VALUE="1">
    <PARAM NAME="Scale" VALUE="100">
    <PARAM NAME="DisplayLegend" VALUE="0">
    <PARAM NAME="BackColor" VALUE="16777215">
    <PARAM NAME="ForeColor" VALUE="32768">
</OBJECT>

RESOURCE: Several third-party charting, graphing, and data-visualization controls are available (although most charge a fee). Several of these charts provide capabilities well beyond the relatively simple functions provided by the Microsoft Chart control. Many of these controls provide methods for reading data from a number of popular file formats, for example, and others provide additional chart styles and control over chart attributes such as size, colors, fonts, and labeling. Finally, data-visualization controls such as mapping controls and flowcharting controls, as well as data-processing controls for manipulating your data prior to presentation, can be obtained. The following Website provides an excellent starting point to find information on a variety of control types; it also enables you to download some trial versions:
http://www.activeX.com/index.html



The Publishers Sales Information Database Application

In the remaining sections of this chapter, you will explore a VB/CGI application that enables users to perform queries of sales information against the pubs database supplied with Microsoft SQL Server. This application enables the user to perform the following types of queries:

So far, you probably are familiar with this type of functionality. In fact, the basics of this functionality were covered in detail in Chapter 18. What is new about this example is the way in which results are presented after they are fetched from the database. This application enables users to view information retrieved in two ways:

This information is presented in graphical format using the Microsoft Chart object presented in the previous section. This is accomplished by extending the techniques used to dynamically create HTML intrinsic objects to ActiveX controls. But that isn't all the functionality provided to the user! After the chart is presented to users, they can change the style of chart used to display the database query results, the type of grid, and the color scheme. This functionality is provided through the use of ActiveX ComboBox controls and VBScript, both of which are created dynamically and added to the HTML document returned to the user. You'll see the following tasks demonstrated in this example:

The pubs_sales_info Query Form Figure 19.14 shows the pubs_sales_info.htm query form that enables users to retrieve various types of sales information from the pubs database. Listing 19.14 shows the code for this form, and Figure 19.15 shows the results of the query.

FIGURE 19.14. The Publishers Sales Information Database query form.

Listing 19.14. Using pubs_sales_info.htm to Create the Database Query Form Shown in Figure 19.14.

<html>
<head>
<title>Publishers Sales Information Database</title>
</head>

<body bgcolor="#FFFFFF">

<p align="center">
<font color="#0000FF" size="6" face="Garamond"><strong>
PublishersSales Information Database</strong></font></p>

<p align="center">
<font color="#000000" size="3" face="Arial">
Use the following form to query the <strong><u>pubs</u></strong>
database and obtain sales information for 
 particular<em><strong>authors</strong></em>,
<em><strong>titles</strong></em>, <em><strong>publishers</strong></em>
or<em><strong>stores</strong></em>. Information matching your query criteria
 will be returned as a bar chart indicating number of <em><strong>units
 sold</strong></em> or <em><strong>total dollars in
 sales</strong></em>.</font></p>

<form action="http://dkittel.clark.net:8080/cgi-win/pubs_sales_info.exe"
method="POST" name="SALESINFO">
    <div align="center"><center><table border="1" width="85%"
    bordercolor="#808080">
        <tr>
            <td valign="top" width="20%" bgcolor="#CDCDCD">
            <strong>Get Sales Info By:</strong></td>
            <td width="50%"><div align="left"><table border="0"
             cellspacing="0" width="100%">
                <tr>
                    <td width="15%" bgcolor="#CDCDCD">
                    <input type="radio" checked name="QUERYBY"
                    value="AUTHOR"><strong>Authors </strong></td>
                    <td width="50%" bgcolor="#CDCDCD"><strong>Lastname</strong>
                    <em><strong>like </strong></em><input
                    type="text" size="15" name="LNAME"
                    value="ALL"></td>
                </tr>
                <tr>
                    <td width="15%" bgcolor="#EFEFEF">
                    <input type="radio" name="QUERYBY"
                     value="TITLE"><strong>Books</strong></td>
                    <td width="50%" bgcolor="#EFEFEF"><strong>Book
                    title</strong><em><strong> like</strong></em>
                    <input type="text" size="15" name="TITLE"
                    value="ALL"></td>
                </tr>
                <tr>
                    <td width="15%" bgcolor="#CDCDCD">
                    <input type="radio" name="QUERYBY"
                     value="PUBLISHER"><strong>Publishers</strong></td>
                    <td width="50%" bgcolor="#CDCDCD">&nbsp;</td>
                </tr>
                <tr>
                    <td width="15%" bgcolor="#EFEFEF">
                    <input type="radio" name="QUERYBY"
                     value="STORE"><strong>Stores</strong></td>
                    <td width="50%" bgcolor="#EFEFEF">&nbsp;</td>
                </tr>
            </table>
            </div></td>
        </tr>
        <tr>
            <td valign="top" width="20%" bgcolor="#CDCDCD">
             <strong>Display  Info By:</strong></td>
            <td><div align="center"><center><table border="0"
            cellspacing="0" width="100%">
                <tr>
                    <td width="30%" bgcolor="#CDCDCD">
                    <input type="radio" checked name="INFOBY"
                    value="QTY"><strong>#Books Sold</strong></td>
                    <td valign="top" width="50%" bgcolor="#CDCDCD">
                    <input type="radio" name="INFOBY" value="SALES">
                    <strong>Total $Sales</strong></td>
                </tr>
            </table>
            </center></div></td>
        </tr>
        <tr>
            <td align="center" colspan="2" bgcolor="#EFEFEF">
            <input type="submit" name="SUBMIT"
            value="SUBMIT SALES QUERY"></td>
        </tr>
    </table>
    </center></div>
</form>
</body>
</html>

After a user submits a query using this form, the ACTION attribute specifies that the CGI program pubs_sales_info.exe be executed to perform the query and generate results. Figure 19.15 shows a sample of the results generated.

FIGURE 19.15. Results of the database query shown in Figure 19.14.

The pubs_sales_info CGI Program The pubs_sales_info Windows CGI program is invoked after the user submits the pubs_sales_info query form discussed in the previous section. It is responsible for interfacing with the pubs database on behalf of a Web client, posting a query, and presenting the results of that query back to the user. This program uses functions from CGI32.BAS Visual Basic framework (presented in Chapter 18) to obtain form input and send information to the results package that the Web server sent to the user. The program also relies on the VB API to SQL Server (presented in Chapter 18). HTML convenience functions developed for examples earlier in this chapter also were used.

The pub_sales_info program was developed using VB 5 (although all code should work with VB 4 as well); it consists of a single form (Form1.frm) and several modules:

Listing 19.15 shows the code for the main module cgi_chart.bas--the CGI_Main() procedure required by Windows CGI and the CGI32.BAS framework.

Listing 19.15. cgi_chart.basthe Main Module Used to Create the pubs_sales_info.exe Windows CGI Program.

Option Explicit
    Dim Login
    Dim Result
    Dim SqlConn

Sub CGI_Main()

    Dim chartTitle

    Dim d_array(100) As String
    Dim h_array(100) As String
    Dim params(100, 2)
    Dim rownames(100)

    Dim ndx
    Dim numParams
    Dim numNames

    ` variables for forming query
    Dim sqlDB As String
    Dim sqlSelect As String
    Dim sqlFrom As String
    Dim sqlWhere As String
    Dim sqlGroupBy As String
    Dim sqlHaving As String
    Dim sqlOrderBy As String
    Dim sqlCmdString As String

    ` variables for handling form data
    Dim bookTitle As String
    Dim authorLName As String
    Dim queryType As String
    Dim InfoType As String

    ` variables for handling db results
    Dim firstName
    Dim lastName
    Dim storeName
    Dim storeID
    Dim numSold
    Dim title
    Dim publisher

    `Load (but don't show) the form containing the
    `VBSQL.OCX control

    Load Form1

    SendHTMLStart

    Send ("<HTML>")
    Send ("<HEAD><TITLE>Publishers Sales Info Query Results</TITLE></HEAD>")

    Send ("<body bgcolor=#FFFFFF>")

    `Process form inputs and format the
    `appropriate SQL query

    queryType = GetSmallField("QUERYBY")
    InfoType = GetSmallField("INFOBY")

    Select Case queryType
        Case "AUTHOR"
            If InfoType = "QTY" Then
                sqlSelect = "SELECT authors.au_fname, authors.au_lname,
                                   Â Sum(titles.ytd_sales) "
                sqlOrderBy = " ORDER BY Sum(titles.ytd_sales)"
                chartTitle = "<H2 align=center><font color=#0000FF>Authors
                                   Â Sales By Units Sold</font></H2>"
            Else
                sqlSelect = "SELECT authors.au_fname, authors.au_lname,
                                   Â Sum(titles.ytd_sales*titles.price) "
                sqlOrderBy = " ORDER BY Sum(titles.ytd_sales*titles.price)"
                chartTitle = "<H2 align=center><font color=#0000FF>Authors
                                   Â Sales By Total Dollars (Thousands)</font></H2>"
            End If

            sqlFrom = " FROM authors, titleauthor, titles "
            sqlWhere = " WHERE titleauthor.au_id = authors.au_id AND
                          Â titles.title_id = titleauthor.title_id "
            sqlGroupBy = " GROUP BY authors.au_fname, authors.au_lname"
            authorLName = GetSmallField("LNAME")

            If authorLName = "ALL" Then
                sqlHaving = " HAVING (authors.au_lname Like `%') "
            Else
                sqlHaving = " HAVING (authors.au_lname Like `%" +
                                   Â authorLName + "%') "
            End If

        Case "TITLE"
            If InfoType = "QTY" Then
                sqlSelect = "SELECT titles.title, Sum(titles.ytd_sales) "
                sqlOrderBy = " ORDER BY Sum(titles.ytd_sales)"
                chartTitle = "<H2 align=center><font color=#0000FF>Book Sales
                                   Â By Units Sold</font></H2>"
            Else
                sqlSelect = "SELECT titles.title, Sum(titles.ytd_sales*price) "
                sqlOrderBy = " ORDER BY Sum(titles.ytd_sales*price) "
                chartTitle = "<H2 align=center><font color=#0000FF>Book Sales
                                   Â By Total Dollars (Thousands)</font></H2>"
            End If
            sqlFrom = " FROM titles "
            sqlWhere = " WHERE titles.price Is Not Null "
            sqlGroupBy = " GROUP BY titles.title "
            bookTitle = GetSmallField("TITLE")

            If bookTitle = "ALL" Then
                sqlHaving = " HAVING (titles.title Like `%') "
            Else
                sqlHaving = " HAVING (titles.title Like `%" + bookTitle + "%') "
            End If
        Case "PUBLISHER"
            If InfoType = "QTY" Then
                sqlSelect = "SELECT publishers.pub_name, Sum(titles.ytd_sales) "
                sqlOrderBy = " "
                chartTitle = "<H2 align=center><font color=#0000FF>Publisher
                                   Â Sales By Units Sold (Thousands)</font></H2>"
            Else
                sqlSelect = "SELECT publishers.pub_name,
                                   Â Sum(titles.ytd_sales*price) "
                sqlOrderBy = " "
                chartTitle = "<H2 align=center><font color=#0000FF>Publisher
                                   Â Sales By Total Dollars (Thousands)</font></H2>"
            End If
            sqlFrom = " FROM publishers, titles "
            sqlWhere = " WHERE  titles.pub_id = publishers.pub_id AND
                          Â ((titles.ytd_sales Is Not Null)) "
            sqlGroupBy = " GROUP BY publishers.pub_name "
            sqlHaving = " "
        Case "STORE"
            If InfoType = "QTY" Then
                sqlSelect = "SELECT stores.stor_name, stores.stor_id,
                                   Â Sum(sales.qty)"
                sqlOrderBy = " ORDER BY Sum(sales.qty)"
                chartTitle = "<H2 align=center><font color=#0000FF>Stores
                                   Â Sales By Units Sold</font></H2>"
            Else
                sqlSelect = "SELECT stores.stor_name, stores.stor_id,
                                   Â Sum(sales.qty*titles.price)"
                sqlOrderBy = " ORDER BY Sum(sales.qty*titles.price)"
                chartTitle = "<H2 align=center><font color=#0000FF>Stores
                                   Â Sales By Total Dollars</font></H2>"
            End If
            sqlFrom = " FROM sales, stores, titles "
            sqlWhere = " WHERE sales.stor_id = stores.stor_id AND
                          Â titles.title_id = sales.title_id "
            sqlGroupBy = " GROUP BY stores.stor_name, stores.stor_id "
            sqlHaving = " "

    End Select

    sqlCmdString = sqlSelect + sqlFrom + sqlWhere + sqlGroupBy +
         Â sqlHaving + sqlOrderBy

    `Initialize DB-Library
    If SqlInit() = "" Then
        Send ("<EM><B>The program encountered an error</B></EM><BR>")
        Send ("<EM><B>DB-Library was not properly initialized</B></EM>")
    Else
        `Get a Login record and set guest login attributes.
        Login = SqlLogin()
        Result = SqlSetLUser(Login, "Guest")
        Result = SqlSetLPwd(Login, "c0rnd0gs")
        Result = SqlSetLApp(Login, "webguest_pubs")

        `Get a connection for communicating with SQL Server.
        SqlConn = SqlOpen(Login, "OMNISCIENT")

        `Put the command into the command buffer.
        Result = SqlCmd(SqlConn, sqlCmdString)

        `Send the command to SQL Server and start execution.
        Result = SqlExec(SqlConn)
        Result = SqlResults(SqlConn)

        If Result = SUCCEED Then
            numParams = 0
            Call HTMLTableStart("100", "1", "#808080", "#EFEFEF")
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                Select Case queryType
                    Case "AUTHOR"
                        firstName = SqlData(SqlConn, 1)
                        lastName = SqlData(SqlConn, 2)
                        numSold = SqlData(SqlConn, 3)
                        rownames(numParams) = firstName + " " + lastName
                        params(numParams, 0) = "data[" + CStr(numParams) +
                                                    Â "][0]"
                        If InfoType = "QTY" Then
                            params(numParams, 1) = CStr(CInt(numSold))
                        Else
                            params(numParams, 1) = CStr(CInt(numSold / 1000))
                        End If
                    Case "TITLE"
                        title = SqlData(SqlConn, 1)
                        numSold = SqlData(SqlConn, 2)
                        rownames(numParams) = Left(title, 10) + "..."
                        params(numParams, 0) = "data[" + CStr(numParams) +
                                                    Â "][0]"
                        If InfoType = "QTY" Then
                            params(numParams, 1) = CStr(CInt(numSold))
                        Else
                            params(numParams, 1) = CStr(CInt(numSold / 1000))
                        End If
                    Case "PUBLISHER"
                        publisher = SqlData(SqlConn, 1)
                        numSold = SqlData(SqlConn, 2)
                        rownames(numParams) = Left(publisher, 8) + "..."
                        params(numParams, 0) = "data[" + CStr(numParams) +
                                                    Â "][0]"
                        params(numParams, 1) = CStr(CInt(numSold / 1000))
                    Case "STORE"
                        storeName = SqlData(SqlConn, 1)
                        storeID = SqlData(SqlConn, 2)
                        numSold = SqlData(SqlConn, 3)
                        rownames(numParams) = Left(storeName, 15) + "..."
                        params(numParams, 0) = "data[" + CStr(numParams) +
                                                    Â "][0]"
                        params(numParams, 1) = CStr(numSold)
                End Select

                numParams = numParams + 1

            Loop

            numNames = numParams
            params(numParams, 0) = "Rows"
            params(numParams, 1) = CStr(numParams)
            numParams = numParams + 1
            params(numParams, 0) = "Columns"
            params(numParams, 1) = "1"
            numParams = numParams + 1

            Send ("<TR><TH COLSPAN=3>" + chartTitle + "</TH></TR>")
            Send ("<TR><TD align=center COLSPAN=3>" +
                          Â MakeAxChart("pubsSalesChart", numParams, params, "AxChartLoad",
                          Â numNames, rownames) + "</TD></TR>")
            Send ("<TR><TD align=center><B>Chart Style</B><BR>" +
                          Â MakeAxComboBox("styleCBox") + "</TD>")
            Send ("<TD align=center><B>Grid Style</B><BR>" +
                          Â MakeAxComboBox("gridCBox") + "</TD>")
            Send ("<TD align=center><B>Color Scheme</B><BR>" +
                          Â MakeAxComboBox("colorCBox") + "</TD></TR>")

            Call HTMLTableEnd

        End If

    `Close connection and exit program.
        SqlExit
        SqlWinExit

    End If

    `Unload the form containing VBSQL.OCX control

    Unload Form1

    ` add scripts to handle user interactions with
    ` combo box controls
    SendVBScript ("D:\WebSite\htdocs\pubs_sales_scripts.txt")

    `Now return to complete HTTP
    Send ("</BODY></HTML>")

End Sub

Sub Inter_Main()
    MsgBox "This is a Windows CGI program"
End Sub

The pub_sales_info Windows CGI program does the following:

SELECT authors.au_fname, authors.au_lname, Sum(titles.ytd_sales)
FROM authors, titleauthor, titles
WHERE titleauthor.au_id = authors.au_id AND
      titles.title_id = titleauthor.title_id
GROUP BY authors.au_fname, authors.au_lname
HAVING (authors.au_lname Like `%')
ORDER BY Sum(titles.ytd_sales)

Figures 19.16 through 19.19 illustrate the results from various user queries with different chart-style modifications selected by the user after the results are displayed. These figures demonstrate the range of output that can be generated dynamically by pubs_sales_info.exe, as well as many of the types of user selectable settings that can be applied after the results are returned. Once again, ActiveX controls and VBScript have provided a powerful mechanism for dynamically creating interactive page content--in this case, from user-specific database query results.

FIGURE 19.16. Results of the database query requesting sales information by book (with the word computer in the title).

FIGURE 19.17. Results of the database query requesting sales information by publisher and presented by number of units sold.

FIGURE 19.18.Results of the database query requesting sales information by store and presented by number of units sold.

FIGURE 19.19. Results of the database query requesting sales information by all book titles and presented by number of units sold.

The MakeAxChart, MakeAxComboBox, and SendVBScript Routines This section presents a few details about pertinent routines in the AxChart, AxComboBox, and htmlstuff modules. Material covered in earlier sections is not repeated here (such as HTML convenience routines for creating tables and HTML intrinsic controls). The MakeAxChart Function The MakeAxChart function is part of the AxChart module and is used to create an instance of a bar chart object with some attributes hard-coded, such as the size. Notice that you could modify this function to avoid the use of hard codes and enable most properties to be set at runtime. Listing 19.16 shows the code for MakeAxChart.

Listing 19.16. Using the MakeAxChart Function to Create an Instance of an ActiveX Chart Control.

` Basic chart object attributes/values were
` generated using ActiveX Control Pad
` chart type 14 is a simple bar chart (n rows x 1 col per row)

Function MakeAxChart(id, nelements, params, subname, nnames, rownames)
   s = "<OBJECT id=" + Chr(34) + id + Chr(34) + "WIDTH=539 HEIGHT=352" + Chr(13)
   s = s + "CLASSID=" + Chr(34) + "CLSID:FC25B780-75BE-11CF-8B01-444553540000"
       Â + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "_ExtentX" + Chr(34) + "VALUE=" + Chr(34)
       Â + "14261" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "_ExtentY" + Chr(34) + "VALUE=" + Chr(34)
       Â + "9313" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "ChartType" + Chr(34) + "VALUE=" +
       Â Chr(34) + "14" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "HorizontalAxis" + Chr(34) + "VALUE=" +
       Â Chr(34) + "0" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "VerticalAxis" + Chr(34) + "VALUE=" +
       Â Chr(34) + "0" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "hgridStyle" + Chr(34) + "VALUE=" +
       Â Chr(34) + "3" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "vgridStyle" + Chr(34) + "VALUE=" +
       Â Chr(34) + "3" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "ColorScheme" + Chr(34) + "VALUE=" +
       Â Chr(34) + "0" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "BackStyle" + Chr(34) + "VALUE=" +
       Â Chr(34) + "1" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "Scale" + Chr(34) + "VALUE=" + Chr(34) +
       Â  "100" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "DisplayLegend" + Chr(34) + "VALUE=" +
       Â  Chr(34) + "1" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "BackColor" + Chr(34) + "VALUE=" +
       Â Chr(34) + "16777215" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "ForeColor" + Chr(34) + "VALUE=" +
       Â Chr(34) + "32768" + Chr(34) + ">" + Chr(13)

   ` build rest of params using input array here

   ndx = 0
   For ndx = 0 To (nelements - 1) Step 1
      s = s + "<param name=" + Chr(34) + params(ndx, 0) + Chr(34)
      s = s + " value=" + Chr(34) + params(ndx, 1) + Chr(34) + ">" + Chr(13)
   Next
   s = s + "</OBJECT>" + Chr(13) + Chr(13)

   ` Build script that sets row names on graph when window loads
   ` (this assumes n rows x 1 col per)
   vbs = "<SCRIPT Language=" + Chr(34) + "VBScript" + Chr(34) + ">" + Chr(13)
   vbs = vbs + "Sub " + subname + Chr(13)

   vbs = vbs + id + ".ColumnIndex=0" + Chr(13)

   ndx = 0
   For ndx = 0 To (nnames - 1) Step 1
      vbs = vbs + id + ".RowIndex=" + Chr(34) + CStr(ndx) + Chr(34) + Chr(13)
      vbs = vbs + id + ".RowName=" + Chr(34) + rownames(ndx) + Chr(34) + Chr(13)
   Next
   vbs = vbs + "End Sub" + Chr(13)
   vbs = vbs + "</SCRIPT>" + Chr(13) + Chr(13)

   MakeAxChart = s + vbs
End Function

MakeAxChart takes a 2D array of param names and values used to further specify the data to be displayed by the chart. Additionally, the function takes a 1D array of row names used to dynamically create an inline VBScript at runtime (the name of the script also is specified by a passed-in parameter). This script is used to set the RowName labels displayed on the chart. As you will see shortly, this script is called from a window_onLoad() procedure when the window initially is loaded in the client's browser. MakeAxChart returns a string that fully specifies a chart object that can be embedded into an HTML document. The base chart object used in this function first was created by using the ActiveX Control Pad. Code generated by this tool then was cut and pasted into this VB function. The MakeAxComboBox Function The MakeAxChart function is part of the AxComboBox module and is used to create an instance of a ComboBox object with some attributes hard-coded (such as the size). You can modify this function to avoid the use of hard codes and enable most properties to be set at runtime. Listing 19.17 shows the code for MakeAxComboBox.

Listing 19.17. Using the MakeAxComboBox Function to Create an Instance of an ActiveX ComboBox Control.

Function MakeAxComboBox(id)
   s = "<OBJECT id=" + Chr(34) + id + Chr(34) + "WIDTH=115 HEIGHT=24" + Chr(13)
   s = s + "CLASSID=" + Chr(34) + "CLSID:8BD21D30-EC42-11CE-9E0D-00AA006002F3"
       Â + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "VariousPropertyBits" + Chr(34) +
       Â "VALUE=" + Chr(34) + "746604571" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "DisplayStyle" + Chr(34) + "VALUE=" +
       Â Chr(34) + "3" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "Size" + Chr(34) + "VALUE=" + Chr(34) +
       Â "3037;635" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "MatchEntry" + Chr(34) + "VALUE=" +
       Â Chr(34) + "1" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "ShowDropButtonWhen" + Chr(34) + "VALUE="
       Â + Chr(34) + "2" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "FontCharSet" + Chr(34) + "VALUE=" +
       Â Chr(34) + "0" + Chr(34) + ">" + Chr(13)
   s = s + "<PARAM NAME=" + Chr(34) + "FontPitchAndFamily" + Chr(34) + "VALUE="
       Â + Chr(34) + "2" + Chr(34) + ">" + Chr(13)
   s = s + "</OBJECT>" + Chr(13) + Chr(13)

   MakeAxComboBox = s

End Function

MakeAxComboBox takes a single parameter that is used to set the object's ID. Note that no other properties associated with the object are set at this time. Instead, CGI_Main calls the SendVBScript procedure to embed VBScript that is invoked when the document is loaded. It is here that options and the initial settings of the ComboBox objects are set. MakeAxComboBox returns a string that fully specifies a ComboBox object that can be embedded into an HTML document. The base ComboBox object used in this function first was created by using the ActiveX Control Pad. Code generated by this tool then was cut and pasted into this VB function. The SendVBScript Procedure SendVBScript simply reads a text file that contains VBScript code that needs to be embedded into an HTML document. It then uses the CGI32.BAS Send() function to forward this to the response package that is returned to the Web client browser. Listing 19.18 shows the code for SendVBScript.

Listing 19.18. Using the SendVBScript Procedure to Read a File Containing VBScript Code and Embedding the Script
Into an HTML Document.

Sub SendVBScript(filepath)
   Open filepath For Input As #11
   Do While Not EOF(11)                 ` loop while not end-of-file
       Line Input #11, LineOfText       ` read line into variable
       Send (LineOfText)                ` send string to results package
    Loop
    Close #11
End Sub

The pubs_sales_info.exe CGI program uses this procedure to read the pubs_sales_script.txt file and send its contents to the response package. Listing 19.19 shows the contents of pubs_sales_script.txt.

Listing 19.19. Using the pubs_sales_script.txt file, Which Contains VBScript Code to be Embedded in the HTML Document Returned to a User.

<SCRIPT LANGUAGE="VBSCRIPT">
<!--

Sub window_onLoad()

   styleCBox.AddItem "2 - Point"
   styleCBox.AddItem "5 - Line"
   styleCBox.AddItem "11 - Column"
   styleCBox.AddItem "14 - Bar"
   styleCBox.Text="14 - Bar"

   gridCBox.AddItem "0 - None"
   gridCBox.AddItem "1 - Solid"
   gridCBox.AddItem "2 - Bold"
   gridCBox.AddItem "3 - Dotted"
   gridCBox.AddItem "4 - Bold Dotted"
   gridCBox.Text = "3 - Dotted"

   colorCBox.AddItem "0 - Red"
   colorCBox.AddItem "1 - Black"
   colorCBox.AddItem "2 - Beige"
   colorCBox.AddItem "3 - Blue"
   colorCBox.AddItem "4 - Green"
   colorCBox.Text = "0 - Red"

   ` call the vbscript that was dynamically added to the
   ` chart object used to plot pubs sales data

   call AxChartLoad

End Sub


Sub colorCBox_Change()
   pubsSalesChart.ColorScheme = CInt(Left(colorCBox.Text, 1))
End Sub

Sub gridCBox_Change()
   pubsSalesChart.hgridStyle = CInt(Left(gridCBox.Text, 1))
   pubsSalesChart.vgridStyle = CInt(Left(gridCBox.Text, 1))
End Sub

Sub styleCBox_Change()
   pubsSalesChart.ChartType = CInt(Left(styleCBox.Text, 2))
End Sub

-->
</SCRIPT>

Once embedded into the HTML document, this code is invoked by the windows onLoad event. Therefore, when the document is loaded by the client browser, this code is executed and is used to set options in the ComboBoxes that were created to enable users to modify the attributes of the chart that display results of the users' database queries. Additionally, the code defines other VBScript procedures used to handle interactions with the ComboBoxes. A change in setting on the styleCBox ComboBox, for example, results in the chart style being changed onscreen. Finally, note the call to AxChartLoad. This is the VBScript procedure name passed to MakeAxChart and used to generate an inline script for setting the row names displayed on the charts. Including it here ensures that the row names are set when the document is loaded by the client browser. A Few Closing Comments about This Example Here are a few closing comments about this application:

Summary

After defining data, information, and an information presentation, this chapter provided an overview of the data types and formats you're likely to encounter. You next looked at using HTML 2.0 elements for data presentations. You examined information on using preformatted text, HTML-formatted text, inline graphics, and the <IMG> element and its attributes. Finally, using the <SELECT> list as a user-input form element and a data-presentation tool was explained.

Examples of HTML 3.2 data-presentation techniques were introduced. Using the <TABLE> element as a formatting tool was discussed in great detail. Extensions such as color, font size, and page backgrounds were discussed, with a focus on using them to enhance presentations. Extensions to the <IMG> element attributes also were presented. Examples showed how these extensions enable you to develop data presentations in which text wraps around inline graphics and images. This section closed with a brief mention of the new HTML 3.2 <FIG> container element.

After a brief summary of style guidelines for developing effective presentations, you jumped right into examples of how you can use CGI programs to dynamically create presentations of data fetched from your databases. These examples included generating HTML tables, hypertext links, and <FORM> elements such as selection lists and checkboxes. You learned that not only can you combine these techniques to produce nicely formatted data presentations, but that you also can use them to dynamically generate additional forms based on the results of a user's query.

The final section presented a sample application that illustrated how you can develop graphical, interactive presentations by using CGI programs to create ActiveX controls and VBScript on-the-fly. The techniques in this example included a number of VB modules that provide a powerful foundation you can use and expand on for developing your own applications.