How to get the Dataset.HasChanges working on the CellValueChanged event

Hi all,

I have a datagridview which displays data through a dataview (DV_weld), which in turn is populated by a dataset (DS_weld1). The dataset gets it's data from SQL Server table through a SQLDataAdapter.

I would like the SQL table to be updated everytime a user edits some data in the datagridview. I have tried using:

    Private Sub DGV_weld_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV_weld.CellValueChanged

        If DS_weld1.HasChanges Then

            SDA_weld.Update(DS_weld1.GetChanges())
            DS_weld1.AcceptChanges()

        End If

    End Sub

But it will not recognise changes until the user moves off the row and edits some more data. How can I get this to work so that changes are dynamically updated in the table (instead of having to create a button for updating) everytime the user makes a change. I will also need to do this on text boxes.

Cheers
LVL 3
FMabeyAsked:
Who is Participating?
 
SanclerConnect With a Mentor Commented:
The issue raised in your last sentence - the possibility of losing changes through crashes etc - is one of the reasons for which I would sometimes 'see justification for straying from the .NET "disconnected" model of data-handling'.  But it's a question of degree.  

I would normally, as a matter of course, include a data-saving routine in some closing event/s.  The first step in that routine would, again as a matter of course, always be an .EndEdit or .EndCurrentEdit.  Often that step would not be strictly necessary, but in those it does no harm and it avoids the sort of problem that this thread raises (of changes in control/s not being committed to the datasource before the save).  

In theory, that should be enough.  But, in practice, if there is a crash, all user-changes made during the session with the application are lost.  So, depending on the likelihood of a crash and the importance / complexity of the changes that I might expect to be made, I will sometimes include some "interim" updating routine/s.  One approach would be with a Timer (think, for example, of the autosave facility in MSWord) and an advantage of that is that I can allow the user to choose how often background "autosaves" should be done.  Another would be a counter: a variable that is incremented each time a value is changed and, when it reaches a certain level, calls a data-saving routine.

In extreme cases, I might save after _every_ change but - and this is the real point here - I would regard a "change" for these purposes as being the change of a _record_ not just of one value in one field/column of a record.  As you've discovered, records (in the datatable) are only - automatically - changed when a user moves focus to a different record (or with some controls, to a different control).  Within a DGV, while the focus remains on the same row/record it is the DGV itself that keeps track of changes in individual cells and it only sends those (and then all of them) to the datasource when it "knows" - because the user has moved to a different row, or to a different control - that the user has finished editing the _row_.  So, if I wanted to save after _every_ change, I would link into one of the _row_ events rather than one of the _cell_ events: e.g. RowEnter, RowLeave.  And this would have the advantage that, because a change of row _automatically_ causes committal of any changes in the row which has just been left to the datasource, no .EndEdit or .EndCurrentEdit should be necessary.  OK, that may result in some change/s being lost if there is a crash, but they would, at the most, be those to the row/record being worked on when the crash occurred.

It is, as I say, a question of degree.  There is no RULE saying you cannot update at the frequency you are currently doing.  But ...

Roger
0
 
Bob LearnedCommented:
There is the EndEdit method for the DataGridView that might help.

Bob
0
 
FMabeyAuthor Commented:
So would I do this?

    Private Sub DGV_weld_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV_weld.CellValueChanged

DGV_weld.endedit()

        If DS_weld1.HasChanges Then

            SDA_weld.Update(DS_weld1.GetChanges())
            DS_weld1.AcceptChanges()

        End If

    End Sub
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Bob LearnedCommented:
Try it and see if it helps.

Bob
0
 
SanclerCommented:
So far as I know, DGV.EndEdit only commits the edit at the CELL level.  Passing changes back from the DGV to the datasource requires committal at the ROW level.  So, if you want to do this, I think you need to use BindingSource.EndEdit or CurrencyManager.EndCurrentEdit.  Precise syntax will depend on your data-binding set-up, but if you have problems on that, come back with details of how DGV_weld is bound to which table in DS_weld1.

But I have to comment that committing changes back to the database with this frequency is not something I would go for, unless the circumstances were very unusual.  I can - sometimes, although not often - see justification for straying from the .NET "disconnected" model of data-handling to the extent of sending back changes as soon as a user's changes to a whole row/record have been committed (by him/her moving off the row in the DGV, or to a different control on the form).  But I find it difficult to envisage circumstances in which, while s/he remains in the same row, updating the database would be desirable.

Roger
0
 
bnavekeCommented:
You need to endedit on the BindingSource not the table.

Me.Validate()
YourBindingSourcename.EndEdit()

 If DS_weld1.HasChanges Then
            SDA_weld.Update(DS_weld1.GetChanges())
            DS_weld1.AcceptChanges()
 End If
0
 
FMabeyAuthor Commented:
Roger,

The main reason I wanted to write changes so regularly is becuase I use the following code to allow the user to reorder the rows in the dgv:

 Private Sub DGV_weld_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DGV_weld.DragDrop
        Dim clientPoint As Point = DGV_weld.PointToClient(New Point(e.X, e.Y))
        rowIndexOfItemUnderMouseToDrop = DGV_weld.HitTest(clientPoint.X, clientPoint.Y).RowIndex
        If e.Effect = DragDropEffects.Move Then

            'Concentrate on the selected row
            For Each dgvrweld As DataGridViewRow In DGV_weld.SelectedRows

                'Determine what makes the items unique
                Dim STR_id As String = dgvrweld.Cells("WLD_ID").Value
                Dim STR_gdr As String = dgvrweld.Cells("WLD_GDR_NO").Value

                'Open the datarow
                Dim weldrow As DataRow() = DS_weld1.WELD.Select(String.Format("WLD_GDR_NO = '{0}' AND WLD_ID = '{1}'", STR_gdr, STR_id))

                'If the item greater than 0. i.e. not the 1st item - If it is the 1st item then makr it 0.
                If rowIndexOfItemUnderMouseToDrop > 0 Then

                    weldrow(0)("WLD_SORT") = rowIndexOfItemUnderMouseToDrop + 1.1

                Else

                    weldrow(0)("WLD_SORT") = 0

                End If

            Next

            'Sort by the new sort order
            DV_weld.Sort = "WLD_SORT"

            'MsgBox("Here we should see the rearranged items")

            '/////////////////////////////////////////////////////////////////////////////////////////

            'For all rows, enter the sort numbers into the temporary sort column (This is not in the SQL table but is in the dataset)
            For Each dgvrtemp As DataGridViewRow In DGV_weld.Rows

                'Determine what makes the items unique
                Dim STR_id As String = dgvrtemp.Cells("WLD_ID").Value
                Dim STR_gdr As String = dgvrtemp.Cells("WLD_GDR_NO").Value

                'Open the datarow
                Dim temprow As DataRow() = DS_weld1.WELD.Select(String.Format("WLD_GDR_NO = '{0}' AND WLD_ID = '{1}'", STR_gdr, STR_id))

                'Set the temp sort value
                temprow(0)("WLD_TEMP_SORT") = temprow(0)("WLD_SORT")

            Next

            'MsgBox("New numbers should be in the temp column")

            DV_weld.Sort = "WLD_TEMP_SORT"

            '/////////////////////////////////////////////////////////////////////////////////////////

            'MsgBox("Now we should see the items, rearranged by the new column")

            Dim INT_sort As Integer = 0

            'For Each dgvrweld As DataGridViewRow In DGV_weld.Rows
            For tRow As Integer = 0 To DGV_weld.Rows.Count - 1

                'Dim STR_sort As String = dgvrweld.Cells("WLD_SORT").Value
                'Dim STR_gdr As String = dgvrweld.Cells("WLD_GDR_NO").Value
                Dim STR_id As String = DGV_weld.Rows(tRow).Cells("WLD_ID").Value
                Dim STR_gdr As String = DGV_weld.Rows(tRow).Cells("WLD_GDR_NO").Value

                Dim weldrow As DataRow() = DS_weld1.WELD.Select(String.Format("WLD_GDR_NO = '{0}' AND WLD_ID = '{1}'", STR_gdr, STR_id))

                INT_sort = INT_sort + 1

                weldrow(0)("WLD_SORT") = INT_sort ''tRow + 1

            Next

            'MsgBox("Just before the final sort")

            DV_weld.Sort = "WLD_SORT"

            If DS_weld1.HasChanges Then

                SDA_weld.Update(DS_weld1.GetChanges())
                DS_weld1.AcceptChanges()
                'SDA_weld.Update(DS_weld1, "WELD")

            End If

            FillIndividualPartsTable()

            'For tRow As Integer = DGV_weld.Rows.Count - 1 To 0 Step -1

            'Next tRow

        End If
    End Sub

    Private Sub DGV_weld_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DGV_weld.DragOver

        e.Effect = DragDropEffects.Move

    End Sub

    Private Sub DGV_weld_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DGV_weld.MouseDown

        rowIndexFromMouseDown = DGV_weld.HitTest(e.X, e.Y).RowIndex
        If rowIndexFromMouseDown <> -1 Then
            Dim dragSize As Size = SystemInformation.DragSize
            dragBoxFromMouseDown = New Rectangle(New Point(e.X - (dragSize.Width / 2), e.Y - (dragSize.Height / 2)), dragSize)
        Else
            dragBoxFromMouseDown = Rectangle.Empty
        End If

    End Sub

    Private Sub DGV_weld_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DGV_weld.MouseMove
        If e.Button = Windows.Forms.MouseButtons.Right Then
            If dragBoxFromMouseDown <> Rectangle.Empty And dragBoxFromMouseDown.Contains(e.X, e.Y) Then
                Dim dropEffect As DragDropEffects = DGV_weld.DoDragDrop(DGV_weld.Rows(rowIndexFromMouseDown), DragDropEffects.Move)
            End If
        End If
    End Sub

As you can see. This operation updates the database table. My problem is that if a user has made changes and then they reorder, this will save all changes. However, if a user makes changes and doesn't reorder, the changes will not be saved and the user will have to save the changes themselves... This isn't great in terms of design. I really need a way in which changes are saved sutomatically. I thought I putting the update on the form close but was concerned what would happen if the application closed unexpectedly (crash etc...).

Any advice / ideas?
0
 
FMabeyAuthor Commented:
Thanks Roger,

As always,  a very concise explaination. I have decided to use certain events to save the data, rather than saving after every change.

As a matter of interest. Do you have any examples of using a timer to autosave? This could be a useful tool.

Cheers
0
 
SanclerCommented:
All you need to do is drag a timer onto your form and use code like this.

Public Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Timer1.Interval = 10000 'in real life, can be replaced ...
        '... with anything else or with optional value set by user
        Timer1.Start()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Timer1.Stop()
        doUpdate()
    End Sub

    Private Sub doUpdate()
        MsgBox("Updated") 'in real life replace ...
        '... with Update routine
        Timer1.Start()
    End Sub
End Class

Roger

0
 
FMabeyAuthor Commented:
Excellent. Thanks Roger.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.