Operation must use an updateable query" errors? - 4GuysFromRolla.com">
  When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Errors


** UPDATED 24 June 2003 **: Why am I getting "Operation must use an updateable query" errors?

[Print this FAQ]

Answer: The answer to this question comes from a post by Derek Branch in the Databases Forum on the ASPMessageboard.

Here's what Microsoft has to say about the error message... It's usually a problem with permissions:

The following is a common error encountered when using ActiveX Data Objects (ADO) with Active Server Pages:

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 three primary causes of this error, and the 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) does not have Write permissions on the database file (.mdb). To fix this problem, use the Security tab in Explorer (see below if the Security tab does not appear!)) 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"

NOTE: By default, the MODE is set to 0 (adModeUnknown), which generally allows updates.

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 group 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.

Alert reader Matt Smith wrote in with the following situation and solution...

"Our situation: ASP and Access DB worked fine locally - did not work when moved to the host server. 'Write' permissions were the obvious culprit, so we modified the permissions on the directory and also thru the IIS Admin interface. Still did not work. Contacted the host and they said "You need to set write permissions before you put the db in it. Try deleting the database, and re-uploading it, and it should work." Had not heard that before, but it worked."


If you are not using NTFS--if you are still using Fat16 or Fat32--then it should not appear. But by the same token, you shouldn't have a need for it on such file systems.

If you are using NTFS--and especially if you are using WindowsXP--then try the following steps:

(1) Bring up "Windows Explorer" or "My Computer" in a window.
(2) Click on the "Tools" menu in that window.
(3) Click on the "Folder Options" menu item.
(4) Click on the "View" tab.
(5) Find the checkbox labelled "Use simple file sharing (Recommended)"
(6) UNCHECK that checkbox!
(7) Say OK. Close the dialog. Close the window.
(8) You might have to log off and back on again. I didn't, but others have reported they had to.
(9) Now go view the properties of a folder or file with Windows Explorer/My Computer and see if the Security tab isn't there!

Happy Programming!

FAQ posted by Scott Mitchell at 12/21/2000 6:38:47 PM to the Databases, Errors category. This FAQ has been viewed 119,416 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article