none
How to get the Columns total in the last rows (in the Footer) of Datagridview ? RRS feed

  • Question

  • Hi, 

    I am using a ms Access databble .

    I want to get the total of datagridview showing in the last line, 

    I try with this code  but I don't get The last Row.

    This Is the code :

    Public Class MainForm
        Public Opst As New OperationsTableauDeBord

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load ShowDataALLPontes1() End Sub


    DataTale:

     Public Function DataLesPontes1() As DataTable
    
    
            Dim selectStatement As String =
                    <SQL>
    
                        SELECT         
                                        LesPontes.CID, LesPontes.Pontes,
                                        LesPontes.Cage, LesPontes.Race, 
                                        dbFécondation.ŒufsFécondés,dbEclosion.ŒufsEclos,
                                        dbMortalité.MortEnNids, dbMortalité.OiseauxSevrés                                  
                                        FROM dbMortalité RIGHT JOIN 
                                        (dbSevrages RIGHT JOIN 
                                        (dbEclosion RIGHT JOIN 
                                        (dbFécondation RIGHT JOIN LesPontes 
                                         ON dbFécondation.CoupleID = LesPontes.CoupleID) 
                                         ON dbEclosion.CoupleID = LesPontes.CoupleID) 
                                         ON dbSevrages.CoupleID = LesPontes.CoupleID) 
                                         ON dbMortalité.CoupleID = LesPontes.CoupleID  order by lesPontes.CID;
    
                    </SQL>.Value
    
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = selectStatement
    
                    Dim dt As New DataTable
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    Return dt
    
                End Using
            End Using
        End Function

    The code:

      Private Sub ShowDataALLPontes1()
            Dgw.DataSource = Opst.DataLesPontes1()
        
            Dim Sum As Integer = 0
            Dim Sum1 As Integer = 0
            Dim Sum2 As Integer = 0
            Dim Sum3 As Integer = 0
            For i As Integer = 0 To Dgw.Rows.Count - 1
    
                Sum += If(Not Convert.IsDBNull(Dgw.Rows(i).Cells(4).Value), Convert.ToInt32(Dgw.Rows(i).Cells(4).Value), 0)
                Sum1 += If(Not Convert.IsDBNull(Dgw.Rows(i).Cells(5).Value), Convert.ToInt32(Dgw.Rows(i).Cells(5).Value), 0)
                Sum2 += If(Not Convert.IsDBNull(Dgw.Rows(i).Cells(6).Value), Convert.ToInt32(Dgw.Rows(i).Cells(6).Value), 0)
                Sum3 += If(Not Convert.IsDBNull(Dgw.Rows(i).Cells(7).Value), Convert.ToInt32(Dgw.Rows(i).Cells(7).Value), 0)
            Next
            Dgw("ŒufsFécondés", Dgw.Rows.Count - 1).Value = Sum
            Dgw("ŒufsEclos", Dgw.Rows.Count - 1).Value = Sum1
            Dgw("MortEnNids", Dgw.Rows.Count - 1).Value = Sum2
            Dgw("OiseauxSevrés", Dgw.Rows.Count - 1).Value = Sum3
            Dgw("Race", Dgw.Rows.Count - 1).Value = "TOTAL"
            Dgw.Rows(Dgw.Rows.Count - 1).DefaultCellStyle.BackColor = Color.YellowGreen
            Dgw.Rows(Dgw.Rows.Count - 1).DefaultCellStyle.ForeColor = Color.Black
            Dgw.Rows(Dgw.Rows.Count - 1).DefaultCellStyle.Font = New Font("Segoe UI", 9, (FontStyle.Bold))
        End Sub

    shotThis Is a  screenshot:

    So Thank you Very Much and Best Regards.



    • Edited by Bajtitou Wednesday, May 15, 2019 9:43 AM
    Wednesday, May 15, 2019 9:29 AM

Answers

  • I would not add the row via the DataGridView but instead via the DataSource so rather than setting the DataGridView DataSource to Opst.DataLesPontes1() do Dim dt = Opst.DataLesPontes1() then work off dt, then do a dt.Rows.Add to add the sum values etc followed by setting dt to the DataSource of the DataGridView.

    Caveat, all of the above is dependent on if the DataGridView is never sorted which appears via the code presented it's not sorted.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Bajtitou Wednesday, May 15, 2019 1:06 PM
    Wednesday, May 15, 2019 10:55 AM
    Moderator
  • Hi,
    change the order of the program lines:

        Private Sub ShowDataALLPontes1()
            Dim dt = Opst.DataLesPontes1()
            Me.dt.DataSource = dt
         
            Dim Sum As Integer = 0
            Dim Sum1 As Integer = 0
            Dim Sum2 As Integer = 0
            Dim Sum3 As Integer = 0
            For i As Integer = 0 To dt.Rows.Count - 1
    
                Sum += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(4).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(4).Value), 0)
                Sum1 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(5).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(5).Value), 0)
                Sum2 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(6).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(6).Value), 0)
                Sum3 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(7).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(7).Value), 0)
            Next
    
            Dim dr As DataRow = dt.NewRow
            dt.Rows.Add(dr)
            Me.dt.DataSource = dt
            Me.dt("Race", Me.dt.Rows.Count - 1).Value = "TOTAL"
    
            Me.dt("ŒufsFécondés", Me.dt.Rows.Count - 1).Value = Sum
            Me.dt("ŒufsEclos", Me.dt.Rows.Count - 1).Value = Sum1
            Me.dt("MortEnNids", Me.dt.Rows.Count - 1).Value = Sum2
            Me.dt("OiseauxSevrés", Me.dt.Rows.Count - 1).Value = Sum3
    
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.BackColor = Color.YellowGreen
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.ForeColor = Color.Black
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.Font = New Font("Segoe UI", 9, (FontStyle.Bold))
        End Sub


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    • Edited by Peter Fleischer Wednesday, May 15, 2019 12:08 PM
    • Marked as answer by Bajtitou Wednesday, May 15, 2019 1:06 PM
    Wednesday, May 15, 2019 12:08 PM

All replies

  • Can you be more specific e.g. if it didn't get the last row, what row did it get or something else because I can't tell from the code presented.

    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 15, 2019 9:47 AM
    Moderator
  • I would not add the row via the DataGridView but instead via the DataSource so rather than setting the DataGridView DataSource to Opst.DataLesPontes1() do Dim dt = Opst.DataLesPontes1() then work off dt, then do a dt.Rows.Add to add the sum values etc followed by setting dt to the DataSource of the DataGridView.

    Caveat, all of the above is dependent on if the DataGridView is never sorted which appears via the code presented it's not sorted.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Bajtitou Wednesday, May 15, 2019 1:06 PM
    Wednesday, May 15, 2019 10:55 AM
    Moderator
  • I would not add the row via the DataGridView but instead via the DataSource so rather than setting the DataGridView DataSource to Opst.DataLesPontes1() do Dim dt = Opst.DataLesPontes1() then work off dt, then do a dt.Rows.Add to add the sum values etc followed by setting dt to the DataSource of the DataGridView.

    Caveat, all of the above is dependent on if the DataGridView is never sorted which appears via the code presented it's not sorted.


    Hi,

    Thank you Karen for your Quick answer,

    I tried this code to add the Total line in the footer of Dgv 

    But the Sum stay obove in the line 41 , so how to remedy.

        Private Sub ShowDataALLPontes1()
            Dim dt = Opst.DataLesPontes1()
            Me.dt.DataSource = dt
         
            Dim Sum As Integer = 0
            Dim Sum1 As Integer = 0
            Dim Sum2 As Integer = 0
            Dim Sum3 As Integer = 0
            For i As Integer = 0 To dt.Rows.Count - 1
    
                Sum += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(4).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(4).Value), 0)
                Sum1 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(5).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(5).Value), 0)
                Sum2 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(6).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(6).Value), 0)
                Sum3 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(7).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(7).Value), 0)
            Next
            Me.dt("ŒufsFécondés", Me.dt.Rows.Count - 1).Value = Sum
            Me.dt("ŒufsEclos", Me.dt.Rows.Count - 1).Value = Sum1
            Me.dt("MortEnNids", Me.dt.Rows.Count - 1).Value = Sum2
            Me.dt("OiseauxSevrés", Me.dt.Rows.Count - 1).Value = Sum3
            Dim dr As DataRow = dt.NewRow
            dt.Rows.Add(dr)
            Me.dt.DataSource = dt
            Me.dt("Race", Me.dt.Rows.Count - 1).Value = "TOTAL"
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.BackColor = Color.YellowGreen
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.ForeColor = Color.Black
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.Font = New Font("Segoe UI", 9, (FontStyle.Bold))
        End Sub

    this is the Shotscreen.

    shot

    Thank you very Much and best Regards.


    • Edited by Bajtitou Wednesday, May 15, 2019 11:44 AM
    Wednesday, May 15, 2019 11:41 AM
  • Hi,
    change the order of the program lines:

        Private Sub ShowDataALLPontes1()
            Dim dt = Opst.DataLesPontes1()
            Me.dt.DataSource = dt
         
            Dim Sum As Integer = 0
            Dim Sum1 As Integer = 0
            Dim Sum2 As Integer = 0
            Dim Sum3 As Integer = 0
            For i As Integer = 0 To dt.Rows.Count - 1
    
                Sum += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(4).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(4).Value), 0)
                Sum1 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(5).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(5).Value), 0)
                Sum2 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(6).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(6).Value), 0)
                Sum3 += If(Not Convert.IsDBNull(Me.dt.Rows(i).Cells(7).Value), Convert.ToInt32(Me.dt.Rows(i).Cells(7).Value), 0)
            Next
    
            Dim dr As DataRow = dt.NewRow
            dt.Rows.Add(dr)
            Me.dt.DataSource = dt
            Me.dt("Race", Me.dt.Rows.Count - 1).Value = "TOTAL"
    
            Me.dt("ŒufsFécondés", Me.dt.Rows.Count - 1).Value = Sum
            Me.dt("ŒufsEclos", Me.dt.Rows.Count - 1).Value = Sum1
            Me.dt("MortEnNids", Me.dt.Rows.Count - 1).Value = Sum2
            Me.dt("OiseauxSevrés", Me.dt.Rows.Count - 1).Value = Sum3
    
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.BackColor = Color.YellowGreen
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.ForeColor = Color.Black
            Me.dt.Rows(Me.dt.Rows.Count - 1).DefaultCellStyle.Font = New Font("Segoe UI", 9, (FontStyle.Bold))
        End Sub


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks


    • Edited by Peter Fleischer Wednesday, May 15, 2019 12:08 PM
    • Marked as answer by Bajtitou Wednesday, May 15, 2019 1:06 PM
    Wednesday, May 15, 2019 12:08 PM