Updating a multi table SQL view through asp...

Posted on 2005-05-12
Last Modified: 2008-03-17
Hello all I have a question. I have a 2 table view that I need to update on my web pages. Table1 and Table2 linked through key ID in Table1 to key ClientID in Table2. I am using ADODB connection and this is my connection string:

strSQL = "SELECT * FROM vw_clients WHERE ID = '" & recordID & "';"
rstcontacts.Open strSQL, cnnLogin, adOpenKeyset, adLockPessimistic, adCmdText

I am passing values to the fields in the view and then I do a .update but I get an error message stating:

Microsoft OLE DB Provider for SQL Server error '80004005'

Cannot insert or update columns from multiple tables.

Why is this happening? I can edit and insert into the view using Enterprise Manager and get no errors.

This question is worth 500 points since I need  a quick fix.


Question by:gchiropoulos
    LVL 28

    Expert Comment

    Please post the definition of your vw_clients view and the command that you use to insert and update the view.
    LVL 25

    Expert Comment

    It depends on your version, but it sounds like you need this hotfix:

    It specifically addresses:

    835677 FIX: You may receive the "Cannot insert or update columns from multiple tables" error message when you try to insert or to update data in a multi-table view by using a server-side cursor

    From that item, they give another workaround:

    To work around this problem, use the Microsoft SQL Server ODBC driver to connect to your instance of SQL Server.
    LVL 25

    Expert Comment

    Any luck?

    Author Comment

    nope. still not working

    Expert Comment

    I had a similar problem and found (from another question) a link to a Microsoft article (;en-us;174640) that helped me - don't know it its of any use to anyone else. In essence the article said:

    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.

    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

       'now update child table
    This behavior is by design.
    LVL 1

    Accepted Solution

    PAQed with points (500) refunded

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now