Updating DataGridview from multiple Tables

[boomboombugsh]

Hello everyone
I have a datagridview and Im populating it through setting its datasource from a dataTable that came form dataset...That came from Multiple tables Inner Joined. I am able to SELECT data from database to display it to the datagridview. But when it comes to UPDATE, INSERT and DELETE statements..Im having problem because the MySQLCOmmandBuilder says it cant update data from multiple table.
Any code snippets will be a great help
Thanks

 

[jmcilhinney]

The DataGridView is really irrelevant to the question. How you're displaying and/or editing the data doesn't matter at all. In order for a command builder to generate SQL for you, your query must involve only one table and it must return primary key information for that table. That's all there is to it. If your query doesn't satisfy those conditions then you can't use a command builder. You need to build the required action commands yourself. You might like to check this out as a comparison of the two strategies:

Retrieving and Saving Data in Databases

 

[cjard]

Do tell us:

How you want visual studio to solve this problem for you. i.e. How would you have it write automatically all the necessary queries to update all the tables?

The problem comes because joins usuall end up with some rows being repeated:

AddressID, Street
1, Primrose Way
2, Elfin Road

PersonID, Person, AddressID
1, John, 1
2, James, 1
3, Joe, 2

Query (person join address)
1, John, 1, Primrose Way
2, James, 1, Primrose Way
3, Joe, 2, Elfin Street

Suppose I edit:
1, John, 1, Smiths Row
2, James, 1, Asgard Street
3, Joe, 2, Elfin Street


You tell me: how can the database simultaneously update Address ID 1 to be two different addresses (Smiths Row, Asgard Street) at the same time?