Operation must use an updateable query

  • Thread starter Thread starter Arpan
  • Start date Start date
A

Arpan

I have always been working with SQL Server 2005 for ASP.NET apps but
due to some reasons, had to revert back to MS-Access 2000.

When I try to insert/update a MS-Access DB table (MDB), ASP.NET
generates the following error:

Operation must use an updateable query.

pointing to a line that says

oledbCmd.ExecuteNonQuery()

I am trying to execute a Query (using the OledbCommand object) that
exists in the Access database.

I have gone through heaps of articles to overcome this error &
concluded that the unanimous & most common reason behind this error is
that the directory which houses the Access MDB file doesn't have the
requisite Write & Modify permissions.

I navigated to the directory where the MDB file resides in Windows
Explorer, right-clicked the directory & clicked 'Properties'. Under
the 'Security' tab, I clicked the 'Add' button which opened a dialog
titled 'Select Users or Groups'. In this dialog, I clicked the
'Advanced...' button which opened another dialog with the same title
'Select Users or Groups'. In this dialog, I clicked the 'Find Now'
button which listed the users/groups existing in my machine. ASPNET &
IUSR_MYPC (where 'MYPC' is the machine name) were listed. I selected
both of them - one by one - & gave both of them 'Full Control'
permissions but still the error persists.

Can someone please help me resolve this error? It's driving me nuts
since last more than 3 hours!!

I am working on WinXP Pro SP2.
 
Yes Teemu....I did check that......the MDB file is not marked read-
only.

What else can I do? Pleassssssssse give me a concrete solution.
 
Yes Teemu....I did check that......the MDB file is not marked read-
only.

What else can I do? Pleassssssssse give me a concrete solution.
Click to expand...


http://support.microsoft.com/kb/q175168/


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
 
Well, I could unearth the cause of the error but couldn't resolve it.

Actually the Query I am invoking in MS-Access is this:

UPDATE FirstGlobal SET Quantity = (SELECT Quantity FROM FirstGlobal
WHERE ClientName = [ClientName1] AND Scrip = [Scrip1] AND ContNoteNo =
[ContNoteNo1]) + [AddQty1]
WHERE ClientName=[ClientName1] AND Scrip=[Scrip1] AND
ContNoteNo=[ContNoteNo1];

When I run the above Query by clicking the 'Run' button (with the
exclamation mark) while I am in SQL View in MS-Access, then I am first
prompted to enter a value for the input parameter 'ClientName1', then
I am prompted to enter a value for the input parameter 'Scrip1', then
I am prompted to enter a value for the input parameter 'ContNoteNo1' &
finally I am prompted to enter a value for the input parameter
'AddQty1'.

At this point, Access generates the 'Operation must use an updateable
query' error.

After some tests, I concluded that the source of the error is the sub-
query 'SELECT Quantity FROM FirstGlobal.......'. If I replace the sub-
query with any number, say, 100 & then add it to the parameter
'AddQty1', then the above Query runs perfectly.

But what is wrong with the sub-query?

Please note that the sub-query will ALWAYS RETURN only ONE VALUE &
will NEVER BE NULL.....that is for sure.
 
It seems the JET DB Engine treats any query with a sub-query as a non-
updateable query which was why Access was generating the error. I
changed the query to overcome the error:

UPDATE FirstGlobal AS FG1 INNER JOIN FirstGlobal AS FG2 ON
(FG1.ClientName = FG2.ClientName) AND (FG1.Scrip = FG2.Scrip) AND
(FG1.ContNoteNo = FG2.ContNoteNo) SET FG1.Quantity = FG1.Quantity +
[AddQty1]
WHERE FG1.ClientName=[ClientName1] AND FG1.Scrip=[Scrip1] AND
FG1.ContNoteNo=[ContNoteNo1];


Well, I could unearth the cause of the error but couldn't resolve it.

Actually the Query I am invoking in MS-Access is this:

UPDATE FirstGlobal SET Quantity = (SELECT Quantity FROM FirstGlobal
WHERE ClientName = [ClientName1] AND Scrip = [Scrip1] AND ContNoteNo =
[ContNoteNo1]) + [AddQty1]
WHERE ClientName=[ClientName1] AND Scrip=[Scrip1] AND
ContNoteNo=[ContNoteNo1];

When I run the above Query by clicking the 'Run' button (with the
exclamation mark) while I am in SQL View in MS-Access, then I am first
prompted to enter a value for the input parameter 'ClientName1', then
I am prompted to enter a value for the input parameter 'Scrip1', then
I am prompted to enter a value for the input parameter 'ContNoteNo1' &
finally I am prompted to enter a value for the input parameter
'AddQty1'.

At this point, Access generates the 'Operation must use an updateable
query' error.

After some tests, I concluded that the source of the error is the sub-
query 'SELECT Quantity FROM FirstGlobal.......'. If I replace the sub-
query with any number, say, 100 & then add it to the parameter
'AddQty1', then the above Query runs perfectly.

But what is wrong with the sub-query?

Please note that the sub-query will ALWAYS RETURN only ONE VALUE &
will NEVER BE NULL.....that is for sure.

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:
Click to expand...
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- Hide quoted text -- Show quoted text -
Click to expand...
Click to expand...
 
Well, I could unearth the cause of the error but couldn't resolve it.

Actually the Query I am invoking in MS-Access is this:

UPDATE FirstGlobal SET Quantity = (SELECT Quantity FROM FirstGlobal
WHERE ClientName = [ClientName1] AND Scrip = [Scrip1] AND ContNoteNo =
[ContNoteNo1]) + [AddQty1]
WHERE ClientName=[ClientName1] AND Scrip=[Scrip1] AND
ContNoteNo=[ContNoteNo1];

When I run the above Query by clicking the 'Run' button (with the
exclamation mark) while I am in SQL View in MS-Access, then I am first
prompted to enter a value for the input parameter 'ClientName1', then
I am prompted to enter a value for the input parameter 'Scrip1', then
I am prompted to enter a value for the input parameter 'ContNoteNo1' &
finally I am prompted to enter a value for the input parameter
'AddQty1'.

At this point, Access generates the 'Operation must use an updateable
query' error.
Click to expand...


man, your problem is not related to
microsoft.public.dotnet.framework.aspnet

And your sql-query is wrong.
After some tests, I concluded that the source of the error is the sub-
query 'SELECT Quantity FROM FirstGlobal.......'. If I replace the sub-
query with any number, say, 100 & then add it to the parameter
'AddQty1', then the above Query runs perfectly.

But what is wrong with the sub-query?

Please note that the sub-query will ALWAYS RETURN only ONE VALUE &
will NEVER BE NULL.....that is for sure.
Click to expand...

How do you want ASP.NET to execute your query if it has unknown values
like [ClientName1]???

I guess you wanna do e.g.

.....WHERE ClientName = 'John'.....

that means you need to pass that value into query BEFORE you do your

oledbCmd.ExecuteNonQuery()

In this case you need following

string clientName = "John";
string sql = String.Format("SELECT Quantity FROM FirstGlobal....WHERE
ClientName = '{0}'....., clientName);

......

oledbCmd.ExecuteNonQuery();
 
Aside from the mentioned fixes to your actual SQL Statetement, you need to
ensure that the folder housing the MDB file has write access from your
application. MS Access creates an xxx.LDB file for locking, and if it cannot
write this file, that's the error you will get.
Peter
 
Aside from the mentioned fixes to your actual SQL Statetement, you need to
ensure that the folder housing the MDB file has write access from your
application. MS Access creates an xxx.LDB file for locking, and if it cannot
write this file, that's the error you will get.
Click to expand...

Peter, he has already set the full control for IUSR on it.
 
I agree it needs an ASP.NET user account on the database folder.
Give it write permissions. Also, in case your Access db has a no-write
switch somewhere deep in its bowels, copy the tables to a
brand new Access mdb that has no such hang-ups. IMHO.
wawens
 
Back
Top