Search Data Using Autocomplete TextBox

Submitted by janobe on Sunday, December 22, 2013 - 17:37.

In this tutorial I will show you how to search records in the MySQL Database using the AutoComplete TextBox. The list of records will display in the Datagridview and it will show you how easy it is to search a record in the Database. Lets’ begin: First create a table in the MySQL Database named “employeesdb”.

CREATE TABLE IF NOT EXISTS `employees` (
  `EMPLOYEE_ID` int(11) NOT NULL,
  `FIRST_NAME` varchar(255) DEFAULT NULL,
  `LAST_NAME` varchar(255) DEFAULT NULL,
  `EMAIL` varchar(255) DEFAULT NULL,
  `PHONE_NUMBER` varchar(255) DEFAULT NULL,
  `HIRE_DATE` datetime DEFAULT NULL,
  `JOB_ID` varchar(255) DEFAULT NULL,
  `SALARY` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Insert the data in the table for your records.

 INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PHONE_NUMBER`, `HIRE_DATE`, `JOB_ID`, `SALARY`) VALUES
 (101, 'neena', 'Kochar', 'nKochar', '123-1231-212', '1989-09-21 00:00:00', 'AD_VP', 17000),
(102, 'lex', 'De Haan', 'lDe Haan', '113-2122-2121', '1990-01-13 00:00:00', 'AD_VP', 17000),
(103, 'alexander', 'Hunold', 'aHunold', '211-4515-1515', '1991-05-21 00:00:00', 'IT_PROG', 9000),
(104, 'Bruce', 'Ernst', 'bErnst', '515-541-151', '1999-07-02 00:00:00', 'IT_PROG', 6000),
(107, 'Diana', 'Lorents', 'dLorents', '515-515-5156', '1999-11-16 00:00:00', 'IT_PROG', 4200),
(124, 'Kevin', 'Mourgos', 'kMourgos', '559-887-333', '1995-10-17 00:00:00', 'IT_PROG', 5800),
(141, 'Trenne', 'Rajs', 'tRajs', '212-548-989', '1997-01-29 00:00:00', 'ST_MAN', 3500),
(142, 'Curtis', 'Davies', 'cDavies', '333-999-777', '1998-03-15 00:00:00', 'ST_CLERK', 3100),
(143, 'Randal', 'Matos', 'rMatos', '4444-5654-54', '1998-09-07 00:00:00', 'ST_CLERK', 2600),
(144, 'Peter', 'Vargas', 'pVargas', '6565-5641-87', '2000-01-10 00:00:00', 'ST_CLERK', 2500),
(149, 'Ellen', 'Zlotkey', 'eZlotkey', '656-4847-45', '1996-11-05 00:00:00', 'ST_CLERK', 10500),
(174, 'Jonathan', 'Abel', 'jAbel', '5988-4556-564', '1999-05-24 00:00:00', 'SA_MAN', 11000),
(176, 'Kimberly', 'Taylor', 'kTaylor', '687-695-8754', '1987-09-17 00:00:00', 'SA_REP', 8600),
(178, 'Jinnefer', 'Grant', 'jGrant', '552-6541-897', '1996-02-17 00:00:00', 'SA_REP', 7000),
(200, 'Michael', 'Whalen', 'mWhalen', '2121-5465-541', '1997-08-17 00:00:00', 'SA_REP', 4400),
(201, 'Pat', 'Hartstein', 'pHartstein', '14564-541-45', '1994-07-07 00:00:00', 'AD_ASST', 13000),
(205, 'Shelley', 'Fay', 'sFay', '515-215-1156', '1994-07-07 00:00:00', 'MK_MAN', 6000),
(206, 'William', 'Higgins', 'wHiggins', '566-112-5156', '1995-09-26 00:00:00', 'AC_MGR', 12000),
(207, 'hatch', 'Glets', 'hGlets', '556-5465-515', '1989-03-07 00:00:00', 'AC_ACCOUNT', 8300),
(100, 'Steven', 'King', '[email protected]', NULL, NULL, NULL, 24000);

Open the Visual Basic 2008, create a project and set your Form just like this. First Form

Click the TextBox and go to the properties. Then, select the AutoCompleteMode and select AppendSuggest. After that, click again the AutoCompleteSource and select CostumSource. Second Form

After that, double click the Form and do this code for setting up the connection of MySQL Database and declare the classes that you needed.

'set imports
Imports MySql.Data.MySqlClient
Public Class Form1
    'set up connection
    Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=employeesdb")
    'a set of commands in MySQL
    Dim cmd As New MySqlCommand	
    'a Bridge between a database and datatable for retrieving and saving data.
    Dim da As New MySqlDataAdapter
    'a specific table in the database
    Dim dt As New DataTable
End Class

Note: add mysql.data.dll as your reference to make MySQL work. Go to Form_Load and do the following codes for retrieving the record in the Database.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'for retrieving the records in the database on the datagridview in the first load.
        Try
            'open the connection
            con.Open()
            'set a new spicific table in the database 
            dt = New DataTable
            'set your commands for holding the data.
            With cmd
                .Connection = con
                .CommandText = "Select * from employees"
            End With
            'filling the table in the database
            da.SelectCommand = cmd
            da.Fill(dt)
            'put your datasource in the datagridview
            DataGridView1.DataSource = dt
 
            'for autocomplete the textbox
            Dim r As DataRow
            TextBox1.AutoCompleteCustomSource.Clear()
            'for each datarow in the rows of the datatable
            For Each r In dt.Rows
                'adding the specific row of the table in the AutoCompleteCustomSource of the textbox
                TextBox1.AutoCompleteCustomSource.Add(r.Item("EMPLOYEE_ID").ToString)
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()
        'close the connection
        con.Close()
    End Sub

Go back to the Design Views, double click the TextBox and do this code for searching the records in the Database.

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
        'for searching the record in the database 
        Try
            'open the connection
            con.Open()
            dt = New DataTable
            'set your commands for holding the data
            With cmd
                .Connection = con
                .CommandText = "Select * from employees where EMPLOYEE_ID like '" & TextBox1.Text & "%'"
            End With	
            'filling the table in the database.
            da.SelectCommand = cmd
            da.Fill(dt)
            'put your datasource in the datagridview
            DataGridView1.DataSource = dt
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        da.Dispose()
        'close the connection
        con.Close()
    End Sub

Download the complete Source Code and run it on your computer.

Add new comment

267 views

Search Data Using Autocomplete TextBox

Tags

Add new comment