locked
How to write back changed datagridview row automatically to the database ? RRS feed

  • Question

  • I display a SQL Server database table in a datagridview. Whenever a row in the grid gets changed, it should be updated automatically in the database table. I tried to do this in several events, so far with no luck:

    1) DataGridView (this would be the natural choice)

    Has no usefull event

     

    2) BindingSource_CurrentItemChanged

    I tried it like this:

    private void userTableBindingSource_CurrentItemChanged(object sender, EventArgs e) {

      DataRow ThisDataRow = ((DataRowView)((BindingSource)sender).Current).Row;

      if (ThisDataRow.RowState==DataRowState.Modified) {

        userTableTableAdapter.Update(ThisDataRow);

      }

    }

    This seems to work for the first changed row. But once I change any other row in the grid, I get a strange error message during the update of the second row, basically because the row seems to be empty. When I check with the debugger, the row has meaningful data before I call the update and only after the run time error it seems to be empty. The update also writes the second record successfully into the database !

     

    3) Strongly Typed DataSet, table.RowChanged Event


     void UserTable_RowChanged(object sender, DataRowChangeEventArgs e) {

      if (e.Row.RowState == DataRowState.Modified) {

        userTableTableAdapter.Update(e.Row);

        e.Row.AcceptChanges();

      }

    }

    It seems the event (UserTable_RowChanged) fires again in the Update statement. Of course, the RowState is still modified (Update has not finished yet). So Update is called again, which leads to a problem with the DataReader.

    Really don't know what to do.

    Wednesday, January 25, 2006 6:11 AM

Answers

  • In 2 days of investigation I learned the following:

    When a cell in the datagridview changes, events fire in this sequence:

    DataGridView_CellBeginEdit       CellEditMode: False
    DataGridView_CellValidating      CellEditMode: True
    DataTable_ColumnChanging         RowState: Unchanged; HasVersion 'DCOP'
    DataTable_ColumnChanged          RowState: Unchanged; HasVersion 'DCOP'
    DataGridView_CellValidated       CellEditMode: True
    DataGridView_CellEndEdit         CellEditMode: False
    DataGridView_RowValidating       CellEditMode: False
    DataTable_RowChanging            RowState: Unchanged; HasVersion 'DCOP'
    BindingSource_CurrentItemChanged RowState: Modified ; HasVersion 'DCO '
    BindingSource_ListChanged        RowState: Modified ; HasVersion 'DCO '
    DataTable_RowChanged             RowState: Modified ; HasVersion 'DCO '
    DataGridView_RowValidated        CellEditMode: False
    DataGridView_Validating          CellEditMode: False  
    DataGridView_Validated           CellEditMode: False

    DataRow Versions: D: Default, C: Current, O: Old, P: Proposed   

    The event "BindingSource_CurrentItemChanged " is fired before the dataTable has completed the row change (event "DataTable_RowChanged"). So if the event "BindingSource_CurrentItemChanged " calls TableAdapter.Update(), the DataRow gets changed again, before the first change was completed.

    Solution:

    Update change to database only when it is guaranteed that the DataRow change is completed. I chose the "BindingSource_PositionChanged" event, because the database should only be updated once all for all cells of a record.

    Of course, in the form closing event, one must check if there is still a row left needing to be written to the database.

     

    private DataRow LastDataRow = null; //tracks for the PositionChanged event the last row

     

    /// <summary>

    /// Checks if there is a row with changes and writes it to the database

    /// </summary>

    private void UpdateRowToDatabase() {

      if (LastDataRow!=null) {

        if (LastDataRow.RowState==DataRowState.Modified) {

          userTableTableAdapter.Update(LastDataRow);

        }

      }

    }

     

    private void userTableBindingSource_PositionChanged(object sender, EventArgs e) {

      // if the user moves to a new row, check if the last row was changed

      BindingSource thisBindingSource = (BindingSource)sender;

      DataRow ThisDataRow=((DataRowView)thisBindingSource.Current).Row;

      if (ThisDataRow==LastDataRow) {

        // we need to avoid to write a datarow to the database when it is still processed. Otherwise we get a problem

        // with the event handling of the dataTable.

        throw new ApplicationException("It seems the PositionChanged event was fired twice for the same row");

      }

      UpdateRowToDatabase();

      //track the current row for next PositionChanged event

      LastDataRow = ThisDataRow;

    }

     

    private void MembersForm_FormClosed(object sender, FormClosedEventArgs e) {

      UpdateRowToDatabase();

      thisForm = null;

    }

    Thursday, January 26, 2006 9:03 AM

All replies

  • I think I might use a combination of the DataGridView's CellBeginEdit and RowValidated events.  Something like this:

     private bool m_bUserChangingRow = false;
    private int m_iEditedRowIndex = 0;

    DGV_CellBeginEdit(...,e)
    {
     m_bUserChangingRow  = true;
     m_iEditedRowIndex = e.RowIndex;
    }


    DGV_RowValidated()
    {
     if(m_bUserChangingRow)
     {
      
      int iSomeID = (int)DGV.Rows[m_iEditedRowIndex].Cells["SomeID"].Value; 

      Datarow[] = dataset.datatable.select("SomeID = " + iSomeID);
      //Save row to database ....
     
      m_iEditedRowIndex = 0;
     }

     m_bUserChangingRow = false;
    }

     

    Wednesday, January 25, 2006 4:07 PM
  • Your suggestion might work since the DataGridView_RowValidated event fires after all events in the DataTable have fired. Disadvantage is that this would save the record to the database everytime a cell has changed.

    My solution looks similar, but I'm using the BindingSource_PositionChanged. Of course I need to check when I close the form if i still need to write one last row.

    Thursday, January 26, 2006 8:22 AM
  • In 2 days of investigation I learned the following:

    When a cell in the datagridview changes, events fire in this sequence:

    DataGridView_CellBeginEdit       CellEditMode: False
    DataGridView_CellValidating      CellEditMode: True
    DataTable_ColumnChanging         RowState: Unchanged; HasVersion 'DCOP'
    DataTable_ColumnChanged          RowState: Unchanged; HasVersion 'DCOP'
    DataGridView_CellValidated       CellEditMode: True
    DataGridView_CellEndEdit         CellEditMode: False
    DataGridView_RowValidating       CellEditMode: False
    DataTable_RowChanging            RowState: Unchanged; HasVersion 'DCOP'
    BindingSource_CurrentItemChanged RowState: Modified ; HasVersion 'DCO '
    BindingSource_ListChanged        RowState: Modified ; HasVersion 'DCO '
    DataTable_RowChanged             RowState: Modified ; HasVersion 'DCO '
    DataGridView_RowValidated        CellEditMode: False
    DataGridView_Validating          CellEditMode: False  
    DataGridView_Validated           CellEditMode: False

    DataRow Versions: D: Default, C: Current, O: Old, P: Proposed   

    The event "BindingSource_CurrentItemChanged " is fired before the dataTable has completed the row change (event "DataTable_RowChanged"). So if the event "BindingSource_CurrentItemChanged " calls TableAdapter.Update(), the DataRow gets changed again, before the first change was completed.

    Solution:

    Update change to database only when it is guaranteed that the DataRow change is completed. I chose the "BindingSource_PositionChanged" event, because the database should only be updated once all for all cells of a record.

    Of course, in the form closing event, one must check if there is still a row left needing to be written to the database.

     

    private DataRow LastDataRow = null; //tracks for the PositionChanged event the last row

     

    /// <summary>

    /// Checks if there is a row with changes and writes it to the database

    /// </summary>

    private void UpdateRowToDatabase() {

      if (LastDataRow!=null) {

        if (LastDataRow.RowState==DataRowState.Modified) {

          userTableTableAdapter.Update(LastDataRow);

        }

      }

    }

     

    private void userTableBindingSource_PositionChanged(object sender, EventArgs e) {

      // if the user moves to a new row, check if the last row was changed

      BindingSource thisBindingSource = (BindingSource)sender;

      DataRow ThisDataRow=((DataRowView)thisBindingSource.Current).Row;

      if (ThisDataRow==LastDataRow) {

        // we need to avoid to write a datarow to the database when it is still processed. Otherwise we get a problem

        // with the event handling of the dataTable.

        throw new ApplicationException("It seems the PositionChanged event was fired twice for the same row");

      }

      UpdateRowToDatabase();

      //track the current row for next PositionChanged event

      LastDataRow = ThisDataRow;

    }

     

    private void MembersForm_FormClosed(object sender, FormClosedEventArgs e) {

      UpdateRowToDatabase();

      thisForm = null;

    }

    Thursday, January 26, 2006 9:03 AM
  • Hi Peter,

    Why use PositionChanged and not CurrentItemChanged?

     

    Thanks,

    Tamir

    Tuesday, October 24, 2006 12:17 AM