none
Help about how to get sum of the column RRS feed

  • Question

  • Good day,everyone can I ask how to get the sum of the column. I started learning few days ago in VB.net since VB 6.0 is not supported by Microsoft nowadays. 

    Here is the sample program I make..

    Dim ctr, i As Integer

                'cn.Open()
                str = "select * from tblarecord where empid = '" & TextBox1.Text & "' ORDER BY empid ASC"
                cmd = New OleDbCommand(str, cn)
                da.SelectCommand = cmd
                da.Fill(ds, "tblarecord")
                ctr = ds.Tables("tblarecord").Rows.Count - 1
            For i = 0 To ctr

                DataGridView1.Rows.Add(ds.Tables("tblarecord").Rows(i)(0).ToString, ds.Tables("tblarecord").Rows(i)(1).ToString, ds.Tables("tblarecord").Rows(i)(2).ToString, ds.Tables("tblarecord").Rows(i)(3).ToString, ds.Tables("tblarecord").Rows(i)(4).ToString, ds.Tables("tblarecord").Rows(i)(5).ToString, ds.Tables("tblarecord").Rows(i)(6).ToString, ds.Tables("tblarecord").Rows(i)(7).ToString)

            Next
            

            For sum = 0 To DataGridView1.Rows.Count() - 1 Step +1

                TextBox2.Text = sum + DataGridView1.Rows(i).Cells(0).Value



            Next

    TextBox2.Text is my problem coz the value he get is the rows. So I want to get the column of data DataGrid.

    if ID number search, dataGridView display the how many absences of the student and display it to TextBox2.Text.

    I hope all can help me about it.Its project porpuses.

    Thanks in advance

    Clynnekent

     
    Tuesday, January 21, 2020 2:00 AM

All replies

  • Hi

    A couple of ideas.

    1. Bind the datatable to thre DataGridfView - then you don't need the very tedious long row add code

    2. When bound, then always do calculations and data manipulation on the underlying data - the DataTable.

    3. Using a DataTable enables some useful functions (like SUM of columns)*

    This code is a stand alone example. Just needs a blank DataGridView1 and 4 TextBoxes (2,2,4 and 5) in the Designer.

    It simulates incoming data from a DB by manually adding the test data - yours would come from the DB.

    There is a Function to get a valid numeric value (Integert) from String values (all DataGridView are Strings).

    *

    The example code uses Columns addressed by name rather than by Index - makes for better readability.

    ' Form1 with DataGridView1,
    ' TextBoxes 2,3,4 and 5
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim tblarecord As New DataTable("MyRable")
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
    		'===============================
    		' simulate the incoming data from DB
    		With tblarecord
    			.Columns.Add("Col0", GetType(String))
    			.Columns.Add("Col1", GetType(String))
    			.Columns.Add("Col2", GetType(String))
    			.Columns.Add("Col3", GetType(Integer))
    			.Columns.Add("Col4", GetType(Double))
    			.Columns.Add("Col5", GetType(Decimal))
    			.Columns.Add("Col6", GetType(String))
    			.Columns.Add("Col7", GetType(String))
    
    			' simulate data - dummy items
    			' adding 10 rows
    			For i As Integer = 10 To 19
    				.Rows.Add(i.ToString, (i * 2).ToString, (i / 3).ToString, i, i / 3, i * 5, (i / 3).ToString, (i * 3).ToString)
    			Next
    		End With
    		' now we have 10 rows of dummy data
    		'===============================
    
    		' assign the datatable as the data
    		' source for DataGridView1
    		DataGridView1.DataSource = tblarecord
    
    		' get SUM of col0 (which are String type)
    		Dim sum As Integer = 0
    		For Each r As DataRow In tblarecord.Rows
    			sum += GetIntegerFromString(r.Item("Col0").ToString)
    		Next
    		TextBox2.Text = sum.ToString
    
    		' get SUM of numeric columns 3,4 and 5
    		TextBox3.Text = tblarecord.Compute("Sum(Col3)", Nothing).ToString
    		TextBox4.Text = tblarecord.Compute("Sum(Col4)", Nothing).ToString
    		TextBox5.Text = tblarecord.Compute("Sum(Col5)", Nothing).ToString
    
    	End Sub
    
    	' a Function to return a valid Integer
    	' from a String value (or zero if not
    	' a valid Integer)
    	Function GetIntegerFromString(s As String) As Integer
    		Dim v As Integer = 0
    
    		' if IS valid integer - Return value
    		If Integer.TryParse(s, v) Then Return v
    
    		' if NOT valid integer - Return zero
    		Return 0
    	End Function
    End Class


    Regards Les, Livingston, Scotland

    Tuesday, January 21, 2020 12:17 PM
  • Despite what some has written on Internet in 2002, was VB6 always a clumsy way of programming. 

    You could not bind data. Therefore the flexgrid was populated in the way you did. 

    Look at the code from Les, 

    Don't misunderstand it. His code could even been shorter as he had not created the database records in his code. 

    Therefore don't follow those persons who have made it years troublefull for others to advice to stay with VB6.

    In 2003 I saw a very dedicated VB6 programmer write. "I was critical of VB.Net but they would have to drag me by my hair crying if I had to work with that again". 


    Success
    Cor

    Tuesday, January 21, 2020 12:32 PM
  • Hello,

    The following shows how to sum DataColumn 

    https://www.aspsnippets.com/Articles/Calculate-Sum-Total-of-DataTable-Columns-using-C-and-VBNet.aspx

    To access the DataTable say in a button click event or whatever event you like use 

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)

    Now in real life a better solution would allow sum values to happen dynamically e.g. a value changes in the DataGridView which would then update the TextBox. This requires you to handle several events for a DataTable, for instance RowDeleted for when a row is removed, RowChanged, ColumnChanged e.g.

    (this is taken from a larger code sample I wrote a while back, bsData is a BindingSource which is set to a DataTable which has data read from a database table)

    Private Sub _ColumnChanged(sender As Object, e As DataColumnChangeEventArgs)
    	If Not e.Row.RowState = DataRowState.Deleted Then
    
    		If DataAccess.ColumnNames.Contains(e.Column.ColumnName) Then
    
    			If Not e.Row.RowState = DataRowState.Detached Then
    
    				If IsDBNull(e.Row.Item(e.Column.ColumnName)) Then
    					e.Row.Item(e.Column.ColumnName) = 0
    				End If
    
    				e.Row.AcceptChanges()
    
    			End If
    
    			UpDateTotal()
    
    		End If
    	End If
    End Sub
    Private Sub _RowChanged(sender As Object, e As DataRowChangeEventArgs)
    	If e.Action = DataRowAction.Add Or e.Action = DataRowAction.Change Or e.Action = DataRowAction.Commit Or e.Action = DataRowAction.Change Then
    		UpDateTotal()
    	End If
    End Sub
    Private Sub _RowDeleted(sender As Object, e As DataRowChangeEventArgs)
    	UpDateTotal()
    End Sub
    Private Sub UpDateTotal()
    
    	Dim dt As DataTable = CType(bsData.DataSource, DataTable)
    
    	If bsData.Position <> -1 Then
    
    		If Not CType(bsData.Current, DataRowView).Row.RowState = DataRowState.Detached Then
    			dt.Rows(bsData.Position).EndEdit()
    		End If
    
    	End If
    
    	Dim dv As New DataView() With {.Table = dt, .RowStateFilter = DataViewRowState.CurrentRows, .AllowDelete = False}
    
    	Try
    		lblTotalSale.Text = "Grand Total: " & (From T In dv.ToTable.AsEnumerable Select T.Field(Of Integer)(DataAccess.RowTotalFieldName)).Sum.ToString
    
    	Catch ex As Exception
    		'
    		' If you land here most likely there is a value for a month that is null
    		'
    		MessageBox.Show("UpDateTotal throw an exception: " & ex.Message)
    	End Try
    
    End Sub
    

    Add in some validation

    Sub DataGridView1_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) _
    	Handles DataGridView1.CellValidating
    
    	If DataGridView1.Item(e.ColumnIndex, e.RowIndex).IsInEditMode Then
    		If DataGridView1.Item(e.ColumnIndex, e.RowIndex).ValueType Is GetType(Int32) Then
    			Dim c As Control = DataGridView1.EditingControl
    			If Not Integer.TryParse(c.Text, Nothing) Then
    				MessageBox.Show("Must be numeric")
    				e.Cancel = True
    				DataGridView1.CancelEdit()
    			End If
    		End If
    	End If
    End Sub
    
    In the following screenshot I've done sub-totals and grand total, and yes the structure is different from your structure but the logic is sound.


    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

    Tuesday, January 21, 2020 12:59 PM
    Moderator
  • Sorry,I think my question that I wrote is wrong. So let me clarify it clearly. So I give a scenario like this.

    Scenario:

             This program is for Attendance monitoring of the employee.I inputed the data through DataGridView and save it in my MS Access Database. After saving the data I search the Employee Id to see how many absences of the employee. So my problem is I don't figure out what code I will create to get the total absences of the employee. I search many items through tutorial in internet but still not get the exact answer of my question. Hope anyone can help me.

    Thanks in advance

    Clynnekent

    Tuesday, January 21, 2020 1:40 PM
  • Hi

    In what way did the replies offering suggestions NOT help you? In particular, Karen gave a more comprehensive reply than I did, but both centered around using your DataTable for data calculations/manipulation - why do you appear to want to ignore this and work with your data directly in the DataGridView?

    Please delete the duplicated posts you have made.


    Regards Les, Livingston, Scotland

    Tuesday, January 21, 2020 2:01 PM
  • Sorry,I think my question that I wrote is wrong. So let me clarify it clearly. So I give a scenario like this.

    Scenario:

             This program is for Attendance monitoring of the employee.I inputed the data through DataGridView and save it in my MS Access Database. After saving the data I search the Employee Id to see how many absences of the employee. So my problem is I don't figure out what code I will create to get the total absences of the employee. I search many items through tutorial in internet but still not get the exact answer of my question. Hope anyone can help me.

    Thanks in advance

    Clynnekent

    Not sure why you are load data into a DataTable tblaRecord then looping through the rows to add DataGridView rows. Instead you should use DataGridView1.DataSource = ds.Tables("tblarecord") then you can when needed use 

    Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)

    To access the data and use code samples from here to perform sums.

    Now if there needs to be a condition on the sum you can use for-each or for-next to do assertion and summing or even with lambda e.g.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dt As DataTable = CType(DataGridView1.DataSource, DataTable)
        Dim totals = dt.AsEnumerable().
                Where(Function(dataRow) dataRow.Field(Of Boolean)("Absense") = true ).
                Select(Function(dataRow) dataRow.Field(Of Integer)("SomeField")).
                ToArray().
                Sum()
    End Sub

    Also, never, ever use string concatenation like this as you are simply waiting for an error to happen, instead use parameters. Never use SELECT *, instead spell out the column names as SELECT * leaves you hanging to which columns are asked for and also in some cases not all columns are needed thus the possibilities of slower loading of data.

    str = "select * from tblarecord where empid = '" & TextBox1.Text & "' ORDER BY empid ASC"


    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

    Tuesday, January 21, 2020 2:32 PM
    Moderator
  • sorry sir its my internet connection problem why my post duplicate. Still in process and trying to test the code that they posted.

    Thanks

    Wednesday, January 22, 2020 12:31 AM