You can find trouble-shooting answers in unexpected places

While using IIS 5.0 to build a Web-based order-entry application for a client, I solved some intriguing error-handling mysteries that reminded me of a lesson we developers sometimes forget: An error in one part of an application can look like an error in another part of the application. Innocuous HTML or server settings can cause you to spend many hours trying to debug SQL code or stored procedures when in fact no problem exists with the SQL code. To demonstrate my point, I'll step through some pitfalls I encountered in building my client's application.

In IIS 5.0, the buffering setting is on by default for an Active Server Pages (ASP) application. This setting makes the application perform better. But if buffering is turned on and, during the development process, an error occurs, you'll get a standard error page telling you that IIS couldn't display the page. If you're lucky, the error page will include the VBScript or ADO and SQL error code and the line number in the ASP page where the error occurred. You can check that line and look up the error code in the Knowledge Base. But sometimes the error-page message doesn't reflect the true causes of the error. For example, an incorrect SQL statement can cause an error, but only the open statement will show in the error page.

Another error-handling approach I use is to add to the page a Response.Write statement that will output values that specify which error occurred. Turning on buffering kills inline error displays generated with a Response.Write statement by trapping the page and displaying the error page instead. Buffering hides the page errors and your output because it can make non-SQL errors look like SQL errors, as I'll demonstrate in a moment. To fix this problem, you need to turn off buffering during development. Open the Internet Services Manager in the Administrative tools folder, then open the properties for the Web application you are working on. Click the Configuration button, and select the App Options tab. Clear the Enable buffering checkbox, which Screen 1, page 56, shows. When the application is working, you can turn on buffering again. Now, you can use Response.Write to show the application's data so you can determine what the error is rather than wasting time going through your SQL code or stored procedures and trying to figure out why they didn't work. I always use a Response.Write statement like the following one to display any dynamic SQL that I use.

Response.write sSQL

This method lets me inspect the SQL statement, copy the statement to the clipboard, and paste it into Query Analyzer or another SQL tool for testing.

For example, I designed one page in the order-entry application to pull values from the form variables on the calling page, look up data in the database, and perform several calculations. While testing the page, I put the results of the calculations in an array in the inline code. This method let me use the array to store costing calculations for all the items in an order for later output. I used a Response.Write statement to output the array data after it was loaded, so I knew that the array contained the correct data.

For the final display, I moved the for/next loop to surround a newly formatted HTML table. The loop now output the array data to the bottom of the page. After I placed the loop around the HTML table, I viewed the page to see my wizardry. But the page blew up with an error. The error message referred to a type match on the array. That message surprised me because I had just tested the code and I knew the array was working correctly. I found that the script block that performed the work was inside a set of script tags. The code looked like this (the ellipses represent the bulk of the script code):

<script runat=server>
dim iError
dim OrderItems(200,4), I

This code included the Dim statement that I used to dimension the OrderItems array, as the code snippet shows. I knew my script was right, so I figured that the database code in the page or the stored procedure must be at fault. I thought that the shortcut tags behaved exactly like the script tags. But I discovered that inside the <script> blocks, the variables were locally scoped into that block of code. Trying to access a variable in the page outside of the script block resulted in an error. Changing the scripting tags to <%...%> simply changes the scope and makes those variables visible outside of the script code. I changed the <script> tags to <%...%> and the code worked.

dim iError
dim OrderItems(200,4), I

The Pitfalls of Reusing Code

I unraveled another error-handling mystery while using the time-honored development technique of revising previously written code. If you're like most developers, whenever you need to create a new feature or application, you copy code from similar applications you've written and make changes. This method works perfectly in theory and somewhat less than perfectly in practice.

For example, my client's application contained a page that showed shipping information for the current order. The user can change the shipping information and perform tasks such as selecting the method of shipping, the shipper, the ship date, etc. The page also contains the shipping and billing addresses, both of which the user can change. The application had another page that contained the code for address information, so I simply copied that HTML and pasted it into the new page. Then, I needed to add a new field to the table that held the address for the shipping phone number; I copied the table row for the state code field and pasted it into the location that I wanted the phone number in. Then I changed the names of the state code fields in the new row to represent the phone number—or so I thought.

Listing 1 shows the affected rows. If you look closely, you'll see that two fields have the name txtShippingState. When I tested this page, it worked fine. The browser didn't generate an error showing the two fields with the same name, and the values in the fields were correct because they were set from different fields in the database. The HTML code in Listing 1 is also part of an HTML form. When the user clicks a button to post the form, the form executes ShippingExecute.asp, which pulls the values from the form fields, creates a SQL statement, and executes the statement to update the shipping information in the database. When I executed the page, the SQL statement triggered an error. The database was complaining about the SQL statement being improperly formatted. So, I looked at the SQL statement that the script generated and found:

exec UpdateShipping OrderID, '213 smith street', 'NC 203.112.2233', 'USA', '27333', ''

At first glance, this statement seemed fine. But no quote or comma was between the state (NC) and the phone number (203.112.2233). I examined the SQL in the stored procedure several times, but I couldn't find any obvious problems. Then I dug into the ASP code that generated the SQL statement, but it also looked fine.

Finally, I looked at the ASP that pulls the data from the form. Here I discovered that the value from the txtShippingPhone field was coming in blank and that the txtShippingState field had both the state code and the phone number in it. One form field and two values? How did that happen? When you request the value from a form field, if two or more fields have the same name, the ASP request object returns the values from all of the fields. This behavior is great when you want it (e.g., when you need to collect the data from a form in one chunk) but maddening when you don't. I fixed this problem by changing the name of the phone field from txtShippingState to txtShippingPhone. Remember that when you're untangling error messages, the answer you're looking for probably isn't in an obvious spot.