vb.netwinformsdatagridviewautocompletetextbox

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:

    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.