Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. PRB: ASP Error "The Query Is Not Updateable" When You Update Table Record

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

View products that this article applies to.

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

↑ Back to the top

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.

↑ Back to the top

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

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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/search/default.aspx?qu=vinterdev

↑ Back to the top

Keywords: kbaspobj, kbdatabase, kberrmsg, kbprb, KB174640

↑ Back to the top

Article Info
Article ID : 174640
Revision : 6
Created on : 5/2/2006
Published on : 5/2/2006
Exists online : False
Views : 454