Solved

update database from bound datagrid on cell value changed

Posted on 2014-07-22
7
760 Views
Last Modified: 2014-07-28
I have been searching on this for quite some time and have been coming up empty.  I apologize for the wordy question, but I figured it's better to lay everything out and what I've done, so no open ended statements exist on my part.  I need to update my database immediately on data change.  But I need to give the user the option to reverse their changes.  This is the best I've come up with thus far.

I have a datagrid that I populate with an SQL dataset on form load.  I then hide a few columns and open 1 column for data changing.

' FULL CASES
Dim sAdapterFC As SqlDataAdapter
Dim sDsFC As New DataSet()
' POPULATE FULL CASES
        Dim sqlFC As String = "SELECT ID, PART_NUM, PART_NAME....."
        Dim sCommandFC As New SqlCommand(sqlFC, connection)
        sAdapterFC = New SqlDataAdapter(sCommandFC)
        Dim sBuilderFC As New SqlCommandBuilder(sAdapterFC)
        Dim sTableFC As DataTable = sDsFC.Tables("tblManuProdPART")
        sAdapterFC.Fill(sDsFC, "tblManuProdPART")
        sCommandFC = Nothing
        DataGridView1.DataSource = sDsFC.Tables("tblManuProdPART")
        If DataGridView1.Columns.Count > 0 Then
            For i = 0 To DataGridView1.Columns.Count - 1
                DataGridView1.Columns(i).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                DataGridView1.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable
                DataGridView1.Columns(i).ReadOnly = True
            Next
        End If
        DataGridView1.Columns(0).Visible = False
        DataGridView1.Columns(1).Visible = False
        DataGridView1.Columns(3).Visible = False
        DataGridView1.Columns(6).Visible = False
        DataGridView1.Columns(4).ReadOnly = False

Open in new window


I then need to handle the edits to the grid, as they will affect other calculations on the form.  The biggest issue is I want to preserve the initial value, to give the user a chance to cancel out of the edit and recalculate the form if they change their mind or make a mistake.  To do this, I'm using the BeginEdit, CellValidating (to ensure the value entered is numeric for calculation purposes), CellValueChanged as such:

Private Sub DataGridView1_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit

        If txtControl.Text = "VIEW" Then
            If (e.ColumnIndex = 4) Then
' CAPTURE ORIGINAL VALUES BEFORE THE EDIT TO ALLOW FOR ROLLBACK / RECALCULATION
                ORIGPARTValue1 = DataGridView1.Rows(e.RowIndex).Cells("PART_USED").Value.ToString
                ORIGentered1 = DataGridView1.Rows(e.RowIndex).Cells("PART_CASES_USED").Value.ToString
            End If
        End If

    End Sub

    Private Sub DataGridView1_CellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating

' CHECK FOR NULLS
        If (String.IsNullOrEmpty(e.FormattedValue.ToString())) Then
            MsgBox("All cells must have a value.  Please try again!", vbOKOnly, "Missing Data!")
            e.Cancel = True
        Else
' VERIFY IF IN COLUMN 4 THE ENTERED VALUE IS NUMERIC.  
' I specify this because another column is calculated based on the value entered in column 4.  If I do not specify the column, the validating hits on the calculated column and then crashes the program, like I'm stuck in a loop.
            If (e.ColumnIndex = 4) Then
                If Not IsNumeric(e.FormattedValue) Then
                    MsgBox("Please enter a numeric value.", vbOKOnly, "Non-Number Error")
                    e.Cancel = True
                    Exit Sub
                Else
                    If DataGridView1.Visible = True Then
                        DataGridView1.CommitEdit(Nothing)
                    End If
                End If
            End If
        End If

    End Sub

    Private Sub DataGridView1_CellValueChanged(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged

        If (e.ColumnIndex = 4) Then
' This recalculates the data in the gridview to compare against reports
            CalcProdFCPART()
            If txtControl.Text = "VIEW" Then

' This checks if the value entered isn't the same as the original.  If not, it kicks off the update process.
                If DataGridView1.Rows(e.RowIndex).Cells("PART_CASES_USED").Value <> ORIGentered1 Then
                    Dim tmpresponse As MsgBoxResult = MsgBox("You are about to adjust the component inventory for the following item:" & vbCrLf & vbCrLf & DataGridView1.Rows(e.RowIndex).Cells("COMP_NAME").Value & vbCrLf & vbCrLf & "These changes CANNOT be undone and will be applied immediately, without saving.  Are you sure you want to commit this change?", vbYesNo, "Inventory Update")
                    If tmpresponse = MsgBoxResult.Yes Then

                        Try
                            
' UPDATE THE ACTUAL DATABASE!!!!
                            Dim cmdFC As New SqlCommandBuilder(sAdapterFC)
        Dim iFC As Integer
        Try
            iFC = sAdapterFC.Update(sDsFC, "tblManuProdPART")
        Catch ex As Exception
            MsgBox(ex.Message, vbOKOnly, "Error Applying FULL CASES")
            Exit Sub
        End Try

                        Catch ex As Exception
                            MsgBox(ex.Message, vbOKOnly, "Error Applying FULL CASES")
                            Exit Sub
                        End Try
                        
                        'MsgBox("Component Inventory updated successfully.", vbOKOnly, "Saved")

                    Else
' ALLOWS THE ORIGINAL VALUE TO REPOPULATE IF THE USER CHOOSES TO CANCEL
                        MsgBox("Changes NOT saved.", vbOKOnly, "Change Failed")
                        DataGridView1.Rows(e.RowIndex).Cells("PART_CASES_USED").Value = ORIGentered1
                        DataGridView1.Refresh()
                    End If
                End If
            End If
        End If

    End Sub

Open in new window


The thing that blows my mind is if I take that same update logic and put it in a button, it works perfectly fine.  I have no idea why it won't update directly, without the use of the button.  I've tried Accepting the changes.  I've tried Committing the changes.  Nothing seems to make it work, without the use of the button.  But I really need them to be able to update immediately, after acknowledging the prompt, as there are other inventory processes I need to update as a result of this change.  But again, if they accidentally changed the wrong value, I need them to be able to back out the change, which would cancel all subsequent inventory changes as a result of the misentry.  I'm literally out of ideas and need the help of the experts.
0
Comment
Question by:fizzlefry
  • 5
  • 2
7 Comments
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 100 total points
ID: 40213810
Have you tried to step through the code to see which statements are executing? Is the update statement executing? If it is, can you check what the UpdateCommand.CommandText is on the adapter?
0
 

Author Comment

by:fizzlefry
ID: 40214862
CodeCruiser, thanks for the quick reply!  It is definitely firing, as far as I can see.  This is my update statement:

Dim cmdFC As New SqlCommandBuilder(sAdapterFC)
Dim iFC As Integer
iFC = sAdapterFC.Update(sDsFC, "tblManuProdPART")

But like I said, it fires and updates if I use it in a button.  If I had something declared incorrectly, wouldn't it affect the button too?  I have the exact same logic in the button and it works.
0
 

Author Comment

by:fizzlefry
ID: 40215208
CodeCruiser, I set a breakpoint for that line of code.  It is firing, however the UpdateCommand and UpdateCommand (DbDataAdapter) text is saying "Nothing".  So I'm guessing that has something to do with my problem.  The thing I don't understand is how and why this fires as a command button event, but doesn't seem to be updating through the DGV?
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

 

Author Comment

by:fizzlefry
ID: 40215320
Ok new discovery made.  I've been banging my head against this wall of code and have found out that the update statement DOES fire, but not on the first shot.  What I did was changed my value in the column from 50 to 75 and acknowledged my prompt and verified that nothing changed in the database.  I then went back to the column and changed 75 to 85 and acknowledged my prompt.  When I checked the database this time, it updated the database to 75!!!  So it's firing and firing correctly.  But something about this isn't capturing the change the first time.  It's always a step behind.  It has to do with the CellValueChanged event.  Because if I don't leave the row, I can change the values all the live long day, and nothing changes in the database.  However, when I change the value and leave the current row, it works, but again, it's a step behind.
0
 

Accepted Solution

by:
fizzlefry earned 0 total points
ID: 40215456
I got it!!!  I had to change the event to the CellEndEdit, rather than CellValueChanged and then call:

Me.Validate()
sAdapterFC.Update(sDsFC, "tblManuProdPART")
sDsFC.AcceptChanges()

Sorry to keep firing away at this post, but I just couldn't walk away from it.  Even after asking for help.  Thanks again for the help. CodeCruiser.  I think partial credit is in order, for at least nudging me in the right direction.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 40215497
Glad that you resolved it :-)
0
 

Author Closing Comment

by:fizzlefry
ID: 40223698
Although CodeCruiser was on the right track, it was changing the events and accepting the changes that ultimately got me where I needed to be.  I simply beat the heck out of Google until I found the right combination of events.  Thanks again to CodeCruiser, as always!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

757 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