In this tutorial, I will teach you how to
search data using a text box in VB.Net and SQL Server database. This program has the
ability to find the data in the datagridview when the text in a textbox is changed. In this way, it will
automatically display in the datagridview, the match data that you type in the textbox.
Creating Database
1. Install the
MSSMS 2018 on your machine.
2. Open the
MSSMS 2018 . After that, right click the database, the select “
New Database” and name it “
dbperson”
3. Do the following query to create a table in the database that you have created
USE [dbperson]
GO
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.
Step 2
Add a
Textboxess, and a
DataGridview inside the Form. Then do the Form just like shown below.
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
searching the data in the database.
Private Sub searchData(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
Go back to the design view, double click the textbox to open the
textchange event
handler on it. After that, add this code inside the “
TextBox1_TextChanged” event to find the data in the database.
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
sql = "SELECT * FROM tblperson WHERE Fname LIKE '%" & TextBox1.Text & "%' OR Lname LIKE '%" & TextBox1.Text & "%'"
searchData(sql, DataGridView1)
End Sub
Step 7
Write the following codes for
retrieving data in the database in the first load of the form.
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
sql = "SELECT * FROM tblperson"
searchData(sql, DataGridView1)
End Sub
Output
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.