Filtering a Group of Data in VB.Net and SQL Server 2019

Submitted by janobe on Saturday, November 16, 2019 - 21:58.

In this tutorial, I will teach you how to filter a group of data using VB.Net SQL Server 2019. This method has the ability to filter a group of data in the datagridview with the use of the radio buttons. You can select whether “Firstname”, “Lastname”, or “All” you want to display in the datagridview. I used Microsoft Visual Studio 2015 and SQL Server 2019 to develop this program. Let’s Begin:

Creating Database

1. Install the MSSMS 2019 on your machine. 2. Open the MSSMS 2019 . After that, right click the database, then select “New Database” and name it “dbperson” fig 1

3. Do the following query to create a table in the database that you have created

 
USE [dbperson]
GO
 
/****** Object:  Table [dbo].[tblperson]    Script Date: 11/10/2019 11:15:11 AM ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[tblperson](
	[PersonID] [INT] IDENTITY(1,1) NOT NULL,
	[Fname] [nvarchar](50) NULL,
	[Lname] [nvarchar](50) NULL
) ON [PRIMARY]
GO

Creating Application

Step 1

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

Step 2

Add three RadioButtons, a GroupBox, and a DataGridview inside the Form. Then do the Form just like shown below. fig 2

Step 3

Press F7 to open the code editor. In the code editor, add a namespace to access SQL Server libraries.

 
Imports System.Data.SqlClient

Step 4

Create a connection between Visual Basic 2015 and SQL Server database. After that, declare and initialize all the classes and variables that are needed.

 
    Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Database=dbperson;trusted_connection=true;")
    Dim cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim dt As DataTable
    Dim sql As String

Step 5

Create a method for displaying data in the database and display it in the datagridview.

 
    Private Sub displayData(sql As String, dtg As DataGridView)
        Try
            con.Open()
            cmd = New SqlCommand()
            da = New SqlDataAdapter
            dt = New DataTable
 
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            With da
                .SelectCommand = cmd
                .Fill(dt)
            End With
            dtg.DataSource = dt
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
    End Sub

Step 6

Write the following codes to retrieve the data in the datagridview in the first load of the form.

 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        sql = "SELECT Fname as Firstname, Lname as Lastname FROM tblperson"
        displayData(sql, DataGridView1)
    End Sub

Step 7

Write the following codes for the selection of data.

    'first name
    Private Sub RadioButton1_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton1.CheckedChanged
        sql = "SELECT Fname as Firstname FROM tblperson"
        displayData(sql, DataGridView1)
    End Sub
    'last name
    Private Sub RadioButton2_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton2.CheckedChanged
        sql = "SELECT Lname as Lastname FROM tblperson"
        displayData(sql, DataGridView1)
    End Sub
    'all
 
    Private Sub RadioButton3_CheckedChanged(sender As Object, e As EventArgs) Handles RadioButton3.CheckedChanged
        sql = "SELECT Fname as Firstname, Lname as Lastname FROM tblperson"
        displayData(sql, DataGridView1)
    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.

Comments

Multi DataGridView Data to Crystal Report

Hi, Could you plz make a tutorial or project on "How to be shown the Multi DataGridView data in CrystalReportView" in VB.NET ? Thanks

Add new comment

Add new comment
1040 views