Auto Search Textbox on VB.Net

Most of data-driven software application or web application has a search box or a search engine for the user to easily find what they are looking for. In a Student Information System for example, a user can find the record  of a student by typing the keyword (like for example student ID) on the search textbox and clicks on the ‘Search’ button to command the program to start looking for the data matching that keyword from the database. If the search command found a result using that student ID entered, the result data will then be fetched and could be shown on a particular application form.

In this article, I will discuss you a better way to implement a search engine on your project. Have you tried searching for something on Google search box and it shows you list of suggested or related searches like the image shown below?

The image you see above is what I am going to show you how to do it on VB.Net using a text box and a button.

Note: This example work for projects with database involved.

Please follow this simple step by step instruction to successfully implement the auto-search.

Step 1.

Create a database on Microsoft SQL Server 2005 or use any existing SQL database if you want.

In this example, I will use my own database. You may follow if you want.

Database name: SMIS

Table name	Fields	Data Type
db_info	user_id	Varchar(10)
	fname	Varchar(15)
	lname	Varchar(15)
	mname	Varchar(15)
	age	int
	gender	Varchar(8)

Step 2.

Create a Visual Basic – Windows project and name it auto_search. On the form, drag a textbox and name it txt_search. Then drag a button, name it btn_search and place it beside the textbox. See sample image below.

Step 3.

In your project solution, right click Form1 and choose ‘view code’. 

Then write or copy the code below.

Note: The sqldata is a string variable declared where data source string is stored. Change the data source value with the actual data source of your project.  Data source used in this example is: ‘Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=SMIS;Integrated Security=True’

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports Microsoft.VisualBasic

Imports Microsoft.SqlServer.Server

Public Class Form1

Private sqldata as string =”data source here”

  Sub openConnection()

        cnn = New SqlConnection

        cnn.ConnectionString = sqldata

        cnn.Open()

End Sub

Public Sub SetAutoComplete()

        Try

            Dim AutoComp As New AutoCompleteStringCollection()

            Dim ConStr As String = sqldata

            Dim sqlCon As New SqlClient.SqlConnection(sqldata)

            sqlCon.Open()

            Dim dsSerch_id As New DataSet

 Dim dsSerch_Lname As New DataSet

            Dim dsSerch_Fname As New DataSet

            Dim dsSerch_Mname As New DataSet

            'Auto Searcn ID number

            Dim Str_sid As String = "select user_id from db_info"

            Dim SqlCom_sid As New SqlClient.SqlCommand(Str_sid, sqlCon)

            Dim sqlAdap_sid As New SqlClient.SqlDataAdapter(SqlCom_sid)

            sqlAdap_sid.Fill(dsSerch_id)

            For i As Integer = 0 To dsSerch_id.Tables(0).Rows.Count - 1

                AutoComp.Add(dsSerch_id.Tables(0).Rows(i)(0).ToString())

            Next

            'Auto Search Lname

            Dim Str As String = "select lname from db_info"

            Dim SqlCom As New SqlClient.SqlCommand(Str, sqlCon)

            Dim sqlAdap As New SqlClient.SqlDataAdapter(SqlCom)

            sqlAdap.Fill(dsSerch_Lname)

            For i As Integer = 0 To dsSerch_Lname.Tables(0).Rows.Count -             1

            AutoComp.Add(dsSerch_Lname.Tables(0).Rows(i)(0).ToString())

            Next

            'Auto Search Fname

            Dim Str_gen As String = "select fname from db_info"

            Dim SqlCom_gen As New SqlClient.SqlCommand(Str_gen, sqlCon)

            Dim sqlAdap_gen As New SqlClient.SqlDataAdapter(SqlCom_gen)

            sqlAdap_gen.Fill(dsSerch_Fname)

            For i As Integer = 0 To dsSerch_Fname.Tables(0).Rows.Count -             1

            AutoComp.Add(dsSerch_Fname.Tables(0).Rows(i)(0).ToString())

            Next

            'Auto Search Mname

            Dim Str_brgy As String = "select mname from db_info"

            Dim SqlCom_brgy As New SqlClient.SqlCommand(Str_brgy, sqlCon)

            Dim sqlAdap_brgy As New SqlClient.SqlDataAdapter(SqlCom_brgy)

            sqlAdap_brgy.Fill(dsSerch_Mname)

            For i As Integer = 0 To dsSerch_Mname.Tables(0).Rows.Count -             1

            AutoComp.Add(dsSerch_Mname.Tables(0).Rows(i)(0).ToString())

            Next

           

            'Set the some propersties of the text box to allow auto                   search

            'Or you may set this manaully on the textbox property window

            txt_search.AutoCompleteMode = AutoCompleteMode.Suggest

            txt_search.AutoCompleteSource =                                           AutoCompleteSource.CustomSource

            txt_search.AutoCompleteCustomSource = AutoComp

        Catch ex As Exception

MessageBox.Show(ex.Message, prog_message_caption, MessageBoxButtons.OK, MessageBoxIcon.Information)

        End Try

    End Sub

Private Sub main_form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Call the auto search code on form load

SetAutoComplete()

End Sub

End Class

Here is a sample image of the running Auto-Search program. The search box can search 

record using id number, first name, last name or middle name.