VB考试项目启动报错：无法关闭SQL连接，连接状态为打开

错误原因 #

启动时出现Connection could not be closed, connection state is open错误，核心问题是重复手动管理数据库连接状态：

• 在Form1_Load中先打开连接，随后调用OgrenciBilgileriniGetir()，该方法内再次尝试打开同一个连接，且在自身的Finally块中关闭了连接；
• 回到Form1_Load的Finally块时，连接已经处于关闭状态，再次执行Close()操作触发错误。
• 此外，SqlDataAdapter的Fill方法会自动处理连接的打开与关闭（若连接未打开，Fill会临时打开并执行后关闭；若已打开则保持状态），手动管理反而容易引发状态冲突。

修复后的代码 #

Imports System.Data.SqlClient

Public Class Form1
    Private OgrenciBilgileriSqlDA As SqlDataAdapter
    Private CengizhanOkulVTDS As New DataSet() ' 补充初始化DataSet
    Private OgrenciTabloBS As New BindingSource() ' 补充初始化BindingSource

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            Dim Sorgu As String = "SELECT * FROM OgrenciTablo"
            ' 直接初始化SqlDataAdapter，无需手动打开连接
            OgrenciBilgileriSqlDA = New SqlDataAdapter(Sorgu, SqlBaglantiCumlesi())

            ' 自动生成增删改命令
            Dim SqlKomutOlusturucu As New SqlCommandBuilder(OgrenciBilgileriSqlDA)

            ' 填充DataSet，SqlDataAdapter会自动处理连接
            OgrenciBilgileriSqlDA.Fill(CengizhanOkulVTDS, "OgrenciTablo")
            OgrenciTabloBS.DataSource = CengizhanOkulVTDS.Tables("OgrenciTablo")
            ' 假设DataGridView名为dgvOgrenci，绑定数据源
            ' dgvOgrenci.DataSource = OgrenciTabloBS
        Catch ex As Exception
            MessageBox.Show($"Hata Oluştu.Lütfen Sistem Yöneticiniz ile bağlantı kurunuz. Hata: {ex.Message}", "Hata")
        End Try
    End Sub

    Private Sub btnKaydet_Click(sender As Object, e As EventArgs) Handles btnKaydet.Click
        VTyeKaydet()
    End Sub

    Private Sub btnSil_Click(sender As Object, e As EventArgs) Handles btnSil.Click
        If OgrenciTabloBS.Current Is Nothing Then Return
        Dim Satir As DataRowView = DirectCast(OgrenciTabloBS.Current, DataRowView)
        Dim Mesaj As String = String.Format("{0} {1} isimli Öğrenciyi silmek istediğinize emin misiniz?", Satir("Adi"), Satir("Soyadi"))
        If MessageBox.Show(Mesaj, "Uyarı", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = DialogResult.Yes Then
            OgrenciTabloBS.RemoveCurrent()
            VTyeKaydet()
        End If
    End Sub

    Private Sub VTyeKaydet()
        Try
            Me.Validate()
            OgrenciTabloBS.EndEdit()
            Dim etkilenenSatir As Integer = OgrenciBilgileriSqlDA.Update(CengizhanOkulVTDS, "OgrenciTablo")
            If etkilenenSatir > 0 Then
                MessageBox.Show("Yapılan tüm değişiklikler başarı ile kaydedildi.", "Bilgi")
                ' ButonlariKullan() fonksiyonunu burada çağırın
            End If
        Catch ex As Exception
            MessageBox.Show($"Hata oluştu. Hata: {ex.Message}", "Hata")
        End Try
    End Sub

    ' 重构数据加载方法，移除手动连接管理
    Private Sub OgrenciBilgileriniGetir()
        Try
            If CengizhanOkulVTDS.Tables.Contains("OgrenciTablo") Then
                CengizhanOkulVTDS.Tables("OgrenciTablo").Clear()
            End If
            ' SqlDataAdapter自动处理连接
            OgrenciBilgileriSqlDA.Fill(CengizhanOkulVTDS, "OgrenciTablo")
            OgrenciTabloBS.DataSource = CengizhanOkulVTDS.Tables("OgrenciTablo")
        Catch ex As Exception
            MessageBox.Show($"Hata oluştu. Hata: {ex.Message}", "Hata")
        End Try
    End Sub

    ' 补充连接字符串获取方法（假设原SqlBaglantiCumlesi()是此方法）
    Private Function SqlBaglantiCumlesi() As String
        ' 返回你的SQL Server连接字符串，例如：
        Return "Data Source=SUNUCU_ADI;Initial Catalog=VERITABANI_ADI;Integrated Security=True;"
    End Function
End Class

关键改动说明 #

• 移除了全局SqlConnection变量，改为在SqlDataAdapter初始化时直接传入连接字符串，由SqlDataAdapter自动管理连接生命周期；
• 删除了所有手动Open()和Close()连接的代码，避免状态冲突；
• 补充了DataSet和BindingSource的初始化（原代码中未显式初始化，会引发空引用错误）；
• 优化了空值判断（如删除操作前检查当前行是否存在）；
• 简化了CommandBuilder的使用，无需手动赋值增删改命令，CommandBuilder会自动关联到DataAdapter。

内容的提问来源于stack exchange，提问作者Cengizhan Şahin