连接没有关闭。连接的当前状态是open.i，使用VB.NET和SQL服务器 [英] The connection was not closed. The connection's current state is open.i using VB.NET and SQL server

问题描述

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class homestaff
    Private cn As New SqlConnection("Data Source=SUKESHANI-PC\SQLEXPRESS;Initial Catalog=CONNECT;Integrated Security=True")
    Private cmd As New SqlCommand
    Private dr As SqlDataReader
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim da As SqlDataAdapter
    Dim i As Integer
    Dim a As New OpenFileDialog

    
    Sub Clear()
        TextBox7.Clear()
        ComboBox1.Text = ""
        TextBox2.Clear()
        TextBox10.Clear()
        DateTimePicker2.Text = Now
        DateTimePicker1.Text = Now
        ComboBox2.Text = ""
        TextBox12.Clear()
        TextBox13.Clear()
        DateTimePicker3.Text = Now
        picname.Image = Nothing
        TextBox7.Focus()
    End Sub
    Sub Showrecord()
        cn.Open()
        With cmd
            .Connection = cn
            .CommandText = "select * from homestaff"
        End With
        da.SelectCommand = cmd
        dt.Clear()
        da.Fill(dt)
        dghomestaff.RowTemplate.Height = 70
        dghomestaff.DataSource = dt
        For i As Integer = 0 To dghomestaff.Columns.Count - 1
            If TypeOf dghomestaff.Columns(i) Is DataGridViewImageColumn Then
                DirectCast(dghomestaff.Columns(i), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch

            End If
        Next

        cn.Close()
    End Sub



    Private Sub Bsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bsubmit.Click
        cn.Open()
        Using cmd As New SqlClient.SqlCommand("INSERT into homestaff(emp_name,emp_type,emp_mem,emp_contact,emp_dob,emp_join,emp_id,emp_proof,emp_addr,last_date,image)values('" & TextBox7.Text & "','" & ComboBox1.SelectedItem.ToString() & "','" & TextBox2.Text & "','" & TextBox10.Text & "','" + DateTimePicker2.Value.Date + "','" + DateTimePicker1.Value.Date + "','" & ComboBox2.SelectedItem.ToString() & "','" & TextBox12.Text & "','" & TextBox13.Text & "','" + DateTimePicker3.Value.Date + "',@picname)", cn)

            cmd.Parameters.Add(New SqlClient.SqlParameter("@picname", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)
            i = cmd.ExecuteNonQuery
        End Using
        If (i > 0) Then
            MsgBox("Save record Successfully")
            Clear()
        End If
        cn.Close()
        Showrecord()
    End Sub



    Private Sub Bsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bsearch.Click
        cn.Open()
        With cmd
            .Connection = cn
            .CommandText = "select * from homestaff WHERE emp_name LIKE '%" & Tsearch.Text & "%'"
        End With
        da.SelectCommand = cmd
        dt.Clear()
        da.Fill(dt)
        dghomestaff.RowTemplate.Height = 70
        dghomestaff.DataSource = dt
        For i As Integer = 0 To dghomestaff.Columns.Count - 1
            If TypeOf dghomestaff.Columns(i) Is DataGridViewImageColumn Then
                DirectCast(dghomestaff.Columns(i), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch

            End If
        Next

        cn.Close()
    End Sub

    Private Sub btnload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnload.Click
        Dim pic1 As String
        a.Filter = Nothing
        pic1 = a.FileName
        a.ShowDialog()
        picname.Image = Image.FromFile(a.FileName)
    End Sub



    Private Sub homestaff_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmd.Connection() = cn
        Showrecord()
    End Sub

    Private Sub Bedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bedit.Click
        cn.Open()
        Using cmd As New SqlClient.SqlCommand("UPDATE homestaff SET emp_name='" & TextBox7.Text & "',emp_type='" & ComboBox1.SelectedItem.ToString() & "',emp_mem='" & TextBox2.Text & "',emp_contact='" & TextBox10.Text & "',emp_dob='" + DateTimePicker2.Value.Date + "',emp_join='" + DateTimePicker1.Value.Date + "',emp_id='" & ComboBox2.SelectedItem.ToString() & "',emp_proof='" & TextBox12.Text & "',emp_addr='" & TextBox13.Text & "',last_date='" + DateTimePicker3.Value.Date + "',image=@picname WHERE emp_code=" & dghomestaff.SelectedRows(0).Cells(0).Value, cn)
            cmd.Parameters.Add(New SqlClient.SqlParameter("@picname", SqlDbType.Image)).Value = IO.File.ReadAllBytes(a.FileName)
            i = cmd.ExecuteNonQuery
        End Using
        If (i > 0) Then
            MsgBox("Update record successfully")
            Clear()
        End If
        cn.Close()
        Showrecord()
    End Sub

    Private Sub Bdelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bdelete.Click
        cn.Open()
        With cmd
            .Connection = cn
            .CommandText = "DELETE FROM homestaff where emp_code=" & dghomestaff.SelectedRows(0).Cells(0).Value
            i = .ExecuteNonQuery
        End With
        If (i > 0) Then
            MsgBox("Delete Record Succsessfully")
            Clear()
        End If

        cn.Close()
        Showrecord()
    End Sub

    Private Sub dghomestaff_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dghomestaff.CellClick
        Try
            TextBox7.Text = dghomestaff.Item(1, e.RowIndex).Value

            ComboBox1.Text = dghomestaff.Item(2, e.RowIndex).Value
            TextBox2.Text = dghomestaff.Item(3, e.RowIndex).Value

            TextBox10.Text = dghomestaff.Item(4, e.RowIndex).Value
            DateTimePicker2.Text = dghomestaff.Item(5, e.RowIndex).Value

            DateTimePicker1.Text = dghomestaff.Item(6, e.RowIndex).Value

            ComboBox2.Text = dghomestaff.Item(7, e.RowIndex).Value
            TextBox12.Text = dghomestaff.Item(8, e.RowIndex).Value
            TextBox13.Text = dghomestaff.Item(9, e.RowIndex).Value
            DateTimePicker3.Text = dghomestaff.Item(10, e.RowIndex).Value



          
            Dim ms As New MemoryStream(changephoto(CInt(dghomestaff.SelectedCells(0).Value)))
            picname.Image = Image.FromStream(ms)


        Catch ex As Exception

        End Try

    End Sub
    Function changephoto(ByVal photo As Integer) As Byte()
        cn.Open()
        With cmd
            .Connection = cn
            .CommandText = "select image from homestaff WHERE emp_code=" & dghomestaff.SelectedRows(0).Cells(0).Value

        End With
        Dim myphoto() As Byte = CType(cmd.ExecuteScalar, Byte())
        cn.Close()
        Return myphoto
    End Function

    
   
End Class

我尝试了什么：



我试过但失败了。它再次显示同样的错误并且agin。

请帮助

What I have tried:

I tried but failed .It shows same error again and agin.
please help

推荐答案

如果收到错误消息你不明白，那么google它：在这种情况下，它会带你解释这个错误意味着什么。

简而言之，它意味着当你再次尝试打开它时，SqlConnection对象已经打开。 />
Wher？不知道。但是你可以采取一些措施来改进代码以摆脱它。

首先：停止这样做！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。使用参数化查询，或者第一个人可以通过键入文本框来删除您的数据库...



然后，使用Try..Catch。 ..最后的块。连接在Try的顶部打开，在Finally中关闭。这样，即使存在其他问题，也会始终执行关闭。

更好的是，不要使用单个常见的SqlConnection对象。相反，每次在Using块中创建一个新的SqlConnection对象，当它完成并超出范围时，它将自动关闭和处理。

If you get an error message you don't understand, then google it: in this case it takes you to a load of explanations of what this error means.
In brief, it means "the SqlConnection object was already open when you tried to open it again".
Wher? Dunno. But there are a few things you could do to improve that code to get rid of it.
Firstly though: stop doing it like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead, or the first person that comes along can delete your database just by typing in a text box...

Then, use Try..Catch...Finally blocks. The Connection is opened at the top of the Try, and closed in the Finally. That way, the close is always executed, even if there are other problems.
Better still, don;t use a single common SqlConnection object. Instead, create a new SqlConnection object each time in a Using block, and it will be automatically closed and disposed when it is done with and goes out of scope.

这篇关于连接没有关闭。连接的当前状态是open.i，使用VB.NET和SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！