Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 174640 - Last Review: May 2, 2006 - Revision: 5.1
PRB: ASP Error "The Query Is Not Updateable" When You Update Table Record
This article was previously published under Q174640
One of the following errors occurs when you update a table record from an
Active Server Pages (ASP) page through ADO's Recordset.update method:
Source: Microsoft OLE DB Provider for ODBC Drivers
Error Number: -2147467259
Description: The query is not updateable because the from clause is
not a single simple table name. This may be caused by an attempt to
update a non-primary table in a view.
-OR-
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'<a column from your table>'.
/<your asp file>.asp, line xxx
-OR-
Microsoft OLE DB Provider for SQL Server '80004005'
Cannot insert or update columns from multiple tables.
-OR-
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
The following conditions cause this error to occur:
- You have created a query that contains more than one table.
- You are updating the records returned by this query and the update
affects fields in more than one table.
Basically, if the query involves tables that have a one-to-many
relationship, the query -as a whole- is not updateable.
Process one table's fields independently from another. Issue an Update
after each table's fields have been modified. For example, in the code
sample below, the "cmdTemp.CommandText" property holds the one-to-many
query and the fields of parent table are updated independently of the child
table.
<Object creation code removed for clarity>
.
.
.
cmdTemp.CommandText = "SELECT stores.state, sales.qty FROM sales INNER
JOIN stores ON sales.stor_id = stores.stor_id"
.
.
.
'update parent table first
Datacommand1("state")="WA"
Datacommand1.update
'now update child table
Datacommand1("qty")=4
Datacommand1.update
This behavior is by design.
For the latest Knowledge Base artices and other support information on
Visual InterDev and Active Server Pages, see the following page on the
Microsoft Technical Support site:
APPLIES TO
- Microsoft Active Server Pages 4.0
- Microsoft Visual InterDev 1.0 Standard Edition
- Microsoft Visual InterDev 6.0 Standard Edition
- Microsoft ActiveX Data Objects 2.0
- Microsoft ActiveX Data Objects 2.1 Service Pack 2
- Microsoft ActiveX Data Objects 2.5
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft Internet Information Server 4.0
- Microsoft Internet Information Services 5.0
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
| kbaspobj kbdatabase kberrmsg kbprb KB174640 |
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