PRB: ASP: "The Query is Not Updateable..." Error when Updating

Last reviewed: December 11, 1997
Article ID: Q174640
The information in this article applies to:
  • Microsoft Active Server Pages, versions 1.0, 1.0b
  • Microsoft Visual InterDev, version 1.0

SYMPTOMS

One of the following errors occurs when updating 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

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.

   <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

STATUS

This behavior is by design.

REFERENCES

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:

   http://support.microsoft.com/support/vinterdev/


Keywords          : VIASP
Technology        : kbInetDev
Version           : WINDOWS:1.0; WINNT:1.0,1.0b
Platform          : WINDOWS winnt
Issue type        : kbprb


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: December 11, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.