ASP FAQ，无法打开关键字的解决方案

Troubleshooting ASP and Microsoft Access Databases

There can be a number of problems that you find when you are dealing with Microsoft Access Databases from ASP and this article tries to show you some of them and the methods to get around the problem. This article will focus on using Microsoft ADO (ActiveX Data Objects) as the method to communicate with the databases.

Problem #1 - Creating an ADO connection string

Depending on the database you are connecting to you need to use different ADO connection strings. These are quite simply to create by hand but I find it much better to use a utility that I wrote to generate the ADO connection strings using a wizard.

This utility including full source code in Delphi is available at http://www.iisfaq.com/Articles/307/ .

Even with the utility above we need to determine the correct driver to use to connect to the database.

Microsoft Access 97	-	Microsoft Jet 3.51 OLE DB Provider
Microsoft Access 2000	-	Microsoft Jet 4.0 OLE DB Provider
Microsoft Access XP	-	Microsoft Jet 4.0 OLE DB Provider

Note: The latest versions of Microsoft MDAC which includes Microsoft ADO does not include the Microsoft Jet 3.51 OLE DB Provider.

To download the latest version of Microsoft MDAC please go to http://www.microsoft.com/data/download.htm ; you can also download the latest version of the Jet Components from the same location.

Example Connection String
Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=c:/testing/TestForWebServer.mdb;Persist Security Info=True

The example connection string above first specifies the driver that we are going to use. The database is an Access 2000 database so we are using the Microsoft.Jet.OLEDB.4.0 provider.

In the example above we have a blank password as shown by the Password="" in the connection string. We need to be awear that the above connection string is not valid in ASP as it stands now.

Using this connection string from an ASP page will result in an error because of the Password=""

Result of above command ADODB.Connection (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /users.asp, line 15

The reason that the above code fails is because if you look closely at the provider string what the ASP engine actually parses is not what you think it is.

The provider string above is actually rendered to :

Provider=Microsoft.Jet.OLEDB.4.0;Password=";Data Source=c:/testing/TestForWebServer.mdb;Persist Security Info=True

If you look closely at the Password= portion of the provider string we see only 1 dbl-quote (") and not the two sets of dbl-quotes the provider string originally contained. This is because the dbl-quote (") is a string delimiter in VB Script. We must escape the quote; this means we need to replace the single dbl-quote (") with a two dbl-quotes ("").

This change to our ASP code is shown below.

Result of above command Nothing is returned because there is no errors our any output from the above command. If you get an error the web server may not have the right to open the database. See the next section for help on this problem.

 

Problem #2 - NTFS Permissions

NTFS permissions play a big role in Microsoft Access Databases and ASP. The reason for this is the fact that the web server runs under a specific user account; this user account is by default the IUSR_XXXX where XXXX is the name of the computer the web server is running on. If the computer name is CCROWE then the web server account by default will be IUSR_CCROWE.

If the web server is configured to run out-of-process which means its Application Protection is set to High (Isolated) on the Home Directory tab of the web server properties then it runs as a different user account. The out-of-process user account is called IWAM_XXXX where XXXX is the name of the computer the web server is running on. So again if we use the computer name of CCROWE the out of process user account is called IWAM_CCROWE.

Note: It is very important to remember the IUSR_ and the IWAM_ accounts when dealing with access permissions.

In our sample below the database is stored at c:/testing/ttestforwebserver.mdb

You may see the following error if you just create a database and try to access it through ASP.

Result of above command Microsoft JET Database Engine (0x80004005) The Microsoft Jet database engine cannot open the file 'C:/testing/TestForWebServer.mdb'. It is already opened exclusively by another user, or you need permission to view its data. /users.asp, line 13

This error message is quite good at explaining the problem; but there are other permission based error messages that can be returned that are very hard to decipher if you have never seen it before.

Result of above command Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x614 Thread 0x6c0 DBC 0x21dd07c Jet'. (FileName), (LineNumber)

The above two error messages relate to the fact that the web server account does not have the rights to access the database.

Setting the following NTFS permissions will cure the errors above.

Task   Permission   User Accounts Actual MDB Database File (*.MDB)   Modify   IUSR_CCROWE, IWAM_CCROWE  Folder where the *.MDB file is located   Modify   IUSR_CCROWE, IWAM_CCROWE  Temp Directories   Modify   IUSR_CCROWE, IWAM_CCROWE

Notes:

• If you set the FOLDER permission first and then create your database the database will by default inherit the permissions from the folder.
• The Modify NTFS permission in Windows 2000 is Change under Windows NT 4.
• Change the IUSR_CCROWE used above to the user account that the web server is using
• I have also found that the JET Engine creates temporary files as well, these normally get stored in the Temp directory (you can find the temp directory by going into Control Panel -> System -> Advanced - Environment Variables, I would set both the TMP and TEMP folders to allow Change Permissions for the IUSR account.

Other References that may be helpful in your database problems.

• Misc Database Related Information on www.iisfaq.com
  http://www.iisfaq.com/DATABASES/
   
• Operation must use an updateable query
  http://www.iisfaq.com/Articles/50/
   
• Error Message: 0x80004005: General Error Unable to Open Registry Key
  http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q295297
   
• "Disk or Network Error" or "Unspecified Error" Returned when Using Jet
  http://support.microsoft.com/support/kb/articles/Q251/2/54.ASP