Update mysql database from datagridview selected row

Question

i have a datagridview which loads mysql database table t_pi_clients on form load event,and i have another tab which contains textboxes of the respective columns of t_pi_client, which am able to get data from fullrowselect mode into those textboxes. now i want to update the database upon changes in the those textbox values. so far i've tried some process and gets my "entry saved" messageBox.show but nothing happens to database, so am hoping someone could help me out maybe am missing something thanks

public partial class frmMain : Form
{

    MySqlConnection connection;
    MySqlDataAdapter mySqlDataAdapter;
    DataSet dt = new DataSet();
    DataSet DS = new DataSet();
    DataSet dg = new DataSet();

    public frmMain()
    {
        InitializeComponent();
    }


    #region Main load
    private void frmMain_Load(object sender, EventArgs e)
    {

        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        connection = new MySqlConnection(connectionString);

        if (this.OpenConnection() == true)
        {
            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_Clients", connection);
            DataSet DS = new DataSet();
            mySqlDataAdapter.Fill(DS);
            kryptonDataGridView1.DataSource = DS.Tables[0];
            kryptonDataGridView1.Columns[0].Visible = false;


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_msg_charge_Rate", connection);
            DataSet dt = new DataSet();
            mySqlDataAdapter.Fill(dt);
            kryptonDataGridView2.DataSource = dt.Tables[0];


            mySqlDataAdapter = new MySqlDataAdapter("select * from t_pi_client_deposits", connection);
            DataSet dg = new DataSet();
            mySqlDataAdapter.Fill(dg);
            kryptonDataGridView3.DataSource = dg.Tables[0];

        }


    } 
          //loads selected row data into textboxes
      private void kryptonDataGridView1_DoubleClick(object sender, EventArgs e)
    {
        textboxClientCode.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientCode"].Value.ToString();
        txtboxClientName.Text = kryptonDataGridView1.SelectedRows[0].Cells["ClientName"].Value.ToString();
        txtboxPostalAddress.Text = kryptonDataGridView1.SelectedRows[0].Cells["PostalAdd"].Value.ToString();
        txtboxTelephone.Text = kryptonDataGridView1.SelectedRows[0].Cells["Telephone"].Value.ToString();
        txtboxFax.Text = kryptonDataGridView1.SelectedRows[0].Cells["Fax"].Value.ToString();
        txtboxEmailAddress1.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd1"].Value.ToString();
        txtboxEmailAddress2.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd2"].Value.ToString();
        txtboxEmailAddress3.Text = kryptonDataGridView1.SelectedRows[0].Cells["EmailAdd3"].Value.ToString();
        txtboxWebsite.Text = kryptonDataGridView1.SelectedRows[0].Cells["Website"].Value.ToString();
        txtboxChargeRate.Text = kryptonDataGridView1.SelectedRows[0].Cells["ChargeRate"].Value.ToString();
        txtboxTotalDepo.Text = kryptonDataGridView1.SelectedRows[0].Cells["TotalDeposit"].Value.ToString();
        txtboxAccountBal.Text = kryptonDataGridView1.SelectedRows[0].Cells["AccountBal"].Value.ToString();
        txtboxEntrydate.Text = kryptonDataGridView1.SelectedRows[0].Cells["EntryDate"].Value.ToString();


    }

now i tried this method to update but doesn't update database

private void kryptonbtnUpdate_Click(object sender, EventArgs e)
    {
        var connectionString = ConfigurationManager.ConnectionStrings["Pigen"].ConnectionString;

        using (MySqlConnection Conn = new MySqlConnection(connectionString))


            if (Conn.State.ToString() != "Open")
            {

            }
            else
            {
                connection.Open();

            }

        try
        {
            DataTable changes = ((DataTable)kryptonDataGridView1.DataSource).GetChanges();
            if (changes != null)
            {
                MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
                mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
                mySqlDataAdapter.Update(changes);
                ((DataTable)kryptonDataGridView1.DataSource).AcceptChanges();
                mySqlDataAdapter.Update(DS);
            }

            //        adapter.Update(rowsToUpdate);

            //   mySqlDataAdapter.Update(DS);



            MessageBox.Show("Entry Saved");
        }

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

Answer 1

This is just a pseudocode of what you need to do

string cmdText = @"UPDATE t_pi_Clients
                 SET ClientName = @ClientName,
                     PostalAdd = @PostalAdd,
                     Telephone = @Telephone,
                     Fax = @Fax,
                     .... etc ....
                 WHERE ClientCode = @ClientCode";
using(MySqlConnection cn = new MySqlConnection(.....))
using(MySqlCommand cmd = new MySqlCommand(cmdText, cn))
{
    cn.Open();
    cmd.Parameters.AddWithValue("@ClientName", txtboxClientName.Text);
    cmd.Parameters.AddWithValue("@PostalAdd", txtboxPostalAddress.Text);
    ....etc etc...
    cmd.Parameters.AddWithValue("@ClientCode", textboxClientCode.Text);
    int rowsUpdated = cmd.ExecuteNonQuery();
    if(rowsUpdated > 0) 
    {
        // extract the code that loads DataGridView1 from the Form_Load
        // and create a reusable method that you could call from here
    }
}

First you build an sql command text with the UPDATE clause. I assume that your primary key (the field that uniquely identifies your records) is the ClientCode field.

Then create the connection and the command. Fill the command parameters collection with the parameters required by your text taking the values from the TextBoxes.
Call the ExecuteNonQuery to store the values.

If you succeed then you need to update or reload your datagridview. The best approach would be setting one by one the gridview cells of the current row with the new values from the textboxes, or you could simply extract the code used in form_load to fill the grid and make a new method that you could call from the button click event. (But this could be slower if you have many records)