AutoComplete TextBox in a DataGridView

Submitted by janobe on Friday, January 3, 2014 - 00:08.
In this tutorial I will teach you how to Auto Complete a TextBox inside the DataGridView in the Visual Basic 2008 and MySQL Database. Others are used to store the list of items in the ComboBox. So, for a change, I used a TextBox because it’s easy to use and you don’t have to drop down in searching for the items that you needed. Just simply type the initial letter of the items that you’re going to search. I based this on my tutorials which are the “Simple AutoComplete ComboBox in a DataGridView” and “Advance Autocomplete/Autosuggest in a Textbox Using Visual Basic 2008 and MySQL database”. To start with: 1. Create a database.
  1. CREATE DATABASE  `test`
2. Create a Table.
  1. CREATE TABLE IF NOT EXISTS `item` (
  2.   `id` int(11) NOT NULL,
  3.   PRIMARY KEY (`id`)
  4. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
3. Insert this records in the table that you have created.
  1. INSERT INTO `item` (`id`) VALUES
  2. (213),                                                                                                                                                               (190),
  3. (102),                                                                                  
  4. (103),                                                                                  
  5. (104),                                                                                  
  6. (107),                                                                                  
  7. (124),                                                                                  
  8. (141),                                                                                  
  9. (142),                                
  10. (143),                              
  11. (144),                                
  12. (149),                                  
  13. (174),                            
  14. (176),                              
  15. (178),                            
  16. (200),                                
  17. (201),                                    
  18. (205),                          
  19. (206),                                  
  20. (207),                                
  21. (100)
4. Open the Visual Basic 2008, Create a project and create a Form. In the Form, add the DataGridView. Fform 5. Double click the Form and above the Form1_Load, set a connection of MySQL Database to Visual Basic 2008. After that, declare all the classes and variables that you needed.
  1. 'add reference
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4.  
  5.     'set up the string connection of MySQL Database
  6.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;database=employeesdb")
  7.     'a set of commands in MySQL
  8.     Dim cmd As New MySqlCommand
  9.     'a Bridge between a database and datatable for retrieving and saving data.
  10.     Dim da As New MySqlDataAdapter
  11.     'a specific table in the database
  12.     Dim dt As New DataTable
  13.  
  14.  
  15.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  16.  
  17.     End Sub
  18. End Class
6. In the Form1_Load, declare the variable txt as a TextBox Column and add it in the DataGridView.
  1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.         'declaring a new textbox column in the datagridview
  3.         Dim txt As New DataGridViewTextBoxColumn
  4.         'adding a textbox column in the datagridview
  5.         DataGridView1.Columns.Add(txt)
  6. End Sub
7. Go back to the Design Views, double click the DataGridview and change the method name in EditingControlShowing. Method Name 8. In the DataGridView1_ EditingControlShowing, do the following code for storing data in the TextBox and the autocomplete properties of it.
  1. Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
  2.         'declare for a new textbox
  3.         Dim txt As New TextBox
  4.         con.Open()
  5.         'set a new spicific table in the database
  6.         dt = New DataTable
  7.         'set your commands for holding the data.
  8.         With cmd
  9.             .Connection = con
  10.             .CommandText = "Select * from employees"
  11.         End With
  12.         'filling the table in the database
  13.         da.SelectCommand = cmd
  14.         da.Fill(dt)
  15.  
  16.         Dim r As DataRow 'represents a row of data in the datatable
  17.         For Each r In dt.Rows 'get a collection of rows that belongs to this table
  18.  
  19.             'the control shown to the user for editing the selected cell value
  20.             If TypeOf e.Control Is TextBox Then
  21.                 txt = e.Control
  22.                 'adding the specific row of the table in the AutoCompleteCustomSource of the textbox
  23.                 txt.AutoCompleteCustomSource.Add(r.Item("EMPLOYEE_ID").ToString)
  24.                 txt.AutoCompleteMode = AutoCompleteMode.Suggest
  25.                 txt.AutoCompleteSource = AutoCompleteSource.CustomSource
  26.             End If
  27.         Next
  28.         'closing the connection
  29.         con.Close()
  30. End Sub
Tags
AutoComplete
textbox
datagridview
visual basic
MYSQL
database

Comments

Submitted by Shirshendu Deb (not verified) on Mon, 11/09/2015 - 23:08

Very very useful material . Indeed very helpful... Bro.. thanks a lot

Submitted by Prosez Technologies (not verified) on Fri, 03/30/2018 - 19:03

Thank you..This code solved my pblm