One of the side-effects of the Internet phenomenon is the adoption of World Wide Web technology for the dissemination of information over organization-wide LANs and WANs. Private networks that deliver documents encoded in HyperText Markup Language
(HTML) are called intranets. The public Internet gets most of the publicity today, but the majority of Visual Basic database developers are likely to derive much more current revenue from developing intranet, rather than Internet, applications. When
electronic commerce on the Internet becomes a reality, probably in 1997 and beyond, developing Internet-based interactive home shopping and related applications is likely to become a major source of income for Visual Basic developers.
This chapter begins with an overview of Microsoft's Internet and intranet technologies, as announced in Microsoft's December 7, 1995 "Embrace and Extend" presentation and described in greater detail at Microsoft's March 1996 Professional
Developer's Conference. A sample application follows that uses a beta version of Microsoft's HTML OLE Control to create a Visual Basic 4.0 Web browser application with client-side database connectivity. The chapter ends with a preview of Internet Explorer
(IE) 3.0, Visual Basic Script (VBScript), and lightweight ActiveX OLE Controls for creating interactive Web pages. (IE 3.0, VBScript, and ActiveX controls were in the alpha-test stage when this edition was written.)
This chapter assumes general familiarity with Web browsers and HTML-encoded documents, but not expertise in the design and creation of Web pages. Designing Web or intranet sites and writing HTML-encoded documents is beyond the scope of this book. The primary content of this chapter is related specifically to connecting databases to intranet and Internet server and client applications. You need Internet Information Server 1+ running on Windows NT Server 3.51+ to use the Visual Basic browser sample application of this chapter.
Microsoft Corporation "owns the desktop" in the realm of PC operating systems and productivity applications, but Novell, Inc., today retains the largest share of the PC networking market, and Netscape, Inc., dominates the Web browser business
as well as, to a lesser extent, the Internet and intranet server market. Microsoft's aggressive marketing of Windows NT has brought Windows NT 3.51+ to a point where it's likely that more than half of all new server installations in the second half of 1996
and beyond will run Windows NT. In early 1996, Microsoft launched a full-scale attack on the Internet and intranet market with its Internet Explorer 2.0, Internet Information Server (IIS) 1.0, and a variety of proposed Internet standards based on the
Common Object Model (COM). Whether Microsoft can gain a majority share of the Web browser and server market by giving away free copies of IE and IIS remains to be seen. Even if not, Microsoft's role in the continued development of Web technology and
standards is of great importance to Visual Basic database developers.
Microsoft missed the first round of the desktop database market. Ashton-Tate's (later Borland's) dBASE and, to a lesser extent, Borland's Paradox had a virtual lock on the character-based PC database development. (At its peak of popularity, dBASE claimed about 4.5 million users.) Microsoft Access now is by far the top-selling desktop database development platform. Microsoft's claim that there are four million "VBA developers" may be somewhat inflated, but there is no question that the various flavors of Microsoft's "Embedded Basic," as it was originally known, are today's most popular application programming languages.
Microsoft's Internet strategy is founded on easing the development burden for creating interactive Web pages. Netscape plug-ins, Java applets, and JavaScript are designed to add interactivity to Web pages, but writing plug-ins, Java applets, and
JavaScript code requires experience with C++ programming. Using COM as the underlying technology for interactive Web browsers and providing VBScript to manipulate lightweight, downloadable OLE Controls brings interactive Web page programming to the million
or more users of Visual Basic and VBA. As a result of Microsoft's emphasis on interactivity, the names of most of the firm's new Internet technologies carry an "Active" prefix. The sections that follow briefly describe the Microsoft ActiveX
Internet environment with emphasis on the "hooks" included for database connectivity.
Internet and intranet applications intrinsically are client/server applications. The server stores Web pages as .htm or .html files and delivers them to client browser applications over the network (Internet or intranet) on demand. Although the
preceding description has a "file-sharing" connotation, high-performance Internet servers are designed to run as a service on application, not file- and printer-sharing servers. When this edition was written, the majority of Internet servers ran
under various flavors of UNIX. Windows NT, like UNIX, is optimized for application server use, thus Windows NT is rapidly gaining ground on UNIX as the platform of choice for hosting Web sites and intranet. One of the primary reasons for Windows NT's
success in the Internet server business is Windows NT Server 3.5+'s ease of installation, administration, and maintenance.
On the server side, Microsoft intends to extend the concept of its integrated BackOffice services suite to the Internet and especially to intranets. Microsoft SQL Server 6+ is the preferred RDBMS for delivering database content to Web pages. SQL Server
6.5 includes a Web Page Wizard, which automatically creates HTML-encoded Web pages from data in SQL Server tables. Microsoft Media Server (formerly code-named Tiger), which is designed to deliver on-demand MPEG-2 encoded video content over wideband
networks (including intranets), is expected to become a part of Microsoft BackOffice in late 1996. The sections that follow describe the primary components of Microsoft's Internet and intranet server-side initiative.
Another factor in the success of Windows NT as an Internet server platform is low implementation cost. Windows NT Server 3.51 has an estimated retail price (ERP) of $999 and includes IIS 1.0. Internet Information Server 1.0 is free when downloaded from
http://www.microsoft.com/infoserv. (A retail version is available for an ERP of $99). Microsoft claims you can install IIS 1.0 under Windows NT Server 3.51 in less than five minutes. (Installing IIS 1.0, which is used for the sample applications later in
this chapter, on the OAKLEAF0 server running the beta version of Windows NT Server 4.0 took about three minutes.) IIS 1.0 automatically installs World Wide Web, file transfer protocol (FTP), and gopher services on your server. You can independently start,
pause, continue, or stop each Internet service.
Internet Information Server 1.0 creates an \inetsvr folder on the server, then installs admin, scripts, wwwroot, and other subfolders in \inetsvr. The \inetsvr\admin folder contains the Internet Service Manager and other files required to aid in
administering IIS 1.0's features. The wwwroot folder and its subfolders store HTML-encoded Web page files; \inetsvr\wwwroot\Default.htm is the home page when you specify the http://servername Uniform Resource Locator (URL) in a browser application.
The sample Default.htm file, which is installed when you set up IIS 1.0, provides hypertext links to several sample pages that demonstrate database connectivity and to a sample Web site, Volcano Coffee Company.
When this chapter was written, Microsoft had announced the forthcoming availability (but not an estimated release date) of an upgraded IIS version 1.1. IIS 1.1 is expected to include a number of new server-side programming features, including support for the use of VBA for writing server administration and server database applications.
The Internet Database Connector (IDC) uses Httpodbc.dll to provide 32-bit ODBC connectivity to RDBMSs such as SQL Server 6+ and Jet 3.0 .mdb files using the SQL Server and Access ODBC database drivers, respectively. Httpodbc.dll relies on .idc text
scripts and .htx HTML scripts, stored in the \inetsvr\samples\scripts folder. The .idc files contain data source and query details, plus the name of the associated .htx file; .htx files are templates for temporary .htm files that display query result sets
in HTML tables.
Following is the content of the \inetsvr\scripts\samples\sample2.idc file used by the \inetsvr\wwwroot\samples\dbsamp\dbsamp2.htm page to display the names of authors whose books have sold more than the amount specified by the %sales% variable:
Datasource: Web SQL Username: sa Template: sample.htx SQLStatement: +SELECT au_lname, ytd_sales + from pubs.dbo.titleview + where ytd_sales > %sales%
Web SQL is an SQL Server 6+ data source for the pubs sample database, which includes the titleview VIEW. The plus signs (+) in the .idc file concatenate text across new lines.
The %sales% variable for sample2.idc is supplied by a HTML form, dbsamp2.htm (see Figure 21.1), with a text box created by the following HTML code:
<FORM METHOD="POST" ACTION="/scripts/samples/sample2.idc"> <P> Enter YTD sales amount: <INPUT NAME="sales" VALUE="5000" > <P> <INPUT TYPE="SUBMIT" VALUE="Run Query"> </FORM>
When you click the Run Query button to submit the form elements, the value entered in the text box is transmitted to the corresponding .idc file variable. The query result appears in a temporary HTML page created from the .htx template file specified
in the .idc file. The HTML code for the sample.htx template appears in Listing 21.1. The %if expression eq%. . .%else%. . .%endif% conditional statement is similar to the conditional compilation directives of Visual Basic 4.0. Data from the query is
displayed in a table created between the %begindetail% and %enddetail% directives. Figure 21.2 shows an example of the page created by specifying a sales value of 2000.
Listing 21.1. An example of HTML code to implement an IDC template file (from the database examples supplied with Microsoft Internet Explorer 1.0)
<HTML> <HEAD><TITLE>Authors and YTD Sales</TITLE></HEAD> <BODY BACKGROUND="/samples/images/backgrnd.gif"> <BODY BGCOLOR="FFFFFF"> <TABLE> <TR> <TD><IMG SRC="/samples/images/SPACE.gif" ALIGN="top" ALT=" "></TD> <TD><A HREF="/samples/IMAGES/db_mh.map"><IMG SRC="/SAMPLES/images/db_mh.gif" ismap BORDER=0 ALIGN="top" ALT=" "></A></TD> </TR> <tr> <TD></TD> <TD> <hr> <font size=2> <CENTER> <%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%> <P> <TABLE BORDER> <%begindetail%> <%if CurrentRecord EQ 0 %> <caption>Query results:</caption> <TR> <TH><B>Author</B></TH><TH><B>YTD Sales<BR>(in dollars)</B></TH> </TR> <%endif%> <TR><TD><%au_lname%></TD><TD align="right">$<%ytd_sales%></TD></TR> <%enddetail%> <P> </TABLE> </center> <P> <%if CurrentRecord EQ 0 %> <I><B>Sorry, no authors had YTD sales greater than </I><%idc.sales%>.</B> <P> <%else%> <HR> <I> The web page you see here was created by merging the results of the SQL query with the template file SAMPLE.HTX. <P> The merge was done by the Microsoft Internet Database Connector and the results were returned to this web browser by the Microsoft Internet Information Server. </I> <%endif%> </font> </td> </tr> </table> </BODY> </HTML>
You can perform virtually any operation supported by the Common Gateway Interface (CGI), including online transaction processing with SQL RDBMSs and Jet 3.0 databases, using the proper combination of .idc and .htx files. Microsoft commissioned an
independent developer to create a sample Web site, Machine Head Botworks, that takes advantage of ActiveX technologies for animation and of Httpodbc.dll to register site visitors and take orders for imaginary (but rather pricey) robots. You can download
either the SQL Server 6+ or Jet 3.0 version of the Botworks application (about 1.2MB for either version) from
http://www.microsoft.com/intdev/solution/botworks.htm
Botworks is of particular interest to Webmasters who are responsible for designing and deploying interactive Web pages under IIS 1+. Figure 21.3 shows the Botworks home page displayed in the alpha version of Internet Explorer 3.0. (SVGA resolution is
recommended for clients browsing Botworks.)
Figure 21.3. The home page of the Machine Head Botworks sample Web site in standard VGA resolution.
You need Access 95 installed on your Internet server or on a client PC with access to the server to set up the Botworks site for SQL Server 6+. Access 95 is not required to run either version of the Botworks example.
The Active Internet Platform API (AKA ActiveX Server Framework, codenamed "Sweeper" in its alpha-testing stage) is included in the ActiveX Software Development Kit that you can download from http://www.microsoft.com/intdev/sdk/. The ActiveX
SDK includes the documentation for the Internet Server API (ISAPI), which provides an alternative to the use of CGI scripts to extend Internet server capabilities. The advantage of ISAPI is that server extensions are in-process Windows DLLs. CGI requires
executable applications that run as individual processes. ISAPI provides "pluggable filters" for preprocessing requests and post-processing responses from interactive Web pages. Httpodbc.dll, which provides the IDC services described in the
preceding section, uses ISAPI to connect IIS and SQL Server 6+.
The ActiveX SDK also includes a variety of documentation for client-side components, such as ActiveX controls and Active Scripting, which are described in the sections that follow.
The capabilities of client-side browsers dictate the features that can be implemented by HTML documents stored on Internet and intranet servers. The outcome of the "Browser War" that was raging between Netscape and Microsoft when this edition
was written will dictate the future course of interactive content development. Netscape has adopted Java applets and JavaScript for its Navigator browser, together with a grab bag of acquired multimedia technologies to implement its LiveMedia audio/video
delivery system and its Live3D Virtual Reality Modeling Language (VRML) extensions to HTML. Microsoft has taken a primarily in-house approach to development of its COM-based interactive media extensions for its Internet Explorer browser, collectively
called ActiveX. Both sides in the browser popularity contest use "open" to describe their proprietary extensions to HTML documents. In this case, "open" appears to refer to the fact that at least some documentation for the extensions is
available on the Internet and, for the most part, alpha or beta versions of the extensions can be downloaded without charge and without signing a non-disclosure agreement (NDA).
All versions of Internet Explorer and each of Microsoft's proposed extensions to HTML for its ActiveX technologies, with the exception of ActiveMovie (in beta testing under NDA when this chapter was written), a can be downloaded in various states of
completion from http://www.microsoft.com. The sections that follow describe Microsoft's Internet Explorer 1+, presently available in version for 16-bit and 32-bit Windows, as well as the Macintosh, and some of the announced ActiveX extensions expected to
appear in 32-bit Internet Explorer 3.0.
Browser-specific code, such as VBScript or JavaScript, embedded in HTML documents is a problem for public World Wide Web sites but not for private intranets. Web pages for public distribution must be written to accommodate lowest-common-denominator browsers, including text-only browsers. In the intranet environment, all clients use (or should use) the same browser. Users install and upgrade their browser from a network setup share. One exception to the "common intranet browser" rule is discussed in the "Creating a Visual Basic 4.0 Intranet Browser with the HTML OLE Control" section, later in this chapter.
Microsoft's announced intention is to incorporate browser capabilities as a component of the operating system in an upgrade to Windows 95, code-named Nashville when this edition was written. The basic concept is to eliminate the distinction between
content stored in conventional files, either on the client or a network server, and HTML-encoded documents on Web servers. Internet Explorer 3.0 will be incorporated in the Windows 95 shell, providing relatively seamless viewing of intranet and Internet
content. The alpha version of IE 3.0, which is included in the ActiveX SDK, runs as a conventional application under Windows 95.
One of the primary features of IE 3.0 that distinguishes the new version from its predecessors is Document Object (DocObject) container capability. DocObject containers, such as the Microsoft Office 95 Binder, allow DocObject servers, such as Word 7.0
and Excel 7.0, to assume total control over the appearance of the container. Figure 21.4 illustrates the Office Binder with sections containing a table of contents (TOC) for this edition in an Excel worksheet and several manuscript chapters as Word
documents. (Visio 4.0 was the only non-Microsoft DocObject server when this edition was written). DocObject servers display their frame adornments, such as the Excel worksheet tabs, within the container area. DocObject also permits multiple views, such as
Word's Normal, Outline, Page Layout, and Master Document views; only Page Layout view is permitted when Word is embedded in an OLE Object control or as an insertable object in a Visual Basic 4.0 form.
Figure 21.4. An Office 95 Binder containing Excel worksheet and Word document sections.
Microsoft did not publicly disclose the DocObject technology behind the Office 95 Binder application until the December 1995 "Embrace and Extend" news conference, which announced Microsoft's intention to take a new tack for its Internet development activities.
IE 3.0 also is a host for a new class of OLE Controls called ActiveX Controls, which can be manipulated by VBScript code embedded in HTML documents. An introduction to VBScript is provided in the "Previewing Internet Explorer 3.0 and Visual Basic,
Scripting Edition" section, near the end of this chapter.
The ActiveX SDK also includes documentation and sample code (primarily C++) for the following Internet extensions to Windows:
The beta versions of a set of 32-bit OLE Controls for the HTTP protocol, HTML browsing, FTP file transfer, and Usenet newsgroup access are available for download from http://www.microsoft.com/icp/. The HTML control is the foundation of the Visual Basic
4.0 browser application described in the "Creating a Visual Basic 4.0 Intranet Browser with the HTML OLE Control" section, later in this chapter.
Although far removed from today's conventional database applications for the Internet and intranets, multimedia content delivered by browsers will play an important role in both educational and entertainment-related sites. The following is a list of
the primary multimedia extensions to the HTML standards proposed by Microsoft:
DirectDraw, DirectSound, DirectVideo, and Direct3D (DirectX) are extensions to Windows 95 that accelerate video and audio reproduction. (DirectDraw is the replacement for the Display Control Interface that was removed from Windows 95 prior to its retail release in August 1995). The DirectX extensions support hardware acceleration devices, such as graphics adapters, sound cards, and video capture cards, for which DirectX drivers are available. If you don't have DirectX hardware, the DirectX extensions emulate hardware acceleration in software.
The popularity of World Wide Web and the availability of a variety of low-cost Web browsers has led to widespread adoption of Internet technology for private (usually corporate) intranets. Many firms consider the browser model to be a more effective
means of providing employees access to corporate data than conventional front-end design. Following are some of the reasons for using a browser as a replacement for database decision-support front-ends:
The downside of using a browser to display a query result set is the current need for creating a temporary HTML-encoded page with the data in tabular format, transmitting the page from the server to the client, then parsing and formatting the page on
the client PC. If the query result set has a large number of rows and columns, response time suffers. Most of the reduction in performance is due to the substantial overhead of HTML encoding, plus extensive page formatting by the client. (HTML overhead is
especially noticeable with a 28.8-Kbps RAS connection to the server.)
The sections that follow describe the design of a 32-bit Visual Basic 4.0 application, DB_Brwsr.vbp, that uses Microsoft's new HTML OLE Control to provide conventional browsing features, but relies on a conventional client/server front-end to process
queries quickly. In an intranet scenario, users with a client license for the RDBMS employ the faster Visual Basic browser that uses the Remote Data Control (RDC) and the Apex Data Bound Grid control to display query result sets. Others receive the
information via a temporary HTML page created on the server. Direct execution of the query lets users make changes to the tables underlying updatable query result sets, if desired. No changes to the content or formatting of HTML pages are required to gain
this flexibility.
The DB_Brwsr.vbp project, described in the sections that follow, requires access to the demonstration .htm pages, plus the .idc and .htx scripts that accompany Microsoft's Internet Information Server 1.0. You must be running IIS 1+ and have a network or RAS TCP/IP connection from your PC to IIS 1+. The starting URL for the DB_Brwsr.vbp project is set by the Text property of the Document combo box (cboURL) to the http://oakleaf0 host, on which inetsvr\wwwroot\default.htm is the IIS 1.0 default page. If you have replaced Default.htm with your own home page, use an http://. . ./default.htm pointer to the new location of the IIS 1.0 Default.htm file. You must maintain the relative folder structure between IIS 1.0's Default.htm, the . . .\samples folder, and the . . .\scripts\samples folder to make the relative jumps in the demonstration files work correctly. Alternatively, you can create aliases for the folders using the Internet Service Manager. If you don't have the Microsoft Internet Control Pack (ICP), you need to download from http://www.microsoft.com/icp/ and run Msicpb.exe, the self-extracting archive file that contains the beta version of the ICP, or a later, production version of the ICP. The 32-bit Microsoft HTML Client control is HTML.OCX.
The Microsoft HTML Client control (Html.ocx) is a relatively light-weight (132KB) OLE Control that provides the basic features required of all Web browsers. You can insert Html.ocx in 32-bit Visual Basic 4.0 and Access 95 forms to add Internet and
intranet browsing capability to your applications.
The beta version of Html.ocx (dated 3/11/96) used to write this chapter appears to have problems with 256-color palettes. You may experience a system lockup (Exception OE blue screens) after repeated use of the Html.ocx followed by use of graphics applications, such as Windows Paint. It's strongly recommended that you close the instance of Visual Basic 4.0 running projects that use Html.ocx before running other applications on your PC. This problem is likely to be rectified in later releases of Html.ocx.
Html.ocx provides the following features:
Lack of HTML page caching by the HTML Client control mitigates the reduced network traffic advantage of direct query processing. It's likely that future or third-party versions of the HTML Client control will offer the option to cache downloaded pages. To minimize network traffic, design the form hierarchy to minimize the number of pages required to navigate to the page with the query form.
You can't produce a "commercial quality" browser with Visual Basic 4.0 and Html.ocx, but you can create a browser-enabled program that is adequate for most, if not all, database-centric applications. The ICP includes a WinHelp95 file,
Icp.hlp, that documents the five Internet controls in the ICP. After adding an Html.ocx control to a form, you can use the Object Browser to explore the properties and methods of the HTML Client control (see Figure 21.5). Using Object Browser gives you a
shortcut to the ICP help topic(s) for the selected collection, object, method, or property.
The sections that follow describe in more detail the most important properties, methods, and events for using the 32-bit HTML OLE Control in Visual Basic 4.0 database applications.
To use Html.ocx for Internet browsing by modem, you must install Internet Explorer 2+ (or another browser with a dialer) on the client to provide the dialup connection to the Internet.
The Forms collection of Html.ocx contains one Form object for each HTML form on the page. The Form object type is HTMLForm. You can determine whether a page contains one or more forms with an If htmName.Forms.Count > 0
Then. . .End If conditional statement. As an example, the dbsamp2.htm page (described in the "Internet Database Connector" section, earlier in this chapter) has a single form with the name "sales" and a default value of
5000. You can use the Method, URL, and URLEncodedBody properties of the Form object to determine all of the properties of each Form in the Forms collection of a page. The URL property returns the URL of the page that processes the response to a submission;
URLEncodedBody returns the text of the submission. Figure 21.6 shows the properties of the sales form of dbsamp2.htm displayed in the Debug window.
Figure 21.6. Displaying the properties of a HTML Form object in the Debug window.
The default property of the Forms collection, Item, is not implemented in the beta version of Html.ocx available when this chapter was written. Thus, use of the conventional htmName.Forms(n) syntax fails. You must use the explicit htmName.Forms.Item(n) syntax to specify a particular member of the forms collection with the beta version of Html.ocx.
DoRequestSubmit is the most useful event for processing database queries. The Visual Basic syntax for the DoRequestSubmit event is as follows:
htmName_DoRequestSubmit (ByVal URL As String, _
ByVal Form As HTMLForm, _
ByVal DocOutput As DocOutput, _
EnableDefault As Boolean)
The HTMLForm object passed by the Form argument substitutes for the Form object specified by the htmName.Forms.Item(n) statement. You can disable processing of the submission by setting the value of the EnableDefault argument to
False. The ability to disable default processing of form submissions is critical to the design of the DB_Brwsr.vbp project.
The Form object doesn't have a Text (or equivalent) property to return the text of the currently loaded document. If you want to parse the text of the current document, you use the DoNewElement event-handling subprocedure to append individual text
elements into a String variable. The general syntax of the DoNewElement event is as follows:
htmName_DoNewElement (ElemType As String, _
EndTag As Boolean, _
Attrs As HTMLAttrs, _
Text As String, _
EnableDefault As Boolean)
To create a readable text string, you append only when ElemType = "" (for text) and use a strText = strText & Text & " " statement to add spaces between the individual words that
comprise text elements. The resulting value of the strText variable is likely to contain spurious spaces, but usually can be parsed by conventional text string searching techniques. Figure 21.7 shows part of the text string generated by the
dbsamp.htm page of the database sample pages included with IIS 1.0 displayed in Notepad. You also can use the DoNewElement event handler to prevent particular element types from appearing in the browser window by specifying the element type (such as a for
the HTML anchor type) and setting the value of EnableDefault to False when the parser encounters a hypertext jump.
The DoNewElement event doesn't trigger unless you set the ElementNotification property of the HTML Client control to True, either in the control's property sheet or in your code. The need to set ElementNotification to True is mentioned in the "Using the HTML Control" help topic, but not in the "DoNewElement Event" topic.
The DB_Brwsr.vbp project derives from the basic design of the Simple.vbp example project that you can download from http://www.microsoft.com/icp/ as a self-extracting archive file, Simple.exe. Simple.vbp has
a single form, Simple.frm, with HTML Client, PicClip, Common Dialog, and Combo Box controls. The DB_Brwsr form has additional controls, and a substantial amount of the code in Simple.vbp is added to and modified in order to detect and process Web pages
with query forms. Figure 21.8 shows the DB_Brwsr.frm in design mode.
Following are the primary additions and changes to Simple.frm to create DB_Brwsr.frm:
In addition to the changes in the preceding list, variable and object names have been changed to conform to the Leszynski Naming Conventions (LNCs) for Microsoft Visual Basic, used throughout this book.
Publishing limitations preclude listing all of the code contained in DB_Brwsr.frm, much of which is not directly related to processing queries. Listing 21.2 contains the code for the DoRequestSubmit event handler. Much of the code parses the message
received when the form is submitted for processing. The read-only URLEncodedBody property of the Form object supplies the message string. The code must handle cases where multiple form elements are included in the submittal message. Multiple form elements
are separated by ampersands (&), as in the following example:
Element1Name=Element1Value&Element2Name=Element2Value&...
The standard version of the form of the dbsamp2.htm page returns sales=value as its message; the default message is sales=5000. Assuming the default value, the SQL statement for the directly processed query is this:
SELECT ytd_sales, title, au_lname, price, pub_id FROM titleview WHERE ytd_sales >= 5000
The code in Listing 21.2 also substitutes Caption values of DBGrid Column objects to make the field name headers of the grid more readable and formats columns with currency values. An alternative to setting Caption values is to alias the field names
with SELECT Field1 Caption1, Field2 Caption2, . . . syntax. (SQL Server's Transact-SQL uses a space rather than SQL's AS reserved word to create a field name alias. You can't include spaces in field name aliases.)
Listing 21.2. Code to detect a form element query criterion and process query results with the Remote Data Control.
Private Sub htmOakLeaf_DoRequestSubmit(ByVal URL As String, _
ByVal Form As HTMLForm, _
ByVal outStream As DocOutput,
_EnableDefault As Boolean)
'Trap the submission (query request) if selected
If tbrMain.Buttons(14).Value <> 0 Then 'Use RDO for Query
'Direct query processing is selected
Screen.MousePointer = ccHourglass
Dim strCode As String
Dim strCrit As String
Dim strSQL As String
Dim strFields As String
Dim intCtr As Integer
EnableDefault = False 'Execute query directly
strCode = Form.URLEncodedBody
'It's assumed that the first element is the criterion
strCrit = Mid$(strCode, InStr(strCode, "=") + 1)
If InStr(strCrit, "&") > 0 Then
strCrit = Left$(strCrit, InStr(strCrit, "&") - 1)
End If
'Test for a hidden SQL statement value
If InStr(strCode, "sql=") > 0 Then
strSQL = Mid$(strCode, InStr(strCode, "sql=") + 4)
If InStr(strSQL, "&") > 0 Then
strSQL = Left$(strSQL, InStr(strSQL, "&") - 1)
End If
'Fix up the encoded value
strSQL = ParseSpaces(strSQL)
strSQL = ParseOperators(strSQL)
strSQL = strSQL & strCrit
Else
If InStr(strCode, "sales=") > 0 Then
'Default SQL statement for the page
strSQL = "SELECT ytd_sales, title, au_lname, "
strSQL = strSQL & "price, pub_id FROM titleview "
strSQL = strSQL & "WHERE ytd_sales >= " & strCrit
Else
'Change the SQL statement accordingly
End If
End If
rdcWebSQL.SQL = strSQL
'Execute the query
rdcWebSQL.Refresh
'Alter column names (could be done by aliasing)
If InStr(strCode, "fieldnames=") > 0 Then
intCtr = dbgWebSQL.Columns.Count
ReDim astrFieldNames(intCtr)
strFields = Mid$(strCode, _
InStr(strCode, "fieldnames=") + 11)
If InStr(strFields, "&") > 0 Then
strFields = Left$(strFields, InStr(strFields, "&") - 1)
End If
strFields = ParseSpaces(strFields)
strFields = RTrim$(strFields)
If Right$(strFields, 1) <> "," Then
strFields = strFields + ","
End If
strFields = strFields + " "
Call ParseFieldNames(strFields, intCtr)
For intCtr = 0 To dbgWebSQL.Columns.Count - 1
dbgWebSQL.Columns(intCtr).Caption = _
astrFieldNames(intCtr)
Next intCtr
Else
If InStr(strCode, "sales=") > 0 Then
dbgWebSQL.Columns(0).Caption = "YTD Sales"
dbgWebSQL.Columns(1).Caption = "Book Title"
dbgWebSQL.Columns(2).Caption = "Author"
dbgWebSQL.Columns(3).Caption = "Price"
dbgWebSQL.Columns(4).Caption = "Pub ID"
Else
'Assign appropriate column captions
End If
End If
'You could add a hidden field for number format
'and alignment, if you're so inclined
If InStr(strCode, "sales=") > 0 Then
dbgWebSQL.Columns(0).NumberFormat = "$#,###"
dbgWebSQL.Columns(0).Alignment = 1
dbgWebSQL.Columns(3).NumberFormat = "$#.00"
dbgWebSQL.Columns(3).Alignment = 1
Else
'Format the columns accordingly
End If
'Make the DBGrid control visible
htmOakLeaf.Visible = False
dbgWebSQL.Visible = True
cmdClose.Visible = True
Screen.MousePointer = ccDefault
End If
End Sub
The code in Listing 21.2 includes tests for sql= and fieldnames= strings in the form submittal message. These form elements are described in the "Using HTML Hidden Input Elements to Supply SQL Statements" section, later in the chapter.
You need Internet Information Server 1+ and SQL Server 6+ running as services under Windows NT 3.51+ to use the DB_Brwsr.vbp project. (You can download a free copy of IIS 1.0 from http://www.microsoft.com). This example assumes that the Internet
Explorer 1.0 sample pages are installed in their default location, the inetsvr\wwwroot folder of your server, and that you have set up the Web SQL ODBC data source in accordance with the instructions included with IIS 1.0. (WebSQL uses the pubs sample
database included with SQL Server 6+.) This example also assumes that you have default sa access (no password) to SQL Server 6+.
Following are the instructions for running DB_Brwsr.vbp:
For simplicity, this example uses RDC and DBGrid controls. A more elegant approach is use of an rdoPreparedStatement object for the SQL passthrough query with an rdoParameter object to process the query. You pass the value of the criterion to the rdoParameter object. Multiple executions of the query with different criterion values are speeded because only the rdoParameter value is passed to the server after the initial query creates the temporary prepared statement on the server. Prepared statements are one of the subjects of the next chapter, "Using the Remote Data Object."
Hard-coding SQL statements in a Visual Basic browser application is not a generally accepted programming practice (GAPP). To the extent possible, the HTML page should provide all of the data required to process the query, either by the combination of
.idc and .htx files or by direct processing with Jet 3.0's DAO or the RDO. To handle direct query processing, you need to add the text of at least the SQL statement to the HTML code for the form. Embedding the SQL statement in the form code lets you write
generic Visual Basic code to process a wide variety of queries. The embedded SQL statement takes the place of the SQL Statement: element of the .idc file. The SQL statement or field name aliases shouldn't be visible to the user, so you must use a hidden
form element. Hidden form elements are relatively uncommon in HTML-encoded documents.
The HTML 2.0 specification contains the following description of hidden form elements: "An INPUT element with TYPE=HIDDEN represents a hidden field. The user does not interact with this field; instead, the VALUE attribute specifies the value of
the field. The NAME and VALUE attributes are required." Hidden fields are very powerful elements of interactive HTML forms, especially those used for database applications. Listing 21.3 shows the modified HTML code for the dbsamp2.htm form that
supplies the SQL statement and field name aliases for direct query processing by the htmOakLeaf_DoRequestSubmit event handler.
Listing 21.3. HTML coding of hidden form elements for an SQL statement and field name aliases.
<FORM METHOD="POST" ACTION="/scripts/samples/sample2.idc"> <P> Enter YTD sales amount: <INPUT NAME="sales" VALUE="5000"> <P> <INPUT TYPE=HIDDEN NAME=sql VALUE="SELECT ytd_sales, title,__ au_lname, price, pub_id FROM titleview WHERE ytd_sales GTEQ "> <INPUT TYPE=HIDDEN NAME=fieldnames VALUE="YTD Sales, _ Book Title, Author, Price, Pub ID"> <INPUT TYPE="SUBMIT" VALUE="Run Query"> </FORM>
With a value of 2000 entered in the text box, the HTML code of Listing 21.3 produces the following submittal message as the value of the URLEncodedBody property of the Form object:
sales=2000&sql=SELECT+ytd_sales,+title,+au_lname,+price, _ +pub_id+FROM+titleview+WHERE+ytd_sales+GTEQ+&fieldnames= _ YTD+Sales,+Book+Title,+Author,+Price,+Pub+ID
The VBA code continuation characters (space + underscore) shown in the preceding HTML-encoded form and form submittal message examples appear for consistency with the formatting of VBA statements whose length exceeds publishing limitations. VBA code continuation characters are not permissible in HTML code. HTML ignores newline pairs, so code continuation characters aren't required by HTML.
Spaces in form submittal messages are replaced by plus signs (+) and, as noted earlier in the chapter, multiple elements of messages are separated by ampersands (&). You cannot include comparison operators, such as <, >, or = in messages; the
operators are replaced by uppercase string equivalents, LT, GT, or EQ, respectively. Thus, the message returned by the value of Form.URLEmbeddedBody requires a substantial amount of processing to produce a legal SQL statement. Listing 21.4 shows the code
required to replace +s with spaces and alphabetic equivalents of comparison operators, as well as to create an array of field headers for the Caption property of the Column objects of a DBGrid control.
Listing 21.4. The two functions and one subprocedure required by DB_Brwsr.vbp to parse form submittal messages.
Private Function ParseSpaces(strIn As String)
'Replace + with space
Dim intPos As Integer
If Right$(strIn, 1) = "+" Then
strIn = Left$(strIn, Len(strIn) - 1) & " "
End If
Do While InStr(strIn, "+") > 0
intPos = InStr(strIn, "+")
strIn = Left$(strIn, intPos - 1) & " " & _
Mid$(strIn, intPos + 1)
Loop
ParseSpaces = strIn
End Function
Private Function ParseOperators(strIn As String)
'Replace GTEQ with >= comparison operator, etc.
Dim intPos As Integer
If Right$(strIn, 1) <> " " Then
strIn = strIn & " "
End If
If InStr(strIn, "GTEQ") Then
intPos = InStr(strIn, "GTEQ")
strIn = Left$(strIn, intPos - 1) & ">=" & _
Mid$(strIn, intPos + 4)
ElseIf InStr(strIn, "LTEQ") Then
intPos = InStr(strIn, "LTEQ")
strIn = Left$(strIn, intPos - 1) & "<=" _
& Mid$(strIn, intPos + 4)
ElseIf InStr(strIn, "GT") Then
intPos = InStr(strIn, "GT")
strIn = Left$(strIn, intPos - 1) & ">" & _
Mid$(strIn, intPos + 2)
ElseIf InStr(strIn, "LT") Then
intPos = InStr(strIn, "LT")
strIn = Left$(strIn, intPos - 1) & "<" & _
Mid$(strIn, intPos + 2)
ElseIf InStr(strIn, "EQ") Then
intPos = InStr(strIn, "EQ")
strIn = Left$(strIn, intPos - 1) & "=" & _
Mid$(strIn, intPos + 2)
End If
ParseOperators = strIn
End Function
Private Sub ParseFieldNames(strIn As String, intCount As Integer)
Dim intCtr As Integer
Dim intPos As Integer
For intCtr = 1 To intCount
astrFieldNames(intCtr) = "Missing"
Next intCtr
intCtr = 0
Do While InStr(strIn, ",") > 0
intPos = InStr(strIn, ",")
astrFieldNames(intCtr) = Left$(strIn, intPos - 1)
strIn = Mid$(strIn, intPos + 1)
intCtr = intCtr + 1
If intCtr > intCount Then
Exit Do
End If
Loop
End Sub
The new feature of Internet Explorer (IE) 3.0 that's likely to be of most interest to Visual Basic developers is the addition of ActiveX Scripting, which lets you embed programming code within HTML documents. When this edition was
written, the only available ActiveX Scripting language, the new name for OLE Scripting, was a preliminary version of Visual Basic, Scripting Edition (VBScript or VBS). According to published reports, Microsoft plans to provide an ActiveX Scripting wrapper
for Sun Microsystems' JavaScript language. In mid-April 1996, Microsoft posted on its Web site several sample applications that demonstrate the capabilities of VBScript. Figure 23.14 shows one of these sample application, Pizza Azzip, available at http://www.microsoft.com/vbscript/us/vbssamp/PDCSamp/VBPizza.htm. The Pizza Azzip page uses the Radio, Check, Button, and Text controls of the Microsoft HTML Intrinsic Controls
ActiveX control (Htmlctl.ocx) that's included with IE 3.0.
You must download the ActiveX SDK described in the "Active Internet Platform API" section (near the beginning of this chapter) to properly display the demonstration ActiveX Web pages discussed in this section.
Listing 21.5 shows the HTML code written by Microsoft to create the Pizza Azzip page with comments rewritten or removed to conserve space. The <SCRIPT LANGUAGE="VBS">. . .</SCRIPT> tags delimit the VBScript source code for the
page. VBScript source code can be placed anywhere in the HTML code for the page. Upon loading the page, IE 3.0 parses the HTML code and, if VBScript code is present, the VBScript interpreter "compiles" the source code. Once compiled,
almost-conventional HTML <INPUT. . .> tags redirect events triggered by the HTML Intrinsic Controls to the appropriate VBS event-handler. As an example, the <INPUT TYPE=CHECKBOX NAME=Pepperoni onClick="SetTotalCost"> expression causes
the onClick event of the Pepperoni check box to call the SetTotalCost subprocedure. An Alert, contained in the SUB DoOrder subprocedure creates a message box. (See Figure 21.15.)
Listing 21.5. The HTML and VBScript code behind the Pizza Azzip page.
<HTML><HEAD><TITLE>Pizza Azzip</TITLE></HEAD><BODY bgproperties=fixed>
<FONT FACE="Comic Sans MS" SIZE=2 COLOR=NAVY>
<CENTER>
<FONT SIZE=6>Pizza Azzip<P>
<IMG SRC="/vbscript/us/vbssamp/pdcsamp/THEPIZZA.GIF" ALIGN=MIDDLE width=150 height=75>
</CENTER>
<SCRIPT LANGUAGE="VBS">
<!
SUB SetTotalCost
'Determine the total cost of the order
Dim Form
Set Form = document.OrderForm
'Get total number of toppings.
total = Form.Pepperoni.checked + _
Form.Onion.checked + _
Form.Pineapple.checked + _
Form.Salmon.checked + _
Form.Anchovies.checked + _
Form.Ham.checked
Form.sum.value = "$" + CStr(10 + (total * 2)) + ".00"
END SUB
SUB SetDescriptionText(strToSet)
'This method will set the description of the pizza.
document.OrderForm.Text1.value = strToSet
END SUB
SUB DoOrder
'Submit and acknowledge the order
SetTotalCost
SetDescriptionText "Thank you, your pizza will arrive piping hot."
'Alert is a method on the window object (a message box)
Alert "Thank you, your pizza will arrive piping hot. Your account " + _
"was billed " + document.OrderForm.sum.value + "."
END SUB
SUB SetIngredients(bPepperoni, bOnion, bPineapple, bSalmon, bAnchovies, bHam)
'Sets the values of the checkboxes and recomputes pizza price
Dim Form
Set Form = document.OrderForm
Form.Pepperoni.checked = bPepperoni
Form.Onion.checked = bOnion
form.Pineapple.checked = bPineapple
Form.Salmon.checked = bSalmon
Form.Anchovies.checked = bAnchovies
Form.Ham.checked = bHam
SetTotalCost
END SUB
SUB DoHawaiian
SetIngredients False, False, True, False, False, True
SetDescriptionText "Our aloha special. Taste the exotic flavor of the big island."
END SUB
SUB DoChicago
SetIngredients True, True, False, False, False, False
SetDescriptionText "Capone's favorite."
END SUB
SUB DoSeattle
SetIngredients False, False, False, True, False, False
SetDescriptionText "Our best rainy day pizza. For the fish lover in you."
END SUB
Sub DoNewYork
SetIngredients True, False, False, False, False, False
SetDescriptionText "For a taste of the Big Apple"
END SUB
SUB DoTheWorks
SetIngredients True, True, True, True, True, True
SetDescriptionText "Our most popular"
END SUB
>
</SCRIPT>
<HR>
</FONT>
<FORM Name="OrderForm">
<TABLE BORDER=1 BGCOLOR="#FFFFCC" WIDTH=200 ALIGN=LEFT>
<FONT FACE="Comic Sans MS" SIZE=2 COLOR=FFFFCC>
<TR><TD BGCOLOR=NAVY ALIGN=CENTER>Pick A Style...</TD></TR></FONT>
<TR><TD><IMG SRC="/vbscript/us/vbssamp/pdcsamp/SLICE.GIF" ALT="*" ALIGN=CENTER>
<INPUT TYPE=RADIO NAME=RadioGroup onClick="DoHawaiian">Hawaiian </TD></TR>
<TR><TD><IMG SRC="/vbscript/us/vbssamp/pdcsamp/SLICE.GIF" ALT="*" ALIGN=CENTER>
<INPUT TYPE=RADIO NAME=RadioGroup onClick="DoChicago"> Chicago </TD></TR>
<TR><TD><IMG SRC="/vbscript/us/vbssamp/pdcsamp/SLICE.GIF" ALT="*" ALIGN=CENTER>
<INPUT TYPE=RADIO NAME=RadioGroup onClick="DoNewYork"> New York </TD></TR>
<TR><TD><IMG SRC="/vbscript/us/vbssamp/pdcsamp/SLICE.GIF" ALT="*" ALIGN=CENTER>
<INPUT TYPE=RADIO NAME=RadioGroup onClick="DoSeattle"> Seattle </TD></TR>
<TR><TD><IMG SRC="/vbscript/us/vbssamp/pdcsamp/SLICE.GIF" ALT="*" ALIGN=CENTER>
<INPUT TYPE=RADIO NAME=RadioGroup onClick="DoTheWorks">The Works </TD></TR>
</TABLE>
<TABLE BORDER=1 BGCOLOR="#FFFFCC" WIDTH=200 ALIGN=RIGHT>
<TR><TD BGCOLOR=NAVY ALIGN=CENTER><FONT COLOR=FFFFCC>Toppings...</TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Pepperoni onClick="SetTotalCost">Pepperoni </TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Onion onClick="SetTotalCost">Onion </TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Pineapple onClick="SetTotalCost">Pineapple </TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Salmon onClick="SetTotalCost">Salmon </TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Anchovies onClick="SetTotalCost">Anchovies </TD></TR>
<TR><TD><INPUT TYPE=CHECKBOX NAME=Ham onClick="SetTotalCost">Ham </TD></TR>
</FONT></TABLE>
<FONT FACE="WINGDINGS" SIZE=6>
<MARQUEE WIDTH=100 DIRECTION=RIGHT ALIGN=MIDDLE BGCOLOR=WHITE>F</MARQUEE>
</FONT>
<BR>
<CENTER>
<BR><FONT SIZE=4>Order Now!<BR><BR>
<INPUT TYPE=BUTTON VALUE="Order" NAME="Order" onClick="DoOrder">
<BR><BR>
</FONT><FONT SIZE=2>
<I> The cost for a Pizza is $10.00 </I> <BR>
<I> Each additional topping is $2.00 </I>
<BR CLEAR=LEFT>
<BR CLEAR=RIGHT><BR>
Description: <INPUT NAME=Text1 SIZE=60>
Total = <INPUT NAME=Sum VALUE="$0.00" SIZE=8><BR>
</CENTER></FONT><BR>
</FORM>
</FONT>
</BODY></HTML>
You can explore the properties and methods of the HTML Intrinsic Controls by creating a reference to Htmlctl.ocx or Htmlctl.oca in 32-bit Visual Basic 4.0. Figure 21.16 shows Object Browser displaying the names of the eight ActiveX controls included in
Htmlctl.ocx. These eight controls correspond to similarly named Visual Basic 4.0 intrinsic controls.
You can't add the HTML Intrinsic Controls to Visual Basic 4.0's toolbox because the names of the HTML controls conflict with the names of Visual Basic 4.0 controls.
Figure 21.16. Object browser displaying the Microsoft HTML Intrinsic Controls provided by the Htmlctl.ocx ActiveX control.
This chapter introduced you to Microsoft's newly developed Internet technologies, including Internet Information Server 1.x, the Internet Server API (ISAPI), the Internet Database Connector (IED), and the Internet-related ActiveX OLE Controls
and other ActiveX components, such as ActiveMovie and ActiveVRML. Although the public Internet garners the most publicity, it's likely that most Visual Basic database developers will obtain more income from developing applications for private intranets
that use Web pages for improving internal communication. The Visual Basic Database Browser described in this chapter uses Microsoft's new HTML Client OLE Control to bring browser capability to 32-bit Visual Basic client/server database applications. The
HTML Client control lets users decide between processing database queries with the IDC or obtain better performance by processing client/server queries with the Remote Data Control and ODBC.
