PRB: ASP: "The query is Not Updateable..." Error when Updating
ID: Q174640
|
The information in this article applies to:
-
Active Server Pages
-
Microsoft Visual InterDev, version 1.0
-
ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
-
Microsoft Internet Information Server versions 4.0, 5.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:
- 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.
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/
Additional query words:
Keywords : kbADO kbASP kbASPObj kbODBC kbOLEDB kbVisID kbVisID100 kbGrpASP kbDSupport kbMDAC210SP2 kbiis400 kbiis500
Version : WINDOWS:1.0,2.0,2.1 SP2; winnt:
Platform : WINDOWS winnt
Issue type : kbprb