Solved

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

Posted on 2007-11-29
10
762 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:FMabey
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20373259
There is the EndEdit method for the DataGridView that might help.

Bob
0
 
LVL 3

Author Comment

by:FMabey
ID: 20373429
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 20374473
Try it and see if it helps.

Bob
0
 
LVL 34

Expert Comment

by:Sancler
ID: 20375662
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
 
LVL 3

Expert Comment

by:bnaveke
ID: 20377731
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:FMabey
ID: 20380490
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
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 20380635
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
 
LVL 3

Author Comment

by:FMabey
ID: 20380664
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
 
LVL 34

Expert Comment

by:Sancler
ID: 20380735
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
 
LVL 3

Author Comment

by:FMabey
ID: 20380749
Excellent. Thanks Roger.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now