How to use Count,Calculate sum(), Caluculate Max() in VB.NET?

Question

• Hi

In VFP9 we can use the followings and How to do it with vb.net?

COUNT FOR(wk_code='Inj ') TO abcd5
CALCULATE SUM(total_hr) FOR (emp_id=epidi1) TO tot1
CALCULATE MAX(en_date) FOR((emp_id=epidi1) TO etydt

--------------------------------------------
In Vb.Net

Dim dbnd1 As New BindingSource
dbnd1.DataSource = DTB4 'Datatable for SEC-ID's
?????????

U.PARANTHAMAN
Tuesday, March 31, 2009 7:38 AM

• In addtion, you can achieve the equivalent calculation via DataTable.Compute method.

```Imports System.Data.OleDb

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Firstly fill data from database table into a DataTable object
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Username FROM UserTable", con)
con.Open()
Dim myDataTable As DataTable = New DataTable()
myDA.Fill(myDataTable)
con.Close()
con = Nothing
'Then you can use myDataTable.Compute() to do something
End Sub

End Class```

A DataTable has a Compute() method to derive an aggregate value for a column expressed in the following format.

 DataTable.Compute("AggregateFunction(DataColumn)", "condition|Nothing|null")

The Compute() method is passed two arguments in a comma-separated list of string values. The first argument is the name of an AggregateFunction() which has the name of a DataColumn included within paretheses; this is the DataSet column to which the aggregate function is applied. The second argument restricts the DataRows that are accessed. If all rows of the column are used in the function, there are no restrictions, so a null value (or the keyword Nothing) is passed. Otherwise, a condition is supplied to identify which rows are selected.

Available aggregate functions include those shown in the following table.

 Avg() The average of values in a column Count() The number of rows (values) in a column Max() The largest value in a column Min() The smallest value in a column StDev() The standard deviation of values in a column Sum() The sum of values in a column Var() The statistical variance of values in a column

DataTable.Compute method http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
• Marked as answer by Wednesday, April 01, 2009 12:01 PM
Wednesday, April 01, 2009 11:08 AM

All replies

• Hi Paramu,

In VB.NET, you can execute T-SQL statement as below code sample to achieve the equivalent features.

Prerequisites: Drag&drop Button1, Button2 and Button3 onto Form1.

```Imports System.Data.OleDb

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT COUNT(wk_code) FROM UserTable Where wk_code='Inj'", con)
con.Open()
Dim abcd5 As Int32 = cmd.ExecuteScalar
MessageBox.Show(abcd5)
con.Close()
con = Nothing
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT SUM(total_hr) FROM UserTable Where emp_id=epidi1", con)
con.Open()
Dim tot1 As Int32 = cmd.ExecuteScalar
MessageBox.Show(tot1)
con.Close()
con = Nothing
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=E:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT MAX(en_date) FROM UserTable Where emp_id=epidi1", con)
con.Open()
Dim etydt As Int32 = cmd.ExecuteScalar
MessageBox.Show(etydt)
con.Close()
con = Nothing
End Sub

End Class```

COMPUTE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms181708(sql.90).aspx

{ AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM }

Best regards,
Martin Xie
Wednesday, April 01, 2009 10:54 AM
• In addtion, you can achieve the equivalent calculation via DataTable.Compute method.

```Imports System.Data.OleDb

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Firstly fill data from database table into a DataTable object
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
Dim cmd As OleDbCommand = New OleDbCommand("SELECT Username FROM UserTable", con)
con.Open()
Dim myDataTable As DataTable = New DataTable()
myDA.Fill(myDataTable)
con.Close()
con = Nothing
'Then you can use myDataTable.Compute() to do something
End Sub

End Class```

A DataTable has a Compute() method to derive an aggregate value for a column expressed in the following format.

 DataTable.Compute("AggregateFunction(DataColumn)", "condition|Nothing|null")

The Compute() method is passed two arguments in a comma-separated list of string values. The first argument is the name of an AggregateFunction() which has the name of a DataColumn included within paretheses; this is the DataSet column to which the aggregate function is applied. The second argument restricts the DataRows that are accessed. If all rows of the column are used in the function, there are no restrictions, so a null value (or the keyword Nothing) is passed. Otherwise, a condition is supplied to identify which rows are selected.

Available aggregate functions include those shown in the following table.

 Avg() The average of values in a column Count() The number of rows (values) in a column Max() The largest value in a column Min() The smallest value in a column StDev() The standard deviation of values in a column Sum() The sum of values in a column Var() The statistical variance of values in a column

DataTable.Compute method http://msdn.microsoft.com/en-us/library/system.data.datatable.compute.aspx

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
• Marked as answer by Wednesday, April 01, 2009 12:01 PM
Wednesday, April 01, 2009 11:08 AM
• MARTIN ....YOU ARE GIVING WONDERFUL SERVICES.!!!!!
THANKS TO THOSE KINDNESS...!!!!!!!

U.PARANTHAMAN
Wednesday, April 01, 2009 12:01 PM
• Hi Paramu,

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Thursday, April 02, 2009 4:51 AM
• Martin,
Excellent advice on the DataTable.Compute method.  I have a further question.  How would you count for two columns and a condition?
Friday, May 15, 2009 7:49 PM
• Gary,

In the way you ask this simple as

\\\
If myCondition then
Dim result = MyTable.Rows(0).items(0) + MyTable.Rows(0).Items(1)
end if
///
This counts the first column and second column in the first row.

However, I have the idea that you mean something different.

Cor

Sunday, May 17, 2009 10:01 AM
• I have two columns: category_name and Weekday_name.  I want to count how many category "A" for weekday "Monday" there are in the table.  The datatable.compute method Martin gave only shows counting for one column and one condition.  Will this method allow for more than one column and more than one condition?  What is the syntax?
Thanks.

Tuesday, May 19, 2009 3:58 PM
• Did you get my response and question?  It has been a couple of days, so I'm just checking.
Gary
Friday, May 22, 2009 2:06 PM
• I have two columns: category_name and Weekday_name.  I want to count how many category "A" for weekday "Monday" there are in the table

Hi Gary,

I think this code snippet is what you want. Please give it a try.

Dim countObject As Object = myDataTable.Compute("Count(category)", "category = 'A' And weekday = 'Monday'")

MessageBox.Show(countObject.ToString)

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Send us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.
Monday, May 25, 2009 4:37 AM
• Martin,
That worked.  Thanks for your great help.
Gary
Tuesday, May 26, 2009 2:36 PM
• Martin,
When using the DataTable.Compute(Sum) method, I get "Conversion from type 'DBNull' to type 'Integer' is not valid" error when the column is empty.  How do I fix this?
Thanks again.
Tuesday, June 02, 2009 8:21 PM
• Hi Gary,

myDataTable.Compute(
"Sum(QualityColumn)", "")
myDataTable.Compute(
"Sum(QualityColumn)", "QualityColumn IsNot Nothing")

2. When retrieve data from database, you can consider avoiding/filtering the Null values via Where clause such as "Select * from Table1 Where QualityColumn is not Null", then fill data into DataTable.

3. Check this similar issue for reference.

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
Thursday, June 04, 2009 11:21 AM
• Martin,
I have tried every combination of the Null advice you gave above, but can't get the DBNull error to stop.  Here is a snippet of my code.  Maybe you can help?

CountSun = db.Compute("Sum(WDTotal)", "WDName='Sunday'")

When there is no Sunday in the column, I get the DBNull error.

Thanks again.
Gary
Friday, June 05, 2009 4:40 PM
• Gary, for your case, you have a problem with your field type . [CountSun or WDTotal or WDName]

----------------------------------------------------------------------------
But Iam using {Fields are strings & numerals}

Dim
countObject As Object = DTB31.Compute("count(itm_description)", "itm_description='" & chkitm & "' and unit_measure='" & uom & "'")

----------------------------------------------------
Try with the following
-----------------------------------------------------

Enum enumObjectType

StrType = 0

IntType = 1

DblType = 2

End Enum

Public Function CheckDBNull(ByVal obj As Object, _

Optional ByVal ObjectType As enumObjectType = enumObjectType.StrType) As Object

Dim objReturn As Object

objReturn = obj

If ObjectType = enumObjectType.StrType And IsDBNull(obj) Then

objReturn = ""

ElseIf ObjectType = enumObjectType.IntType And IsDBNull(obj) Then

objReturn = 0

ElseIf ObjectType = enumObjectType.DblType And IsDBNull(obj) Then

objReturn = 0.0

End If

Return objReturn

-------------------------------------------------------------------------------------------

U.PARANTHAMAN

End Function

Saturday, June 06, 2009 9:44 AM
• Paramu,
Can the Dim CountObject As Object line be used alone to fix problem, or does the Public Function have to go along with it?

In the Dim CountObject can you further explain what "chkitm", "unit_measure" and "uom"?  I don't see how those tie in with my variables.

How does your Public Function tie in with my variables?
Gary

Sunday, June 07, 2009 3:38 AM
• Gary, it seems to be,

1. Try with

Dim CountSun as object= db.Compute("Sum(WDTotal)", "WDName='Sunday'") [must solve your problem]

2. Check "WDTotal" is string or numerals & "WDName" also string or numerals...

3. Suppose if all the above are perfect, then you have to check for your date field null values
If your date is null, you cannot convert the name of the day..is it not?

U.PARANTHAMAN
Sunday, June 07, 2009 5:46 AM
• Paramu,

After implementing your statement on my variables, I get the below error on my next statement:
Dim AvgSun as object = CountSun / CountWDTot

error:
Operator '/' is not defined for type 'DBNull' and type 'Long'.

Somehow, I need to filter out all DBNull.

Gary
Sunday, June 07, 2009 6:19 PM
• Martin,

I have a hw on a DataSet application, it is required to (1) use DataReader to populate two types of data into the ComboBox on the interface.  then (2) create a DataSet that contains all teh records retrieved from the voting record data base table.  (3) use the Binding Navigator control to browse and modify records in the DataSet.

the last question is to use DataTable.Compute() method to calculate an aggregate funtion against the records in the DataTable.  I created a TextBox control at the bottom of the Form.  The TextBox is used to displau the result of the function whenever the First ComboBox (which is contained the populated data in the (1)  )  The Scenario is about the voting records, the First ComboBox contains the BillNumber, second ComboBox contains CongressPerson ID, third ComboBox is for vote (Yes/No).  Then then TextBox.

if there are mor Yes votes than Not votes the status (showed in TextBox) should be "Pass" otherwise should be "FAil".

I've have completed all those steps then got stuck on the DataTable.  I dont know whether to put the codes on Form Load or the First ComboBox_SelectedIndexChanged, if so then what's next? Do I need to do like above as you said?  Please give me some help.  Thank you.

Sunday, April 10, 2011 1:29 PM
• Hi Martin,

I am novice in VB.net and I am trying to make a windows.exe program using Visual Express 2008.

I am making my own 10 pin bowling program, My problem at the moment is how to calculate the sum of a several fields and put it on another field.

I have several tables, I am picking up values from the table named - Games.

Table: Games

Fields:

GameDate

BowlerID

TeamID

Games1

Games2

Games3

TotalPlayed

How can I sum all the scores in Games1 or Games2 or Games3 where TeamID ="1" or ... and GameDate="24/01/2012"?

Your help is very much appreciated.

Thursday, March 01, 2012 6:38 AM
• I suggest you ask this in a new thread in the VB General forum:

Armin

Thursday, March 01, 2012 1:07 PM