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

Loading…

localhost

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
   
   
   
5. )
   
   ENGINE
   
   =
   
   InnoDB
   
   DEFAULT
   
   CHARSET
   
   =
   
   latin1;
   
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.

Step 2

Do the form just like shown below.

Step 3

Add MySQL.Data.dll
Step 4

Press F7 to open the code editor. In the code editor, add a namespace to access MySQL

libraries

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.

Download