Datagridview cell value change update database

Question

I have retrieved data from Mysql database into a DataGridView1. Let us suppose I am in Row 0. When I change the contents of Row 0, Cell 1 and press enter key or a button, the Update query should modify that row, but I am unable to modify the value of the cell. The cell maintains its previous value when i reload data and the database is not modified. For example, if I change the contents of a cell under column Client_Name from "Acs" to "Gmt", how can I change the value of the cell from "Acs" to "Gmt"? and to have it updated into Mysql database, I am using c# in Vs 2012. below is my code that retrieves my database into datagridview1 any help is welcomed thanks.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data.SqlClient;

namespace PI.Gen
{
    public partial class frmMain : Form
    {
        MySqlConnection Conn;


        public frmMain()
        {
            InitializeComponent();
            btnDisconnect.Enabled = true;
            btnLoadData.Enabled = false;
            btnLoadClients.Enabled = false;

        }





        private void btnConnect_Click(object sender, EventArgs e)
        {

            string strConnect = "server=" + txtServer.Text + ";uid=" + txtUsername.Text + ";pwd=" + txtPassword.Text + ";database=" + txtDatabase.Text;
            try
            {
                if (txtServer.TextLength <= 0 || txtUsername.TextLength <= 0 || txtDatabase.TextLength <= 0)
                {
                    MessageBox.Show("You have an empty database connection field. Please supply a valid value.");
                    return;
                }

                Conn = new MySqlConnection(strConnect);
                Conn.Open();

                if (Conn.State.ToString() != "Open")
                {
                    MessageBox.Show("Could not open database connection");
                    return;
                }
                btnDisconnect.Enabled = true;
                btnConnect.Enabled = false;
                btnLoadData.Enabled = true;
                btnLoadClients.Enabled = true;
                //  btnSubmitClient.Enabled = true;
            }
            catch (Exception ex)  // catch on general exceptions, not specific
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }



        private void frmMain_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (Conn != null)
            {
                Conn.Close();
            }
        }

        private void btnDisconnect_Click(object sender, EventArgs e)
        {
            try
            {
                Conn.Close();
                Conn = null;
                btnDisconnect.Enabled = false;
                btnConnect.Enabled = true;
                btnLoadData.Enabled = false;
                btnLoadClients.Enabled = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }





        private void btnLoadData_Click(object sender, EventArgs e)
        {
            try
            {
                string CmdString = "SELECT * FROM t_receipients";
                MySqlDataAdapter sda = new MySqlDataAdapter(CmdString, Conn);
                DataSet ds = new DataSet();

                sda.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0].DefaultView;


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }

         }


        private void btnLoadClients_Click(object sender, EventArgs e)
        {
            try
            {

                string CmdString = "SELECT * FROM t_clients";
                MySqlDataAdapter sda = new MySqlDataAdapter(CmdString, Conn);

                DataSet ds = new DataSet();
                sda.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0].DefaultView;

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }

        }

Answer 1

After series of trials and error, i finally found what i was looking for, thus being able to update database from datagridview below is my worked around code which works 100% hope it helps someone in future, and thanks @RageComplex for helping out, but one more thing does anyone know how to implement that i mean instead of hitting the enter button to take changes in the datagridview you rather click on a button ty

   private void dataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
    {
        try
        {
            DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges();
            if (changes != null)
            {
                MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
                mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
                mySqlDataAdapter.Update(changes);
                ((DataTable)dataGridView1.DataSource).AcceptChanges();

                MessageBox.Show("Cell Updated");
                return;
            }


        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


    }

Answer 2

You are not updateing your changes to the database. While you keep your connection open doesn't mean that this will automatically update your data.

First of all, don't keep your connection open. In your app you have a connect button which is good for testing but not for really keeping the connection open, not with databases in my opinion.

The way you load data is correct.

You give the datagridview an DataSource which is a table from your DataSet. So changes made in the datagridview ARE saved to your DataSet but not to your database.

This is how you update your database

   public void UpdateTable(DataSet ds)
    {
        using (MySqlConnection connect = new MySqlConnection(ConnString))
        {
            connect.Open();
            MySqlDataAdapter adapt = new MySqlDataAdapter();
            MySqlCommandBuilder commbuilder = new MySqlCommandBuilder(adapt);
            adapt.SelectCommand = new MySqlCommand("SELECT * FROM t_receipients", connect);
            adapt.Update(ds.Tables[0]); 
        }
    }

Make sure, before you Update your database, you use datagridview1.EndEdit()

Also, you using, this will ensure a connection is closed again after completing that code, best is to always have it in a try-except.

You had struggles with connecting the database as it appears to be in the commends. I've also forgot to include MySqlDataAdapter above, I used an adapter globally in that case. I didn't want to report this question as duplicated, but now it kinda does look like this answer.

Answer 3

I would like to give code which I have tested in my application.I used it for button click event.

private void button3_Click(object sender, EventArgs e)
    {
        string StrQuery;
        try
        {
            string MyConnection2 = "server=localhost;user id=root;password=;database=k";
            using (MySqlConnection conn = new MySqlConnection(MyConnection2))
            {
                using (MySqlCommand comm = new MySqlCommand())
                {
                    comm.Connection = conn;
                    conn.Open();
                    for (int i = 0; i < dataGridView3.Rows.Count; i++)
                    {
                        StrQuery = @"update s set  Quantity='" + dataGridView3.Rows[i].Cells["Quantity"].Value.ToString() + "' where No='" + dataGridView3.Rows[i].Cells["Item No"].Value.ToString() + "';";

                        comm.CommandText = StrQuery;
                        comm.ExecuteNonQuery();
                    }
                }
            }
        }
        catch
        { 
        }
    }

I think it may help you