Refresh Datagridview (datasource is bindingsource).

[coolwater]

I have two datagridviews (parentDatagridview and childDatagridView). The parentDatagridview datasource is Bindingsource1 and childDatagridview datasource is Bindingsource2. Thanks to jmcilhinney for the Master/Detail code.

The form is used to monitor transactions of users (no add, edit and deletes). The parentDatagridview is a listing of Users and the childDatagridview is a listing of user transactions.

Question: I will have a refresh button that will refresh the childDatagridview to see if there where transactions added to a user. How do I refresh the bindingsource2?

Any help is highly appreciated.

[jmcilhinney]

It's not a really a matter of refreshing the BindingSource, but rather refreshing the DataTable whose data is exposed by that BindingSource. The BindingSource contains no data of it's own; it simply provides access to data contained in another list for the purposes of binding. Modify the underlying list and notify the BindingSource via the appropriate event, which a DataTable will do automatically, and the BindingSource will do the rest. This is a question of using a data adapter or table adapter to populate a DataTable and that's all.

So, the first thing to do is to decide what strategy you're going to use refresh the data in the already-bound DataTable. You have three primary choices:

1. Throw out everything you already have and replace it with the data currently in the database.
2. Retrieve everything currently in the database and compare it to what you already have, then modify the existing DataTable appropriately.
3. Retrieve only those records from the database that have changed since you last retrieved data and update the DataTable appropriately.

The first option is the simplest but the least desirable. The last option would be the best practice but it also requires that the database schema and usage support it, i.e. you would need a LastUpdatedTime column in the table that gets updated each time a record is inserted or updated and also an IsActive column that gets set to False instead of a record being deleted. Once you have decided on a stratgey, you can think about implementing it.

[coolwater]

jmcilhinney tnx for replying. Hmmm my user transactions (tblUsers_OR) have a LastUpdate (datetime) and TStamp (timestamp) columns. I don't have a isActive column. I guess I'll go with the 1st solution.

[jmcilhinney]

I guess I'll go with the 1st solution.

In that case, you're going to have to remove each child row for the selected parent from the child DataTable, then query the database and and filter by parent ID to repopulate the child DataTable. That might look like this:

'Don't raise any events so the grid doesn't update until we're done.
childBindingSource.RaiseListChangedEvents = False
 
'Get all the rows currently displayed in the child grid.
Dim childRows = childBindingSource.Cast(Of DataRowView)().Select(Function(drv) drv.Row).ToArray()
 
'Discard the current child records.
For Each childRow In childRows
    childDataTable.Rows.Remove(childRow)
Next
 
'Get the ID of the selected parent.
Dim parentRow = DirectCast(parentBindingSource.Current, DataRowView)
Dim parentId = CInt(parentRow("ParentId"))
 
'Set the query filter.
childDataAdapter.SelectCommand.Parameters("ParentId").Value = parentId
 
'Get the current data.
childDataAdapter.Fill(childDataTable)
 
'Refresh the grid with the new data.
childBindingSource.RaiseListChangedEvents = True
childBindingSource.ResetBindings(False)

Note that the query executed by the data adapter will be something like:

SELECT * FROM ChildTable WHERE ParentId = @ParentId

[coolwater]

Tnx for replying. I got this error: An SqlParameter with ParameterName 'UserID' is not contained by this SqlParameterCollection.

This is my GetDataSet Code:

    Private Function GetDataSet() As DataSet

        Dim data As New DataSet
        Dim parent As DataTable = Me.GetParentTable()
        Dim child As DataTable = Me.GetChildTable()

        data.Tables.Add(parent)
        data.Tables.Add(child)

        'Add a relationship between the ID of the parent
        'table and the ParentID of the child table.
        data.Relations.Add("ParentChild", parent.Columns("UserID"), child.Columns("UserID"))

        Return data

    End Function

My GetParentTable code:

Private Function GetParentTable() As DataTable

        Using con As SqlConnection = New SqlConnection(CS)

            daParent = New SqlDataAdapter("NET_tblPos_Users_isDisabled", con)
            daParent.SelectCommand.CommandType = CommandType.StoredProcedure

            dsParent = New DataSet
            dtParent = New DataTable("Parent")

            daParent.Fill(dsParent)
            daParent.Fill(dtParent)

        End Using

        Return dtParent

    End Function

My GetChildTable Code: My storedproc is select all (no parameters)

    Private Function GetChildTable() As DataTable

        Using con As SqlConnection = New SqlConnection(CS)

            daChild = New SqlDataAdapter("NET_tblPos_OfficialReceipt_SelectAll", con)
            daChild.SelectCommand.CommandType = CommandType.StoredProcedure

            dsChild = New DataSet
            dtChild = New DataTable("Child")

            daChild.Fill(dsChild)
            daChild.Fill(dtChild)

        End Using

        Return dtChild

    End Function

My cmdRefresh_Click Code:

    Private Sub cmdRefresh_Click(sender As System.Object, e As System.EventArgs) Handles cmdRefresh.Click

        'Don't raise any events so the grid doesn't update until we're done.
        BindingSource2.RaiseListChangedEvents = False

        'Get all the rows currently displayed in the child grid.
        Dim childRows = BindingSource2.Cast(Of DataRowView)().Select(Function(drv) drv.Row).ToArray()

        'Discard the current child records.
        For Each childRow In childRows
            dtChild.Rows.Remove(childRow)
        Next

        'Get the ID of the selected parent.
        Dim parentRow = DirectCast(BindingSource1.Current, DataRowView)
        Dim parentId = CInt(parentRow("UserID"))

        'Set the query filter.
        daChild.SelectCommand.Parameters("UserID").Value = parentId

        'Get the current data.
        daChild.Fill(dtChild)

        'Refresh the grid with the new data.
        BindingSource2.RaiseListChangedEvents = True
        BindingSource2.ResetBindings(False)

    End Sub

[jmcilhinney]

So add the parameter. If you're going to set a parameter then it should be obvious that there has to be a parameter to set.

[coolwater]

I modified the 'Set the query filter

 'Set the query filter.

        daChild.SelectCommand.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
        daChild.SelectCommand.Parameters("@UserID").Value = parentId

        'Get the current data.
        daChild.Fill(dtChild)

Now I get this error: The ConnectionString property has not been initialized.

daChild.Fill(dtChild)

[jmcilhinney]

So initialise the ConnectionString property. This is simply creating a data adapter. You obviously already know how to do that because you've already done it to get the data in the first place.

[coolwater]

I'm still lost. To my understanding to initialize a connectionstring is to open a connection. This is what I did.

        'Set the query filter.
        Using con As SqlConnection = New SqlConnection(CS)

            daChild = New SqlDataAdapter("NET_tblPos_OfficialReceipt_UserID", con)
            daChild.SelectCommand.CommandType = CommandType.StoredProcedure
            daChild.SelectCommand.Parameters.Add("@UserID", SqlDbType.BigInt, 8, "UserID")
            daChild.SelectCommand.Parameters("@UserID").Value = parentId

            MsgBox(daChild.SelectCommand.Parameters("@UserID").Value)

            dsChild = New DataSet
            dtChild = New DataTable("Child")

            daChild.Fill(dsChild)
            daChild.Fill(dtChild)

        End Using

Everytime I click refresh the rows on my child datagridview is removed. If I select another user from my parent datagirdview and click refresh I get this error below:

Error: The given DataRow is not in the current DataRowCollection (childRow).

    
For Each childRow In childRows
   dtChild.Rows.Remove(childRow)
Next

Anyway thanks for the help. I need sleep. I'll get back and try to figure this out tomorrow. Thanks.

[jmcilhinney]

This worked perfectly for me:

Imports System.Data.SqlClient
 
Public Class Form1
 
    Private ReadOnly data As New DataSet
    Private ReadOnly parentTable As DataTable = data.Tables.Add("Parent")
    Private ReadOnly childTable As DataTable = data.Tables.Add("Child")
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Using connection As New SqlConnection("connection string here"),
              parentCommand As New SqlCommand("SELECT * FROM Parent", connection),
              childCommand As New SqlCommand("SELECT * FROM Child", connection)
            connection.Open()
 
            Using reader = parentCommand.ExecuteReader(CommandBehavior.KeyInfo)
                Me.parentTable.Load(reader)
            End Using
 
            Using reader = childCommand.ExecuteReader(CommandBehavior.KeyInfo)
                Me.childTable.Load(reader)
            End Using
        End Using
 
        Me.data.Relations.Add("ParentChild", parentTable.Columns("ParentId"), childTable.Columns("ParentId"))
 
        Me.parentBindingSource.DataMember = "Parent"
        Me.parentBindingSource.DataSource = Me.data
 
        Me.childBindingSource.DataMember = "ParentChild"
        Me.childBindingSource.DataSource = Me.parentBindingSource
 
        Me.parentGrid.DataSource = Me.parentBindingSource
        Me.childGrid.DataSource = Me.childBindingSource
    End Sub
 
    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Me.childBindingSource.RaiseListChangedEvents = False
 
        Dim childRows = Me.childBindingSource.Cast(Of DataRowView)().Select(Function(drv) drv.Row).ToArray()
 
        For Each childRow In childRows
            Me.childTable.Rows.Remove(childRow)
        Next
 
        Dim parentId = CInt(DirectCast(Me.parentBindingSource.Current, DataRowView)("ParentId"))
 
        Using connection As New SqlConnection("connection string here"),
              command As New SqlCommand("SELECT * FROM Child WHERE ParentId = @ParentId", connection)
            command.Parameters.AddWithValue("@ParentId", parentId)
 
            connection.Open()
 
            Using reader = command.ExecuteReader(CommandBehavior.KeyInfo)
                Me.childTable.Load(reader)
            End Using
        End Using
 
        Me.childBindingSource.RaiseListChangedEvents = True
        Me.childBindingSource.ResetBindings(False)
    End Sub
 
End Class

It uses exactly the principles demonstrated in my CodeBank thread that you got the parent/child code from and here, with the only real difference being the fact that it uses data readers instead of data adapters because there's no need to save any changes to the data.