Advance Autocomplete/Autosuggest in a Textbox Using Visual Basic 2008 and MySQL database

Submitted by janobe on Saturday, December 14, 2013 - 12:11.

When searching a data from the database using textbox in Visual Basic 2008, commonly, it’s hard for us to search the exact data. So, in this tutorial I created an autosuggest textbox, so that it will be easy for you to search the correct record in your table. For instance, the word “google”. Let’s begin: 1.First Create a database in MySQL named “employeesdb” then create the table.

 
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,
  `COMMISSION_ID` int(11) DEFAULT NULL,
  `MANAGER_ID` int(11) DEFAULT NULL,
  `DEPARTMENT_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  KEY `COMMISSION_ID` (`COMMISSION_ID`),
  KEY `DEPARTMENT_ID` (`DEPARTMENT_ID`),
  KEY `JOB_ID` (`JOB_ID`),
  KEY `MANAGER_ID` (`MANAGER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. Insert all the data in the employees table.

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

3. Now, Open the Microsoft Visual Basic 2008 and create a form. Put a TextBox and a Label. Search Form

4. Click the TextBox and go to the properties. Under it, click the AutoComplete mode and select Suggest. After selecting it, click AutoComplete Source and select Custom Source.

5. Double click a Form and set up your connection. And do the declaration.

Imports MySql.Data.MySqlClient
Public Class Form1
 
    Public Function mysqldb() As MySqlConnection
        Return New MySqlConnection("server=localhost;user id=root;database=employeesdb")
    End Function
    Public con As MySqlConnection = mysqldb()
 
    Public cmd As New MySqlCommand
    Public da As New MySqlDataAdapter
    Public dt As New DataTable
 
end class

Reminders: Before setting up your connection make sure that you add MySql.Data.dll as your reference. 6. After the declaration, create a public sub of the AutoSuggest event.

  Public Sub auto_suggest(ByVal member As String, ByVal table As String, ByVal txt As Object)
        Try
            dt = New DataTable
            'open the connection
            con.Open()
            'holds the data in the database
            With cmd
                .Connection = con
                .CommandText = "select " & member & " from " & table
            End With
            '''''''''''''''fill data in the table
            da.SelectCommand = cmd
            da.Fill(dt)
 
            ''function of autocomplete
            Dim r As DataRow
            txt.AutoCompleteCustomSource.Clear()
 
            For Each r In dt.Rows
                txt.AutoCompleteCustomSource.Add(r.Item(0).ToString)
 
            Next
            ''''''''''''''''''''''''
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ''''close the connection
        con.Close()
        da.Dispose()
 
    End Sub

7. After that, go back to the design view and double click the Form. Call the name of your public sub and put it in a Form1_Load.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'call a public sub
        'the job_id is the field of a table , the employees is the name of a table and a textbox is an object
        auto_suggest("JOB_ID", "employees", TextBox1)
 
    End Sub

8. Now run your work. Complete Source Code is included. Download it and run on your computer.