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”.
  1. CREATE TABLE IF NOT EXISTS `employees` (
  2.   `EMPLOYEE_ID` int(11) NOT NULL,
  3.   `FIRST_NAME` varchar(255) DEFAULT NULL,
  4.   `LAST_NAME` varchar(255) DEFAULT NULL,
  5.   `EMAIL` varchar(255) DEFAULT NULL,
  6.   `PHONE_NUMBER` varchar(255) DEFAULT NULL,
  7.   `HIRE_DATE` datetime DEFAULT NULL,
  8.   `JOB_ID` varchar(255) DEFAULT NULL,
  9.   `SALARY` int(11) DEFAULT NULL,
  10.   PRIMARY KEY (`EMPLOYEE_ID`)
  11. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Insert the data in the table for your records.
  1.  INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `EMAIL`, `PHONE_NUMBER`, `HIRE_DATE`, `JOB_ID`, `SALARY`) VALUES
  2.  (101, 'neena', 'Kochar', 'nKochar', '123-1231-212', '1989-09-21 00:00:00', 'AD_VP', 17000),
  3. (102, 'lex', 'De Haan', 'lDe Haan', '113-2122-2121', '1990-01-13 00:00:00', 'AD_VP', 17000),
  4. (103, 'alexander', 'Hunold', 'aHunold', '211-4515-1515', '1991-05-21 00:00:00', 'IT_PROG', 9000),
  5. (104, 'Bruce', 'Ernst', 'bErnst', '515-541-151', '1999-07-02 00:00:00', 'IT_PROG', 6000),
  6. (107, 'Diana', 'Lorents', 'dLorents', '515-515-5156', '1999-11-16 00:00:00', 'IT_PROG', 4200),
  7. (124, 'Kevin', 'Mourgos', 'kMourgos', '559-887-333', '1995-10-17 00:00:00', 'IT_PROG', 5800),
  8. (141, 'Trenne', 'Rajs', 'tRajs', '212-548-989', '1997-01-29 00:00:00', 'ST_MAN', 3500),
  9. (142, 'Curtis', 'Davies', 'cDavies', '333-999-777', '1998-03-15 00:00:00', 'ST_CLERK', 3100),
  10. (143, 'Randal', 'Matos', 'rMatos', '4444-5654-54', '1998-09-07 00:00:00', 'ST_CLERK', 2600),
  11. (144, 'Peter', 'Vargas', 'pVargas', '6565-5641-87', '2000-01-10 00:00:00', 'ST_CLERK', 2500),
  12. (149, 'Ellen', 'Zlotkey', 'eZlotkey', '656-4847-45', '1996-11-05 00:00:00', 'ST_CLERK', 10500),
  13. (174, 'Jonathan', 'Abel', 'jAbel', '5988-4556-564', '1999-05-24 00:00:00', 'SA_MAN', 11000),
  14. (176, 'Kimberly', 'Taylor', 'kTaylor', '687-695-8754', '1987-09-17 00:00:00', 'SA_REP', 8600),
  15. (178, 'Jinnefer', 'Grant', 'jGrant', '552-6541-897', '1996-02-17 00:00:00', 'SA_REP', 7000),
  16. (200, 'Michael', 'Whalen', 'mWhalen', '2121-5465-541', '1997-08-17 00:00:00', 'SA_REP', 4400),
  17. (201, 'Pat', 'Hartstein', 'pHartstein', '14564-541-45', '1994-07-07 00:00:00', 'AD_ASST', 13000),
  18. (205, 'Shelley', 'Fay', 'sFay', '515-215-1156', '1994-07-07 00:00:00', 'MK_MAN', 6000),
  19. (206, 'William', 'Higgins', 'wHiggins', '566-112-5156', '1995-09-26 00:00:00', 'AC_MGR', 12000),
  20. (207, 'hatch', 'Glets', 'hGlets', '556-5465-515', '1989-03-07 00:00:00', 'AC_ACCOUNT', 8300),
  21. (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.
  1. 'set imports
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4.     'set up connection
  5.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=employeesdb")
  6.     'a set of commands in MySQL
  7.     Dim cmd As New MySqlCommand
  8.     'a Bridge between a database and datatable for retrieving and saving data.
  9.     Dim da As New MySqlDataAdapter
  10.     'a specific table in the database
  11.     Dim dt As New DataTable
  12. 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.
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         'for retrieving the records in the database on the datagridview in the first load.
  3.         Try
  4.             'open the connection
  5.             con.Open()
  6.             'set a new spicific table in the database
  7.             dt = New DataTable
  8.             'set your commands for holding the data.
  9.             With cmd
  10.                 .Connection = con
  11.                 .CommandText = "Select * from employees"
  12.             End With
  13.             'filling the table in the database
  14.             da.SelectCommand = cmd
  15.             da.Fill(dt)
  16.             'put your datasource in the datagridview
  17.             DataGridView1.DataSource = dt
  18.  
  19.             'for autocomplete the textbox
  20.             Dim r As DataRow
  21.             TextBox1.AutoCompleteCustomSource.Clear()
  22.             'for each datarow in the rows of the datatable
  23.             For Each r In dt.Rows
  24.                 'adding the specific row of the table in the AutoCompleteCustomSource of the textbox
  25.                 TextBox1.AutoCompleteCustomSource.Add(r.Item("EMPLOYEE_ID").ToString)
  26.             Next
  27.         Catch ex As Exception
  28.             MsgBox(ex.Message)
  29.         End Try
  30.         da.Dispose()
  31.         'close the connection
  32.         con.Close()
  33.     End Sub
Go back to the Design Views, double click the TextBox and do this code for searching the records in the Database.
  1. Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
  2.         'for searching the record in the database
  3.         Try
  4.             'open the connection
  5.             con.Open()
  6.             dt = New DataTable
  7.             'set your commands for holding the data
  8.             With cmd
  9.                 .Connection = con
  10.                 .CommandText = "Select * from employees where EMPLOYEE_ID like '" & TextBox1.Text & "%'"
  11.             End With   
  12.             'filling the table in the database.
  13.             da.SelectCommand = cmd
  14.             da.Fill(dt)
  15.             'put your datasource in the datagridview
  16.             DataGridView1.DataSource = dt
  17.  
  18.         Catch ex As Exception
  19.             MsgBox(ex.Message)
  20.         End Try
  21.         da.Dispose()
  22.         'close the connection
  23.         con.Close()
  24.     End Sub
Download the complete Source Code and run it on your computer.
Tags
Search Data Using Autocomplete TextBox
datagridview
MySQL Database
visual basic

Add new comment

About text formats
  • 284 views