How to Filter Data in the DataGridview Using CheckBoxes in VB.Net

Submitted by janobe on Sunday, September 22, 2019 - 10:02.
In this tutorial, I will teach you how to filter the data in the datagridview using checkboxes in vb.net. This method has the ability to retrieve the data in the database and It will be displayed in the datagridview depending on the checked in a group of checkboxes. This procedure is very helpful if you are currently working on the inventory of products.

Creating a Database

Go to http://localhost/phpmyadmin/ and create a new database named "tuts_persondb". after that execute the query for creating a table and adding the data in the table.
  1. CREATE TABLE `tblperson` (
  2.   `PersonID` int(11) NOT NULL,
  3.   `Fullname` varchar(90) NOT NULL,
  4.   `CivilStatus` varchar(30) NOT NULL
  6.  
  7. --
  8. -- Dumping data for table `tblperson`
  9. --
  10.  
  11. INSERT INTO `tblperson` (`PersonID`, `Fullname`, `CivilStatus`) VALUES
  12. (1, 'Janobe Sourcecode', 'Single'),
  13. (2, 'Mark Lim', 'Married'),
  14. (3, 'Jake Cueca', 'Widow');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for visual basic. figure 1

Step 2

Do the form just like shown below. figure 2

Step 3

Add MySQL.Data.dll

Step 4

Press F7 to open the code editor. In the code editor, add a namespace to access MySQLlibraries
  1.  
  2. using MySql.Data.MySqlClient;

Step 5

Establish a connection between visual basic and MySQL database. After that, declare all the classes and variables that are needed.
  1.  
  2.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id =root;password=;database=tuts_persondb;sslMode=none")
  3.     Dim cmd As MySqlCommand
  4.     Dim da As MySqlDataAdapter
  5.     Dim dt As DataTable
  6.     Dim sql As String = ""
  7.     Dim stat_single As String = ""
  8.     Dim stat_married As String = ""
  9.     Dim stat_widow As String = ""

Step 6

Create a method for retrieving data in the database.
  1.  
  2.     Private Sub loadData(sql As String)
  3.         Try
  4.             con.Open()
  5.             cmd = New MySqlCommand
  6.             da = New MySqlDataAdapter
  7.             dt = New DataTable
  8.  
  9.             With cmd
  10.                 .Connection = con
  11.                 .CommandText = sql
  12.             End With
  13.  
  14.             da.SelectCommand = cmd
  15.             da.Fill(dt)
  16.  
  17.             DataGridView1.DataSource = dt
  18.  
  19.         Catch ex As Exception
  20.             MsgBox(ex.Message)
  21.         Finally
  22.             con.Close()
  23.             da.Dispose()
  24.         End Try
  25.     End Sub

Step 7

Write the following code to display the data into the DataGridView in the first load of the form.
  1.  
  2.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  3.         sql = "SELECT * FROM `tblperson`"
  4.         loadData(sql)
  5.     End Sub

Step 7

Write the following code for filtering the data in the DataGridView using the checkboxes when it's checked.
  1.  
  2.     Private Sub chkSingle_CheckedChanged(sender As Object, e As EventArgs) Handles chkSingle.CheckedChanged, chkMarried.CheckedChanged, chkWidow.CheckedChanged
  3.  
  4.  
  5.         If chkSingle.Checked Then
  6.             stat_single = chkSingle.Text
  7.         End If
  8.  
  9.         If chkMarried.Checked Then
  10.             stat_married = chkMarried.Text
  11.         End If
  12.  
  13.         If chkWidow.Checked Then
  14.             stat_widow = chkWidow.Text
  15.         End If
  16.  
  17.  
  18.         sql = "SELECT * FROM `tblperson` WHERE `CivilStatus` in ('" & stat_single & "','" & stat_married & "','" & stat_widow & "')"
  19.         loadData(sql)
  20.  
  21.     End Sub
The complete source code is included you can download it and run it on your computer. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.
Tags
filtering data using checkbox in vb
vb
vb.net
MySQL Database
retrieving data in the database in vb