Solving Error-Handling Mysteries

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):

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