CODEWITHSUNDEEP
vb.netwinformsdatagridviewvisual-studio-2013dataset
marky
marky

Reputation: 5068

How to compare DataGridView cell data to DataSet cell data

In a VB.NET WinForms project I'm working on in VS2013 I am detecting when the user changes something in a cell on the CellValueChanged event. When a row in the DataGridView is found that is different from the DataSet I highlight the row in pink.

In my code though, I only know how to iterate through all of the rows, rather than just compare the row that fired the CellValueChanged event to the DataSet.

Here's my current code:

Private Sub dgvEmployees_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvEmployees.CellValueChanged
    ' Pass the row and cell indexes to the method so we can change the color of the edited row
    CompareDgvToDataSource(e.RowIndex, e.ColumnIndex)
End Sub

Private Sub CompareDgvToDataSource(ByVal rowIndex As Integer, ByVal columnIndex As Integer)

    ' Force ending Edit mode so the last edited value is committed
    EmployeesBindingSource.EndEdit()

    Dim dsChanged As DataSet = EmployeesDataSet.GetChanges()

    If Not dsChanged Is Nothing Then
        For Each dt As DataTable In dsChanged.Tables
            For Each row As DataRow In dt.Rows
                For i As Integer = 0 To dt.Columns.Count - 1
                    Dim currentColor As System.Drawing.Color = dgvEmployees.AlternatingRowsDefaultCellStyle.BackColor

                    If Not row(i, DataRowVersion.Current).Equals(row(i, DataRowVersion.Original)) Then
                        Console.WriteLine("Row index: " & dt.Rows.IndexOf(row))
                        ' This works
                        dgvEmployees.Rows(rowIndex).DefaultCellStyle.BackColor = Color.LightPink
                    Else
                        ' Need to change the BackColor back to what it should be based on its original alternating row color
                    End If
                Next
            Next
        Next
    End If
End Sub

As you can see, I'm passing the row and column index of the changed cell, so how can I take that and compare it to the specific row and/or cell in the unchanged DataSet?

UPDATE

Nocturnal reminded me that I need to allow for sorting of the DGV, so using row indexes won't work. He offered this as a solution (changed slightly to work with my objects):

If Not dsChanged Is Nothing Then
    For Each dtrow As DataRow In dsChanged.Tables("employees").Rows
        If DirectCast(dtrow, EmployeesDataSet.employeesRow).employeeID.ToString = dgvEmployees.Rows(rowIndex).Cells("employeeID").Value.ToString Then
            For i As Integer = 0 To dsChanged.Tables("employees").Columns.Count - 1
                If Not dtrow(i, DataRowVersion.Current).Equals(dtrow(i, DataRowVersion.Original)) Then
                    Console.WriteLine("Employees ID: " & DirectCast(dtrow, EmployeesDataSet.employeesRow).employeeID)
                    dgvEmployees.Rows(rowIndex).Cells(columnIndex).Style.BackColor = Color.LightPink
                Else
                    ' Need to change the BackColor back to what it should be based on its original alternating row color
                End If
            Next
        End If
    Next
End If

However, I'm getting an error on the If DirectCast... line saying "Column named "employeeID" cannot be found." I'm not sure if the error is on the DataSet or on the DGV, but there is an employeeID column in the database and DataSet. There is an employeeID as a bound column for the DataGridView, but it is set to Visible = False. That's the only thing I can think of that could possibly cause that error, but if it's a bound column, I would think it could be compared against as in this case.

FINAL WORKING VERSION

Private Sub dgvEmployees_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvEmployees.CellValueChanged
    ' Pass the row and cell indexes to the method so we can change the color of the edited row
    CompareDgvToDataSource("employees", e.RowIndex, e.ColumnIndex)
End Sub

Private Sub CompareDgvToDataSource(ByVal dataSetName As String, ByVal rowIndex As Integer, ByVal columnIndex As Integer)
    ' Takes a dataset and the row and column indexes, checks if the row is different from the DataSet and colors the row appropriately

    EmployeesBindingSource.EndEdit()

    Dim dsChanges As DataSet = EmployeesDataSet.GetChanges()

    If Not dsChanges Is Nothing Then
        For Each dtrow As DataRow In dsChanges.Tables("employees").Rows
            If DirectCast(dtrow, EmployeesDataSet.employeesRow).employeeID.ToString = dgvEmployees.Rows(rowIndex).Cells("employeeID").Value.ToString Then
                For i As Integer = 0 To dsChanges.Tables("employees").Columns.Count - 1

                    If dtrow.RowState.ToString = DataRowState.Added.ToString Then
                        ' TODO: Color entire new row
                    ElseIf dsChanges.Tables(dataSetName).Rows(0).HasVersion(DataRowVersion.Original) Then
                        If Not dtrow(i, DataRowVersion.Current).Equals(dtrow(i, DataRowVersion.Original)) Then
                            Console.WriteLine("Employees ID: " & DirectCast(dtrow, EmployeesDataSet.employeesRow).employeeID)
                            dgvEmployees.Rows(rowIndex).Cells(columnIndex).Style.BackColor = Color.LightPink
                        Else
                            ' TODO: Need to change the BackColor back to what it should be based on its original alternating row color
                        End If
                    End If
                Next
            End If
        Next
    End If
End Sub

My project has four DGVs in it, so this will eventually be expanded to allow for all four DGVs to be checked for changes.

Upvotes: 0

Views: 2296

Answers (1)

Nocturnal
Nocturnal

Reputation: 386

have a look at this approach it is simlar but only iterating the specific Table

Private Sub dgvEmployees_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvEmployees.CellValueChanged ' Pass the row and cell indexes to the method so we can change the color of the edited row CompareDgvToDataSource(e.RowIndex, e.ColumnIndex, sender.DataSource.datamember.ToString) End Sub

and comparing the ID of the given Table

    Private Sub CompareDgvToDataSource(ByVal rowIndex As Integer, ByVal columnIndex As Integer, tablename As String)

    EmployeesBindingSource.EndEdit()

    Dim dsChanged As DataSet = EmployeesDataSet.GetChanges()

    If Not dsChanged Is Nothing Then
        For Each dtrow As DataRow In dsChanged.Tables(tablename).Rows

            If DirectCast(dtrow, EmployeesDataSet.EmployeeRow).EmployeeID = dgvEmployees.Rows(rowIndex).Cells("EmployeeID").Value Then
                Dim currentColor As System.Drawing.Color = dgvEmployees.AlternatingRowsDefaultCellStyle.BackColor

                For i As Integer = 0 To dsChanged.Tables(tablename).Columns.Count - 1
                    If Not dtrow(i, DataRowVersion.Current).Equals(dtrow(i, DataRowVersion.Original)) Then
                        Console.WriteLine("Employees ID: " & DirectCast(dtrow, EmployeesDataSet.EmployeeRow).EmployeeID)
                        ' This works
                        dgvEmployees.Rows(rowIndex).Cells(columnIndex).Style.BackColor = Color.LightPink
                    Else
                        ' Need to change the BackColor back to what it should be based on its original alternating row color
                    End If
                Next
            End If
        Next
    End If
End Sub

and i am only changing the backcolor of the cell that was changed not the entire row....but depends how you would use that information anyways

EDIT:

with

Console.WriteLine(EmployeesDataSet.Employee(rowIndex)(columnIndex, DataRowVersion.Original).ToString())
Console.WriteLine(EmployeesDataSet.Employee(rowIndex)(columnIndex, DataRowVersion.Current).ToString())

you can see Current and Original Values 123456

Upvotes: 1

Related Questions