Displaying and Counting the Total Value of the Records in the DataGridView

Submitted by janobe on Wednesday, January 22, 2014 - 10:03.
In this tutorial I will show you how to display and sum up the total values of the records in the DataGridView. The records that are displayed are items that has its price, then, the total value will be displayed in the last row of the DataGridView . I’m using MySQL Database as my database and Visual Basic 2008. The features of this is to calculate the salaries of all employees Let’s begin: First, create a database named “payroll”.
  1. CREATE DATABASE `payroll` ;
Second, create a table named “employees”.
  1. CREATE TABLE IF NOT EXISTS `employees` (
  2.   `EMPLOYEE_ID` int(11) NOT NULL,
  3.   `FIRST_NAME` varchar(255) DEFAULT NULL,
  4.   `LAST_NAME` varchar(255) DEFAULT NULL,
  5.    `SALARY` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`EMPLOYEE_ID`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Third, insert all data in the database.
  1. INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `SALARY`) VALUES
  2. (102, 'lex', 'De Haan',   17000),
  3. (103, 'alexander', 'Hunold', 9000),
  4. (104, 'Bruce', 'Ernst', 6000),
  5. (107, 'Diana', 'Lorents',4200),
  6. (124, 'Kevin', 'Mourgos', 5800),
  7. (141, 'Trenne', 'Rajs', 3500),
  8. (142, 'Curtis', 'Davies', 3100),
  9. (143, 'Randal', 'Matos', 2600),
  10. (144, 'Peter', 'Vargas',  2500),
  11. (149, 'Ellen', 'Zlotkey', 10500),
  12. (174, 'Jonathan', 'Abel', 11000),
  13. (176, 'Kimberly', 'Taylor', 8600),
  14. (178, 'Jinnefer', 'Grant',  7000),
  15. (200, 'Michael', 'Whalen', 4400),
  16. (201, 'Pat', 'Hartstein',  13000),
  17. (205, 'Shelley', 'Fay',  6000),
  18. (206, 'William', 'Higgins', 12000),
  19. (207, 'hatch', 'Glets', 8300);
Open Visual Basic 2008, create a Project and set up you Form just like this. First Form Double click the Form. Set up your connection and declare all the classes that you needed above the Form1_Load.
  1. 'reference
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4.  
  5.     'setting up the string connection of MySQL Database
  6.     Dim con As MySqlConnection = _
  7. New MySqlConnection("server=localhost;user id=root;database=payroll")
  8.     'a set of command in MySQL
  9.     Dim cmd As New MySqlCommand
  10.     'Bridge between a database and the datatable for retrieving and saving data.
  11.     Dim da As New MySqlDataAdapter
  12.     'a specific table in the database
  13.     Dim dt As New DataTable
Go back the Design Views and double click the “Load” Button. After that, do these codes in the display_Click.
  1. Private Sub display_Click _
  2. (ByVal sender As System.Object, ByVal e As System.EventArgs) _
  3.  Handles Button1.Click
  4.         'for displaying records in the datagridview from the database
  5.         Try
  6.             'openning connection
  7.             con.Open()
  8.  
  9.             'set a new spicific table in the database
  10.             dt = New DataTable
  11.             'set your commands for holding the data.
  12.             With cmd
  13.                 .Connection = con
  14.                 .CommandText = "Select * from employees"
  15.             End With
  16.             'filling the table in the database
  17.             da = New MySqlDataAdapter("Select * from employees", con)
  18.             da.Fill(dt)
  19.             'getting the datasource that will display on the datagridview
  20.             DataGridView1.DataSource = dt
  21.             'declaring variable as integer to store the value of the total rows in the datagridview
  22.             Dim max As Integer = DataGridView1.Rows.Count - 1
  23.             'putting the string value in the last row of the datagridview
  24.             DataGridView1.Rows(max).Cells(2).Value = "Total"
  25.             'putting the value which is 0 in the last row of the datagridview
  26.             'this is the first value that appear to the last row in the column 3
  27.             'this is for avoiding error when you begin calculating
  28.             DataGridView1.Rows(max).Cells(3).Value = 0
  29.         Catch ex As Exception
  30.             MsgBox(ex.Message)
  31.         End Try
  32.         'closing connection
  33.         con.Close()
  34.     End Sub
Go back the Design Views and double click the “Calculate” Button. After that, do these codes in the Calculate_Click. This is for calculating all the value in the rows.
  1. Private Sub Calculate_Click _
  2.     (ByVal sender As System.Object, ByVal e As System.EventArgs) _
  3.     Handles Button2.Click
  4.         Try
  5.             'declaring variable as integer to store the value of the total rows in the datagridview
  6.             Dim max As Integer = DataGridView1.Rows.Count - 1
  7.             'getting the values of a specific rows
  8.             For Each row As DataGridViewRow In DataGridView1.Rows
  9.                 'formula for adding the values in the rows
  10.                 DataGridView1.Rows(max).Cells(3).Value += row.Cells(3).Value
  11.             Next
  12.         Catch ex As Exception
  13.             MsgBox(ex.Message)
  14.         End Try
  15.     End Sub
Tags
Calculate
datagridview
MYSQL
database
visual basic

Add new comment

About text formats
  • 459 views