CODEWITHSUNDEEP
c#mysqlwinforms
Lukas Häfliger
Lukas Häfliger

Reputation: 526

Automatically Update Values in Database from DataGridView

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.

Upvotes: 10

Views: 3309

Answers (5)

Aggelos Tzitzifas
Aggelos Tzitzifas

Reputation: 1

Try this example out:

public void UpdateAllFromDgv(DataGridView dataGridView1)
{
    string query = "Update List set ColumnName1=@Value1" +
        ",ColumnName2=@Value2" +
        ",ColumnName3=@Value3" +
        ",ColumnName4=@Value4" +
        ",ColumnName5=@Value5" +
        ",ColumnName6=@Value6  where ColumnName0=@Value0";
    try
    {
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            using (MySqlConnection con = new MySqlConnection(ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@Value0", row.Cells[0].Value);
                    cmd.Parameters.AddWithValue("@Value1", row.Cells[1].Value);
                    cmd.Parameters.AddWithValue("@Value2", row.Cells[2].Value);
                    cmd.Parameters.AddWithValue("@Value3", row.Cells[3].Value);
                    cmd.Parameters.AddWithValue("@Value4", row.Cells[4].Value);
                    cmd.Parameters.AddWithValue("@Value5", row.Cells[5].Value);
                    cmd.Parameters.AddWithValue("@Value6", row.Cells[6].Value);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    dataGridView1.ResetBindings();
                    con.Close();
                }
            }

        }
    }
    catch (MySqlException MsE)
    {
        MessageBox.Show(MsE.Message.ToString());

    }
}

Upvotes: 0

Azhar Khorasany
Azhar Khorasany

Reputation: 2709

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"

Upvotes: 3

Simon
Simon

Reputation: 9425

Try moving your update code from the CellEndEdit event to the CellValueChanged event and see if this works.

Upvotes: 0

AAlferez
AAlferez

Reputation: 1502

Did you forget to execute the warenlagerMySqlDataAdapter.UpdateCommand? You are just setting the command and the parameters but not executing it.

What I see is that you are calling the update when the info is updated, but your update command is not loaded. You just call updateWarenlagerView when you update the row, but where are you calling initialzeFields?

Or am I missing code?

Upvotes: 2

Janty
Janty

Reputation: 1714

Please try this update query it works.

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

Upvotes: 3

Related Questions