ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

How to Display and Count the Total Value of the Records in the DataGridView Using Visual Basic 2008

In this tutorial I will show you how to display and sum up the total values of the records in the DataGridView using Visual Basic 2008. The records that are displayed are items that has its price, then, the over all price 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.

displaycalculateoverall_first_form1

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

The complete sourcecode is included. Download it and run it on your computer.
DOWNLOAD HERE

Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!