vb.net winforms datagridview autocomplete textbox

how to Force user only select suggested entry from datagridview Autocomplete textbox in VB.Net

I'm Trying to Force user only select suggested entry from datagridview Autocomplete textbox in VB.Net

and one more I want the "Unit" column to appear according to the database when the "NameProduct" Column is typed correctly

so when the user types then the autocomplete appears if the user does on the keyboard backspace and esc then the results are outside the list then the message box appears and I want to not allow the user to leave column until a valid input is entered. Please guide me

Thanks

Imports System.Data.OleDb
Imports Dapper

Public Class Form1
    Dim ProductsService As New ProductsService()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub

    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        Dim column As Integer = DataGridView1.CurrentCell.ColumnIndex
        Dim headerText As String = DataGridView1.Columns(column).HeaderText

        If headerText.Equals("ProductName") Then
            Dim tb As TextBox = TryCast(e.Control, TextBox)

            If tb IsNot Nothing Then
                tb.AutoCompleteMode = AutoCompleteMode.Suggest
                tb.AutoCompleteSource = AutoCompleteSource.CustomSource
                tb.AutoCompleteCustomSource.AddRange(ProductsService.GetByProductname().Select(Function(n) n.ProductName).ToArray())
            End If
        Else
            Dim tb As TextBox = TryCast(e.Control, TextBox)
            If tb IsNot Nothing Then
                tb.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If

    End Sub
End Class
Public Class Products
    Public Property Id() As Integer
    Public Property ProductName() As String

    Public Property Unit() As String

End Class
Public Class ProductsService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\test03092024.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetByProductname() As IEnumerable(Of Products)
        Dim sql = "SELECT ProductName AS ProductName FROM [Products]"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Products)(sql).ToList()
        End Using
    End Function
End Class

Sample Table Products

ProductName Unit
Absecon         Cup1
Abstracta   Pcs2
Abundantia  Cup3
Academia    Pcs4
Acadiau         Cup5
Acamas          Pcs6
Ackerman    Cup7
Ackley          Pcs8
Ackworth    Cup9
Acomita         Pcs10
Aconcagua   Cup11
Acton           Pcs12
Acushnet    Cup13
Acworth         Pcs14
Ada         Cup15
Ada         Pcs16
Adair           Cup17
Adairs          Pcs18
Adair           Cup19
Adak            Pcs20
Adalberta   Cup21
Adamkrafft  Pcs22
Adams           Cup23

Solution

Add to the ProductsService class a method that returns distinct IEnumerable<T> elements where T is a model that encapsulates properties represent the required database fields. Remove the DISTINCT predicate from the SELECT statement if the database table does not contain duplicate products.

Public Class ProductsService
    Private Shared Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=|DataDirectory|\test03092024.accdb;
                Persist Security Info=False;"
    End Function

    ' ...

    Friend Shared Function GetDistinctProducts() As IEnumerable(Of Product)
        Dim sql = "SELECT DISTINCT ProductName, Unit 
                   FROM Products 
                   ORDER BY ProductName"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Product)(sql)
        End Using
    End Function
End Class

Public Class Product
    Public Property ProductName As String
    Public Property Unit As String

    Public Overrides Function ToString() As String
        Return $"{ProductName} {Unit}".Trim()
    End Function
End Class

Override the Form's OnLoad method to set the grid up. Bind an empty BindingList<Product>, and initialize a class field of type Dictionary<Of String, String) where the Keys are the products and the Values are the corresponding units. The dictionary is used to:

Fill the empty DataGridViewTextBoxEditingControl.AutoCompleteCustomSource string collection with the Keys (the products) in the DataGridView.EditingControlShowing event.
Validate the ProductName cell in the DataGridView.CellValidating event. The Keys must contain the user input to be pushed to the underlying data source. Otherwise, the cell remains focused in edit mode and the current row displays the error icon.
Set the corresponding Unit of the valid product in the DataGridView.CellValidated event.
Set the corresponding unit of the last selected product - if any - in the DataGridView.CellEndEdit event if the user chooses to cancel editing by pressing the Esc key.

Public Class Form1
    Private dictProducts As Dictionary(Of String, String)

    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)

        dictProducts = ProductsService.GetDistinctProducts().
            ToDictionary(Function(p) p.ProductName, Function(p) p.Unit)

        DataGridView1.DataSource = New BindingList(Of Product)
        DataGridView1.Columns("Unit").ReadOnly = True
    End Sub

    Private Sub DataGridView1_EditingControlShowing(
        sender As Object,
        e As DataGridViewEditingControlShowingEventArgs) Handles _
        DataGridView1.EditingControlShowing
        If TypeOf e.Control Is TextBox Then
            Dim dgv = DirectCast(sender, DataGridView)
            Dim tb = DirectCast(e.Control, TextBox)

            If dgv.CurrentCell.ColumnIndex = dgv.Columns("ProductName").Index Then
                If tb.AutoCompleteCustomSource.Count = 0 Then
                    tb.AutoCompleteSource = AutoCompleteSource.CustomSource
                    tb.AutoCompleteCustomSource.AddRange(dictProducts.Keys.ToArray())
                End If
                tb.AutoCompleteMode = AutoCompleteMode.Suggest
            Else
                tb.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellValidating(
        sender As Object,
        e As DataGridViewCellValidatingEventArgs) _
        Handles DataGridView1.CellValidating
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
           e.RowIndex <> dgv.NewRowIndex Then
            Dim key = e.FormattedValue?.ToString()

            If String.IsNullOrEmpty(key) OrElse
               Not dictProducts.ContainsKey(key) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

                boundItem.Unit = Nothing
                dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
                dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellValidated(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            Dim boundItem = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If boundItem IsNot Nothing Then
                Dim value = dictProducts(boundItem.ProductName)
                If value <> boundItem.Unit Then
                    boundItem.Unit = value
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                End If
                dgv.Rows(e.RowIndex).ErrorText = Nothing
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellEndEdit(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            If Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
                Dim unit As String = Nothing

                If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                   dictProducts.TryGetValue(boundItem.ProductName, unit) Then
                    boundItem.Unit = unit
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                    dgv.Rows(e.RowIndex).ErrorText = Nothing
                End If
            End If
        End If
    End Sub
End Class

To get the underlying data source.

Dim src = DirectCast(DataGridView1.DataSource, BindingList(Of Product))

If the database table contains products with duplicate names and different units and changing the names is not an option, then you need to control what the grid should display and what it should produce.

Note
All types and methods in this section are taken from the previous one unless otherwise stated.

Override the Onload method to populate a class field of type Dictionary(Of String, Product). To get unique keys for the dictionary, we get here a collection of what the model's ToString method override returns.

Public Class Form1
    Private dictProducts As Dictionary(Of String, Product)

    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)

        dictProducts = ProductsService.GetDistinctProducts().
            ToDictionary(Function(p) p.ToString(), Function(p) p)

        DataGridView1.DataSource = New BindingList(Of Product)
        DataGridView1.Columns("Unit").ReadOnly = True
    End Sub
End Class

Keep the EditingControlShowing event as is. The TextBox auto complete list displays the ProductName and the corresponding Unit values so the user knows which duplicate product to select.

Edit the CellValidating, CellValidated, and CellEndEdit events as follows:

Private Sub DataGridView1_CellValidating(
    sender As Object,
    e As DataGridViewCellValidatingEventArgs) _
    Handles DataGridView1.CellValidating
    Dim dgv = DirectCast(sender, DataGridView)

    If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
        e.RowIndex <> dgv.NewRowIndex Then
        Dim key As String

        If dgv.IsCurrentCellInEditMode Then
            Dim p = dictProducts.Values.
                FirstOrDefault(Function(x) x.ProductName.
                Equals(e.FormattedValue.
                ToString(), StringComparison.InvariantCultureIgnoreCase))

            If p IsNot Nothing Then
                key = p.ToString()
            Else
                key = e.FormattedValue.ToString()
            End If
        Else
            key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        End If

        If String.IsNullOrEmpty(key) OrElse
            Not dictProducts.ContainsKey(key) Then
            Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

            boundItem.Unit = Nothing
            dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
            dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            e.Cancel = True
        End If
    End If
End Sub

Private Sub DataGridView1_CellValidated(
    sender As Object,
    e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
    Dim dgv = DirectCast(sender, DataGridView)

    If e.ColumnIndex = dgv.Columns("ProductName").Index Then
        Dim p As Product = Nothing
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()

        If key Is Nothing Then Return
        If dictProducts.TryGetValue(key, p) Then
            Dim boundItem = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

            If p.Unit <> boundItem.Unit Then
                boundItem.Unit = p.Unit
                dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            End If
            dgv.Rows(e.RowIndex).ErrorText = Nothing
        End If
    End If
End Sub

Private Sub DataGridView1_CellEndEdit(
    sender As Object,
    e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Dim dgv = DirectCast(sender, DataGridView)
    Dim src = DirectCast(dgv.DataSource, BindingList(Of Product))

    If src.Count = e.RowIndex Then Return
    If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
        Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        Dim p As Product = Nothing
        Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

        If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                dictProducts.TryGetValue(key, p) Then
            boundItem.Unit = p.Unit
            dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            dgv.Rows(e.RowIndex).ErrorText = Nothing
        End If
    End If
End Sub

Handle the grid's CellFormatting event to display the ProductName value instead of the value selected from the auto-complete list.

Private Sub DataGridView1_CellFormatting(
    sender As Object,
    e As DataGridViewCellFormattingEventArgs) Handles _
    DataGridView1.CellFormatting
    Dim dgv = DirectCast(sender, DataGridView)
    Dim src = DirectCast(dgv.DataSource, BindingList(Of Product))

    If src.Count = e.RowIndex Then Return
    If e.ColumnIndex = dgv.Columns("ProductName").Index Then
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        Dim p As Product = Nothing

        If Not String.IsNullOrWhiteSpace(key) AndAlso
            dictProducts.TryGetValue(key, p) Then
            e.Value = p.ProductName
            e.FormattingApplied = True
        End If
    End If
End Sub

To get the underlying data source with the original ProductName values:

Dim displayList = DirectCast(DataGridView1.DataSource, BindingList(Of Product))
Dim outputList = displayList.
    Select(Function(p) New Product With {
    .ProductName = dictProducts(p.ProductName).ProductName,
    .Unit = p.Unit
})
' ... etc.