asked on

refresh datagridview bound to dataview when underlying datatable has new rows added

Hello experts.
I have a datatable that gets information from a SQl datatabase

    
Private Sub BuildEmployeeTable()
        Using MySqlDataClass As New SqlData
            With MySqlDataClass
                .SSqlStatement = "EmployeeManagement_Show"
                DtEmployeeTable = .GetSqlData
                DtEmployeeTable.TableName = "DefaultEmployeeDataFromSQL"
            End With
        End Using
    End Sub

Open in new window

I take that datatable and filter it in a dataview:

Private Sub FilterdataSets()
        With EmployeeDv
            .Table = DtEmployeeTable
            If ActiveStatusFilter = Nothing Then
                .RowFilter = EmployeeNameFilter & " And " & DepartmentFilter & " And " & ShiftFilter & " And " &
                RoleFilter & " And " & QualFilter & "And " & PlantFilter
            Else
                .RowFilter = EmployeeNameFilter & " And " & DepartmentFilter & " And " & ShiftFilter & " And " &
                    RoleFilter & " And " & QualFilter & "And " & ActiveStatusFilter & "And " & PlantFilter
            End If

        End With
end sub

Open in new window

Take that dataview and bind it to by datagridview:

  DgemployeeList.DataSource = EmployeeDv

Open in new window

Now the problem i am having is that the datagridview is bound to the dataview and when a new row is added by the user, it adds a row to the datatable but not the dataview.
So when a user enters a new row and switches to another new row, the row disappears from the datagridview because it was committed to the datatable and the dataview hasn't been updated. (I think)

I have tried in rowleave, detecting if a new datagridview row and updating...but it did not work.

    Private Sub MyRowLeave(sender As Object, e As DataGridViewCellEventArgs)
        If e.RowIndex >= CountOfFilteredRows - 1 Then
            FilterDataSets()
            DgEmployeeList.DataSource = EmployeeDv
        End If
    End Sub

Open in new window

1. Which event should I use to refresh the datagridview?
2. What code would you suggest to update the datagridview?
Please some sample code would be helpful.

Thanks
K.

Scott McDaniel (EE MVE )

You'd have to rebuild the EmployeeDV datasource before rebinding it, so do this:

BuildEmployeeTable
FilterDataSet

I'd suggest you explore the RowValidating and/or RowValidated events to do this. Those fire when changes are made to the Row, so you can determine if you're on the new row line and take action. To determine if you're on the New Row Line, check the NewRowIndex in the Validating event:

If e.RowIndex = YourDataGridView.NewRowIndex Then

kerrymr

ASKER

Thanks for the direction.

Here's the issue

1. RowValidating

    Private Sub DgEmployeeList_RowValidating(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DgEmployeeList.RowValidating
        If e.RowIndex >= CountOfFilteredRows - 1 And DgEmployeeList.CurrentRow.Cells("LastName").Value IsNot Nothing Then
            DgEmployeeList.CommitEdit(DataGridViewDataErrorContexts.Commit)
            FilterDataSets()
            EmployeeBindingSource.ResetBindings(False)
        End If
    End Sub

Open in new window

This causes Cell leave to fire, row leave to fire, Row validating to fire (infinite loop).

If instead of EmployeeBindingSource.ResetBindings(False) I have:
EmployeeDg.Datasource=Nothing
EmployeeDg.DataSource = employee DV

Then also infinite loop.

If I do:
EmployeeDg.DataSource = employee DV
the datagridview does not refresh.

Scott McDaniel (EE MVE )

RowValidating should contain logic that will VALIDATE your data only. You should fire events in the RowValidated event. RowValidating gives you a method to cancel any further actions if your validation fails:

If <your validation fails> then
e.Cancel = True
End If

If the RowValidating does not hit that "e.Cancel" line, then code you put in the RowValidated event will fire.

kerrymr

ASKER

After working with this for some time, I closed out of visual studio and restarted and it worked without changes.
Bug of some kind maybe or maybe some line of code somewhere made an impact.

I did try the validating and validated approaches but neither seemed to resolve the issue.

Thanks for your help Scott.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.