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



FAQ
faq@microsoft.com        Download the code (1KB)
Srinath Vasireddy

Troubleshooting ASP and ADO Errors

M
y ASP file doesn’t access my database." "I can’t connect to my database from my code." "I’m having problems calling and debugging stored procedures." These are some of the problems I hear every day as a Microsoft® developer support engineer. I’ve collected some useful troubleshooting techniques for these data-centric problems. And while some of my suggestions may seem simple, they’re proven to solve fundamental problems.

The Basics
      When your code breaks, a good starting place for fixing it is to figure out which portions of your code work OK. If you’ve recently added new code to your data-centric project, and the old code connected to the database properly, you can narrow down the problem to the new code. The problem could reside in the logic or in an incorrect Data Source Name (DSN) in the connect string, among several other things.
      If your existing code is completely hosed and you can’t get anything to work, start with the fundamentals—testing the Web services. Run the default.htm file. Once you’re sure that HTML files work fine, run this simple test.asp file: 

 <% 
     Response.write "Hello, I am alive" 
 %>
If this test works, ASP is not your problem. If you get an error with test.asp, there could be some problem with your Internet Information Server (IIS) installation. In some cases this can be attributed to a missing or an unregistered copy of asp.dll. You should be able to register this DLL from a command prompt with the following statement: 
 REGSVR32 C:\WINNT\system32\inetsrv\asp.dll
(If you’re using Personal Web Server with Windows® 98, this file will be found in C:\Windows\System\Inetsrv\asp.dll instead.) Reinstalling the appropriate applications and Windows service packs will generally resolve any configuration problems you might have. This information is usually available in the readme files for the associated software.

Problems Connecting to a Database
      Many developers get buried under huge amounts of code in the problem-solving process. Sometimes it’s like searching for a needle in a haystack. You should always break down your code into smaller chunks to isolate the section that’s causing the error. For example, if you are trying to debug a database connection error in code generated by the Data Form Wizard, you will probably spend most of your time trying to understand the code rather than finding a solution to the problem. Use the technique we in Microsoft developer support often use: work with simple test code to narrow down the problem. Figure 1 is a code snippet that tests the database connection for a predefined DSN.
      Two error messages commonly encountered in the event of a connection failure are 

 [Microsoft][ODBC Driver Manager] Data source name not found
and 
 no default driver specified
"Data source name not found" indicates that the error occurred in the ODBC Driver Manager, so the problem could be with the DSN. Make sure you have a valid system DSN. You can check this by running the ODBC Data Source Administrator from the Windows Control Panel. Select the system DSN that you are working with and click the Configure button to start a wizard. Before completing the configuration, you will get a chance to test the connection by clicking the Test Data Source button. If this gives you a "Tests Failed!" message, your problem lies with the connection parameters you entered in the wizard. If the connection succeeds, you will get a "Tests Completed Successfully!" message. At this point, check the connect string in your ASP code to see if you have used the right DSN. If not, change it to the correct DSN (as specified in ODBC Data Source Administrator) to resolve the issue. This testing feature is not available when you connect to a Microsoft Access database.
      The following error message tells you that the specified server in the connection string is not available: 
 [Microsoft][ODBC SQL Server Driver][DBNMPNTW]Specified SQL server not found.
A simple reason could be that the SQL Server™ service is not running. This message also shows which network library is in use. In this example, DBNMPNTW indicates that a Named Pipes library is used. Figure 2 lists the network libraries you might see here. You can find more information by looping through the ADO errors collection: 
 Dim errLoop , ErrCol
 'Here Cnn is the ADO connection object.
 Set ErrCol = Cnn.Errors
 
 'You can also check the other properties for Number, Source, SQLState and 
 'NativeError.
 For Each errLoop In ErrCol
     Response.write errLoop.Description & "<BR>"
 Next
This will give you a more detailed message than "You are not able to establish a connection to the SQL Server." The ADO errors collection returns an error message that looks something like this: 
 [Microsoft][ODBC SQL Server Driver][DBNMPNTW]Specified SQL server not found.
 [Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionOpen (CreateFile()).
      If you are having trouble setting up or using a DSN connection, try to get your data without using a DSN connection. A typical DSN-less connection to SQL Server would look like this: 
 cnnPubs.ConnectionString = "Driver={SQL _
 Server};server=srinathv2;database=Pubs;uid=sa;pwd="
And a DSN-less connection to Microsoft Access would looks like this: 
 cnnStr = "Driver={Microsoft Access Driver (*.mdb)};" & _
          "Dbq=C:\Database\advworks.mdb;" & _
          "Uid=;Pwd="
      Your database connection might be failing for still other reasons. If your global.asa file is not firing, then the connection string stored in the Session variable will not be available. To make sure this works, try to print the Session variable storing the connection string from an ASP page. Knowledge Base article Q188715 (http://support.microsoft.com/support/kb/articles/q188/7/15.asp) explains in detail why this would fail. Security reasons could also be a factor, as explained in Knowledge Base article Q175671 (http://support.microsoft.com/support/kb/articles/q175/6/71.asp).
      Another common error message you might see is: 
 Microsoft OLE DB Provider for ODBC error 80004005
You will find several troubleshooting tips for this problem in Knowledge Base article Q183060 (http://support.microsoft.com/support/kb/articles/q183/0/60.asp).

Know Your Defaults
      It’s very important to understand the default settings you are working with. Do you know what the default values are for the recordset properties CursorLocation, CursorType, and LockType? If not, Figure 3 shows a table for easy reference. When you aren’t familiar with these, you may experience unexpected behavior.
      There are two common scenarios when you work with OLE DB providers for ODBC (the default) and SQL Server. In the first scenario, the code in Figure 4 fails because the default value for the RecordSet’s LockType property is set to adLockReadOnly. Changing the LockType to adLockOptimistic resolves this issue. As for the second scenario, did you ever wonder why your recordset’s RecordCount property returns one, even though you know it has more than one record? There are several factors responsible for this behavior. One is the effect of the default settings of CursorLocation and CursorType. When CursorLocation is set to adUseServer and CursorType is set to adOpenForwardOnly or adOpenDynamic, RecordCount returns one. To get the proper RecordCount, you have to change these values.
      If you are working with different data providers, a function that could be particularly useful is the recordset’s Supports method. You can use this method to check whether the provider supports some particular functionality. See the online ADO documentation, found at http://www.microsoft.com/data/ado/adords15, for more information on this method.

Working with Stored Procedures
      Working with the Command object is challenging; it’s easy to make mistakes. Two particularly error-prone areas are the CommandType property and the Parameters collection.
      You can avoid most of the most common errors I see by choosing an appropriate CommandType for the specified CommandText. If the CommandType is not specified, it defaults to adCmdUnknown. ADO makes calls to the provider by executing the CommandText first as plain text, then as a stored procedure call, and finally as a base table name as it tries to determine the appropriate type. To see this in action, take a look at an ODBC trace when you use different CommandType values.
      Many users have a problem calling stored procedures that have parameters. Most errors are the result of an incorrectly specified data type or direction. Figure 5 shows some simple test code that iterates through the Parameters collection and displays its individual properties. Once you know the right parameters to use, your calls to the stored procedure will go off without a hitch.
      You can read the Output and Return values of the stored procedure only after closing the recordset by setting it to the value nothing. For more information on how to invoke a stored procedure using ADO, refer to the Knowledge Base article Q185125 (http://support.microsoft.com/support/kb/articles/q185/1/25.asp).

SQL Trace and ODBC Trace
      The SQL Trace program is one of the SQL Server client tools. You can use this tool to trace calls to the SQL Server database. SQL Trace can be particularly useful for tracing dynamically generated SQL statements. It can also be used to check stored procedure calls and parameters.
Figure 6: ODBC Traccing
Figure 6: ODBC Traccing

      ODBC Trace is another useful utility for databases that don’t have tracing facilities like the ones you get with SQL Server. This feature comes with ODBC Data Source Administrator, which can be accessed from the Control Panel. You can turn on the tracing feature by choosing the ODBC Control Panel applet, selecting the Tracing tab and clicking the Start Tracing Now button (see Figure 6). The trace information is logged in the sql.log text file, although the output log can be set from this Tracing screen as well. If you discover errors originating in your ASP files, check for the sections titled SQL_ERROR and DIAG in the log file, where you will find an appropriate error description. And just so you don’t gum up your system’s performance, make sure you turn off tracing after you’re finished debugging.

Conclusion
      I’ve described some of the fundamental ASP and ADO programming issues I’m confronted with most frequently. I’ve also provided some helpful tips for debugging them and a brief overview of the tools and samples you can use to troubleshoot them. Next time you run into some trouble, try some of the techniques I’ve suggested. It could save you a call to developer support!

From the November 1998 issue of Microsoft Interactive Developer.