基于MS Access实现自动完成文本框联动显示对应Rate值

需求说明 #

• Textbox1基于Access数据库Stock表的Description字段实现自动完成功能
• Textbox1触发Leave事件（失去焦点）时，Textbox2显示对应记录的Rate字段值

现有自动完成代码（Form_Load事件） #

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\MSDB.accdb"
Dim cmd As New OleDbCommand("Select Description from Stock", con)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds)
Dim column1 As New AutoCompleteStringCollection
Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
    column1.Add(ds.Tables(0).Rows(i)("Description").ToString())
Next
TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
TextBox1.AutoCompleteCustomSource = column1
TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest

实现Leave事件逻辑（TextBox1_Leave事件） #

步骤1：绑定Leave事件 #

在窗体设计器中双击Textbox1，或手动添加TextBox1_Leave事件处理方法。

步骤2：编写查询代码 #

Private Sub TextBox1_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
    ' 空值判断，避免无效查询
    If String.IsNullOrWhiteSpace(TextBox1.Text) Then
        TextBox2.Clear()
        Return
    End If

    ' 参数化查询，防止SQL注入
    Dim query As String = "SELECT Rate FROM Stock WHERE Description = @Description"
    
    Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\MSDB.accdb")
        Using cmd As New OleDbCommand(query, con)
            cmd.Parameters.AddWithValue("@Description", TextBox1.Text)
            
            Try
                con.Open()
                Dim result As Object = cmd.ExecuteScalar()
                
                If result IsNot DBNull.Value Then
                    TextBox2.Text = result.ToString()
                Else
                    TextBox2.Clear()
                End If
            Catch ex As Exception
                MessageBox.Show("查询出错：" & ex.Message)
            End Try
        End Using
    End Using
End Sub

关键注意点 #

• 参数化查询：避免SQL注入风险，同时兼容特殊字符
• 资源自动释放：使用Using语句自动回收数据库连接和命令对象
• 异常处理：捕获连接失败、查询错误等异常情况
• 空值处理：无匹配记录时清空Textbox2，避免显示无效内容

性能优化方案（缓存数据） #

如果数据库记录较多，可在窗体加载时缓存Description与Rate的对应关系，减少数据库访问次数：

' 窗体类全局字典，存储描述与费率的映射
Private descriptionRateMap As New Dictionary(Of String, String)

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\MSDB.accdb"
    Dim cmd As New OleDbCommand("Select Description, Rate from Stock", con)
    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataSet
    da.Fill(ds)
    
    Dim column1 As New AutoCompleteStringCollection
    For Each row As DataRow In ds.Tables(0).Rows
        Dim desc As String = row("Description").ToString()
        column1.Add(desc)
        ' 避免重复键，仅存储第一条匹配记录
        If Not descriptionRateMap.ContainsKey(desc) Then
            descriptionRateMap(desc) = row("Rate").ToString()
        End If
    Next
    
    TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
    TextBox1.AutoCompleteCustomSource = column1
    TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
End Sub

' 优化后的Leave事件
Private Sub TextBox1_Leave(sender As Object, e As EventArgs) Handles TextBox1.Leave
    If String.IsNullOrWhiteSpace(TextBox1.Text) Then
        TextBox2.Clear()
        Return
    End If
    
    If descriptionRateMap.TryGetValue(TextBox1.Text, Dim rate As String) Then
        TextBox2.Text = rate
    Else
        TextBox2.Clear()
    End If
End Sub

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