I'm Trying autocomplete with textbox event TextChanged but running so slow with VB.NET.
even though there are only 6 records, is there something wrong with my code, please guide me.
I attached a gif of an example file when I typed "KBE" it ran very slowly and come up with some suggestions until it finishes by itself and then I tried typing "KBT" it ran very slowly too and come up with some suggestions until it finishes by itself
Thanks
Public Class Form3
Private iService As New ItemService()
Private bindingSource1 As BindingSource = Nothing
Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
lblcodeproduct.Visible = False
TextBox1.AutoCompleteMode = AutoCompleteMode.SuggestAppend
TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
TextBox1.AutoCompleteCustomSource.AddRange(iService.GetByCodeProduct().Select(Function(n) n.CodeProduct).ToArray())
End Sub
Private Sub GetItemData2(ByVal iditem As String)
Dim item = iService.GetByCodeProductOrBarcode(iditem)
If item IsNot Nothing Then
If String.Equals(iditem, item.CodeProduct, StringComparison.CurrentCultureIgnoreCase) Then
TextBox1.Text = item.CodeProduct
End If
TextBox2.Text = item.Barcode
Else
TextBox2.Clear()
Return
End If
End Sub
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
If TextBox1.Text = "" Then
lblcodeproduct.Visible = False
ErrorProvider1.SetError(TextBox1, "")
Else
bindingSource1 = New BindingSource With {.DataSource = New BindingList(Of Stock)(CType(iService.GetByCodeProductlike(TextBox1.Text), IList(Of Stock)))}
If bindingSource1.Count > 0 Then
lblcodeproduct.Visible = False
ErrorProvider1.SetError(TextBox1, "")
Else
lblcodeproduct.Visible = True
End If
GetItemData2(TextBox1.Text)
End If
End Sub
End Class
Public Class Stock
Public Property Id() As Integer
Public Property CodeProduct() As String
Public Property Barcode() As String
End Class
Public Class ItemService
Public Function GetOledbConnectionString() As String
Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIAL.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 GetByCodeProduct() As IEnumerable(Of Stock)
Dim sql = "SELECT CodeProduct AS CodeProduct FROM Items"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Stock)(sql).ToList()
End Using
End Function
Public Function GetByCodeProductlike(ByVal CodeProduct As String) As IEnumerable(Of Stock)
Dim sql = $"SELECT CodeProduct FROM Items WHERE CodeProduct LIKE '%" & CodeProduct & "%'"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Stock)(sql).ToList()
End Using
End Function
Public Function GetByCodeProductOrBarcode(ByVal code As String) As Stock
Dim sql = $"SELECT * FROM Items WHERE CodeProduct = '{code}' or Barcode = '{code}'"
Using _conn = New OleDbConnection(GetOledbConnectionString())
Return _conn.Query(Of Stock)(sql).FirstOrDefault()
End Using
End Function
End Class
RESULT AUTOCOMPLETE TEXTBOX
You have recursive calls to the TextChanged event because GetItemData2 might change TextBox1. I have marked the changes I made with '<<<<<<<<<<<<<<<<<<<<<<<<<.
EDITED - see comments
Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
Static recursive As Boolean = False '<<<<<<<<<<<<<<<<<<<<<<<<<
If recursive Then Exit Sub '<<<<<<<<<<<<<<<<<<<<<<<<<
recursive = True '<<<<<<<<<<<<<<<<<<<<<<<<<
If TextBox1.Text = "" Then
lblcodeproduct.Visible = False
ErrorProvider1.SetError(TextBox1, "")
recursive = False '<<<<<<<<<<<<<<<<<<<<<<<<<
Else
Try '<<<<<<<<<<<<<<<<<<<<<<<<<
bindingSource1 = New BindingSource With {.DataSource = New BindingList(Of Stock)(CType(iService.GetByCodeProductlike(TextBox1.Text), IList(Of Stock)))}
If bindingSource1.Count > 0 Then
lblcodeproduct.Visible = False
ErrorProvider1.SetError(TextBox1, "")
Else
lblcodeproduct.Visible = True
End If
' the following method might change TextBox1
' which results in the .TextChanged event being called again
' the changes mean that second call stops the processing of the .TextChanged event
GetItemData2(TextBox1.Text)
Finally '<<<<<<<<<<<<<<<<<<<<<<<<<
recursive = False '<<<<<<<<<<<<<<<<<<<<<<<<<
End Try
End If
End Sub