Click here to Skip to main content
14,693,744 members
Please Sign up or sign in to vote.
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:

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:

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:

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:

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

1 solution

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

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("???")};


// 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();
      }
    }
  }
}
   
v4
Comments
Zachery Hysong 3-Jul-14 10:15am
   
This looks very much like what I am attempting to do. I would like to explain a few other details to see if this will still work.

the primary key in this DB is a "hidden" column, and when the table is normally saved (from the temp table to the actual DB) the code simply sets the primary key to zero, and uses another value for a second column.

Will this still work in this case?
George Jonsson 3-Jul-14 10:26am
   
You can use any unique column or columns, aka natural key, as the primary key for the DataTable.
Which column(s) would you use for your update operation?
Zachery Hysong 3-Jul-14 11:12am
   
They would be any of the other columns other than the first 2 or 3.
George Jonsson 3-Jul-14 11:26am
   
Show me an update string and then I can tell you which column(s) to use as primary key.
Basically it is the column or columns used in the WHERE clause.
George Jonsson 4-Jul-14 1:13am
   
Did the solution solve your problem?

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900