VB DataGridView Automatically Saving Updates to a Bound Database

• Download demo project - 215 Kb

Introduction

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.

Background

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 BindingSource's 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

The 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.

' We need an indicator to know when we need to update the source database
Dim UpdatePending As Boolean = False

Private Sub ExampleBindingSource_ListChanged(ByVal sender As Object, _
        ByVal e As System.ComponentModel.ListChangedEventArgs) _
        Handles ExampleBindingSource.ListChanged
    ' Whenever there is an update, note that a change is pending.
    '
    ' ListChanged does not fire when moving within a row, so this will not
    ' mark updates until done with the row. (Here "done" could mean moving
    ' to another row or closing the form.)
    If Me.ExampleDataSet.HasChanges Then
        Me.UpdatePending = True
        
    End IfEnd
Sub

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 ListChanged event.

Private Sub DataGridView1_RowValidated(ByVal sender As Object, _
        ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
        Handles DataGridView1.RowValidated
    ' The RowValidated event occurs after
    ' BindingSource_*Changed operations, which
    ' makes it a good place to update our source database.
    ' However, this event fires at a number
    ' of times when we don't have pending updates.
    ' That's why we need the UpdatePending indicator 
    ' to tell us whether to do anything.
    ' If we have an update pending, copy it to the source database
    If UpdatePending Then
        Me.ExampleTableAdapter.Update(Me.ExampleDataSet.Example)
        Me.UpdatePending = False
    End IfEnd
Sub

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.

History

• 07/11/2006: original posting.