This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

MIND

Beyond the Browser
beyond@microsoft.com         Download the code (2KB)
Ken Spencer

Work Around Errors in Database Web Apps
O
ne of the issues you face when building Web applications is handling the errors you encounter when interacting with a back-end database. I was recently working with someone to create a new Web site with SQL Server™, ActiveX®Data Objects (ADO), and ASP. Lots of little things came up that I thought were worth sharing with MIND readers, so I'll focus this column on what I learned from this experience and the solutions to many of the problems I faced. Some of the solutions outlined here may seem obvious, but you'd be surprised how often these problems crop up.
      The first step in my project was creating the database. No problems there; just use SQL Server Enterprise Manager or the Visual Database Tools to create the tables and off you go. I also had some tables in a Microsoft®Access database that I imported into SQL Server 7.0 using the product's new import features. With the database in place, I began building the application.
      The first step in creating the database was to create an include file with the frequently used database routines in it. By using a standard include file for the code library, I could use the same routines over and over from various ASP files. So I created an include file named Database.asp. You might wonder why I'd call this file Database.asp and not Database.inc. The Visual InterDev™6.0 editor handles ASP files differently than other files. For instance, if you open an .inc file in the source editor, the command completion and IntelliSense®features will not work. Rename the file to .asp and you get the features. So that's just what I did. I also enabled the Visual InterDev Include design-time control and used it to create the references to the include files in the application.
      My first cut at Database.asp looks like the code in Figure 1. If you have kept up with the Beyond the Browser column, you may recognize some of the routines in this file. Yes, they are essentially the same as those used in the high-performance application I covered recently that used Microsoft Transaction Server (MTS), ADO, Visual Basic®, and ASP. If things work, why not reuse them? So I took the .bas Visual Basic module and turned it into Database.asp. I then tweaked the procedures and had several standard functions for using ADO against SQL Server.
      One of the most important functions in Database.asp is GetDSN. This function passes the correct DSN information, including the security ID when needed. Want to change the DSN or security information? Change GetDSN and you're all set. So this function is reused throughout the application.
      The RunWithRS and RunWithRS_RW functions are also used extensively throughout the application. Every time the application needs to create a recordset, I use one of these functions unless there is something special the code needs to do that these functions do not support. Once these functions were working correctly, I thought I was finished with the ADO stuff. Not so fast!
      After I had four or five ASP files working, I created another new page and tried to use it. This page uses the same RunWithRS_RW function that I had employed in several other pages. Every time I viewed the page, it blew up. ADO simply returned the error description "Cursor Error." So the trial-and-error process started. Naturally, since the error said something about a cursor, that's where I started looking first. I copied the RunWithRS_RW code into a test ASP file and began trying things.
      I will skip over some of the trial-and-error process I went through and show you what I found. I had to change one line of code in the RunWithRS_RW procedure to make the code run. The code was using a client cursor; I changed this to a server cursor. That's it. So I created this new procedure and added it to Database.asp: 


 Function RunWithRS_RWS( strsql)
     on error resume next
     ' Set up Command and Connection objects
     Dim rsrw, cmd

     Set rsrw = server.createobject("ADODB.Recordset")
     Set cmd = server.createobject("ADODB.Command")

     'Run the procedure
     cmd.ActiveConnection = GetDSN()
     cmd.CommandText = strsql
     cmd.CommandType = adCmdText
     rsrw.CursorLocation = adUseServer
     rsrw.Open cmd, , adOpenDynamic, adLockOptimistic
     Set cmd = Nothing
     Set RunWithRS_RWS = rsrw
 End Function
The last S in RunWithRS_RWS stands for Server cursor.
      If you look at this new function carefully, you will see that the new procedure uses the following line to set the cursor location to the server: 



 rsrw.CursorLocation = adUseServer
I could not find anything in my SQL statement or in my use of the recordset that would require a server cursor instead of a client cursor. Go figure. Since any SQL statement that failed with RunWithRS_RW worked with RunWithRS_RWS, that's what I used.
      Next, I had some interesting issues with field types and sizes. As the application development proceeded, I ran into another spot where I was getting an error message that referred to cursors. I tried both RunWithRS_RW and RunWithRS_RWS functions with this code to no avail. This brought about much head scratching and gnashing of teeth as I tried to solve the problem. What was the resolution? Well, believe it or not, it was a field length error. The database field would not accept the data that was being stuffed into it because it was too long. I changed the field length and it worked. I added code like this to make sure that I passed only valid data into the database: 


 rs("ShipAddress1") = trim(request("ShipAddress1"))
      This trims all the blanks from the ShipAddress1 request variable before the data is set in the ShipAddress1 database field. If you do not do this, you may inject unwanted blank characters into the database.
      Here's another tip for using Trim: one of the pages hits an ASP file from a credit card processing company. This page took its input in the form of Request variables. I kept getting an error complaining that my customer ID was too long. I continually checked the customer ID, and it was OK. So I finally added Trim to all the other Request fields. Guess what? It worked.
      Figure 2: The Customers Table Designer
Figure 2: The Customers Table Designer

      At another point in the application, the team decided to make a small change to the database. The DBA used Enterprise Manager to open and modify the Customers table. Figure 2 shows the Customers table just after the Email Datatype cell was changed from nVarChar to nChar. No big deal; just change the datatype and save the changes, right? Notice that the length of the Email column is now 10? Well, it was 50 before the datatype was changed to nChar. Save these changes now and the data in this column is truncated.
      It does not matter if you select the new datatype from the dropdown list or type it in; Enterprise Manager changes the length to 10 for an nChar or Char. Well, the DBA made the changes quickly and saved the table without realizing the problem. The States table was also changed and saved. Once the changes were made, the DBA told me to go ahead and test the application again.
      Ha! The application started blowing up everywhere. So after a few minutes I happened to look in the States table, and guess what? The state name had been truncated to 10 characters! A little investigation uncovered these inadvertent changes in several tables. A few more hours went into changing the column lengths back to the correct length and cleaning up the data. Finally, hours later, the application worked and got beyond the problem the database was changed to correct in the first place.
      Around this time, the DBA also changed some fields from nChar and nVarChar to Char. This time he made sure the field lengths were correct so there would not be any problems. The changes this time did not cause truncation of any data. So I ran the application again and it failed in certain areas. This time I discovered that all the columns that were changed had been padded with spaces. Makes sense, right? If you put 10 characters in a Char column that is 20 characters long, there are probably 10 extra spaces there. Hmmm…. This time I changed the ASP code to handle the extra spaces like this: 



 if trim(rs("CustomerName")) = "" then
     rs("CustomerName") = trim(request("ShipName"))
 end if
This makes sure both the If test and the setting of the CustomerName column do not have any additional spaces used in the test. If the If statement did not use the Trim function, then it would fail if the CustomerName column had spaces padded to the end of the data.
      You can also use the TSQL Rtrim and Ltrim functions to trim spaces in stored procedures or in a SQL statement. Both the ASP and TSQL functions have their place, depending upon your application's needs. Just don't forget to handle the spaces or Null values, or you are in for a headache.
      Another little surprise popped up in the midst of the project. The DBA created a new table that I needed and told me to test it. I tried running the part of the application that used it. ADO complained that the table was not there. I looked at the table and it seemed to be there. What gives?
      Well, some poking around in Enterprise Manager showed something interesting. The original tables used in the database were created by the dbo user. These worked fine in the application. The new tables were created under another login, so guess what? They did not work because of the security context for the table.
Figure 3: Enterprise Manager Database Tables

      Figure 3: Enterprise Manager Database Tables

       Figure 3 shows Enterprise Manager with the tables for the database shown. You can see that several tables are in the database twice. For instance, GlobalSettings shows up once with dbo as the owner and again with CustomerUser as the owner. These are two different tables and behave exactly that way. This makes it very interesting when developers are hitting the database with one login ID or another. I had to go back to basics and make sure all the security settings were correct. Then I could complete the changes and test again. At that point, the application worked correctly. The moral? Before you go making a bunch of changes when problems occur, make sure your security settings are correct for both the login ID and the table owner. Remember the GetDSN function? Using GetDSN makes sure all parts of the application are using the same security.
      Another thing I found interesting was the process of separating the ADO and SQL issues from Visual InterDev and other parts of the application. For instance, let's say you create the following SQL in your code: 



 sSQL = "SELECT CartOrderItems.ID, CartOrderItems.ItemID,
   CartOrderItems.Quantity, "  & _
   Items.ItemName, Items.ItemPrice  & _
   FROM CartOrderItems INNER  & _
   JOIN Items ON CartOrderItems.ItemID = Items.ItemID where CartID = '" &_
       CartID & "'"
      Assume that your application does not work correctly when you use this SQL statement. How do you test this? Well, the first thing that I do is display the entire SQL string in the browser so I can see exactly what is going on. The string is usually displayed with this ASP code 


 Response.Write "<br> SQL is : " & ssql
which results in the following string in the browser: 


 SQL is : SELECT CartOrderItems.ID, CartOrderItems.ItemID,
 CartOrderItems.Quantity, Items.ItemName, Items.ItemPrice FROM CartOrderItems 
 INNER JOIN Items ON CartOrderItems.ItemID = Items.ItemID where CartID = '114'
      Now, you might say this is pretty simple because it just lets you see if the CartID field is correct in the SQL. That's not entirely the point, although it helps. If this SQL generates an error, how can I tell whether the SQL is incorrect or if it's just ADO or ASP complaining? Maybe Visual InterDev is getting in the way. What I do here is copy the SQL from the browser to the clipboard, then fire up the SQL Server Query Analyzer, select the same database the application uses, and paste in the SQL. Next, I execute the SQL and look at the results. Sometimes you will get code to execute in Query Analyzer that will not execute using ADO. When this occurs, you can search the Microsoft Knowledge Base for ADO problems or try tweaking the SQL to get it to work. At least you have a better idea of where the problem is. Of course, you can use Debug mode in Visual InterDev to inspect the sSQL variable and copy it to the clipboard.
      Another area that I have not really discussed is runtime error handling. What happens when an error does occur? Since VBScript does not have very good error handling, you must rely on simple error handling using the On Error Resume Next statement. This lets the application continue executing after an error occurs instead of just blowing up with an error message.
      You must make sure you are trapping the correct error. For instance, you might check the error object like this: 


 If Err.Number <> 0 Then . . .
This may or may not work. What happens when an error occurred 10 lines before this one? If you have On Error Resume Next turned on, the application continues to run and you don't find out about the error until you check the If statement. You must check the Err.Number value after each operation to determine where the error occurred. If you don't check for errors until the end of a block of code, Err may report an error that had happened much earlier. You can clear errors from the Error object with the Err.Clear method. After clearing it, any new errors will reset the error code. To work with SQL Server or ADO errors in your application, you can search Knowledge Base for those errors and build traps for them into your application. This would be a good candidate for another routine in Database.asp.
      This application does not use the Scripting Object Model, but if it did, you would want to have Visual Studio®6.0 Service Pack 3 loaded on the Web server. SP3 updates the Scripting Object Model to report better error messages from the database functions. This is handy when you have deployed your application and users are testing it. The updated error messages make it easier to track down the errors. To take full advantage of SP3, you must also copy the new Script Library files installed by the service pack into your application. You can literally copy the files directly into the _ScriptLibrary directory in your Web project. This will update your project files with the new ones from SP3.
      When creating any application, you are bound to run into a few problems along the way. When you do, it pays to take the time to try to find the heart of the problem and solve it, not just flail away at symptoms. With a bit of trial and error, you can work around most issues.

From the October 1999 issue of Microsoft Internet Developer.