tutlogger

Auto Search Textbox on VB.Net


In Google search engine, when you type a keyword to search, it automatically shows list of suggested or related searches like the image shown below. This auto-suggestion of Google, serves a very big help to every user browsers specially when we can't find the right keyword to search.



In this tutorial, I'm  going to do an auto-suggestion application program on VB.Net.

Please follow the step by step instructions provided 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 text box 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.  The 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 records using ID number, first name, last name and or middle name.





SHARE THIS POST

1 comment:

  1. Ahm. Hello. When is cnn declared ? Just a noobie here. Thanks

    ReplyDelete

PRIVACY POLICY | TERMS OF SERVICE | COPYRIGHT POLICY