MySQL / C# Commit DataGridView Changes on cell edit?

Question

0.00/5 (No votes)

See more:

I have a Form with 5 dataGridViews, and I need to be able to save the changes to the Views when you click into a cell, similar to how you would work in Excel.

Here is my connection info:

C#

public static MySqlConnection db = new MySqlConnection();
public string jobnumber = "";
public string str = "SERVER=192.168.1.149; DATABASE=starflitesystems; UID=iuapp; " +
    "Password=iuapp";

My Form Open Code:

C#

public frmPricingTemplate_Start(string s)
{
    InitializeComponent();

    createTempTable();

    setDGVQueries();

    hidePanels(pnlBasePackage);
    btnReset(btnBasePackage);

    this.WindowState                = FormWindowState.Maximized;

    jobnumber                       = s;
    txtJobNumber.Text               = s;
    comboBox1.Visible               = false;
    comboBox2.Visible               = false;
    dataGridRefresh();

    dg2.AutoSizeColumnsMode         = DataGridViewAutoSizeColumnsMode.Fill;
    dg3.AutoSizeColumnsMode         = DataGridViewAutoSizeColumnsMode.Fill;
    dg4.AutoSizeColumnsMode         = DataGridViewAutoSizeColumnsMode.Fill;
    dg5.AutoSizeColumnsMode         = DataGridViewAutoSizeColumnsMode.Fill;
}

My Database Queries:

C#

public void setDGVQueries()
{
    /* Strings for dataGridViews */
    selectDGV1 = "SELECT `Group`, Material, `Sub-Material` " +
                                            "FROM temporary_table " +
                                            "WHERE tab='" + activeTab + "';";

    selectDGV2 = "SELECT Quantity as `Quantity`, Cost as `Cost` " +
                                            "FROM temporary_table " +
                                            "WHERE tab='" + activeTab + "';";

    selectDGV3 = "SELECT Quantity2 as `Quantity`, Cost2 as `Cost` " +
                                            "FROM temporary_table " +
                                            "WHERE tab='" + activeTab + "';";

    selectDGV4 = "SELECT Quantity3 as `Quantity`, Cost3 as `Cost` " +
                                            "FROM temporary_table " +
                                            "WHERE tab='" + activeTab + "';";

    selectDGV5 = "SELECT Quantity as `Quantity`, Cost as `Cost` " +
                                            "FROM temporary_table " +
                                            "WHERE tab='" + activeTab + "';";
}

My Database Refresh Function:

C#

public void dataGridRefresh()
{
    /* Define all dataTables for the dataGridViews */
    MySqlDataAdapter return1 = new MySqlDataAdapter(selectDGV1, str);
    DataTable dt1 = new DataTable("base");
    return1.Fill(dt1);

    MySqlDataAdapter return2 = new MySqlDataAdapter(selectDGV2, str);
    DataTable dt2 = new DataTable("base");
    return2.Fill(dt2);

    MySqlDataAdapter return3 = new MySqlDataAdapter(selectDGV3, str);
    DataTable dt3 = new DataTable("base");
    return3.Fill(dt3);

    MySqlDataAdapter return4 = new MySqlDataAdapter(selectDGV4, str);
    DataTable dt4 = new DataTable("base");
    return4.Fill(dt4);

    MySqlDataAdapter return5 = new MySqlDataAdapter(selectDGV5, str);
    DataTable dt5 = new DataTable("base");
    return5.Fill(dt5);

    /* Set DataSources for all datagridViews */
    dg1.DataSource = dt1;
    dg2.DataSource = dt2;
    dg3.DataSource = dt3;
    dg4.DataSource = dt4;
    dg5.DataSource = dt5;
}

Basically, this loads certain information into my dataGridViews, but now, after that data is loaded, I need to be able to click into a cell, edit it, and have it automatically commit that change to the table once the focus leaves the cell.

I looked up a few things online about it, but they all appear to have used a different method to connect to the db, so I don't think they will work for me.

My theory is that I need to use a `for each (row r in dg1.SelectedRows){} Block, but I am unsure of exactly what to do.

Any and all help is greatly appreciated.

Posted 3-Jul-14 1:51am

Zachery Hysong

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2014-07-03T04:09:00

This approach "should" work. No guarantees, though.
I hope it helps,
George

Some pointers.

1. Call AcceptChanges for the DataTable
This will make sure the rows are in an unchanged state.

2. One or more primary key must exist.
See DataTable.PrimaryKey

C#

MySqlDataAdapter return1 = new MySqlDataAdapter(selectDGV1, str);
DataTable dt1 = new DataTable("base");
return1.Fill(dt1);
dt1.AcceptChanges();
// I don't know your primary key
dt1.PrimaryKey = new DataColumn[] {new DataColumn("???")};

C#

// You can use the same event implementation for all grids
private void dg_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
  // Who called me?
  DataGridView dg = (sender as DataGridView);
  if (dg == null)
    return;

  DataTable dt = null;
  dt = (dg.DataSource as DataTable);

  if (dt != null)
  {
    foreach (DataRow dr in dt.Rows)
    {
      // Assuming the user cannot add or delete rows, only modify
      if (dr.RowState == DataRowState.Modified)
      {
        StringBuilder sbColumns = new StringBuilder();
        StringBuilder sbValues = new StringBuilder();
        foreach (DataColumn dc in dr.Table.Columns)
        {
          // Skip the primary key
          if (dt.PrimaryKey.Contains(dc))
            continue;

          sbColumns.Append(dc.ColumnName);
          sbColumns.Append(",");
          sbValues.Append(dr[dc.ColumnName].ToString());
          sbValues.Append(",");
        }

        // Create the WHERE columns and values
        StringBuilder sbPrimaryKey = new StringBuilder();
        foreach (DataColumn dcPrim in dt.PrimaryKey)
        {
          if (sbPrimaryKey.Length > 0)
            sbPrimaryKey.Append(" AND ");
          sbPrimaryKey.AppendFormat("{0} = '{1}'", dcPrim.ColumnName, dr[dcPrim]);
        }
        string sqlUpdate = String.Format("UPDATE {0} ({1}) VALUES ({2}) WHERE {3};",
            dt.TableName, sbColumns.ToString().TrimEnd(','), 
            sbValues.ToString().TrimEnd(','), 
            sbPrimaryKey);

        // Send the data to the data base
        ...
         
        // Make sure the data tables is updated
        dt.AcceptChanges();
      }
    }
  }
}