It seems like it should be easy. It even is easy. But finding out how to configure bound
DataGridView controls to automatically update the source data can be a challenge.
Have you tried to catch the updates when they happen in the
DataGridView or in the
BindingSource and added updating code only to find error after cryptic error? If so, then the following approach may solve your programming puzzle.
The task of storing updates doesn't seem to be as easy as it should, at least at first glance. Bound
DataGridView controls seem to get very grumpy when one tries to update their source concurrently with their own internal updates.
One approach to getting around this issue using the
PositionChanged event can be found in Peter Huber SG's "Auto Saving DataGridView Rows to SQL Database". However, the
PositionChanged approach can leave behind row deletions, especially when several rows are deleted in sequence. Nonetheless, Peter Huber SG's work is worth reviewing, especially for his trace of events related to
DataGridView content changes.
Delaying Updates to Avoid Conflict
PositionChanged approach started with the right idea. In order to update the underlying source for a bound
DataGridView, changes must be noted when they occur but must happen later. Unfortunately, events outside of the change process can not necessarily note every change. For instance,
PositionChanged will not necessarily fire between deletions.
Instead, simply use an indicator to note when there has been a change as it happens.
Dim UpdatePending As Boolean = False
Private Sub ExampleBindingSource_ListChanged(ByVal sender As Object, _
ByVal e As System.ComponentModel.ListChangedEventArgs) _
If Me.ExampleDataSet.HasChanges Then
Me.UpdatePending = True
Then, check the indicator after the
BindingSource is finished with the change. The
RowValidated event works well for this, since it reliably fires shortly after the
Private Sub DataGridView1_RowValidated(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
If UpdatePending Then
Me.UpdatePending = False
About the Demo Project
The demo project makes use of the above solution in its
Form1 with an included example SQL Express database file within a Visual Studio 2005 project.
Form2 is an example of the
PositionChanged approach in Visual Basic.
- 07/11/2006: original posting.