How to Use TextBox Event Changed so that it does not always appear messagebox when typing in vb.net

Question

How to Use TextBox Event Changed so that it does not always appear messagebox when typing in vb.net.

Actually I use the textbox changed event if it does not match from the database then a messagebox will appear whether there is something wrong with my code. Please Guide me.

Thanks

 Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\demo.accdb;Persist Security Info=False;"
    Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim acsCustomers As AutoCompleteStringCollection = New AutoCompleteStringCollection()
        For Each dr As DataRow In Me.GetData().Rows
            acsCustomers.Add(dr("CODEPRODUCT").ToString())
        Next
        TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
        TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
        TextBox1.AutoCompleteCustomSource = acsCustomers
    End Sub
    Private Function GetData() As DataTable
        Dim conString As String = connectionString
        Dim query As String = "select CODEPRODUCT from Items"
        Using con As OleDbConnection = New OleDbConnection(conString)
            Using cmd As OleDbCommand = New OleDbCommand(query, con)
                Using sda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                    Using dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        Return dt
                    End Using
                End Using
            End Using
        End Using
    End Function

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        Dim rows As DataRow() = Me.GetData().Select("CODEPRODUCT='" & TextBox1.Text.Trim() & "'")
        If rows.Length = 0 Then
            MessageBox.Show("Please Select From List Only")
        End If
    End Sub

Answer 1

Several points here to consider.

First of all, the GetData() function returns a disposed object because you are using the Using statement to create it. What's the point of returning a disposed object, ready to be collected and destroyed?

You should have instead:

Private Function GetData() As DataTable
    Dim conString As String = connectionString
    Dim query As String = "SELECT CODEPRODUCT FROM Items"
    Dim dt As New DataTable()

    Using con As OleDbConnection = New OleDbConnection(conString),
        cmd As OleDbCommand = New OleDbCommand(query, con),
        sda As OleDbDataAdapter = New OleDbDataAdapter(cmd)
        sda.Fill(dt)
    End Using

    Return dt
End Function

Second of all, you shouldn't validate while the text is changing and execute database queries that much. Get the input as a whole and validate it. And speaking of the database. Look carefully at your code and you'll find that you no longer need to access it anymore to validate a product code. You have them all filled in the TextBox.AutoCompleteCustomSource collection and you can use it instead as the products source.

For example, in the Validating event:

Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim acsCustomers As New AutoCompleteStringCollection

    Using dt As DataTable = GetData()
        acsCustomers.AddRange(
            dt.AsEnumerable().
            Select(Function(r) r.Field(Of String)("CODEPRODUCT")).ToArray())
    End Using

    TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
    TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
    TextBox1.AutoCompleteCustomSource = acsCustomers
    AutoValidate = AutoValidate.EnableAllowFocusChange
End Sub

Private Sub TextBox1_Validating(sender As Object, e As CancelEventArgs) Handles TextBox1.Validating
    Dim product = TextBox1.Text.Trim()
    Dim products = TextBox1.AutoCompleteCustomSource.Cast(Of String)

    If product.Length > 0 AndAlso products.Any() AndAlso
        Not products.Contains(product, StringComparer.OrdinalIgnoreCase) Then
        MessageBox.Show("Please Select From List Only...")
        e.Cancel = True
        TextBox1.SelectAll()
        ' Or
        ' TextBox1.Clear()
    End If
End Sub

Remove the product.Length > 0 condition if the user should select something. Also, you can force validation on command key press by calling the Form.ValidateChildren method.

Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyDown
    If e.KeyCode = Keys.Enter Then
        Me.ValidateChildren()
    End If
End Sub

Use this solution for example to close the Form when the user clicks the close button. Setting Me.AutoValidate = AutoValidate.Disable is enough though if you don't have an option to cancel closing the Form.

Protected Overrides Sub WndProc(ByRef m As Message)
    If m.Msg = &H10 Then
        Dim av = Me.AutoValidate
        Me.AutoValidate = AutoValidate.Disable
        MyBase.WndProc(m)
        Me.AutoValidate = av
        Return
    End If
    MyBase.WndProc(m)
End Sub

Do note, in a data-entry Form, you should set its CancelButton property to a Cancel button whose CausesValidation property is set to False. This tells the Form to bypass the Me.ValidateChildren routine.

Side note, no need to execute a database SELECT query and fill a DataTable object to only check whether or not a product exists. Run a COUNT query instead. In other words, call your GetData method to create and fill AutoCompleteStringCollection object, and create another method to execute a COUNT command. Again, this is a side note and as I mentioned earlier, you don't need the count method in this context.