Click here to Skip to main content
15,356,336 members
Articles / Database Development / SQL Server
Posted 11 Jul 2006


62 bookmarked

VB DataGridView Automatically Saving Updates to a Bound Database

Rate me:
Please Sign up or sign in to vote.
4.53/5 (20 votes)
11 Jul 2006CPOL2 min read
Demonstrates how to configure a DataGridView control to automatically store edits to bound datasets into the source database.


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

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.UpdatePending = False
    End IfEnd

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.


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


About the Author

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

Comments and Discussions

GeneralMy vote of 5 Pin
Member 1373782920-Mar-18 18:38
MemberMember 1373782920-Mar-18 18:38 
QuestionHandling exception Pin
amannini10-Dec-13 23:57
Memberamannini10-Dec-13 23:57 
SuggestionAn alternative approach based on this excellent article Pin
Arnaud Dovi17-Mar-12 10:28
MemberArnaud Dovi17-Mar-12 10:28 
QuestionMy Data Disappears from Database after a successful update Pin
Onyebuchim4-Nov-11 5:35
MemberOnyebuchim4-Nov-11 5:35 
GeneralTrying to create universal decision Pin
Alex Shestakov4-Dec-10 10:12
MemberAlex Shestakov4-Dec-10 10:12 
GeneralQuestion Pin
gandalf971-Jun-10 9:47
Membergandalf971-Jun-10 9:47 
GeneralAbout DataGridView in VB.Net [modified] Pin
Alok K Seth27-Nov-09 22:28
MemberAlok K Seth27-Nov-09 22:28 
QuestionListChanged event does not fire moving within row, does in my grid rows!! Pin
Moorboxer21-Oct-09 8:20
MemberMoorboxer21-Oct-09 8:20 
GeneralSuggestion Pin
Baruch Burstein10-Sep-09 1:46
MemberBaruch Burstein10-Sep-09 1:46 
QuestionWill the code work for MS access? Pin
mliu ism9-Jul-09 13:03
Membermliu ism9-Jul-09 13:03 
QuestionRetreiving autonumber PK from Access Pin
User 441871030-Mar-09 5:54
MemberUser 441871030-Mar-09 5:54 
GeneralNice! Pin
crsqaguy12-Dec-07 8:53
Membercrsqaguy12-Dec-07 8:53 
QuestionUpdating Pin
GryphonsClaw25-Apr-07 18:50
MemberGryphonsClaw25-Apr-07 18:50 
AnswerRe: Updating Pin
GryphonsClaw25-Apr-07 19:07
MemberGryphonsClaw25-Apr-07 19:07 
AnswerRe: Updating Pin
Andrew Timberlake-Newell26-Apr-07 3:13
MemberAndrew Timberlake-Newell26-Apr-07 3:13 
Questionupdating datagridview Pin
Member 393593720-Mar-07 0:06
MemberMember 393593720-Mar-07 0:06 
AnswerRe: updating datagridview Pin
Andrew Timberlake-Newell26-Apr-07 3:26
MemberAndrew Timberlake-Newell26-Apr-07 3:26 
GeneralVERY GOOD... Pin
Domingo M. Asuncion28-Feb-07 20:17
MemberDomingo M. Asuncion28-Feb-07 20:17 
GeneralVery Helpful Pin
JShow19-Dec-06 9:45
MemberJShow19-Dec-06 9:45 
GeneralExcellent work Pin
WillemM9-Oct-06 20:41
MemberWillemM9-Oct-06 20:41 
GeneralGood Job Pin
davet651026-Sep-06 7:19
Memberdavet651026-Sep-06 7:19 

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

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