UPDATE TableAdapter when query has INNER JOIN

[FreeriderUK]

I want to be able to fill a dataset with information from two tables (Customers & Orders)
This dataset then fills a datagridview which will show order details (from Orders Table), along with the customer name (from Customer Table) - This I can do.

I also have another Dataset which contains the Orders Table only.

I *thought* the most efficient way of doing this was to create TWO DataSources - CustomersAndOrders and OrdersOnly

The second query in the TA for CustomersAndOrders uses an INNER JOIN.

I can’t seem to be able to UPDATE any changes made to the CustomersAndOrders DataSource (which I assume is because of the lack of UPDATE command generated).

Is there a better way of doing what I want? (assuming anyone understands me...)

 

[cjard]

OK, here's why:

When you make a join, unless you cover all columns in the primary key on both sides then the database cannot perform key preservation:

Customers.CustomerID, Name, Orders.CustomerID, Orders.OrderID, Description
1, John Smith, 1, 1, Apples
1, John Smith, 1, 2, Oranges


UPDATE (the join) SET Name = Description


So are you going to set John Smith to "Apples" or "Oranges" ?


This is why we cant update joins that dont preserve the keys.. The customers PK is REPEATED in the results block, (1 appears twice) so we dont ahve a unique row any more.


-

Now, you must either:

Decide to only update the most unique table, i.e. Orders, and write your own update query yourself (its not hard, but if youre pulling your face, remember: you got yourself into this!). Dont write a query to update Customers, because it'll screw up: Your user can concetually change the 2 instances of John Smith to different names and depending on the call order it will be updated to one or the other. Not good.

Or learn how to present related data on the client side, so that both parts remain updatable. see the dw2 link in my sig, Dispalying and Saving Related Data

 

[FreeriderUK]

Thanks,

Decide to only update the most unique table, i.e. Orders, and write your own update query yourself (its not hard, but if youre pulling your face, remember: you got yourself into this!).

That's exactly what I want to do. I don't need to update the Customer Table (in fact the column is Read Only) - I just want to update the Orders table - but how?
I've been searching for examples of how to write the UPDATE command myself, but without success.

Also, what is the *proper* way of doing this kind of thing?

 

[cjard]

Honestly? I take the easy route:

New tableadapter
SELECT * FROM Orders
Finish
Go to SelectCommand property and design it
SELECT * FROM ORDERS INNER JOIN Customers USING(OrderID)
"Do you want to update the updating commands based on the new Select COmmand you have written?" NO

 

[FreeriderUK]

Thanks cjard - this works fine.

I don't know why I manage to find the most complicated way of doing things - No need for a second Dataset!

I'm back to using Access DBs, so needed a slight alteration.