none
Datagridview with Bindinsource filter and Grand Total RRS feed

  • Question

  • HI all

    I have a DGV bound to BindingSource (Bound to data Table from Sql database)

    I added code to compute totals for numeric columns in the DGV, and a Button to filter rows in the DGV. (usinf BindingSource.Filter)

    So when the BS filter is used the rows  in DGV will change and I have to compute again the Gran Total

    The question is : Which DGV event is raised when di Binding source filter is used  ?

    Thursday, August 2, 2018 8:21 AM

Answers

  • Ok Karen but I have the following problem:

    I have to compute Grand Total for numeric column in the DGV. 

    I have a checkbox for every row in the DGV and I can select multi row using shift key.

    If I handle Bs.Listchange Event in this way

     Private Sub BS_ListChanged(sender As Object, e As ListChangedEventArgs) Handles BS.ListChanged
            ComputeTotal()
            ShowTotal()
        End Sub

    the sub ComputeTotal is called for every row I checked and this slowdown the display.

    How can I avoid this by handlig event just once ?

     

     

    The ListChangedEventArgs has a ListChangedType property.  I haven't verified this, but I suspect that a filter operation will be marked as a Reset since the entire list significantly changes.  Just checking an item should be an item or property descriptor change.  So I would try the following to see if it addresses the issue before moving on to a different solution:

    Private Sub BindingSource1_ListChanged(sender As Object, e As ListChangedEventArgs) Handles BindingSource1.ListChanged
        If e.ListChangedType = ListChangedType.Reset Then
            ComputeTotal()
            ShowTotal()
        End If
    End Sub
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Claudio111 Friday, August 3, 2018 4:06 PM
    Friday, August 3, 2018 1:56 PM
    Moderator

All replies

  • Hi

    It does not trigger any event, just control the DataSource of Datagridview

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim dt As DataTable
        Dim bind As New BindingSource
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter("Select * From Student", conn)
                dt = New DataTable()
                sda.Fill(dt)
                bind.DataSource = dt
                DataGridView1.DataSource = dt
            End Using
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            bind.Filter = "Uid=1"
            Dim j = 0
            For Each i In DataGridView1.Rows
                j += i.Cells(3).value
            Next
            TextBox1.Text = j.ToString
        End Sub
    End Class
    


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 2, 2018 9:05 AM
  • HI Alex

    Thank you but this is not the solution for me. I don't whant to click a Button to compute Total

    Maybe I found a solution

    When you filter BS in any case the Event DGV.DataBindingComplete is raised

    Handling the event you can show total without any click on a Button

    Thursday, August 2, 2018 9:20 AM
  • Hi

    This is also possible.If your issue is solved, please "Mark as answer" .

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 2, 2018 9:42 AM
  • Personally I would not use the DGV values to compute. I would either use the DataTable Compute method or iterate the bindingsource

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Thursday, August 2, 2018 12:12 PM
  • I have not tested this (currently I'm updating Visual Studio so it's down right now)

    ListedChanged event, then call an event such as the following in my MSDN code sample, look at the Update method.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 2, 2018 12:24 PM
    Moderator
  • Okay, I just tested it and the following provides the count of rows during a filter. 'bs' is the BindingSource.

    In form load (bs.DataSource is a DataTable)

    AddHandler bs.ListChanged, AddressOf bsListChanged

    The ListChangeEvent

    Private Sub bsListChanged(sender As Object, .e As ListChangedEventArgs)
        Console.WriteLine(CType(bs.DataSource, DataTable).DefaultView.Count)
    End Sub

    Here I did a filter with no results and a filter with four results (several in between)

    Note in the header "Custom BindingSource", it derives from a BindingSource and is seen here, you might find it useful.

    Edit: Note I access DefaultView which is where the filter is, if you access Rows.Count it will not reflect the current filter.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Thursday, August 2, 2018 1:52 PM
    Moderator
  • Ok Karen but I have the following problem:

    I have to compute Grand Total for numeric column in the DGV. 

    I have a checkbox for every row in the DGV and I can select multi row using shift key.

    If I handle Bs.Listchange Event in this way

     Private Sub BS_ListChanged(sender As Object, e As ListChangedEventArgs) Handles BS.ListChanged
            ComputeTotal()
            ShowTotal()
        End Sub

    the sub ComputeTotal is called for every row I checked and this slowdown the display.

    How can I avoid this by handlig event just once ?

     

     

    Friday, August 3, 2018 7:58 AM
  • There is no clean way to do this in regards to automatically computing totals dealing with both filtering and checking rows especially if there is a lot of data which is most likely why the compute code is slow.

    The nature of ListChanged is just that, when the list changed trigger the event.

    Have you looked at DataColumn Expression



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, August 3, 2018 9:19 AM
    Moderator
  • I could be misunderstanding this whole thing, but here is my 2 cents...

    Public Class DGVExample
        Dim DT As New DataTable
        WithEvents BS As New BindingSource
        Private Sub DGVExample_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            With DT
                .Columns.Add("Select", GetType(System.Boolean))
                .Columns("Select").DefaultValue = False
    
                ''here you could fill your table with reader/adapter
                ''I just manually added some columns for test
                .Columns.Add("StringCol", GetType(System.String))
                .Columns.Add("ColToSum", GetType(System.Double))
            End With
            With BS
                .DataSource = DT
                For i = 0 To 100
                    .AddNew()
                    DirectCast(.Current, DataRowView)("StringCol") = "WhatEver" & i
                    DirectCast(.Current, DataRowView)("ColToSum") = 100 * Rnd() + 1
                Next
                .EndEdit()
            End With
            With DataGridView1
                .DataSource = BS
            End With
        End Sub
        Private Sub DataGridView1_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End Sub
    
        ''Sum by iteration
        Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
            If DataGridView1.CurrentCell.OwningColumn.Name = "Select" Then
                Dim Total As Double = 0
                For Each DRV As DataRowView In BS
                    If Convert.ToBoolean(DRV("Select")) Then
                        Total += Convert.ToDouble(DRV("ColToSum"))
                    End If
                Next
                TextBoxTotal.Text = Total.ToString
                BS.EndEdit()
            End If
        End Sub
    
        ''Sum By Compute
        Private Sub ComputeTotal() Handles BS.ListChanged
            Dim TotalFromCompute As Object = Nothing
            TotalFromCompute = DT.Compute("Sum(ColToSum)", "Select=True")
            TextBoxTotal.Text = TotalFromCompute.ToString
        End Sub
    End Class


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, August 3, 2018 12:54 PM
  • Ok Karen but I have the following problem:

    I have to compute Grand Total for numeric column in the DGV. 

    I have a checkbox for every row in the DGV and I can select multi row using shift key.

    If I handle Bs.Listchange Event in this way

     Private Sub BS_ListChanged(sender As Object, e As ListChangedEventArgs) Handles BS.ListChanged
            ComputeTotal()
            ShowTotal()
        End Sub

    the sub ComputeTotal is called for every row I checked and this slowdown the display.

    How can I avoid this by handlig event just once ?

     

     

    The ListChangedEventArgs has a ListChangedType property.  I haven't verified this, but I suspect that a filter operation will be marked as a Reset since the entire list significantly changes.  Just checking an item should be an item or property descriptor change.  So I would try the following to see if it addresses the issue before moving on to a different solution:

    Private Sub BindingSource1_ListChanged(sender As Object, e As ListChangedEventArgs) Handles BindingSource1.ListChanged
        If e.ListChangedType = ListChangedType.Reset Then
            ComputeTotal()
            ShowTotal()
        End If
    End Sub
    


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by Claudio111 Friday, August 3, 2018 4:06 PM
    Friday, August 3, 2018 1:56 PM
    Moderator
  • Thank you Gtripodi

    even if it  does'n solve my problem I appreciate the suggestion to loop thru BS

    Friday, August 3, 2018 3:52 PM
  • Hi Reed

    It seems to work. The ComputeTotal Sub is called just once

    ThankYou

    Friday, August 3, 2018 3:53 PM
  • hello, 

    these are another events related to DataGridView data bindings : 

    Friday, August 3, 2018 4:07 PM
  • Can you be more clear please 

    Thanks Cherkaoui

    Friday, August 3, 2018 4:26 PM
  • Hi Karen

    I had a look to your example in the Sub _RowChanged that handle also the row change in DataTable

    But even there the sub UpDateTotal is called for every cell in the row not just once

    So you run UpDateTotal too many times

    Friday, August 3, 2018 4:40 PM
  • you are Welcome, 

    thanks.

    Friday, August 3, 2018 5:30 PM
  • Thank you Gtripodi

    even if it  does'n solve my problem I appreciate the suggestion to loop thru BS

    Can you show me your "ComputeTotal" Sub. I feel like youre making this more difficult than it should be. 

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, August 3, 2018 5:43 PM