CODEWITHSUNDEEP
vb.netdatagridviewconnection
user3600024
user3600024

Reputation:

"The connection was not closed the connection's current state is open"

I have a datagridview (unbound) which is populated from Access database. I have checkboxes which can be ticked and then deletes the records. I have added a sub called Update() which updates the availability of the member based on the ID deleted from the datagridview. The delete works perfectly when I comment out the Update() so I have identified the error

The connection was not closed, the connection's current state is open

To be coming from this: 

    Public Sub Update()

    For Each row As DataGridViewRow In dgvMember.Rows
        con.Open()
        If row.Cells(3).FormattedValue Then
            Using cmd As New OleDbCommand("UPDATE Members SET Available = 1 WHERE ID = " & (row.Cells(3).FormattedValue) & "", con)
                cmd.CommandType = CommandType.Text
                result = cmd.ExecuteNonQuery
            End Using
        End If
    Next
    con.Close()
End Sub

I have tried almost everything. i.e. If con.State = ConnectionState.Open Then con.Close()

A day ago it was working fine and updating the available field in my database now it keeps coming up with that error and not deleting all the records. Am I missing something? Why is it doing this?

Upvotes: 0

Views: 6043

Answers (2)

paulsm4
paulsm4

Reputation: 121809

Problem #1 - you're calling conn.open inside your loop.

Problem #2 - there's no error detection/error handling

Suggested alternative:

Public Sub Update()

try   
  con.Open()
  For Each row As DataGridViewRow In dgvMember.Rows
    If row.Cells(3).FormattedValue Then
        Using cmd As New OleDbCommand("UPDATE Members SET Available = 1 WHERE ID = " & (row.Cells(3).FormattedValue) & "", con)
            cmd.CommandType = CommandType.Text
            result = cmd.ExecuteNonQuery
        End Using
    End If
  Next
Catch ex as Exception
  MsgBox("Can't load Web page" & vbCrLf & ex.Message)
Finally
  if (con.State = ConnectionState.Open) then conn.Close
End Sub

Additional suggestion: check your DataGrid properties in the MSVS IDE and make sure you're not also opening a connection there, as well.

Upvotes: -1

yolad
yolad

Reputation: 120

I would recommend wrapping the connection in a using block:

using (SqlConnection connection = new SqlConnection(connectionString))
{
     //etc...
}

Alternatively, add a catch block to the try-finally:

conn.Open();

try
{

}
catch
{

}
finally
{
    conn.Close();
}

Upvotes: 3

Related Questions