Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 175168 - Last Review: January 23, 2006 - Revision: 4.3
ASP returns "Operation must use an updateable query" error
This article was previously published under Q175168
You may encounter the following common error when you use ActiveX Data Objects
(ADO) with Active Server Pages (ASP):
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access 97 Driver] Operation must use an
updateable query.
This article explains the four primary causes of this error and the corresponding workarounds. Although this article refers to Microsoft Access databases, the information provided here also applies to other types of databases.
This error is typically encountered when your script attempts to perform an
UPDATE or some other action that alters the information in the database.
This error occurs because ADO is unable to write to the database for one of
the following reasons:
- The most common reason is that the Internet Guest account (IUSR_MACHINE), which is by default part of the "Everyone" group, does not have Write permissions on the database file (.mdb). To fix this problem, use the Security tab in Explorer to adjust the properties for this file so that the Internet Guest account has the correct
permissions.
NOTE: When using Microsoft Access databases with ADO, it is also necessary to give the Internet Guest account Write permissions on the directory containing the .mdb file. This is because Jet creates an .ldb file to handle database locking. You may also need to give read/write permission on the "Temp" folder because Jet may create temporary files in this directory. - A second cause of this error is that the database was not opened with
the correct MODE for writing. If you perform the Open on the Connection
object, you use the Mode property to indicate the permissions on the
connection as shown here:
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
- Another cause of this error is that the "Read Only" setting may be
checked in the Options page for this DSN in the ODBC Manager.
- The last issue and work around pertains to any SQL data source. The error can be caused by SQL statements that violate referential integrity of
the database. Here are a few of the most common queries that fail:
- The simplest groups to deal with are those you cannot change: crosstab,
SQL pass-through, union, or update (or make-table) action queries that
have UniqueValue properties set to Yes.
- Another very common cause is when the join includes linked ODBC tables
that do not have unique indexes. In this case, there is no way for SQL
to guarantee that records are unique in a table that has fields whose
value will change with the query.
- One cause does have a robust workaround. If you try to update a join
field on the "one" side of a "one-to-many" query it will fail unless
you turn on cascading updates. This way, you delegate referential
integrity to the JET engine.
This behavior is by design.
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft Internet Information Server 4.0
- Microsoft Internet Information Services 5.0
- Microsoft ActiveX Data Objects 2.0
- Microsoft ActiveX Data Objects 2.1
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.7
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
| kbdatabase kberrmsg kbprb KB175168 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please