VB.Net项目点击添加按钮触发SqlConnection InvalidOperationException问题求助

兄弟，我帮你排查了下代码，你遇到的The connection is already open异常，核心问题出在全局复用的数据库连接对象没有被正确管理：

• 你在类级别定义了SQLConnection，所有增删改查操作都共用这个对象。当你点击添加按钮时直接调用SQLConnection.Open()，但如果之前的操作（比如第一次添加后的load()方法、或者异常导致连接没关闭）已经让连接处于打开状态，这时候再调用Open()就会触发异常。
• 另外还有个隐藏坑：你的SQL语句里用了Group作为列名，这是MySQL的保留关键字，直接使用会导致语法错误，这个问题虽然没触发当前异常，但后续肯定会出问题。

我给你调整了代码，主要做了这几个关键优化：

1. 用Using语句自动管理数据库资源 #

每次数据库操作都创建新的连接/命令，并用Using包裹。Using会在代码块结束时自动关闭并释放资源，不管有没有异常，彻底避免连接状态混乱的问题。

2. 修正SQL关键字冲突 #

把列名Group用反引号`包裹，解决MySQL语法错误问题。

3. 补充异常调试信息 #

空的Catch块会隐藏错误细节，现在改成弹出提示框显示异常信息，方便你定位问题。

调整后的完整代码 #

Imports MySql.Data.MySqlClient
Public Class Form1
    Dim ServerString As String = "Server = localhost;User Id =root;Password=;Database=library"

    Sub load()
        ' 使用Using管理连接，自动释放资源
        Using SQLConnection As New MySqlConnection(ServerString)
            Dim query As String = "SELECT * FROM books"
            Dim adpt As New MySqlDataAdapter(query, SQLConnection)
            Dim ds As New DataSet()
            adpt.Fill(ds, "EMP")
            DataGridView1.DataSource = ds.Tables(0)
        End Using

        ' 补充清空所有输入框
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()
        TextBox5.Clear()
        TextBox6.Clear()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        load()
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim gridrow As DataGridViewRow = DataGridView1.CurrentRow
        Try
            TextBox1.Text = gridrow.Cells(0).Value.ToString()
            TextBox5.Text = gridrow.Cells(1).Value.ToString()
            TextBox2.Text = gridrow.Cells(2).Value.ToString()
            TextBox3.Text = gridrow.Cells(3).Value.ToString()
            TextBox4.Text = gridrow.Cells(4).Value.ToString()
            TextBox6.Text = gridrow.Cells(5).Value.ToString()
        Catch ex As Exception
            MessageBox.Show("加载单元格数据失败：" & ex.Message)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Using SQLConnection As New MySqlConnection(ServerString)
                SQLConnection.Open()
                Using cmd As MySqlCommand = SQLConnection.CreateCommand()
                    ' Group是MySQL关键字，用反引号包裹
                    cmd.CommandText = "INSERT INTO Books(`Group`,Book_Name,Publisher,Author,Publishing_Year)VALUES(@Group,@Book_Name,@Publisher,@Author,@Publishing_Year);"
                    cmd.Parameters.AddWithValue("@Group", TextBox5.Text)
                    cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text)
                    cmd.Parameters.AddWithValue("@Publisher", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@Author", TextBox4.Text)
                    cmd.Parameters.AddWithValue("@Publishing_Year", TextBox6.Text)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            load()
            MessageBox.Show("添加成功！")
        Catch ex As Exception
            MessageBox.Show("添加失败：" & ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Using SQLConnection As New MySqlConnection(ServerString)
                SQLConnection.Open()
                Using cmd As MySqlCommand = SQLConnection.CreateCommand()
                    cmd.CommandText = "update Books set `Group`=@Group, Book_Name=@Book_Name, Publisher=@Publisher, Author=@Author, Publishing_Year=@Publishing_Year where Book_ID=@Book_ID ;"
                    cmd.Parameters.AddWithValue("@Book_ID", TextBox1.Text)
                    cmd.Parameters.AddWithValue("@Group", TextBox5.Text)
                    cmd.Parameters.AddWithValue("@Book_Name", TextBox2.Text)
                    cmd.Parameters.AddWithValue("@Publisher", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@Author", TextBox4.Text)
                    cmd.Parameters.AddWithValue("@Publishing_Year", TextBox6.Text)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            load()
            MessageBox.Show("更新成功！")
        Catch ex As Exception
            MessageBox.Show("更新失败：" & ex.Message)
        End Try
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Try
            Using SQLConnection As New MySqlConnection(ServerString)
                SQLConnection.Open()
                Using cmd As MySqlCommand = SQLConnection.CreateCommand()
                    cmd.CommandText = "DELETE FROM Books WHERE Book_ID=@Book_ID;"
                    cmd.Parameters.AddWithValue("@Book_ID", TextBox1.Text)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
            TextBox1.Clear()
            load()
            MessageBox.Show("删除成功！")
        Catch ex As Exception
            MessageBox.Show("删除失败：" & ex.Message)
        End Try
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Me.Close()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        ' 修正索引越界问题
        If DataGridView1.CurrentRow.Index < DataGridView1.Rows.Count - 1 Then
            DataGridView1.Rows(DataGridView1.CurrentRow.Index + 1).Selected = True
        End If
    End Sub
End Class

额外小提示 #

• 永远不要复用全局的数据库连接对象，ADO.NET的连接池会自动管理连接复用，每次创建新连接的开销非常小，但能避免大量状态问题。
• 一定要处理异常并输出详细信息，空Catch块会让你完全摸不着出错的原因。
• 遇到MySQL关键字作为列名/表名时，记得用反引号`包裹。

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