VB.NET中如何使用TextBox TextChanged事件避免输入时频繁弹出MessageBox

问题根源 #

你的代码每次输入字符都会触发TextBox1_TextChanged事件，此时用户输入的是不完整的内容（比如输入"PROD001"时，会依次触发输入P、PR、PRO...的检查），这些不完整内容自然匹配不到数据库里的完整CODEPRODUCT值，所以每次都会弹出提示框。另外还有两个潜在问题：

• 每次TextChanged都调用GetData()查询数据库，频繁操作会造成性能浪费
• 使用字符串拼接SQL语句存在SQL注入风险

修复方案 #

方案1：改用Validating事件（推荐） #

放弃在TextChanged里验证，改用Validating事件（控件失去焦点时触发），只在用户输入完成后验证一次：

' 先在Form_Load时把AutoComplete集合保存为类级变量，避免重复查库
Private validProductCodes As AutoCompleteStringCollection

Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    validProductCodes = New AutoCompleteStringCollection()
    For Each dr As DataRow In Me.GetData().Rows
        validProductCodes.Add(dr("CODEPRODUCT").ToString())
    Next
    TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
    TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
    TextBox1.AutoCompleteCustomSource = validProductCodes
End Sub

' 替换TextChanged事件为Validating事件
Private Sub TextBox1_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles TextBox1.Validating
    Dim inputText As String = TextBox1.Text.Trim()
    ' 空输入可根据需求调整处理逻辑
    If String.IsNullOrEmpty(inputText) Then
        Return
    End If
    ' 直接用保存的集合检查，不用再查数据库
    If Not validProductCodes.Contains(inputText) Then
        MessageBox.Show("Please Select From List Only")
        e.Cancel = True ' 阻止控件失去焦点，直到输入合法内容
    End If
End Sub

方案2：优化TextChanged事件（保留实时提示但避免频繁弹窗） #

如果一定要实时验证，不要每次都查数据库，且只在用户停止输入短时间后再验证，避免每输入一个字符就弹窗：

Private validProductCodes As AutoCompleteStringCollection
Private WithEvents delayTimer As New Timer()

Private Sub Form7_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    validProductCodes = New AutoCompleteStringCollection()
    For Each dr As DataRow In Me.GetData().Rows
        validProductCodes.Add(dr("CODEPRODUCT").ToString())
    Next
    TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
    TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
    TextBox1.AutoCompleteCustomSource = validProductCodes
    
    delayTimer.Interval = 500 ' 设置0.5秒延迟，用户停止输入后再验证
    delayTimer.Enabled = False
End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    delayTimer.Stop()
    delayTimer.Start()
End Sub

Private Sub delayTimer_Tick(sender As Object, e As EventArgs) Handles delayTimer.Tick
    delayTimer.Stop()
    Dim inputText As String = TextBox1.Text.Trim()
    If String.IsNullOrEmpty(inputText) Then
        Return
    End If
    If Not validProductCodes.Contains(inputText) Then
        MessageBox.Show("Please Select From List Only")
    End If
End Sub

额外修复：解决SQL注入问题（原代码的隐患） #

如果一定要用数据库查询验证，必须使用参数化查询，避免SQL注入：

Private Function IsProductCodeExists(code As String) As Boolean
    If String.IsNullOrEmpty(code) Then
        Return False
    End If
    Dim conString As String = connectionString
    Dim query As String = "SELECT COUNT(*) FROM Items WHERE CODEPRODUCT = ?"
    Using con As OleDbConnection = New OleDbConnection(conString)
        Using cmd As OleDbCommand = New OleDbCommand(query, con)
            cmd.Parameters.AddWithValue("@code", code)
            con.Open()
            Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())
            Return count > 0
        End Using
    End Using
End Function

内容的提问来源于stack exchange，提问作者user22579796