Question How to Update Datagridview that has Multiple Joins

[ncastillo]

I need to update the changes in a datagridview. My query has multiple joins, so the commandbuilder does not work. I never created an update command using update statements with parameters before and would appreciate any help. Do I have to get the changes and put them in a new dataset first? The fields that need to be updated are CheckedOutTo, DueDate, Comments, and Analyst. Please see query string below. Thanks!

Dim sql As String = <s> SELECT a.Loaner, a.Code, a.Model, b.CheckedOutTo, b.DueDate, b.Comments, b.Analyst
FROM tblLoaners AS a
LEFT JOIN (SELECT tblTransactions.CheckedOutTo, tblTransactions.DueDate,
tblTransactions.Comments, tblTransactions.Analyst, tblTransactions.Loaner
FROM ((tblTransactions INNER JOIN tblBorrowers ON tblTransactions.CheckedOutTo = tblBorrowers.ID)
INNER JOIN tblAnalysts ON tblTransactions.Analyst = tblAnalysts.AnalystID)
WHERE tblTransactions.CheckedInDate IS NULL) AS b ON a.ID = b.Loaner
WHERE a.Active = 'Active' </s>

 

[jmcilhinney]

The data should already be in a DataTable so there's no need to put it anywhere. You presumably called Fill on a data adapter to populate a DataTable in the first place. Whether that DataTable is part of a DataSet is irrelevant. You simply call Update on the same data adapter to save the changes from the DataTable back to the database. As you say, you just have to create the UpdateCommand yourself. To learn how, check this out:

Retrieving and Saving Data in Databases

 

[ncastillo]

Thank you very much. This was very helpful! Just having trouble with inserting. I need to insert a new record in tblTransactions for those records in tblLoaner (Left Join) that are not equal to any records in tblTransactions.