Click here to Skip to main content
Click here to Skip to main content

VB DataGridView Automatically Saving Updates to a Bound Database

, 11 Jul 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
Demonstrates how to configure a DataGridView control to automatically store edits to bound datasets into the source database.

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.

License

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

Share

About the Author

Andrew Timberlake-Newell
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionHandling exception Pinmemberamannini10-Dec-13 23:57 
SuggestionAn alternative approach based on this excellent article PinmemberArnaud Dovi17-Mar-12 10:28 
QuestionMy Data Disappears from Database after a successful update PinmemberOnyebuchim4-Nov-11 5:35 
GeneralTrying to create universal decision PinmemberAlex Shestakov4-Dec-10 10:12 
GeneralQuestion Pinmembergandalf971-Jun-10 9:47 
GeneralAbout DataGridView in VB.Net [modified] PinmemberAlok K Seth27-Nov-09 22:28 
QuestionListChanged event does not fire moving within row, does in my grid rows!! PinmemberMoorboxer21-Oct-09 8:20 
GeneralSuggestion PinmemberBaruch Burstein10-Sep-09 1:46 
QuestionWill the code work for MS access? Pinmembermliu ism9-Jul-09 13:03 
QuestionRetreiving autonumber PK from Access Pinmemberleonelgaln30-Mar-09 5:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 11 Jul 2006
Article Copyright 2006 by Andrew Timberlake-Newell
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid