• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Updating a multi table SQL view through asp...

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.

Thanks

0
gchiropoulos
Asked:
gchiropoulos
1 Solution
 
rafranciscoCommented:
Please post the definition of your vw_clients view and the command that you use to insert and update the view.
0
 
jrb1Commented:
It depends on your version, but it sounds like you need this hotfix:

http://support.microsoft.com/kb/839801/en-us

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:

http://support.microsoft.com/kb/835677/

To work around this problem, use the Microsoft SQL Server ODBC driver to connect to your instance of SQL Server.
0
 
jrb1Commented:
Any luck?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gchiropoulosAuthor Commented:
nope. still not working
0
 
Rob4077Commented:
I had a similar problem and found (from another question) a link to a Microsoft article (http://support.microsoft.com/default.aspx?scid=kb;en-us;174640) that helped me - don't know it its of any use to anyone else. In essence the article said:

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.
0
 
DarthModCommented:
PAQed with points (500) refunded

DarthMod
Community Support Moderator
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now