Create Database Apps with the IDC and VBScript
Rick Dobson
This month, Rick shows you how to build Internet/intranet systems that query, display, and update remote ODBC data sources. His examples reveal how to combine VBScript and the Internet Database Connector (IDC) to accomplish these goals. THIS is the third installment in a three-part series on VBScript. The first article (December 1996) introduced VBScript and described how it interacts with HTML. The second (February 1997) explained how to make Web pages dynamic and interactive with ActiveX controls.
This article presents an overview of the process for creating dynamic data-based Web pages with VBScript, the idc, and ODBC data sources. I'll describe four prototypical tasks to demonstrate the process in representative situations. My goal in reviewing model tasks is to provide building blocks for your own Web data processing brews.
How IDC generates a dynamic Web app
The IDC permits a Web application to generate dynamic HTML pages reflecting the contents of a database on a server. The process starts at the browser by sending a URL (and, optionally, some form fields) to Microsoft's Internet Information Server (IIS).
The URL designates an IDC file consisting of several fields. I'll describe the three required fields in more detail later. the data source field identifies an ODBC resource to proscess. The SQL statement field is an SQL command string, which depends on the SQL conventions of the database type in the data source field. The template field references an HTX file, a text file that includes standard HTML tags along with other special tags for formatting the return set.
Figure 1 depicts the overall process. The browser issues a URL and, optionally, some form fields, which will normally be standard HTML form fields. The URL references a form fields, which will normally be standard HTML form fields. The URL references a server-based IDC file. when the IIS receives the input, it passes control to the IDC that interprets the IDC file along with any form field contents passed by the browser. IDC passes SQL commands to the ODBC driver for processing. when the ODBC driver completes execution of its SQL commands to the ODBC driver for processing. When the ODBC driver completes execution of its SQL command string, the IDC uses the HTX file to Format the return set so that the IIS can pass an HTML page back to the browser.
Figure 1. An overview of the IDC browser/server database processing flows.
Numbers mark key points fo involvement for an Internet developer using VBScript and the IDC.
Developers manage three key points in this process. First, they write code to pass the URL and form FIELDS TO THE SERVER. VBScript CAN HELP AT THIS POINT. sECOND, THEY WRITE AN idc FILE TO SPECIFY processing for a data source. They key SQL Statement field requires some knowledge of SQL. third, they prepare an HTX file to format the return set for browsers. VBScript can also enhance this effort.
Developers can also become involved in setting up the data source. You must specify the new system data source type that the 32-bit ODBC driver introduced. IF YOU'RE USING Access with NT 4.0, you need a special patch file which you can get get from..
www.microsoft.com/kb/deskapps/access/q151186.htm. Unlike other databases, the Access database participates only in designing the system, not managing client contact. Other databases can therefore perform server-side processing permitted by their SQL because the database participates in processing client requests.
Computing a value
For any given screen in an IDC app, you're likely to have three files-one that lets the browser submit a request, an IDC file that specifiess what processing to perform, and an HTX file that formats the results for viewsing.
The following HTML code represents a page for counting the number of orders in a database. The data source points to the NORTHwind database that ships with Access.
Store the HTML file in your server's Wwwroot directory. The file lets a user perform the count by clicking an anchor or a button. The VBScript code illustrates how to construct and pass a URL to the server:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<! - -
Sub ComputeIt_OnClick ( )
Dim strAnchor
strAnchor = "/ scripts /TNumber.idc"
Location.HRef = strAnchor
End Sub
- ->
< / SCRIPT>
<BODY>
<H2>
Compute Total NUMBER OF Orders as of Right now
</H2>
<UL>
<LI>
<A HREF= "/scripts/TNumber.idc">Total NUMBER OF Orders
</A>
<HR>
<LI>
<INPUT TYPE=Button NAME =ComputeIt
VALUE="Compute IT!">
For IE 3.0+ Users Only
</UL>
</BODY>
</HTML>
The next code sequence shows the IDC file refferenced by the URL. The SQL STATEMENT FIELD ALLOWS YOU TO DIVIDE THE COMMAND INTO PARTS BY PRECEDING EACH PART WITH A PLUS SIGN. i F you aren't familiar with SQL, use a tool like the Query-By- Example grid in Access to build queries. Then, just copy and reformat the command for the SQL Statement field:
Datasource:NorthwWind
Template:TNumber.htx
SQLStatement:
+ SELECT Count (Orders.OrderId)
+ AS TotalNumberOfOrders
+ FROM Orders;
The following code shows Tnumber.htx, with VBScript time-stamping each page to show a count. The Now function supplies a value for a variable. The code uses the document's Write method to stamp the HTML page. bound return fields from the IDC file, such as TotalNumberOfOrders, are in between the percent (%) symbols:
<HTML>
<HEAD>
<TITLE>North Wind Sales Information</TITLE>
</HEAD>
<BODY>
<H2>NorthWind Count Example </H2>
<B>Total Number of Sales = </B>
<%TotalNumberOfOrders%>
<P>Date of this query:
<SCRIPT LANGUAGE="VBS">
RightNOW = Now
Document.Write RightNow
</SCRIPT>
<BODY>
</HTML>
Passing parameters
This first application is dynamic because it changes as orders are added or deleted. This provides a clear advantage over static Web pages, but users still can't control the results directly. The following HTML page provides two text boxes where the user can specify the month and year over which to count orders:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<!- -
Sub ComputeIt_OnClick ( )
dim strAnchor
'A simple validity check routing
If IsNumeric (frmMoYr.MONTH. Value) Then
frmMoYr.Submit
Else
msgbox "Fix IT, THEN TRY AGAIN !"
End If
End sub
- ->
</SCRIPT>
<BODY>
<H2>
Compute total number of orders as of right now.
</H2><HR>
Start by choosing month and year.
<HR>
<Form NAME=frmMoYR ACTION="scripts/TNumMoYR.idc"
METHOD=Post>
<UL>
<LI> Indicate the month with a number (1-12):
<INPUT TYPE=Text NAME=Month SIZE=3 VALUE="7">
<LI> Year:
<INPUT TYPE=TEXT NAME=YEAR SIZE=4 VALUE="1993">
<P><P><P>
<HR>
<LI>
<INPUT TYPE=Button NAME=ComputeIt
VALUE="Compute IT !">
</UL>
</FORM>
</BODY>
</HTML/
I recommend passing parameters to the IDC with HTML forms. Verify the validity of form data with VBScript to avoid unnecessary error messages. When using a form, specify the IDC file's URL in the form's ACTION attribute. Use EITHER pOST OR Get for a METHOD when your IDC file launces a query. Use the Post METHOD for IDC files that change the data source on the server.
After verifying that the fields contain valid data, invoke the form's Submit method.
recall from the first installment of this VBScript series (in the December 1996 issue of Visual Basic Developer) that this requires a general-purpose button on the form rather than a Submit button.
this IDC file is nearly identical to the one in the first application. The only difference is that you bound passed parameters in "%" signs (notice the MONTH and Year in the following IDC file). The parameter names must match their NAME attribute in the form on the HTML page launching the request;
DATASOURCE:NorthWind
template:TNumMoYr.htx
SQLSTATEMENT:
+ SELECT Count (Orders.OrderID) AS TotalNUMBERofORDERS,
+ Month( [OrderDate]) AS RETmONTH,
+ Year ([OrderDate]) AS RetYear
+ FROM orders
+ WHERE (((Month ([ OrderDAte])) =% Month%)
+ AND (( Year ([ORDERdATE])) =%Year%))
+ GROUP BY Month ([OrderDate]), Year ([OrderDate]);
Recall that the Web is stateless-it has no direct way to remember the passed parameters. Nevertheless, YOU OFTEN NEED TO SHOW THESE PARAMETERS along with your return set. Circumvent this difficulty by including passed parameters in the return set (see the RetMONTH AND retYear fields in the preceding SQL Statement).
The HTX file referenced in the preceding IDC file follows the same formate as in the first example. This second HTX file adds an extra line showing the month and year
for which it reports a count of orders:
<P>month =<%RetMonth%> , and year = <%retYear%>
Drilling down into a database
A drill-down application involves at least two linked screens. Users view a data display with hot spots on it in the first screen. They can "drill down" into the database by clicking on any of these hot spots.
My first HTML page for the drill-down application has a format that's almost identical to the preceding example; it lets a user specify a month and year. Clicking the form's button launces an IDC file. In this instance, however, the ACTION attribute of the FORM TAG sPECIFIES a different IDC file. clicking the form's button creates a return set listing orders for the month and period specified. HER'S THE idc FILE THAT generates the list:
Datasources:NorthWind
Template: ListORDERS.HTX
SQLStatement:
+ SELECT orders.ORDERid,
+ Orders.OrderDate as OrderDate,
+ Sum ([Quantity] * [UnitPRICE]) * 100/100 AS Amount
+ FROM Orders INNER JOIN [Order DETAILS ]
+ ON Orders.OrderID = [Order Details]. OrderID
+ WHERE ((Month ((OrderDate])= %Month%)
+ AND (Year ([OrderDate])=%Year%))
+ GROUP BY Orders.OrderID, Orders.OrderDate,
+ Orders.CustomerID
The IDC file generates a return set with four columns denoting OrderID, orderDATE,
CustomerID, and the sum of Extended PRICE ACROSS ALL THE LINE ITEMS IN AN ORDER. tHE EXTENDED PRICE TOTAL IS MULTIPLIED AND DIVIDED BY 100, which convertssss the number so it has just two spaces after the decimall. this feature is particularly beneficial with VBScript BECAUSE IT DOESN'T HAVE A BUILT-IN currency format conversion function.
When you pass a table of numbers back, your HTX file must contain two special tags:
<%begindetail%>and<%enddetail%>. The following HTX file segment ILLUSTRATES HOW to use these tags for the first of two drill-down screens. the tags surround the portion of a table that lists the outcome from a query. Additional special tags support limited branching functions, the number of records returned, and the current record number:
<TABLE BORDER>
<TR>
<TH>Order Date</TH><TH>Order ID</TH>
<TH>customer</TH>>TH>>Amount</TH>
</TR>
<%BEGINDETAIL%>
<TR>
<TD>>%OrderDate%>>/TD>
<TD>
<A HREF=
" /scripts/ODetails.IDC?OrderID=<%OrderID%>">
<%OrderID%>>/A>>/TD>
<TD>>%Customer%>< /TD>
<TD>$<%Amount%>< /TD>
</TR>
<%enddetail%>
</TABLE>
A key point to note about the file excerpt is that it includes an anchor to another screen. This anchor is the drill-down point. Clicking the anchor opens a view of the individual line items composing an order.
The anchor has two parts. First, it includes a reference to a new IDC file followed by an equal sign(=). Second, it includes a parameter, namely a refeence to the OrderID field returned by the first IDC file. I bound OrderID in between "%" signs, and I include the whole anchor reference in quotes. In addition, I repeat the reference to the OrderID field so that it shows in the table. The OrderID number appears in the color that your browser uses for showing anchors. This marks the hot spot that a user clicks to view theindividual line items in an order.
When a user clicks an OrderID number, he or she invokes the ODETAILS.idc file and passes it a parameter, NAMELY THE order ID numer, he or she invokes the ODetails.idc FILE AND PASSES IT A PARAMETER, NAMELY THE ORDER NUMBER. THE FILE USES THE ORDER NUMBER TO EXTRACT THE LINE ITEM RECORDS COMPRISING THAT ORDER. tHE oDETAILS.idc file looks like this:
Datasource:NorthWind
Template: ODetails.htx
SQLStatement:
+SELECT Products.ProductID, Products.ProductName,
+ [Order Details]. ORDERid, Quantity,
+ [Order Details]. UnitPRICE,
+ Quantity * [Order Details]. UnitPRICE AS Subtotal
+ FROM Products, [Order Details]
+ WHERE Products.ProductID = [Order Details]. ProductID
+ AND OrderID=%OrderID%
tHE FOLLOWING htx FILE SEGMENT FORMATS the output from ODetails.idc FOR A BROWSER. it starts by displaying the OrderID. Next, it prints a separate line in a table for each record in the return set. Once again, the <%begindetail%> and <%enddetail%> tags indicate where to insert the records in the HTML page:
Details on Order #<%OrderID%>
<TABLE BORDER>
<TR>
<TH>Product Name </TH>
<TH>Unit PRICE</TH>
<TH>Quantity</TH>
<TH>Subtotal</TH>
</TR>
<%BEGINDETAIL%>
<TR>
<TD>>%ProductName%></TD>
<TD align="right">$<% Unitprice%>< /TD>
<TD align= "right" ><%Quantity%></TD>
<TD align="right" >$<%Subtotal%></TD ><TR>
<%enddetail%>
</TABLE>
Appending a table
All the applications to this point read from a server-based database. The IDC permits the full range of SQL commands for a database. In the case of Access, this involves appending, updatiing, and deleting records for an existing table; making a new tabl' and more. Furthermore, you can have Access write the SQL command string when you set up the query in the query-By-Example grid.
I'll illustrate the simplicity and power of this capability with an application to add new
Shippers to the NorthWind Shippers table. This table includes just three fields, one of which is an autonumber field. the other two fields are companyName and Phone.
The following code presents the first page of the application that appends new records to the Shippers table. It includes two text boxes for CompanyName and phone, respectively, and a button. the VBScript OnClick event procedure for the button performs a simple validity check and submits the form to the server. This also invokes the ShipperAd.idc file:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<! - -
Sub AddIt_OnClick( )
Dim strAnchor
'A simple validity check routine
If Document.Forms(0). companyName.Value< > " " Then
frmShipperAd.Submit
Else
msgbox "Fux utm tgeb trt agaub ! "
End If
End Sub
- ->
</SCRIPT>
<BODY>
<H2>Enter company Name and phone for new Shipper.</H2>
<HR>
<Form NAME=frmShipperAd ACTION =
"/scripts/ShipperAd.idc" METHOD=Post>
<UL>
<LI>Company NAME:
<INPUT TYPE=Text NAME=CompanyNAME size=40
VALUE="Company Name goes here.">
<LI>Telephone:
<INPUT TYPE=Text NAME=Telephone SIZE=24
VALUE="Enter phone # here.">
<HR>
<LI><INPUT TYPE=Button NAME=AddIT VALUE= "Add IT !">
</BODY>
</HTML>
The IDC file is very simple. It uses the Insert Into command to add the CompanyNAME and Telephone fields from the browser input form to the table.
Access automatically adds an appropriate autonumber field:
Datasource: NORTHWIND
TEMPLATE;SHIPPERaD.HTX
SQLStatement:
+ INSERT INTO Shippers
+ (CompanyName, Phone)
+ VALUES (' %CompanyName%' , ' %Telephone%');
because the application adds data to a table, there's no return set to show. However, it would be informative to confirm receipt after the addition has been completed. Recall that the IDC invokes the HTX file only after the SQL statement field IN THE idc FILE IS COMPLETED. The HTX file in my sample application issues a brief confirmation that the new record was added successfully. I pull the CompanyName term, bound with "%" signs, accomplises this task:
<HTML>
<HEAD>
<TITLE>Shipper Addition Confirmation</TITLE>
</HEAD>
<BODY>
This is to confirm your addition of a new shipper. The shipper name is
<%idc.CompanyName%>.
</BODY>
</HTML>
Summary
You now have four basic building blocks and a general introduction to building dynamic internet or database applications using VBScript and the IDC. The Microsoft Job Access Forum presents a completed application that showcases the full potential of using the IDC with Access
(www.microsoft.com/access/internet/jobforum/). A white paper provides a thorough review of the technology in the application and a good general introduction to IDC issues (www.microsoft.com/AccessDev/accwhite/JobForPa.htm). I found the Mastering Internet Development CD (us $99.95) from Microsoft to be a good general Internet resource; chapters six and seven are particularly relevant to the content of this installment.
Note to Windows NT users: The same IDC/HTX file syntax works with the Internet Database Connecction that ships with all versions of Internet Information Server as well as the Personal Web Server for Windows 95. Your server must be running the HTTPODBC DLL to provide data in response to dynamic visitor requests. Servers running NT 4.0 should get a patch for IDC/HTX files to function properly. the patch is available at www.microsoft.com/kb/articles/q155/2/55.htm.s
DOBSON3.ZIP at www.pinpub.com/vbd
Rick dobson, Ph.D., is president of CAB Inc., a database and Internet application development consultancy. His articles appear in bte and PC, and he writes the
Office Developer column for access/Visual Basic Advisor. Fax 502-426-3743,
rick_dobson@msn.com.
To find out more about Visual Basic Developer and Pinnacle Publishing, visit their website at: http://www.pinppub.com/vbd/
Note: This is not a Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the March 1997 issue of Visual Basic Developer. copyright1997, by Pinnacle Publishing, inc., unless otherwise noted. all rights are reserved. Visual Basic Developer is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc.
To contact Pinnacle Publishing, Inc., please call (800) 788-1900 or (206) 251-1900.
_________________________________________________________________________________
Rick Dobson
This month, Rick shows you how to build Internet/intranet systems that query, display, and update remote ODBC data sources. His examples reveal how to combine VBScript and the Internet Database Connector (IDC) to accomplish these goals. THIS is the third installment in a three-part series on VBScript. The first article (December 1996) introduced VBScript and described how it interacts with HTML. The second (February 1997) explained how to make Web pages dynamic and interactive with ActiveX controls.
This article presents an overview of the process for creating dynamic data-based Web pages with VBScript, the idc, and ODBC data sources. I'll describe four prototypical tasks to demonstrate the process in representative situations. My goal in reviewing model tasks is to provide building blocks for your own Web data processing brews.
How IDC generates a dynamic Web app
The IDC permits a Web application to generate dynamic HTML pages reflecting the contents of a database on a server. The process starts at the browser by sending a URL (and, optionally, some form fields) to Microsoft's Internet Information Server (IIS).
The URL designates an IDC file consisting of several fields. I'll describe the three required fields in more detail later. the data source field identifies an ODBC resource to proscess. The SQL statement field is an SQL command string, which depends on the SQL conventions of the database type in the data source field. The template field references an HTX file, a text file that includes standard HTML tags along with other special tags for formatting the return set.
Figure 1 depicts the overall process. The browser issues a URL and, optionally, some form fields, which will normally be standard HTML form fields. The URL references a form fields, which will normally be standard HTML form fields. The URL references a server-based IDC file. when the IIS receives the input, it passes control to the IDC that interprets the IDC file along with any form field contents passed by the browser. IDC passes SQL commands to the ODBC driver for processing. when the ODBC driver completes execution of its SQL commands to the ODBC driver for processing. When the ODBC driver completes execution of its SQL command string, the IDC uses the HTX file to Format the return set so that the IIS can pass an HTML page back to the browser.
Figure 1. An overview of the IDC browser/server database processing flows.
Numbers mark key points fo involvement for an Internet developer using VBScript and the IDC.
Developers manage three key points in this process. First, they write code to pass the URL and form FIELDS TO THE SERVER. VBScript CAN HELP AT THIS POINT. sECOND, THEY WRITE AN idc FILE TO SPECIFY processing for a data source. They key SQL Statement field requires some knowledge of SQL. third, they prepare an HTX file to format the return set for browsers. VBScript can also enhance this effort.
Developers can also become involved in setting up the data source. You must specify the new system data source type that the 32-bit ODBC driver introduced. IF YOU'RE USING Access with NT 4.0, you need a special patch file which you can get get from..
www.microsoft.com/kb/deskapps/access/q151186.htm. Unlike other databases, the Access database participates only in designing the system, not managing client contact. Other databases can therefore perform server-side processing permitted by their SQL because the database participates in processing client requests.
Computing a value
For any given screen in an IDC app, you're likely to have three files-one that lets the browser submit a request, an IDC file that specifiess what processing to perform, and an HTX file that formats the results for viewsing.
The following HTML code represents a page for counting the number of orders in a database. The data source points to the NORTHwind database that ships with Access.
Store the HTML file in your server's Wwwroot directory. The file lets a user perform the count by clicking an anchor or a button. The VBScript code illustrates how to construct and pass a URL to the server:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<! - -
Sub ComputeIt_OnClick ( )
Dim strAnchor
strAnchor = "/ scripts /TNumber.idc"
Location.HRef = strAnchor
End Sub
- ->
< / SCRIPT>
<BODY>
<H2>
Compute Total NUMBER OF Orders as of Right now
</H2>
<UL>
<LI>
<A HREF= "/scripts/TNumber.idc">Total NUMBER OF Orders
</A>
<HR>
<LI>
<INPUT TYPE=Button NAME =ComputeIt
VALUE="Compute IT!">
For IE 3.0+ Users Only
</UL>
</BODY>
</HTML>
The next code sequence shows the IDC file refferenced by the URL. The SQL STATEMENT FIELD ALLOWS YOU TO DIVIDE THE COMMAND INTO PARTS BY PRECEDING EACH PART WITH A PLUS SIGN. i F you aren't familiar with SQL, use a tool like the Query-By- Example grid in Access to build queries. Then, just copy and reformat the command for the SQL Statement field:
Datasource:NorthwWind
Template:TNumber.htx
SQLStatement:
+ SELECT Count (Orders.OrderId)
+ AS TotalNumberOfOrders
+ FROM Orders;
The following code shows Tnumber.htx, with VBScript time-stamping each page to show a count. The Now function supplies a value for a variable. The code uses the document's Write method to stamp the HTML page. bound return fields from the IDC file, such as TotalNumberOfOrders, are in between the percent (%) symbols:
<HTML>
<HEAD>
<TITLE>North Wind Sales Information</TITLE>
</HEAD>
<BODY>
<H2>NorthWind Count Example </H2>
<B>Total Number of Sales = </B>
<%TotalNumberOfOrders%>
<P>Date of this query:
<SCRIPT LANGUAGE="VBS">
RightNOW = Now
Document.Write RightNow
</SCRIPT>
<BODY>
</HTML>
Passing parameters
This first application is dynamic because it changes as orders are added or deleted. This provides a clear advantage over static Web pages, but users still can't control the results directly. The following HTML page provides two text boxes where the user can specify the month and year over which to count orders:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<!- -
Sub ComputeIt_OnClick ( )
dim strAnchor
'A simple validity check routing
If IsNumeric (frmMoYr.MONTH. Value) Then
frmMoYr.Submit
Else
msgbox "Fix IT, THEN TRY AGAIN !"
End If
End sub
- ->
</SCRIPT>
<BODY>
<H2>
Compute total number of orders as of right now.
</H2><HR>
Start by choosing month and year.
<HR>
<Form NAME=frmMoYR ACTION="scripts/TNumMoYR.idc"
METHOD=Post>
<UL>
<LI> Indicate the month with a number (1-12):
<INPUT TYPE=Text NAME=Month SIZE=3 VALUE="7">
<LI> Year:
<INPUT TYPE=TEXT NAME=YEAR SIZE=4 VALUE="1993">
<P><P><P>
<HR>
<LI>
<INPUT TYPE=Button NAME=ComputeIt
VALUE="Compute IT !">
</UL>
</FORM>
</BODY>
</HTML/
I recommend passing parameters to the IDC with HTML forms. Verify the validity of form data with VBScript to avoid unnecessary error messages. When using a form, specify the IDC file's URL in the form's ACTION attribute. Use EITHER pOST OR Get for a METHOD when your IDC file launces a query. Use the Post METHOD for IDC files that change the data source on the server.
After verifying that the fields contain valid data, invoke the form's Submit method.
recall from the first installment of this VBScript series (in the December 1996 issue of Visual Basic Developer) that this requires a general-purpose button on the form rather than a Submit button.
this IDC file is nearly identical to the one in the first application. The only difference is that you bound passed parameters in "%" signs (notice the MONTH and Year in the following IDC file). The parameter names must match their NAME attribute in the form on the HTML page launching the request;
DATASOURCE:NorthWind
template:TNumMoYr.htx
SQLSTATEMENT:
+ SELECT Count (Orders.OrderID) AS TotalNUMBERofORDERS,
+ Month( [OrderDate]) AS RETmONTH,
+ Year ([OrderDate]) AS RetYear
+ FROM orders
+ WHERE (((Month ([ OrderDAte])) =% Month%)
+ AND (( Year ([ORDERdATE])) =%Year%))
+ GROUP BY Month ([OrderDate]), Year ([OrderDate]);
Recall that the Web is stateless-it has no direct way to remember the passed parameters. Nevertheless, YOU OFTEN NEED TO SHOW THESE PARAMETERS along with your return set. Circumvent this difficulty by including passed parameters in the return set (see the RetMONTH AND retYear fields in the preceding SQL Statement).
The HTX file referenced in the preceding IDC file follows the same formate as in the first example. This second HTX file adds an extra line showing the month and year
for which it reports a count of orders:
<P>month =<%RetMonth%> , and year = <%retYear%>
Drilling down into a database
A drill-down application involves at least two linked screens. Users view a data display with hot spots on it in the first screen. They can "drill down" into the database by clicking on any of these hot spots.
My first HTML page for the drill-down application has a format that's almost identical to the preceding example; it lets a user specify a month and year. Clicking the form's button launces an IDC file. In this instance, however, the ACTION attribute of the FORM TAG sPECIFIES a different IDC file. clicking the form's button creates a return set listing orders for the month and period specified. HER'S THE idc FILE THAT generates the list:
Datasources:NorthWind
Template: ListORDERS.HTX
SQLStatement:
+ SELECT orders.ORDERid,
+ Orders.OrderDate as OrderDate,
+ Sum ([Quantity] * [UnitPRICE]) * 100/100 AS Amount
+ FROM Orders INNER JOIN [Order DETAILS ]
+ ON Orders.OrderID = [Order Details]. OrderID
+ WHERE ((Month ((OrderDate])= %Month%)
+ AND (Year ([OrderDate])=%Year%))
+ GROUP BY Orders.OrderID, Orders.OrderDate,
+ Orders.CustomerID
The IDC file generates a return set with four columns denoting OrderID, orderDATE,
CustomerID, and the sum of Extended PRICE ACROSS ALL THE LINE ITEMS IN AN ORDER. tHE EXTENDED PRICE TOTAL IS MULTIPLIED AND DIVIDED BY 100, which convertssss the number so it has just two spaces after the decimall. this feature is particularly beneficial with VBScript BECAUSE IT DOESN'T HAVE A BUILT-IN currency format conversion function.
When you pass a table of numbers back, your HTX file must contain two special tags:
<%begindetail%>and<%enddetail%>. The following HTX file segment ILLUSTRATES HOW to use these tags for the first of two drill-down screens. the tags surround the portion of a table that lists the outcome from a query. Additional special tags support limited branching functions, the number of records returned, and the current record number:
<TABLE BORDER>
<TR>
<TH>Order Date</TH><TH>Order ID</TH>
<TH>customer</TH>>TH>>Amount</TH>
</TR>
<%BEGINDETAIL%>
<TR>
<TD>>%OrderDate%>>/TD>
<TD>
<A HREF=
" /scripts/ODetails.IDC?OrderID=<%OrderID%>">
<%OrderID%>>/A>>/TD>
<TD>>%Customer%>< /TD>
<TD>$<%Amount%>< /TD>
</TR>
<%enddetail%>
</TABLE>
A key point to note about the file excerpt is that it includes an anchor to another screen. This anchor is the drill-down point. Clicking the anchor opens a view of the individual line items composing an order.
The anchor has two parts. First, it includes a reference to a new IDC file followed by an equal sign(=). Second, it includes a parameter, namely a refeence to the OrderID field returned by the first IDC file. I bound OrderID in between "%" signs, and I include the whole anchor reference in quotes. In addition, I repeat the reference to the OrderID field so that it shows in the table. The OrderID number appears in the color that your browser uses for showing anchors. This marks the hot spot that a user clicks to view theindividual line items in an order.
When a user clicks an OrderID number, he or she invokes the ODETAILS.idc file and passes it a parameter, NAMELY THE order ID numer, he or she invokes the ODetails.idc FILE AND PASSES IT A PARAMETER, NAMELY THE ORDER NUMBER. THE FILE USES THE ORDER NUMBER TO EXTRACT THE LINE ITEM RECORDS COMPRISING THAT ORDER. tHE oDETAILS.idc file looks like this:
Datasource:NorthWind
Template: ODetails.htx
SQLStatement:
+SELECT Products.ProductID, Products.ProductName,
+ [Order Details]. ORDERid, Quantity,
+ [Order Details]. UnitPRICE,
+ Quantity * [Order Details]. UnitPRICE AS Subtotal
+ FROM Products, [Order Details]
+ WHERE Products.ProductID = [Order Details]. ProductID
+ AND OrderID=%OrderID%
tHE FOLLOWING htx FILE SEGMENT FORMATS the output from ODetails.idc FOR A BROWSER. it starts by displaying the OrderID. Next, it prints a separate line in a table for each record in the return set. Once again, the <%begindetail%> and <%enddetail%> tags indicate where to insert the records in the HTML page:
Details on Order #<%OrderID%>
<TABLE BORDER>
<TR>
<TH>Product Name </TH>
<TH>Unit PRICE</TH>
<TH>Quantity</TH>
<TH>Subtotal</TH>
</TR>
<%BEGINDETAIL%>
<TR>
<TD>>%ProductName%></TD>
<TD align="right">$<% Unitprice%>< /TD>
<TD align= "right" ><%Quantity%></TD>
<TD align="right" >$<%Subtotal%></TD ><TR>
<%enddetail%>
</TABLE>
Appending a table
All the applications to this point read from a server-based database. The IDC permits the full range of SQL commands for a database. In the case of Access, this involves appending, updatiing, and deleting records for an existing table; making a new tabl' and more. Furthermore, you can have Access write the SQL command string when you set up the query in the query-By-Example grid.
I'll illustrate the simplicity and power of this capability with an application to add new
Shippers to the NorthWind Shippers table. This table includes just three fields, one of which is an autonumber field. the other two fields are companyName and Phone.
The following code presents the first page of the application that appends new records to the Shippers table. It includes two text boxes for CompanyName and phone, respectively, and a button. the VBScript OnClick event procedure for the button performs a simple validity check and submits the form to the server. This also invokes the ShipperAd.idc file:
<HTML>
<SCRIPT LANGUAGE="VBScript">
<! - -
Sub AddIt_OnClick( )
Dim strAnchor
'A simple validity check routine
If Document.Forms(0). companyName.Value< > " " Then
frmShipperAd.Submit
Else
msgbox "Fux utm tgeb trt agaub ! "
End If
End Sub
- ->
</SCRIPT>
<BODY>
<H2>Enter company Name and phone for new Shipper.</H2>
<HR>
<Form NAME=frmShipperAd ACTION =
"/scripts/ShipperAd.idc" METHOD=Post>
<UL>
<LI>Company NAME:
<INPUT TYPE=Text NAME=CompanyNAME size=40
VALUE="Company Name goes here.">
<LI>Telephone:
<INPUT TYPE=Text NAME=Telephone SIZE=24
VALUE="Enter phone # here.">
<HR>
<LI><INPUT TYPE=Button NAME=AddIT VALUE= "Add IT !">
</BODY>
</HTML>
The IDC file is very simple. It uses the Insert Into command to add the CompanyNAME and Telephone fields from the browser input form to the table.
Access automatically adds an appropriate autonumber field:
Datasource: NORTHWIND
TEMPLATE;SHIPPERaD.HTX
SQLStatement:
+ INSERT INTO Shippers
+ (CompanyName, Phone)
+ VALUES (' %CompanyName%' , ' %Telephone%');
because the application adds data to a table, there's no return set to show. However, it would be informative to confirm receipt after the addition has been completed. Recall that the IDC invokes the HTX file only after the SQL statement field IN THE idc FILE IS COMPLETED. The HTX file in my sample application issues a brief confirmation that the new record was added successfully. I pull the CompanyName term, bound with "%" signs, accomplises this task:
<HTML>
<HEAD>
<TITLE>Shipper Addition Confirmation</TITLE>
</HEAD>
<BODY>
This is to confirm your addition of a new shipper. The shipper name is
<%idc.CompanyName%>.
</BODY>
</HTML>
Summary
You now have four basic building blocks and a general introduction to building dynamic internet or database applications using VBScript and the IDC. The Microsoft Job Access Forum presents a completed application that showcases the full potential of using the IDC with Access
(www.microsoft.com/access/internet/jobforum/). A white paper provides a thorough review of the technology in the application and a good general introduction to IDC issues (www.microsoft.com/AccessDev/accwhite/JobForPa.htm). I found the Mastering Internet Development CD (us $99.95) from Microsoft to be a good general Internet resource; chapters six and seven are particularly relevant to the content of this installment.
Note to Windows NT users: The same IDC/HTX file syntax works with the Internet Database Connecction that ships with all versions of Internet Information Server as well as the Personal Web Server for Windows 95. Your server must be running the HTTPODBC DLL to provide data in response to dynamic visitor requests. Servers running NT 4.0 should get a patch for IDC/HTX files to function properly. the patch is available at www.microsoft.com/kb/articles/q155/2/55.htm.s
DOBSON3.ZIP at www.pinpub.com/vbd
Rick dobson, Ph.D., is president of CAB Inc., a database and Internet application development consultancy. His articles appear in bte and PC, and he writes the
Office Developer column for access/Visual Basic Advisor. Fax 502-426-3743,
rick_dobson@msn.com.
To find out more about Visual Basic Developer and Pinnacle Publishing, visit their website at: http://www.pinppub.com/vbd/
Note: This is not a Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the March 1997 issue of Visual Basic Developer. copyright1997, by Pinnacle Publishing, inc., unless otherwise noted. all rights are reserved. Visual Basic Developer is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc.
To contact Pinnacle Publishing, Inc., please call (800) 788-1900 or (206) 251-1900.
_________________________________________________________________________________
No comments:
Post a Comment