PRB: ASP returns "Operation Must Use an Updateable Query" (the Operation Must Use updatable Query) Error

Symptom

When you use ActiveX Data Objects (ADO) with Active Server Pages (ASP), you may encounter the following common errors:

Microsoft ole db Provider for ODBC Drivers error '000000'
[Microsoft] [ODBC Microsoft Access 97 Driver] Operation must use an updateable query.

Back to Top

Cause

This article describes the four main causes of this error and the corresponding alternative methods. Although this article discusses Microsoft Access databases, the information provided here applies to other types of databases.

Back to Top

Solution

This error is typically encountered when your script attempts to perform updates or other operations that change the information in the database. The cause of this error is that ADO cannot write data to the database due to the following reasons:

1.	The most common cause is that the Internet Guest Account (IUSR_MACHINE, which belongs to the "Everyone" group by default) has no write permission on database files (. mdb. To solve this problem, use the "Security" option card in Explorer to adjust the properties of this file so that the Internet Guest account has the correct permissions. Note:: When using the Microsoft Access database with ADO, you must also grant the Internet Guest account the write permission to the directory containing. mdb files. This is because Jet will create a. ldb file for processing database locks. Since Jet may create temporary files in the "Temp" directory, you may also need to grant read/write permissions to this folder.
2.	The second cause of this error is that the database is not opened in the correct mode with write permission. If you perform an Open operation on the Connection object, you should use the Mode attribute to indicate the permissions on the Connection, as shown below: SQL = "UPDATE Products Set UnitPrice = 2;" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Mode = 3 '3 = adModeReadWrite Conn.Open "myDSN" Conn.Execute(SQL) Conn.Close Note:: By default, MODE is set to 0 (adModeUnknown). This value is usually allowed to be updated.
3.	Another cause of this error is that in ODBC manager, the "read-only" setting on the "options" page of DSN may be selected.
4.	The last question and alternative method apply to all SQL data sources. This error can be caused by SQL statements that violate the integrity of database references. The following are some of the most common failed queries: • The simplest query group is a query that you cannot change as follows: Cross-Table query with the UniqueValue attribute set to Yes, SQL transfer query, joint query or update (that is, Table Generation) operation query. • Another common cause is that the index of the linked ODBC table contained in the join is not unique. In this case, SQL cannot guarantee that the records in the table are unique, and the field values in the table will change with the query. • There is one reason that there is indeed a reliable alternative. If you try to update the "one" join field of the "one-to-many" query, the Operation will fail unless you enable cascading update. In this way, you can delegate the integrity of the reference to the JET engine.