Automatically Update Values in Database from DataGridView

Question

I'm currently working on a project using MySql in combination with C#. The Data for the DataGridView is provided by a join from multiple tables in the DB. To show the data I use the following, working, code:

adapter.SelectCommand = new MySqlCommand(
            " SELECT" +
            " l.lot AS Lot, "+
            " m.comment AS Bemerkungen," +
            ... (multiple columns from different tables) ...
            " FROM m " +
            " JOIN m2p ON m.m2p_id = m2p.id" +
            ... (more joins) ...
            , this._mySqlConnection);
dataGridView1.DataSource = data;
adapter.Fill(data);

Now the user of the GUI is allowed to modify a certain column (the "comment" column). So I assigned an eventHandler to the CellEndEdit event and when the user modified the allowed column the adapter.Update(data) is called. Now this doesn't perform the correct action.

To define my updatecommand I used the following code:

adapter.UpdateCommand = new MySqlCommand(
                " UPDATE m" +
                " JOIN  l ON m.l_id = l.id" +
                " SET m.comment = @comment" +
                " WHERE l.lot = @lot"
                , this._mySqlConnection);
adapter.UpdateCommand.Parameters.Add("@comment", MySqlDbType.Text, 256, "Bemerkungen");
adapter.UpdateCommand.Parameters.Add("@lot", MySqlDbType.Text, 256, "Lot");

Could you explain me how I fix my code to automatically Update the database?

EDIT: added further source code:

private MySqlDataAdapter warenlagerMySqlDataAdapter, kundenMySqlDataAdapter;
private DataTable warenlagerData, kundenData;
private DataGridView warenlagerGridView;

private void updateWarenlagerView(object sender, EventArgs e) {
            warenlagerMySqlDataAdapter.Update(warenlagerData);
}

private void initialzeFields() {
            warenlagerGridView.CellEndEdit += new DataGridViewCellEventHandler(this.updateWarenlagerView);
            warenlagerMySqlDataAdapter = new MySqlDataAdapter();
            warenlagerData = new DataTable();
            }

private void initializeWarenlagerView() {
            warenlagerMySqlDataAdapter.SelectCommand = new MySqlCommand(
                " SELECT" +
                " c.name AS Ursprung, " +
                " m2p.art_nr AS ArtNr," +
                " m.delivery_date AS Eingangsdatum," +
                " CONCAT(FORMAT(m.delivery_amount / 100, 2), 'kg') AS Eingangsmenge, " +
                " l.lot AS Lot," +
                " m.quality AS Qualität," +
                " m.comment AS Bemerkungen," +
                " CONCAT(m.units, 'kg') AS Units," +
                " CONCAT(FORMAT(s.amount / 100, 2), 'kg') AS Lagermenge, " +
                " FORMAT(m.base_price / 100, 2) AS Einkaufspreis," +
                " FORMAT(s.amount/10000 * m.base_price, 2) AS Wert" +
                " FROM mushrooms AS m " +
                " JOIN mushroom2path AS m2p ON m.mushroom2path_id = m2p.id" +
                " JOIN countries AS c ON m.origin_id = c.id" +
                " JOIN lots AS l ON m.lot_id = l.id" +
                " JOIN stock AS s ON s.mushrooms_id = m.id"
                , this._mySqlConnection);
            warenlagerGridView.DataSource = warenlagerData;
            warenlagerMySqlDataAdapter.Fill(warenlagerData);
            warenlagerMySqlDataAdapter.UpdateCommand = new MySqlCommand(
                " UPDATE mushrooms AS m" +
                " JOIN lots AS l ON m.lot_id = l.id" +
                " SET m.comment = @comment" +
                " WHERE l.lot = @lot"
                , this._mySqlConnection);
            warenlagerMySqlDataAdapter.UpdateCommand.Parameters.Add("@comment", MySqlDbType.Text, 256, "Bemerkungen");
            warenlagerMySqlDataAdapter.UpdateCommand.Parameters.Add("@lot", MySqlDbType.Text, 256, "Lot");
        }

This is the whole code concerning this problem. I'm 100% sure the adapter.Update(data) method is called (debugging). And the data which is passed to the adapter.Update() method contains the new data.

Answer 1

Please try this update query it works.

UPDATE mushrooms 
SET comment = @comment
WHERE 
l_id=(select id from l where lot=@lot)

Answer 2

Your update statement is incorrect. It should be:

"UPDATE m FROM mushrooms m JOIN lots l ON m.lot_id = l.id SET m.comment = @comment WHERE l.lot = @lot"