Issues updating database from DataGridView

[zilonox]

I have a DataGridView that is attached to a BindingSource that gets data from a SQL Server database. I can populate the DataGridView without a problem. I can even insert/update it without issue if I run the update code from a click event of a button. But where I run into issues is when I try to update the database as soon as the user is done editing a row.

I thought that the DataGridView_RowLeave event would be perfect for that, but it actually does not commit the changes until it has been fired twice (that is, after an edit, I have to click on a different row or arrow down twice before the edit shows up in the database). Then I thought why not call the update code on both the _RowLeave and the _RowEnter? That worked fine for updates, but then I found that three records were inserted into the database when I added a new row to the DataGridView (the data gets inserted, then a null value, then the data again).

After digging around on the web for most of the day, I found that the _RowLeave event actually gets called pretty early on when you move to another row, so I figured maybe the changes made in the DataGridView aren't getting transmitted to the underlaying DataTable before the DataAdapter.Update method is being called. That would explain why the data would not update with the first call to _RowLeave, but would when using a click on a save button (since all the DataGridView events would complete before the button click event fires). So, I begain experimenting by placing my update code into various events. I tried _CellEndEdit, _CellValueChanged, _CurrentCellDirtyStateChanged (with a call to DataGridView.CommitEdit), and _RowValidating. I even tried calling BindingContext(DataGridView.DataSource).EndCurrentEdit() in conjunction with my update sub, but none of those would update the database when the focus left the edited row. :(

I'm at my wits end here. Surley it's possible to commit the changes made on the DataGridView to the database when the focus is moved off the edited/new row. It's obvious to me that I need to flush the changes shown on the DataGridView down to the DataTable, but I can't seem to find the right method/event to do just that. Can anyone offer a pointer here?

[M.Waqas Aslam]

well i think it is much better that you allow your user to make changes in grid after that press save button to save all the changes , this will not put load on your db , but i think in your case you may use cellvaluechange event , to use update code.

Regards

[zilonox]

Thank you for the suggestion, waqasaslammmeo, but as stated in my post, I did try the _CellValueChanged event, but it did not work. I'm looking into BindingSource events now - I'll update if I find a solution.

Of course, I'd still happily take more suggestions in the mean time. :)

[zilonox]

AH-HA! I knew I just had to find the right event. Turns out it was the BindingSource_PositionChanged event that I needed. However, since I'm creating the DataTable and the BindingSource at run time, I had to add WithEvents to my BindingSource declaration statement.

Private WithEvents BindingSource1 As New BindingSource

    Private Sub BindingSource1_PositionChanged(ByVal sender As Object, ByVal e As EventArgs) Handles BindingSource1.PositionChanged
    SaveData()
End Sub

(Where SaveData() is a sub routine where I define and execute the UpdateCommand and the InsertCommand for the DataAdapter)

Hopefully this will help save someone else a lot of searching time in the future.