如何修复VB.NET中“读取器关闭时调用MetaData无效”错误

开发搜索系统时遇到以下异常：点击Button1设置ComboBox2.Text = sqldr.Item("id_symptom")时，触发**"Invalid attempt to call MetaData when reader is closed"**错误。系统需求为：

• 点击按钮时从数据库填充文本框与组合框；
• 组合框选项变更时自动填充关联文本框。

相关对象说明：

• sqlconn：SqlConnection实例
• sqldr：全局SqlDataReader实例
• textbox1：填充ID
• combobox1关联textbox2，combobox2关联textbox3
• textbox4：搜索栏，combobox3：搜索分类
• koneksi.connect()：数据库连接模块方法

代码片段：

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        koneksi.connect()
        strsql = "SELECT disease_name FROM tb_disease where id_disease='" & ComboBox1.SelectedItem & "'"
        sqlcmd = New SqlCommand(strsql, sqlconn)
        sqldr = sqlcmd.ExecuteReader
        sqldr.Read()
        If sqldr.HasRows Then
            TextBox3.Text = sqldr.Item("disease_name")
        End If
        sqldr.Close()
    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
        koneksi.connect()
        strsql = "SELECT symptom_name FROM tb_gejala where id_symptom='" & ComboBox2.SelectedItem & "'"
        sqlcmd = New SqlCommand(strsql, sqlconn)
        sqldr = sqlcmd.ExecuteReader
        TextBox5.Text = ""
        If sqldr.HasRows Then
            TextBox5.Text = sqldr.Item("symptom_name")
        End If
        sqldr.Close()
    End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If ComboBox3.Text = "Search Category:" Then
            MsgBox("Choose The Search Method First")
        ElseIf ComboBox3.Text = "ID RULE" Then
            sqlcmd = New SqlCommand("SELECT * FROM tb_rule WHERE id_rule Like '%" & TextBox2.Text & "%'", sqlconn)
            sqldr = sqlcmd.ExecuteReader()
            sqldr.Read()
            If sqldr.HasRows Then
                TextBox1.Text = sqldr.Item("id_rule")
                ComboBox1.Text = sqldr.Item("id_disease")
                ComboBox2.Text = sqldr.Item("id_symptom") ' 触发错误的代码行
            Else
                MsgBox("No Data Match To The Following ID")
            End If
            sqldr.Close()
        End If
    End Sub

已尝试无效方案：

• 移除任意ComboBox的SelectedIndexChanged事件，对应文本框无法填充数据；
• 移除Button1_Click中设置ComboBox2.Text的代码，ComboBox2为空。

全局共享的sqldr实例被多个事件冲突使用：

1. Button1_Click中打开全局sqldr读取数据；
2. 设置ComboBox2.Text时立即触发ComboBox2_SelectedIndexChanged事件；
3. 该事件重新给全局sqldr赋值为新的SqlDataReader并执行sqldr.Close()；
4. 此时Button1_Click中原来的sqldr已被关闭，后续访问元数据时触发错误。

同时代码存在SQL注入风险，直接拼接用户输入到SQL语句中存在安全隐患。

1. 使用局部SqlDataReader实例，避免全局冲突 #

将每个事件中的sqldr改为局部变量，确保各事件的读取操作独立不干扰：

修改ComboBox1_SelectedIndexChanged：

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    koneksi.connect()
    Dim strsql As String = "SELECT disease_name FROM tb_disease where id_disease=@id_disease"
    Using sqlcmd As New SqlCommand(strsql, sqlconn)
        sqlcmd.Parameters.AddWithValue("@id_disease", ComboBox1.SelectedItem)
        Using sqldr As SqlDataReader = sqlcmd.ExecuteReader()
            If sqldr.Read() Then
                TextBox3.Text = sqldr.Item("disease_name").ToString()
            End If
        End Using
    End Using
End Sub

修改ComboBox2_SelectedIndexChanged：

Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
    koneksi.connect()
    Dim strsql As String = "SELECT symptom_name FROM tb_gejala where id_symptom=@id_symptom"
    Using sqlcmd As New SqlCommand(strsql, sqlconn)
        sqlcmd.Parameters.AddWithValue("@id_symptom", ComboBox2.SelectedItem)
        Using sqldr As SqlDataReader = sqlcmd.ExecuteReader()
            TextBox5.Text = ""
            If sqldr.Read() Then
                TextBox5.Text = sqldr.Item("symptom_name").ToString()
            End If
        End Using
    End Using
End Sub

修改Button1_Click：

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    If ComboBox3.Text = "Search Category:" Then
        MsgBox("Choose The Search Method First")
        Return
    End If

    If ComboBox3.Text = "ID RULE" Then
        koneksi.connect()
        Dim strsql As String = "SELECT * FROM tb_rule WHERE id_rule Like @id_rule"
        Using sqlcmd As New SqlCommand(strsql, sqlconn)
            sqlcmd.Parameters.AddWithValue("@id_rule", "%" & TextBox2.Text & "%")
            Using sqldr As SqlDataReader = sqlcmd.ExecuteReader()
                If sqldr.Read() Then
                    ' 先读取数据到局部变量
                    Dim idRule = sqldr.Item("id_rule").ToString()
                    Dim idDisease = sqldr.Item("id_disease").ToString()
                    Dim idSymptom = sqldr.Item("id_symptom").ToString()

                    ' 临时移除事件处理，避免重复触发数据库查询
                    RemoveHandler ComboBox1.SelectedIndexChanged, AddressOf ComboBox1_SelectedIndexChanged
                    RemoveHandler ComboBox2.SelectedIndexChanged, AddressOf ComboBox2_SelectedIndexChanged

                    TextBox1.Text = idRule
                    ComboBox1.Text = idDisease
                    ComboBox2.Text = idSymptom

                    ' 恢复事件处理
                    AddHandler ComboBox1.SelectedIndexChanged, AddressOf ComboBox1_SelectedIndexChanged
                    AddHandler ComboBox2.SelectedIndexChanged, AddressOf ComboBox2_SelectedIndexChanged
                Else
                    MsgBox("No Data Match To The Following ID")
                End If
            End Using
        End Using
    End If
End Sub

2. 关键优化说明 #

• 使用Using块自动释放SqlCommand和SqlDataReader资源，无需手动调用Close()；
• 采用参数化查询彻底避免SQL注入漏洞；
• 在Button1_Click中先读取数据到局部变量，再临时移除事件处理程序，减少不必要的数据库查询触发。

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