How do I update the changes from my DataGridView into my SQL table?

How do I update the changes from my DataGridView into my SQL table?

• redshock

  I also tried looping though the whole Datagrid one by one and the same things happened. Always one row is not updated :-(((

  if ( selectedRowCountVisible > 0 )
  {
  for ( int i = 0 ; i < selectedRowCountVisible ; i++ )
  {
  if (dataGridView1.Rows.Selected)
  {
  //MessageBox.Show(dataGridView1.Rows.Cells[2].Value.ToString());
  dataGridView1.Rows.Cells[7].Value = 1;
  da.Update(table);
  }
  }
  }

  I am sure this must be something silly I am missing.... please anyone out there to point me to the right direction

  Thank you

  JCDS

  
  
  

• Vormav

  da.Update(table) should send all the changed rows to the sql data base.
  
  Note table.AcceptChanges marks all the rows as unchanged so you should always call this after the data adapters update method.
  
  The SQLCommandBuilder will create the update, insert, and delete commands for the data adapter.
  
  
  

• Itzik Paz

  Hello JCDS

  your modifications are not updated to the sql table in your database because you are binding your Grid to a dataview whose "AllowEdit" property is false by default so first you have to do the following

  dataview.AllowEdit = true;

  then to update the first row in the dataview for example you have to do the following:

  dataview[0].BeginEdit();

  dataview[0]["column_name"] = new value;

  dataview[0].EndEdit();

  Yet in your case I suggest that you bind your DataGridView to the DataTable itself not the default view:

  so the code would look something like this:

  DataTable dt = new DataTable("myTable")

  da.Fill(dt)

  DataGridView1.DataSource = dt;

  then when clicking the update button after iterating through the rows of your datagridview

  give the desired value to the 7'th Cell (row.cells[7] = 1)

  and then as u were doing call the update method of the sqlDataAdapter on the datatable of your grid which you can reference it as follows: da.Update((DataTable)DataGridView1.DataSource );

  Hope this helps if not I'm ready to post code sample

  Thanks

  
  

• krunal k

  See this project.
  
  

• Tonito01

  This will work:

  row.Cells[7].Value = 1;

  ((DataRowView)row.DataBoundItem).Row.EndEdit();

  
  
  

• slizard00

  That worked great!!!!!!!

  Thank you so much BonnieB and everyone else here who helped me in this issue.

  I certainly learned a lot and will research and study more the different approaches suggested here.

  If you guys know any books out there C#/database please let me know - Thanks again!

  
  
  

• BurritoSmith

  Simply bind your grid to the datatable itself instead of binding it to the defaultview because the dataview by default has the property allowEdit set to false then in order to reflect the updates on the datatable you have to get the index of the row of the grid from the databiew then do the following for example if the row index was zero then :

  dataview[0].BeginEdit();

  dataview[0]["column_name"] = new value;

  dataview[0].Endedit();

  where as in your approach if you bind your grid directly to the datatable then calling the update method of the dataadapter will work just fine

  just inform me if you need any sample code

  
  

• jbassmanp

  veeery interesting...first of all thanks for your reply Bonnie. Your suggestion fixed the problem but I am still a little bit clueless about what was wrong. I debug the code below line by line and even though I changed 10 rows out of 250 it would return DataRowVersion.Proposed = TRUE to only ONE of the rows (the last row selected). Here is the code:

  foreach ( DataGridViewRow row in this.dataGridView1.SelectedRows )
  {
  row.Cells[7].Value = 1;
  // how can I run the EndEdit method here for the current selected row
  }

  for ( int nRow = 0 ; nRow < table.Rows.Count ; nRow++ )
  {
  if ( table.Rows[nRow].HasVersion(DataRowVersion.Proposed) )
  {
  table.Rows[nRow].EndEdit();
  MessageBox.Show("Proposed: " + nRow.ToString());
  }
  }

  da.Update((DataTable)dataGridView1.DataSource);

  Question:
  How can I run the EndEdit method here for the current selected row

  Thank you!

  
  
  

• DavidThi808

  Hello Ken!

  Your suggestion did not work. I won't update my table. I lost hope on this since I have checked lots of samples in the internet and I have no idea why it won't save the data into SQL. Here is my code:

  private void Form1_Load(object sender, EventArgs e)
  {
  sqlConn = new SqlConnection();
  sqlConn.ConnectionString = strConn;
  sqlConn.Open();

  da = new SqlDataAdapter();

  // Create the SelectCommand - // set up the paging stored procedure
  SqlCommand sqlSelectCommand = new SqlCommand();
  sqlSelectCommand.Connection = sqlConn;
  sqlSelectCommand.CommandText = PAGING_SP;
  sqlSelectCommand.CommandType = CommandType.StoredProcedure;
  sqlSelectCommand.Parameters.Add("@PageCommand", SqlDbType.NVarChar, 10);
  sqlSelectCommand.Parameters.Add("@FirstSTBId", SqlDbType.NVarChar);
  sqlSelectCommand.Parameters.Add("@LastSTBId", SqlDbType.NVarChar);
  sqlSelectCommand.Parameters.Add("@PageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
  sqlSelectCommand.Parameters.Add("@CurrentPage", SqlDbType.Int).Direction = ParameterDirection.InputOutput;
  da.SelectCommand = sqlSelectCommand;

  // Create the UpdateCommand
  SqlCommand sqlUpdateCommand = new SqlCommand();
  sqlUpdateCommand.Connection = sqlConn;
  sqlUpdateCommand.CommandText = "UPDATE STBData SET Flag = 1 " + "WHERE STBid = @STBid";
  sqlUpdateCommand.Parameters.Add("@STBid", SqlDbType.UniqueIdentifier, 36, "STBid");
  da.UpdateCommand = sqlUpdateCommand;

  table = new DataTable("STBData");
  da.FillSchema(table, SchemaType.Source);

  dataGridView1.DataSource = table.DefaultView;
  }

  private void btnUpgrade_Click(object sender, EventArgs e)
  {
  string strAccountId, strSTBid, strSTBType;
  string bFlag;

  listBox1.Items.Clear();
  foreach ( DataGridViewRow row in this.dataGridView1.SelectedRows )
  {
  strSTBid = row.Cells[2].Value.ToString();
  listBox1.Items.Add(strSTBid);
  row.Cells[7].Value = 1;

  da.Update(table); // <=== this is not working. It won't update my table nor give me any error.
  }
  }

  What am I missing :-(

  Thank you so much

  JCDS
  

  
  
  

• AshN

  Hello Ken,

  Are there any known issues with the DataGridView I see the following behaviour on my code:

  If I select one row and change a value of a field and then call DataAdapter.Update nothing happens. If I select another row and then select the previous row then it will commit the data into SQL.

  Also, if I select 3 rows it will just commit the data for 2 rows. For instance, when you do a :
  
  foreach ( DataGridViewRow row in this.dataGridView1.SelectedRows )

  I can see that it processes row 3 first then row 2 then row 1 BUT only rows 2 and 1 are commited. What I realized is that the first call to DataAdapter.Update never does anything but the subsequent calls do update the data.

  Have you guys seen that before

  Thank you

  
  
  

• Tanglesoft

  Data in a DataRow has several different versions. First, there's the original version. Then, when it's being edited, it becomes a Proposed version and once it's done being edited it becomes the Current version. Sometimes when editing, the row is left in the Proposed state and needs to be ended.

   

  I'm pretty sure that there's probably a different way to handle this for Grids, but since I don't use them much at the moment, I handle the problem with the DataSet itself prior to saving the data.

   

  Here's a method I *always* call before I attempt to save data:

   

  protected virtual void CommitProposedChanges(DataSet ds)

  {

      if (ds == null)

          return;

   

      for (int nTable = 0; nTable < ds.Tables.Count; nTable++)

      {

          for (int nRow = 0; nRow < ds.Tables[nTable].Rows.Count; nRow++)

          {

             if (ds.Tables[nTable].Rows[nRow].HasVersion(DataRowVersion.Proposed))

             {

                 ds.Tables[nTable].Rows[nRow].EndEdit();

             }

           }

      }

  }

  
  
  

• Don Isenor

  Hi Zhi,

  That really did not work for me :-(
  I was reading about the BindingSource and together with Datasets, DataGridView, DataAdapter it got me more confused.

  From the project you mentioned I can see that an event is triggered for "Position Change", however my users won't be editing the cells neither will they jump from one row to another.

  All they do is select a range of rows and I want to process the selected rows and change the values automatically. I can see that in the DataGridView the data is changed but it never commits to the SQL table.

  Also I am not using a Dataset, just a Table, DataAdapter and DataGridView. All I need is to commit the new data into SQL.
  
  This is .Net! It should not be that hard. The lack of good books out there just leave me here high and dry hoping that the gurus in this forum will give me a hint or point us to the right direction. I also understand that I've been programming for 2 months so I may not understand the basics yet :-)

  With that said, is there a sample anywhere that shows how to update the data from a DataGridView into SQL
  The idea is to run through the range of selected rows one by one and change the fields values, update SQL and then go to the next row change its field values and update SQL, etc.....

  Thank you so much!

  JCDS

  
  
  

• kevin D. white

  I found out what was missing..I had to add the line below:

  da.UpdateCommand.Parameters["@STBid"].Value = strSTBid;

  ====> disregard the comment above. It was false alarm; that line does not change the behaviour of my issue.

  Thanks

   

  
  
  

• Tom B

  hello alihijazi,

  that unfortunately did not work as well. Here are the modifications I did:

  table = new DataTable("STBData");
  table.DefaultView.AllowDelete = false;
  table.DefaultView.AllowEdit = true;
  dataGridView1.DataSource = table;
  ...
  dataGridView1.BeginEdit(true); //dataview[0]["column_name"] = new value;
  row.Cells[7].Value = 1;
  dataGridView1.EndEdit();
  da.Update((DataTable)dataGridView1.DataSource);

  Please provide me a sample code or some links to try to understand this concept.

  Thank you !!!

  JSDS

  
  
  
• How do I update the changes from my DataGridView into my SQL table?