Microsoft KB Archive/174640

= PRB: ASP Error "The Query Is Not Updateable" When You Update Table Record =

Article ID: 174640

Article Last Modified on 5/2/2006

-

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

-



This article was previously published under Q174640



SYMPTOMS
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

''.

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



CAUSE
The following conditions cause this error to occur:
 * 1) You have created a query that contains more than one table.
 * 2) 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.



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



STATUS
This behavior is by design.

