Since Microsoft developed the ODBC, the Windows operating systems, one of the most popular web servers available, and two major databases (Access, and SQL Server) and acquired FoxPro a few years ago, you can bet you will see more than your share of Microsoft data connection error messages. Fortunately, Microsoft and the rest of the world are phenomenal about documenting both problems and solutions. In addition to the common Microsoft error messages we detail in this section, you might also want to consult www.microsoft.com and www.macromedia.com, newsgroups dedicated to web programming or your chosen database, and the major search engines.
If you are using Access as a back-end database for your website, the web server will need the appropriate read and write permissions to the database file. IIS runs as a web service typically under the user account IUSR_<machine name>, but most web administrators immediately change the user account the web service runs under to something else for security reasons. If you do that, you must also add the new, renamed web service user account to the access rights of the database file. If the web server does not have read and write permissions to the database, you can see any of the following error messages.
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access 2000 Driver] Couldn't use '(unknown)'; file already in use. Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data. Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access 2000 Driver] Couldn't lock file.
To apply appropriate permissions, you need to get to the Properties ® File Security window for you Access file on your web server computer. Be certain that the user under which the web server is running has access to the file. In addition, make sure the web server user has read and write access, as shown in Figure 10.23.
Figure 10.23: The web server must have access to your database file
One of the most common SQL Server connection errors occurs when you attempt to connect to SQL Server. You receive the following error message:
Login failed for user 'NULL'. Reason: Not associated with a trusted SQL Server connection
This error is typically caused by an authentication setting and is usually easily resolved. To do so, you'll need to alter your SQL Server security settings. Follow these steps:
Choose Start ® Programs ® Microsoft SQL Server ® Enterprise Manager to open SQL Server Enterprise Manager.
Right-click on your server, and choose Properties from the shortcut menu to open the SQL Server Proprieties (Configure) dialog box, as shown in Figure 10.24.
Figure 10.24: The Security tab in the SQL Server Properties (Configure) dialog box
Click the Securities tab, and in the Authentication section, click the SQL Server and Windows option.
Click OK. SQL Server prompts you to stop and restart SQL Server. When SQL Server restarts, you can access the databases in the server.
When you attempt to view a dynamic web page, you receive the following error message due to a number of possible scenarios. The DSN may be missing or corrupt, the DSN may not be a System DSN on the web server, or the Access database may be in use and locked.
80004005-Data source name not found and no default driver specified
To solve the problem, first be sure that the DSN exists on the web server as a System DSN. If the DSN exists as a Local DSN, delete the Local DSN and create a System DSN. If you suspect the Access database file is locked because someone else is accessing it, locate the Access file on the server and search for a locked file. The locked file will have the extension .ldb. Delete the locked file, restart the server, and try to access the dynamic page again.
When you try to view a page that connects to SQL Server, you may see the following error message. This error message is typically generated when SQL Server does not authenticate the account or password that the web page is submitting to access a database.
Typically this error is born of permission problems. In the "SQL Server Login Failed" section, we've already suggested setting SQL Server security to Server and Windows Security. Another possible solution is to check for an underline (_) in the name of the SQL account name used to log into SQL Server. SQL Server does not allow an underscore in SQL account names.
You'll receive the following error message if one of your web pages attempts to update an Access Date/Time column with an empty string or a value that cannot be converted to a Date/Time value.
80040e07-Data type mismatch in criteria expression
To avoid this error message, simply avoid inserting or updating a Date/Time column if you have no value to insert.
When your web page attempts to pass a SQL statement to a database and references a data column that does not exist in the table, you may receive the following error message.
80040e10-Too few parameters
In most cases, the cause of the error is a misspelled data column name. Carefully compare the data column names in your SQL statement with the data column names in your database table.