Tuesday, 30 June 2020

How to Search Like Google in VB.NET with SQL Server Database & (DataGridView, ComboBox, TextBox)

Hello Everyone,
if you want to show student record in DataGridView by Selecting Class name from ComboBox and then all student record will display in GridView.

There some Tips & Trick that will help you to create this project

Step-1 Add following control on the form


  1. TextBox
  2. ComboBox
  3. DataGridView
  4. Button

  • Change the ComboBox Property (Name = "cmbClass")
  • Change the Button Property (Name= "btnSearch" & Text ="Search")
StudentListByClass



Step-2 Write Following code 

  

       

            Imports System.Data.SqlClient
Imports System.Data
Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadDatainGrid()
        bindcmb()
    End Sub
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        If con.State = 1 Then con.Close()
        ds.Clear()

        DataGridView1.DataSource = Nothing
        qry = "Select * from tblStudent with (nolock) where ClassName='" & cmbClass.Text & "'"
        ds = FetchData(qry)
        If ds.Tables(0).Rows.Count > 0 Then
            DataGridView1.DataSource = ds.Tables(0)
        Else
            MessageBox.Show("Data not found....")
            DataGridView1.DataSource = Nothing
        End If
    End Sub
    Public Sub LoadDatainGrid()
        If con.State = 1 Then con.Close()
        ds.Clear()
        DataGridView1.DataSource = Nothing
        qry = "Select * from tblStudent with (nolock)"
        ds = FetchData(qry)
        If ds.Tables(0).Rows.Count > 0 Then
            DataGridView1.DataSource = ds.Tables(0)
        Else
            MessageBox.Show("Data not found....")
        End If
    End Sub

    Private Sub bindcmb()
        qry = "select * from tblClass with (nolock)"
        Try
            If con.State = 1 Then con.Close()
            con.Open()
            cmd = New SqlCommand(qry, con)
            Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
            'Fill the Dataset with records from Table.
            Dim ds1 As DataSet = New DataSet()
            sda.Fill(ds1, "tblClass")
            'Dim a As Integer = 
            Dim i As Integer = 0
            cmbClass.Items.Clear()
            For i = 0 To ds1.Tables("tblClass").Rows.Count - 1
                cmbClass.Items.Add(ds1.Tables("tblClass").Rows(i)(1).ToString())
            Next
        Catch ex As Exception
            MessageBox.Show("Can not open connection ! ")
        End Try
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        DataGridView1.DataSource = Nothing
        cmbClass.Text = ""
        If TextBox1.Text <> "" Then
            If con.State = 1 Then con.Close()
            ds.Clear()
            DataGridView1.DataSource = Nothing
            qry = "select Student_Name, Father_Name,ClassName,Gender,Mobile,City from tblStudent with(nolock) where Student_Name LIKE'%" & TextBox1.Text & "%' or ClassName LIKE '%" & TextBox1.Text & "%' or Father_Name LIKE '%" & TextBox1.Text & "%' or City LIKE '%" & TextBox1.Text & "%' or Mobile LIKE '%" & TextBox1.Text & "%'"
            ds = FetchData(qry)
            If ds.Tables(0).Rows.Count > 0 Then
                DataGridView1.DataSource = ds.Tables(0)
            Else
                MessageBox.Show("Data not found....")
                Exit Sub
            End If
        Else
            DataGridView1.DataSource = Nothing
            TextBox1.Clear()
        End If
    End Sub

    Private Sub cmbClass_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbClass.SelectedIndexChanged
        TextBox1.Clear()
        DataGridView1.DataSource = Nothing
    End Sub
End Class


       
 


Step-3 Create a module file in project and write following code.


       

            Imports System.Data.SqlClient
Imports System.Data

Module Module1
    Public connnectionstring As String = "Data Source=.;Initial Catalog=DemoExample;Integrated Security=True"
    Public con As New SqlConnection(connnectionstring)
    Public cmd As New SqlCommand
    Public da As New SqlDataAdapter
    Public dr As SqlDataReader
    Public ds As New DataSet

    Public qry As String = ""


    'search gridview

    Public Function FetchData(ByVal qry As String) As DataSet
        If con.State = 1 Then con.Close()
        con.Open()
        da = New SqlDataAdapter(qry, con)
        ds = New DataSet
        da.Fill(ds)
        Return ds
        con.Close()

    End Function
End Module

       
 


WATCH FULL VIDEO TUTORIAL 


     
 DOWNLOAD SOURCE CODE:











3 comments:

  1. I like your coding good work bro continue

    ReplyDelete
  2. I am your student and watch your videos complete every time to get knowledge accordingly.

    ReplyDelete

Popular Posts