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




-26-

Building a Simple Order-Entry Application Template



This chapter continues with the knowledge you've gained in previous chapters and shows you how to use existing template databases for generating your own Web database applications. You will be using the Microsoft Access Database Wizard to generate a template database with sample data in Microsoft Access. You then will use the Access Upsizing Wizard to convert your Access database into a SQL Server database that will be used as the back-end database engine for the sample Visual Basic order-entry template application.

What to Expect in This Chapter

To help you better understand exactly how you will be implementing the sample order-entry application template, here is a list of the steps covered in this chapter:

Using a Microsoft Access Template to Build a Web Database

If you have Microsoft Access at your site, you have a wealth of database templates already built into your Access installation. In this chapter, you will be building a Web database application from scratch and using Microsoft SQL Server to service that Web database. To accomplish this, you will use the order-entry database template available with Microsoft Access. After you build the database, you will port it to a SQL Server environment and use it as the data repository for the sample Web database application.


NOTE: This Access database is included on the accompanying CD-ROM for installations that do not have Access but do have SQL Server.

The Access Database Wizard

If you've never used the Access Database Wizard, you'll find that it is a very easy way to generate a standard database. This does not mean that you are limited to using the exact database structure of the template. Instead, you have the opportunity to add and remove tables and columns from your newly created database. In this example, you will use the template database exactly as it exists.

Selecting a Database Template

After you start the Access application, choose File | New Database. The New dialog box appears, as shown in Figure 26.1.

FIGURE 26.1.The General tab of the New dialog box.

Notice that the New dialog box includes two selection tabs: General and Databases. By default, the General tab is selected and includes the default Blank database. You want to get access to the database templates, so click the Databases tab. Figure 26.2 shows the available database templates in databases view.

FIGURE 26.2. Viewing the database templates on the Databases tab.

As you can see, the number of databases is too large to fit in the window. You can control the presentation of the databases by clicking one of the three buttons (text under icon, text to the right of icon, and column formatted) to the right of the Databases window. Click the middle button and then select the Order Entry.mdz file by clicking it. Figure 26.3 shows the full template database view.

FIGURE 26.3. .The full template database view

Click OK, and Access begins to create your new database from the selected template.

Naming Your New Database

The next window prompts you for a database name for your new database. You can select where to store your database in addition to selecting an appropriate name. I chose webentry.mdb as my database name and used the default directory as the location for storing the database. Figure 26.4 shows the File New Database dialog box. In the File name combo box, enter your database name.

FIGURE 26.4. The File New Database dialog box.

Using the Database Wizard to Design Your Database

Now that you've selected the database name and have chosen where the database will be saved, you are ready to begin the process of using the wizards to actually generate the database objects you want for your new database. Click Create, and the program begins to load all the wizards it needs to build your new database. After the wizards are loaded, you are presented with an information window showing the details of the template database. Figure 26.5 shows the order-entry database template information window.

FIGURE 26.5. The order-entry template information window.

Click Next to go to the table- and column-selection window shown in Figure 26.6. You now are ready to begin selecting specific tables and columns to be included in the new database. If you scroll to the bottom of the list in the Fields in the table listbox, you'll see two columns that are italicized. As the comments at the top of the dialog box state, these are fields that are not included by default. You can include them, though, simply by enabling the checkbox beside the column entry. Also, this dialog box provides a checkbox for determining whether you want the newly created database to include sample data. For this example, you do want sample data, so enable the Yes, include sample data checkbox.

FIGURE 26.6. The table- and column-selection window.

Selecting Application Styles

Now that you've identified the tables and columns you want included in the new database, you are almost finished. Click Next, and you are presented with a screen-style selection dialog box, as shown in Figure 26.7. The screen style is used only for applications developed in Access. If you are planning on using an Access database as your data repository for Web access, you can use this window to define how your database screens will appear in your Access program. For this example, you will be upsizing (porting) this database to a SQL Server environment, so you simply can choose the default Standard style.

FIGURE 26.7. The screen-style selection dialog box.

Click Next again, and the wizard presents you with the report-style selection dialog box shown in Figure 26.8. Again, depending on the database platform you choose for your Web database application, you may want to play with the selection options in this dialog box to define how your database reports are presented when executed. Six options are available for the style of report to be generated: Bold, Casual, Compact, Corporate, Formal, and Soft Gray. In this dialog box, again choose the default entry. In this dialog box, again choose the default entry.

FIGURE 26.8. The report-style selection dialog box.

Setting Up Reports

You've almost completed the database-creation process. Click Next, and you are presented with the report title and name-selection dialog box shown in Figure 26.9.

FIGURE 26.9. The report title and name-selection dialog box.

Finalizing the Database Wizard Process

Finally, click Next, and the wizard will have all the information it needs to build your new database. The final dialog box simply prompts the user to determine whether the database should be started after it is generated (see Figure 26.10). Additional help is available by enabling the Display Help on using a database checkbox.

FIGURE 26.10. The final Database Wizard dialog box.

Click Finish, and Access creates your new database. Because this final phase actually generates your database, creates sample data, generates reports, and so on, the process can take a long time. On my system (a Pentium 100 running Windows NT 4.0), the process was complete in about 10 minutes.

The Main Switchboard Window

After the entire process is complete, you are presented with the Main Switchboard window, which enables you to view the database structure, enter data, view report information, and exit the database (see Figure 26.11).

FIGURE 26.11. The Main Switchboard window.

A Sample Prebuilt Data-Entry Form

Developers who plan on using a Microsoft Access database as their data repository will find that the Database Wizard generates all the needed reports, query forms, and so on. Take a quick look at a screen view of a specific customer's ordering information. Select the Enter/View Orders by Customer option on the Main Switchboard window. The Orders by Customer query form appears, as shown in Figure 26.12. Here, the database information is available by clicking the selection arrows near the bottom of the form.

FIGURE 26.12. The Orders by Customer form.

The Main Switchboard as an Intranet Application Front-End

As you can see, the Access Database Wizard virtually builds your database application for you. The Main Switchboard window is a fully functional front-end to your Access database. Assuming that your Access database is your data repository for Web access, internal corporate users could use the Main Switchboard for the processing and handling of orders placed by Internet users.

Setting Up an ODBC Source

Upsizing an Access database to SQL Server requires that you have set up an Open Database Connectivity (ODBC) source for the new database. If you plan on creating a new database during the upsizing process, you need to set up an ODBC data source for the SQL Server Master database. For this chapter's sample application, you will be creating a new database in SQL Server that mirrors your Access database. You'll start by creating your ODBC source for the Master database.


NOTE: Your ODBC resource must be configured on the NT Server machine where your SQL Server database is located.

From the Control Panel on your desktop, locate your ODBC icon, as shown in Figure 26.13.

FIGURE 26.13. Locating the ODBC icon in the Control Panel.


NOTE: Depending on your system and previously installed ODBC software, the ODBC icon may be titled "ODBC Administrator," "32-bit ODBC," "ODBC," or something similar.

Double-click the ODBC icon. The ODBC Data Sources dialog box appears, showing the currently configured ODBC resources (see Figure 26.14).

FIGURE 26.14. The ODBC Data Sources dialog box.

As you can see, you need to set up a data source for your SQL Server Master database. Click Add to access the Add Data Source dialog box. Add a new data source by selecting the SQL Server driver, as shown in Figure 26.15.

FIGURE 26.15. The ODBC Add Data Source dialog box.

Click OK, and the ODBC SQL Server Setup dialog box appears. Here, you configure your ODBC data source for the SQL Server Master database. Figure 26.16 shows the ODBC SQL Server Setup dialog box with the values I selected for the Master database.

FIGURE 26.16. The ODBC SQL Server Setup dialog box.

Select a data source name and description and enter those values in the appropriate text-entry boxes. For my example, I chose SQLmasterdb as the data source name and SQL Server Master Database as the data source description. The data source name is used later when upsizing the database to SQL Server. Finally, select your server name. Because my SQL Server engine was running on the same machine from which I was upsizing my Access database, I simply chose local for the server name.

Click OK, and a new entry for your SQLmasterdb data source is added to the Data Sources dialog box.

You now can click Close to close the Data Sources dialog box, because you've completed the process needed to set up your ODBC source. Now you can begin the process of upsizing the database to SQL Server.

The Microsoft Access Upsizing Tool

The Microsoft Access Upsizing tool is available for free from Microsoft and is used to automatically convert Access databases into their SQL Server equivalent. The Upsizing tool migrates your Access database to SQL Server while maintaining your data, indexes, defaults, counters, table relationships, and referential integrity.


NOTE: Upsizing from Access 95 to SQL Server 4.21, 6.0, and 6.5 are all supported options.

For the sample order-entry processing application, you'll need to build the Web interface to the database. You will be using the new SQL Wizard included in the latest release of SQL Server. Before you can do that, however, you need to upsize the Access order-entry database to the SQL Server equivalent.


RESOURCE: The Upsizing tool is free and can be downloaded from this page:
http://www.microsoft.com/Accessdev/AccInfo/AccInfo.htm



If you do not currently have the Access Upsizing tool, download and install it before continuing with the order-entry database application.

Before Using the Access Upsizing Tool

Before you begin your upsizing process, you must review a few items to reduce the possibility of upsizing errors. Every installation depends on so many factors that I cannot cover all of them in this book. If you follow these few recommendations, though, you can reduce many of the potential error conditions that arise during the execution of the Upsizing tool:


RESOURCE: Microsoft provides very useful online information related to all aspects of Access at this address:
http://www.microsoft.com/accessdev/



Running the Access Upsizing Wizard

If your Access webentry database is closed, open it now so that you can begin to use the Upsizing tool for migrating it to a SQL Server environment. After your database is open and you load the Upsizing tool, you are ready to migrate the order-entry database to SQL Server.

From the menu bar, choose Tools | Add-Ins | Upsize to SQL-Server. Figure 26.17 shows the first window for the Access Upsizing tool.

FIGURE 26.17. The Access Upsizing Wizard.

Because you will be creating a new SQL Server database that is a mirror of the Access database, you will choose the Create new database option.


NOTE: If you already have created a SQL Server database, or one previously exists that you want to incorporate your Access data into, select the default option to upsize to an existing database.

Select the "Create new database" option and click Next to begin the upsizing process. The SQL Data Sources dialog box appears, which includes the SQLmasterdb data source, as shown in Fig-ure 26.18.

FIGURE 26.18. The SQL Data Sources dialog box.

Highlight the SQLmasterdb data source and click OK. The SQL Server Login dialog box appears, prompting the user for a SQL Server logon with the proper privileges for creating a new database (see Figure 26.19).

FIGURE 26.19. The SQL Server Login dialog box.

Enter an appropriate SQL Server logon and password and click OK. The next window enables you to set up SQL Server devices for the newly upsized database. Select the database device and log device you want to use for the new database. For the example, you will be creating new devices for the webentry database. After you select the Create New Device list entry, a New Device dialog box appears that enables you to set the name of the SQL Server device for the data or log device (see Figure 26.20).

FIGURE 26.20. The SQL Server New Device dialog box.

Notice in Figure 26.20 that I chose a name of webentry_log for my log device. For my data device, I chose webentry_data. Click OK to return to the device-selection window with the newly set options for creating new data and log devices (see Figure 26.21).


TIP: You'll need to set appropriate sizes for your new devices. Consult your SQL Server Database Administrator to determine appropriate sizes and names for your devices.

FIGURE 26.21. The Upsizing Wizard device-selection window.

Click Next, and a new window appears in which you can define the new SQL Server database name, database size, and log size for the upsized database (see Figure 26.22). I chose the default database name, which is a concatenation of the Access database with a SQL extension (webentrySQL).


NOTE: My Access database is about 1.5MB. Based on the estimated size needed to upgrade an Access database to a SQL Server database, I chose a database size of 5MB and a log size of 1MB. Both these sizes should be more than adequate for my sample database.

FIGURE 26.22. The Upsizing Wizard database name and data/log sizes dialog box.

Click Next, and a listing of all of the available tables from the Access webentry database appears, as shown in Figure 26.23. You will want to export all the tables into the webentrySQL SQL Server database, so simply click the >> button to select all the tables for export. The tables that will be exported are listing in the Export to SQL Server box.

FIGURE 26.23. Tables to be exported to SQL Server.

You now can click Next to select attributes for your new database (see Figure 26.24). Because you want to completely mirror the Access database, you will choose all the default options.

FIGURE 26.24. The database attribute-selection window.

Note that, by default, Access will link the tables from the newly created SQL Server database to the old Access database. Click Next, and the Upsizing Wizard displays a dialog box asking whether you want the Upsizing Wizard to create a report of the upsizing process (see Figure 26.25). Enable the Create upsizing report checkbox so that you'll have a report to review in the event of an error.

FIGURE 26.25. The upsizing report dialog box.

Now you simply can click Finish to begin the upsizing process. While the Upsizing Wizard is performing its magic, a status window tracks its progress. The whole process took only about five minutes on my machine. Depending on your machine configuration and the size of the database you are upsizing, your times may vary.

Voilà! After the process finally finishes, you see the Upsizing complete! message box in Figure
26.26.

FIGURE 26.26. The Upsizing complete! message box.

Assuming that your upsizing process was a success, you now have your webentry database migrated to a SQL Server database. The next step for the sample application is to build Web pages based on the information in the newly created database.

The SQL Server Web Assistant

The SQL Server Web Assistant is a tool that is still in its infancy stage of development. The current version of the Web Assistant can provide some basic HTML generation based on simple database queries, however. The Web Assistant is capable of generating dynamic documents at specified intervals. You can build a page with the Web Assistant and have SQL Server rebuild the page whenever the contents of a specified table change, for example. Another scenario might be that you would want to rebuild a page on a daily basis--maybe something like a page displaying a "Fact of the Day" or "Hot, Special Deals."

This section looks at how you can use the Web Assistant to automatically build simple HTML documents. Later in this chapter, you'll learn how to generate HTML documents that go beyond simple HTML tables by incorporating use of the Internet Database Connector (IDC) discussed in Chapter 20, "The IIS Internet Database Connector (IDC)."

Using the SQL Server Web Assistant

When you first start the SQL Server Web Assistant, you are presented with a logon window that identifies the SQL Server and user logon you want to use for accessing your SQL Server database (see Figure 26.27).

FIGURE 26.27. The Login dialog box.

Enter the name of the SQL Server you want to access. You might be able to leave this entry blank if the SQL Server exists on the current machine. Next, enter a proper logon and password and click Next.

The Web Assistant Query Form

The Web Assistant next connects to the SQL Server Engine and generates a Query form (see Figure 26.28). In this form, you have the option of specifying how you want your data selected for the newly created Web page. By default, the Build a query from a database hierarchy option is selected. For this example, you will be specifying a freeform text query, so select the Enter a query as free-form text option. Next, select the webentrySQL database from the Which database do you want to query? drop-down listbox.

Finally, you need to specify the freeform text for the query. Because you want a total sales figure for each salesperson, you simply can specify the query as the following:

SELECT   FirstName, LastName, SUM(Quantity*UnitPrice)
FROM     Employees, Orders, Order_Details
WHERE    Employees.EmployeeId = Orders.EmployeeId
AND      Orders.OrderId = Order_Details.OrderId
GROUP BY FirstName, LastName

Figure 26.28 shows the Query form with the proper query information specified.

FIGURE 26.28. The Query dialog box.

Scheduling Options

Click Next, and the Web Assistant displays the Scheduling dialog box, which prompts you for information on when to generate the Web page (see Figure 26.29).

Assume that this company pays its sales staff a commission on sales whenever an order has been shipped. To automatically generate the Web page based on the shipping date, you can specify the ShipDate column as the specific column that, when updated, will rebuild the Web page.

Click the down-arrow in the When do you want to create your web page? drop-down listbox. You'll notice several options that determine when the page will be generated or updated. For pages that will require rebuilding based on changed input, you can select the When Data Changes entry. When you select this entry, you'll notice that a new Table(s) selection list appears. In this selection list, you can specify the tables and columns that, when changed, will generate the Web page to be rebuilt. When you select the When Data Changes entry, the Web Assistant creates or updates an existing trigger based on the table or column selections specified in the Table(s) listbox. This trigger will fire and execute the sp_makewebpage stored procedure, which is responsible for generating the updated Web page.

FIGURE 26.29. The Scheduling dialog box.


NOTE: Any tables or columns that are highlighted in green can cause the sp_makewebpage stored procedure to execute and build the specified Web page.

The sample in Figure 26.29 is based on a trigger firing when the ShipDate column of the Orders table is updated.

For this example, you want to generate a document now so that you can look at the contents as generated by the Web Assistant. Select the Now entry in the When do you want to create your Web page? listbox.

Options for Saving the HTML File

Click Next to specify the file options (see Figure 26.30). Here, you specify the URL location for the HTML file to be generated. You should specify a path that is in your Web server document root path. Specify the title for the page and the title for the query results in the appropriate textboxes.

FIGURE 26.30. The File Options dialog box.

Formatting Web Wizard Output

Click Next, and the Formatting dialog box appears. Here, you can specify minimal formatting for the database output. For the example, you can use the default selections and click Finish, and the Web Assistant generates the database trigger based on the specified table to re-create the Web page whenever the data changes. If the Web Assistant has accepted your information and registered the page to be built, the window shown in Figure 26.31 appears.

FIGURE 26.31. The SQL Server Web Assistant completion window.

The Web Wizard-Created Document

Now look at the HTML page that you just created. Listing 26.1 shows the source code for the page.

Listing 26.1. The HTML Page You Created.

<HTML>
<HEAD>
<TITLE>Sales-to-Date Statistics</TITLE>
</HEAD>

<BODY>
<H2>Sales-to-Date Statistics</H2>
<HR>

<PRE><TT>Last updated: Jan 18 1997  8:07AM</TT></PRE>

<P>
<P><TABLE BORDER=1>
<TR>
<TH ALIGN=LEFT>FirstName</TH>
<TH ALIGN=LEFT>LastName</TH>
<TH ALIGN=LEFT>n/a</TH>
</TR>

<TR>
<TD NOWRAP><TT>Andrew</TT></TD>
<TD NOWRAP><TT>Fuller</TT></TD>
<TD NOWRAP><TT>252.55</TT></TD>
</TR>

<TR>
<TD NOWRAP><TT>Janet</TT></TD>
<TD NOWRAP><TT>Leverling</TT></TD>
<TD NOWRAP><TT>179.55</TT></TD>
</TR>

<TR>
<TD NOWRAP><TT>Margaret</TT></TD>
<TD NOWRAP><TT>Peacock</TT></TD>
<TD NOWRAP><TT>24.8</TT></TD>
</TR>

<TR>
<TD NOWRAP><TT>Nancy</TT></TD>
<TD NOWRAP><TT>Davolio</TT></TD>
<TD NOWRAP><TT>176.25</TT></TD>
</TR>

<TR>
<TD NOWRAP><TT>Steven</TT></TD>
<TD NOWRAP><TT>Buchanan</TT></TD>
<TD NOWRAP><TT>408.75</TT></TD>
</TR>

</TABLE>
<HR>
</BODY>
</HTML>

Figure 26.32 shows how the HTML page will look when rendered on a Web browser.

FIGURE 26.32. The document generated by the Web Assistant.

As you can see, the Web Assistant easily can generate "canned" HTML documents. For Web pages that require user input as query criteria, however, you need a more advanced mechanism. The next section discusses how to incorporate the IDC to generate dynamic hypermedia documents.

Using the Internet Database Connector

As discussed in Chapter 20, the IDC is an Internet Server API (ISAPI) DLL used to retrieve information from ODBC databases. This section focuses on how to set up the proper IDC files and HTML extension files required to automate the webentrySQL database into a WWW application.

Using the knowledge gained in Chapter 20 on how to build .idc and .htx files, you now can begin to put together the sample template order-entry application. As mentioned earlier, this application is based on an intranet example that enables internal users to process order entries that could have been generated by a Web-interface application such as an online store.


NOTE: All the source files for this chapter are stored in a single directory on the CD-ROM. You can access these files and applications directly from the CD-ROM by adding a new directory to your IIS World Wide Web Service that points to the ch26 directory on your CD-ROM.

Figure 26.33 shows the Directory Properties dialog box for adding a new directory to the IIS WWW Service.

FIGURE 26.33. The IIS WWW Service Directory Properties dialog box.

This example shows a few forms that easily can be modified to implement a simple solution for building your own order-entry processing application. The forms in this example are not all complete, and there certainly are numerous ways to expand on these examples. I present them here simply as templates for you to build on.


NOTE: Not all functions are implemented in this application. It is merely meant as a starting template by which developers can generate their own Web database applications.

As noted in earlier chapters, security can be implemented in many ways. For an application such as an order-entry processing system, you would want to at least verify and log transactions at the user level. In most cases, you easily can track an employee's identification number. In this example, an employee logs onto the system with a user ID. This user ID then can be used to maintain information such as commissions data, to monitor work content (such as when an employee is paid based on quantity), and so on.

Another form of security that should be enforced when implementing intranet applications is security at the server level. On the authors' machines, we set up an entire directory structure for the application and defined only our machines (IP addresses) as having access to that directory structure.

The application starts with the logon window shown in Figure 26.34. I could have added a PASSWORD input field, but my database wasn't set up to handle passwords. When designing your intranet application and database, however, be sure to include a mechanism for authenticating user access.

FIGURE 26.34. The order-entry application logon window.

Listing 26.2 shows the code that generated this first logon window.

Listing 26.2. Generating the first logon window.

<HTML>
<HEAD><TITLE>Online Order Entry System</TITLE></HEAD>
<BODY BGCOLOR="#FFFFFF">

<CENTER>
<IMG SRC="/ch26/bsball.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/caffrey.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/bkball.jpg" ALIGN="CENTER">
<HR>
<FORM METHOD="POST" ACTION="/ch26/main.idc">
<P>

<IMG SRC="/ch26/employee.jpg"><INPUT NAME="EmployeeID" SIZE="5">

<P>
<INPUT TYPE="SUBMIT" VALUE=" Login ">
</FORM>
</CENTER>
</BODY>
</HTML>

As you can see, there is nothing fancy about this document. There are two things worth mentioning in this code. First, the ACTION attribute of the FORM is identified as /ch26/main.idc. After the user clicks the Login button, this .idc file is executed. In the main.idc file, the user's identification is authenticated against the database. Second, the user logon INPUT field is given the NAME EmployeeID. In the main.idc file, this field name (in the form of a variable) is used in the database query. Listing 26.3 shows the main.idc file.

Listing 26.3. The main.idc file.

Datasource: webentrySQL
Username: sa
Template: main.htx
RequiredParameters: EmployeeID
SQLStatement:
+SELECT FirstName, LastName
+ from employees
+ where employeeid = %EmployeeID%

As you can see, the EmployeeID field was used in the SQLStatement to get the FirstName and LastName user entries. In .idc files, field names that are passed from HTML form documents are surrounded by percent (%) signs. The Template field is specified to tell the Web server to use the resulting fields from SQLStatement to populate the HTML extension file main.htx.

Also, note in the main.idc file listing that I used the RequiredParameters field. By specifying the EmployeeID entry, you can do some initial field validation prior to connecting to the database and executing your database query. If the EmployeeID field is left blank when the form is submitted, the IIS returns an error message, as shown in Figure 26.35.

FIGURE 26.35. The Error Performing Query window.

Specify a 2 as the input for the EmployeeID field. The employee's identification is validated, and the resulting FirstName and LastName fields are used to populate the main.htx file. Figure 26.36 shows the main.htx file as rendered by a browser.

FIGURE 26.36. The order-entry form.

Listing 26.4 shows the code that generated the application's main screen.

Listing 26.4. Generating the applications main screen.

<html>
<title>Order Entry Form</title>
<BODY BGCOLOR="#FFFFFF">
<CENTER>
<IMG SRC="/ch26/bsball.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/caffrey.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/bkball.jpg" ALIGN="CENTER">
<HR>

<%begindetail%>
<%if CurrentRecord EQ 0 %>

<h4><EM>Online Sports Center</EM></h4>
<P>
(Sales Associate: <%FirstName%> <%LastName%>)
<P>
<FORM METHOD="POST" ACTION="/ch26/customer.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%CustomerID%>">
<INPUT TYPE="hidden" NAME="OrderID" VALUE="<%OrderID%>">
<INPUT TYPE="IMAGE" SRC="/ch26/selcust.jpg">
</FORM>

<FORM METHOD="POST" ACTION="/ch26/products.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%CustomerID%>">
<INPUT TYPE="hidden" NAME="OrderID" VALUE="<%OrderID%>">
<INPUT TYPE="IMAGE" SRC="/ch26/selprod.jpg">
</FORM>

<FORM METHOD="POST" ACTION="/ch26/shipping.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%CustomerID%>">
<INPUT TYPE="hidden" NAME="OrderID" VALUE="<%OrderID%>">
<INPUT TYPE="IMAGE" SRC="/ch26/shipmeth.jpg">
</FORM>

<IMG SRC="/ch26/viewcust.jpg">

<%endif%>
<%enddetail%>
<%if CurrentRecord EQ 0 %>
<h2>Invalid employee id specified.</h2>
<a href="/ch26/emp_logon.htm">Try Again</a>
<%endif%>
</body>
</html>

The returned query result values (FirstName and LastName) were used in the following line to specify the sales associate's name:

(Sales Associate: <%FirstName%> <%LastName%>)

Note that the application will loop through every row returned from the query for all lines between the <%begindetail%> and <%enddetail%> tags. The CurrentRecord built-in variable is used within a conditional statement to test for the occurrence of the first record (record 0). If, at the end of the loop (just after the <%enddetail%> line), the value of CurrentRecord equals 0, no row results were returned.

Looking at the code for this page, you'll see two things that appear to be out of place. First, I used hidden fields to pass application state information between forms. As noted in earlier chapters, application state is not maintained by the application itself; the developer has to use other methods to maintain application information. Because this application enables users to browse between forms, the values are passed along between the forms as hidden fields. If a field has not yet been filled in or selected by the user, the entry simply remains empty.

The second thing that might look odd about Listing 26.4 is the use of multiple forms within the document. I chose this method simply as a means for giving the user the capability to choose his or her path in the application. Also note that I used image graphics as my INPUT object for each of the forms. This just provides a more presentable means than the standard Submit buttons for submitting a document for processing.


NOTE: When you are using multiple FORM elements within a document, the only fields that are passed to the ACTION attribute are those fields contained within the FORM element that was submitted. For this reason, I had to specify the hidden fields within each FORM element.

Now select the Select Customer text-image area, and the form is submitted to the customer.idc file for the purpose of selecting currently valid customers from the webentrySQL database. The basic logic for building the next form is replicated across the other forms available with this template application.

Listing 26.5 shows the code for the customer.idc file.

Listing 26.5. The customer.idc file.

Datasource: webentrySQL
Username: sa
Template: customer.htx
RequiredParameters: EmployeeId
SQLStatement:
+SELECT CompanyName, CustomerID
+ FROM Customers
+ ORDER BY CompanyName ASC

Figure 26.37 shows the Customer Selection List window.

FIGURE 26.37. The Order Entry-Customer Selection List window.

The CompanyName and CustomerID query result fields are used to populate a SELECT list within the HTML form. Listing 26.6 shows the customer.htx template file that will be populated with the query results.

Listing 26.6. The customer.htx template file.

<html>
<title>Order Entry --- Customer Selection List</title>
<BODY BGCOLOR="#FFFFFF">

<CENTER>
<IMG SRC="/ch26/bsball.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/caffrey.jpg" ALIGN="CENTER">
<IMG SRC="/ch26/bkball.jpg" ALIGN="CENTER">
<HR>
<%begindetail%>

<%if CurrentRecord EQ 0 %>
<FORM METHOD="POST" ACTION="/ch26/products.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%idc.CustomerID%>">
<INPUT TYPE="hidden" NAME="ProductID" VALUE="<%idc.ProductID%>">

<P>
<center>
<h3>Registered Online Customers</h3>

<p>
Select a customer from the list below.

<p>
<SELECT NAME="CustomerID" SIZE="3">
<%endif%>

<OPTION VALUE="<%CustomerID%>"><%CompanyName%>
<%enddetail%>

<%if CurrentRecord EQ 0 %>
<h4>No customer records found.
Select "Add Customer" below to add a new record.</h4>

<%else%>
</SELECT>

<p>
<INPUT TYPE="IMAGE" SRC="/ch26/selprod.jpg">
</FORM>

<%endif%>
<hr>
<P>
<FORM METHOD="POST" ACTION="/ch26/main.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%idc.CustomerID%>">
<INPUT TYPE="hidden" NAME="ProductID" VALUE="<%idc.ProductID%>">
<INPUT TYPE="IMAGE" SRC="/ch26/home.jpg">
</FORM>

<IMG SRC="/ch26/addcust.jpg">

<FORM METHOD="POST" ACTION="/ch26/shipping.idc?">
<INPUT TYPE="hidden" NAME="EmployeeID" VALUE="<%idc.EmployeeID%>">
<INPUT TYPE="hidden" NAME="CustomerID" VALUE="<%idc.CustomerID%>">
<INPUT TYPE="hidden" NAME="ProductID" VALUE="<%idc.ProductID%>">
<INPUT TYPE="IMAGE" SRC="/ch26/shipmeth.jpg">
</FORM>

<IMG SRC="/ch26/viewcust.jpg">

</center>
</body>
</html>

As you can see in Figure 26.37, you are presented with a listing of available customers. The user simply can choose a customer from the list and select the Select Products input text image to begin selecting products. The user has the option, with the other selectable text images, to bypass customer selections and move to another form.

Other forms are included with this application that enable users to select products and shipping methods. The software logic is basically the same for these forms as it is for the customer-selection form. This is certainly not a fully functional order-entry application. The big pieces of the puzzle were presented here to give you insight into a possible solution for Web database applications.

Summary

In this chapter, you learned how to create and upsize Access databases to the SQL Server database arena. Even though the example focused mostly on Microsoft solutions, you can use much of the same material in different contexts. You can set up ODBC resources that point to multiple distributed databases, for example, and have those databases service any ODBC-capable Web server. For this sample application template, I used IIS to integrate the webentrySQL ODBC resource database (upsized SQL Server) with a Web-based application.