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.

CREATE DATABASE  `test`

2. Create a Table.

CREATE TABLE IF NOT EXISTS `item` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3. Insert this records in the table that you have created.

INSERT INTO `item` (`id`) VALUES
(213),                                                                                                                                                               (190),
(102),                                                                                  
(103),                                                                                  
(104),                                                                                  
(107),                                                                                  
(124),                                                                                  
(141),                                                                                  
(142),                                
(143),                               
(144),                                
(149),                                  
(174),                            
(176),                              
(178),                            
(200),                                
(201),                                    
(205),                          
(206),                                   
(207),                                 
(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.

'add reference
Imports MySql.Data.MySqlClient
Public Class Form1
 
    'set up the string connection of MySQL Database
    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
 
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    End Sub
End Class

6. In the Form1_Load, declare the variable txt as a TextBox Column and add it in the DataGridView.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'declaring a new textbox column in the datagridview
        Dim txt As New DataGridViewTextBoxColumn
        'adding a textbox column in the datagridview
        DataGridView1.Columns.Add(txt)
End Sub

7. Go back to the Design Views, double click the DataGridview and change the method name in EditingControlShowing.

8. In the DataGridView1_ EditingControlShowing, do the following code for storing data in the TextBox and the autocomplete properties of it.

Private Sub DataGridView1_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        'declare for a new textbox
        Dim txt As New TextBox
        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)
 
        Dim r As DataRow 'represents a row of data in the datatable
        For Each r In dt.Rows 'get a collection of rows that belongs to this table
 
            'the control shown to the user for editing the selected cell value
            If TypeOf e.Control Is TextBox Then
                txt = e.Control
                'adding the specific row of the table in the AutoCompleteCustomSource of the textbox
                txt.AutoCompleteCustomSource.Add(r.Item("EMPLOYEE_ID").ToString)
                txt.AutoCompleteMode = AutoCompleteMode.Suggest
                txt.AutoCompleteSource = AutoCompleteSource.CustomSource
            End If
        Next
        'closing the connection
        con.Close()
End Sub

AutoComplete TextBox in a DataGridView

Tags

Comments

Software Professional

Thank You

Add new comment