Solved

Operation must use an updateable query

Posted on 2001-06-13
7
198 Views
Last Modified: 2008-03-17
Hi!

I have polls on my website that use databases.
When I view the pages on my hard disk I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

I also uninstalled IIS and reinstalled it again. It worked the first time. Then I don't recall what
I did and it now gives me the same error.

I have been to Microsoft's Knowledge base (Article Q175168) but that has not been of any help.
I have also been to aspfaq.com where I found that you need to set write permissions on the folder that contains the database before you actually put the database in it. I tried that too and it did not work.

Please help.

Husain.
0
Comment
Question by:Husain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:yas022100
ID: 6188212
What is your Query looks like?
You could be doing something like..

Call cn.Execute("Select * from TableName")

Instead of
Set rs = cn.Execute("Select * from TableName")

Does that make sense?
0
 

Expert Comment

by:cgoldfarb
ID: 6188216
This error is typically encountered when your ASP 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 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.

If you get this error and your site is hosted by someone else like an ISP you can fix it yourself in some situations. First try to find a CGI-BIN directory and place your database in their. ISP's normally give the CGI-BIN folder read/write access rights and you can use these so that your database is able to write.

So how do you setup your ADO conenction string to work in the CGI-BIN folder?

Set oConn = Server.CreateObject ("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/cgi-bin/MyFile.mdb")


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
 
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 if you are using an ODBC DSN.


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.
0
 
LVL 11

Expert Comment

by:ASPGuru
ID: 6188217
post your code...

you probably try to update an readonly Recordset....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Accepted Solution

by:
jitganguly earned 50 total points
ID: 6188221
Using Access ?
Need to setup security on the mdb file. Go to Windows explorer, choose your mdb, r-click to properties, security. Make sure the user IUSR_{yourservername} has ful access.
Your web pages are by default accessed by an user named IUSR_{yourservername}
0
 
LVL 15

Expert Comment

by:ameba
ID: 6188696
>It worked the first time. Then I don't recall what I did and it now gives me the same error.

Did you add ";" character at the end of your sql statement?
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6189254
Permissions, permissions, permissions...

Husain, you didn't read your previous Q comments from the experts...
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 6189745
cgoldfarb  posted the correct solution for you.. this is a typical error in access databases on hosted sites..
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question