In "Dynamic Datasheets," October 2000, I showed you how to use FrontPage 2000 to publish SQL Server datasheets from a Web site or intranet site. To demonstrate how easy it is to create a subweb that serves as a web site for publishing SQL Server database contents, I presented two publishing examples that relied on SQL Server tables. In this issue, I cover publishing SQL Server datasheets that are based on custom T-SQL statements, and I show you a workaround for a problem that the FrontPage 2000 Database Wizard has with publishing certain T-SQL statements. I also give an example of how to create a search form that lets users designate at runtime the contents that will appear in a published datasheet. The Database Wizard is attractive because it dynamically reveals the contents of a SQL Server database, a feature that can be useful when realtime updates of database contents are necessary, as in inventory applications.
Setting Up and the First Example
For this article's examples, you need to open FrontPage 2000 and create a subweb for the examples. From the root FrontPage web, choose File, New, Web. In the New dialog box's control for specifying the subweb's location, enter SQLMagDBWiz2 as the subweb's name under the root web (http://server_name). Next, start the Database Wizard; choose Insert, Database, Results to open the first of five main Database Wizard screens. Create a database connection (or reference an existing connection) to the Northwind database on the SQL Server machine to which you log in. ("Dynamic Datasheets" gives detailed instructions for setting up the connection.) Then, advance to the second screen by clicking Next.
On the second screen, select Custom query, then click Edit to open the Custom Query dialog box that Figure 1 shows. In this dialog box, you can enter any custom T-SQL string to specify a result set for a datasheet. The example in Figure 1 returns the CategoryName, ProductName, UnitsInStock, UnitsOnOrder, and ReorderLevel columns from the joined Categories and Products tables. Note that you can paste the query from the Clipboard. So, if you're developing with Microsoft Office, you can use the Microsoft Access visual design tool to graphically create a view, then copy the T-SQL code from Access to the Custom Query dialog box. ("SQL Server Views with Access 2000," January 2000, describes how to create views graphically.) If you have the SQL Server client tools but not the Office 2000 suite, you can use Enterprise Manager to create the views. See "How to access the Query Designer to design a view" in SQL Server Books Online (BOL) for detailed instructions.
To finish publishing a datasheet based on the custom T-SQL statement, click OK to close the Custom Query dialog box, then click Next in the second, third, and fourth Database Wizard screens. On the final wizard screen, create an Active Server Pages (ASP) page based on the datasheet by clicking Finish. Before you can view the page, you must save it by choosing File, Save As and specifying full_category_product_list as the filename. FrontPage 2000 automatically designates the currently active subweb as the storage location and assigns the page an .asp file type. You can click the Preview In Browser button on the toolbar or navigate your browser to http://server_name/ SQLMagDBWiz2/full_category_ product _list.asp to view the page.
Using > and
When you construct custom T-SQL statements, your WHERE clauses often include a greater-than (>) or less-than (<) symbol. The datasheet in the preceding example lists all products and the categories they belong to and the products' UnitsInStock, UnitsOnOrder, and ReorderLevel values. However, you might easily show just the products with UnitsInStock values that are less than their ReorderLevel values. The T-SQL statement to generate this result set is identical to the statement in the first example, with the addition of a WHERE clause. The necessary WHERE clause requires a
Figure 2 shows the Custom Query dialog box for a T-SQL statement that includes a WHERE clause like the one I just described. To check the statement's syntax, click the Verify Query button in the Custom Query dialog box. The FrontPage Database Wizard replies with another dialog box that says the query was verified. Click OK to close this dialog box, then continue through to the end of the wizard screens, as you did in the preceding example. Finally, save the page as stock_below_reorder_string.
When you attempt to examine this second datasheet by either clicking the Preview In Browser toolbar control or navigating in a browser to http://server_name/ SQLMagDBWiz2/stock_below_reorder_string.asp, you get an error message like the one in Figure 3. Curiously, the message points to an error near the > symbol despite the fact that the query statement contains no > symbol. And you saw that the Database Wizard confirmed the validity of the statement.
So, what causes this error? And more important, how can you work around the problem? In this instance, the problem stems from the WHERE clause because this statement is otherwise identical to the preceding statement. Evidently, the FrontPage Database Wizard doesn't process this WHERE clause properly. When I examined in Notepad the ASP script that the wizard generated, I discovered the following expression:
fp_sQry="SELECT Categories.CategoryName, Products.ProductName, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID WHERE (Products.UnitsInStock l)".
Notice that the three characters >|) aren't part of a standard T-SQL SELECT statement. These characters include a > symbol, which is where the error message said a syntax error existed. If you remove those three nonstandard characters and save the page, your browser can open the page and browse the corrected T-SQL string. Unfortunately, if you subsequently try to use the FrontPage Editor to edit the page, FrontPage automatically restores the faulty characters.
A more robust solution to the problem is to use the T-SQL statement to create a view ("SQL Server Views with Access 2000" explains how). Then, you can publish a datasheet based on the view. To test this solution, you can use the T-SQL statement from Figure 2 to create a view named SQLMagDBWiz2, then invoke the Database Wizard and select the SQLMagDBWiz2 view as the source for a datasheet. Save the new web page based on the view as stock_below_reorder_view. Figure 4 shows the web page displayed in a browser. Notice that each row has a UnitsInStock value that is less than the ReorderLevel value, as the WHERE clause specified.
Creating a Search Form
Although many applications require you to set up custom queries at design time, other applications require the flexibility to let users designate query criteria at runtime. Runtime designation is typical of search forms in which users specify criteria that generate a result set. For example, you can let users search for all records with a UnitsOnOrder value equal to a value they designate. The FrontPage Database Wizard automates the creation of search forms and of corresponding datasheets for the result sets.
Figure 5, page 36, shows a search form that the Database Wizard generated. The form's source database object is the SQLMagDBWiz2 view. This form initially returns rows with a UnitsOnOrder value of zero, but you can specify any default value. In addition, users can input a value, say 10, in the UnitsOnOrder text box and click Submit Query. This search criterion generates a result set containing rows with a UnitsOnOrder value of 10.
When creating search forms with the Database Wizard, always click the More Options button on the Database Results Wizard - Step 3 of 5 screen, which opens a More Options dialog box that has Criteria and Ordering buttons. A Defaults button appears but is initially disabled. Click Criteria to open the Criteria dialog box, in which you can designate a field for users to input a search value at runtime.
Figure 6 shows the Criteria and Add Criteria dialog boxes as they appear after you click the Add button in the Criteria dialog box. In the Add Criteria dialog box, you can use the top drop-down list box to designate a Field Name for the form to search on. Next, you can designate a comparison operator between the value that a user specifies and the value in a row. Available operators besides the Equals operator include Not Equal, Less Than, Not Less Than, Greater Than, and Not Greater Than. Type in the Value text box the name of a column value to compare with the user-specified value, and make sure the Use this search form field check box is selected. You can specify a criterion that users can't alter from the search form. Clear the check box so that you can specify the criteria and assign them default values as the next paragraph describes. You can also designate as many criteria fields as there are columns in the source datasheet. And you can combine criteria by using the And or the Or operator.
After you specify the criteria and click OK to return to the More Options dialog box, FrontPage enables the Defaults control. You can use the Defaults dialog box to set initial values for each criterion field. If you don't specify an initial value for a criterion field, the datasheet below the search form will remain empty until a user specifies a criterion value.
Click OK to close the Defaults and More Options dialog boxes. Then, progress through the Database Wizard screens as you did in previous examples and save the resulting web page. (Your form will look like the one in Figure 5.)
FrontPage to the Rescue
Getting to databases across the Web is increasingly important these days. Web access to a database vastly expands the potential community of users, which increases the database's value. Making a database accessible across the Web can often involve immersing yourself in complicated scripted solutions interspersed with HTML. Busy Office developers or SQL Server DBAs might not have time to acquire those skills, but the FrontPage 2000 Database Wizard can come to the rescue by providing a simple yet flexible way to report SQL Server data over web connections. And because the wizard uses ASP to publish datasheets and forms, users browsing a page with Microsoft Internet Explorer (IE) or Netscape Navigator browsers will always see the latest data.
