0

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Why always the system returns that message?

I have a sub:

Private Sub Initialization()    

    If myCEConnection.State = ConnectionState.Closed Then '--> *1
        Try
            myCEConnection.Open()
        Catch ex As Exception

        End Try
    End If

    Dim reader As SqlCeDataReader
    Dim myCommand As SqlCeCommand = myCEConnection.CreateCommand()

    myCommand.CommandText = "Command goes here"
    Try
        reader = myCommand.ExecuteReader() 
    Catch ex As Exception '--> *2

    End Try

    myCEConnection.Close()
End Sub

And I called that function from this sub:

Public myCEConnection As New SqlCeConnection("Connection String Goes Here")

Private Sub Report()
    If myCEConnection.State = ConnectionState.Closed Then '--> *1
        Try
            myCEConnection.Open()
        Catch ex As Exception

        End Try
    End If

    Initialization()

    myCEConnection.Close()
End Sub

When I try to set a breakpoint to the *1 line, the system returns myCEConnection.State = Open {1}. But when I continue to run the breakpoint, on line *2 I got: ExecuteReader requires an open and available Connection. The connection's current state is Closed.

enter image description here

What's wrong with the code?

Note: Every sub/function on the class always have the If myCeConnection.State = ..... part to check the connection is already open or not. Maybe this caused the problem but I'm not quite sure.

mrjimoy_05
  • 3,452
  • 9
  • 58
  • 95
  • 1
    Are you sure the connection is open? Can you recheck and verify if the connectionstate is open after the try catch clause. – Prashanth Thurairatnam May 05 '12 at 03:48
  • You're checking the state at *1 however you haven't opened it yet. How could it possibly be open before the `myCEConnection.Open()` line? And since your `Catch` doesn't do anything, how are you sure that the connection has been opened, unless you actually spit out an exception instead of simply ignoring it? – Aaron Bertrand May 05 '12 at 03:52
  • Hi, I have edited the code above, pls take a look. And yes, the connection is already open. I call it from another sub that has already open the connection. – mrjimoy_05 May 05 '12 at 03:58
  • where have you defined myCommand? (in the debugging step image you have attached) – Prashanth Thurairatnam May 05 '12 at 04:13

1 Answers1

1

You need to associate the connection and the command:

myCommand.Connection = myCEConnection

Also consider changing your code into something like this:

Dim reader As SqlCeDataReader

Using myCEConnection As New SqlCeConnection("Connection String Goes Here")
  myCEConnection.Open()
  Using myCommand As SqlCeCommand = myCEConnection.CreateCommand()
    myCommand.Connection = myCEConnection
    myCommand.CommandText = "Command goes here"
    reader = myCommand.ExecuteReader()
  End Using
End Using

You don't really need the Try-Catch'es, and my guess is the initial myCEConnection.Open() is throwing an exception, but you never see it because of the Try-Catch.

Jakob Gade
  • 12,319
  • 15
  • 70
  • 118
  • 1
    I would say yes: The Using statement is powerful and convenient, and keeping open connections around is generally a bad idea. Did you remember to associate the connection with command? I've updated my sample... – Jakob Gade May 05 '12 at 04:12