CODEWITHSUNDEEP
vb.net
user2550171
user2550171

Reputation: 23

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

I am creating a connection to my database in SQL Server 2008 Management Studio edition from Visual Studio 2008.

I stored a procedure called "CTable" in my database and I want to execute it (from Visual Studio).

This is my code:

    Dim strConn As String
    strConn = "Server=(local);Database=dbAjout;Integrated Security=True"

    Dim MyConn As New SqlConnection(strConn)
    MyConn.Open()
    Dim cmd As SqlCommand

    Dim query As String = "EXEC CTable"

    cmd = New SqlCommand(query, MyConn)

    MyConn.Open()
    cmd.ExecuteNonQuery()
    MyConn.Close()
End Sub

but I have this error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information:

The connection was not closed. The connection's current state is open.

What do you suggest to fix this?

Upvotes: 2

Views: 40779

Answers (2)

Karl Anderson
Karl Anderson

Reputation: 34844

Change your code to this:

Dim strConn As String
strConn = "Server=(local);Database=dbAjout;Integrated Security=True"

Dim MyConn As New SqlConnection(strConn)
Dim cmd As SqlCommand

Dim query As String = "EXEC CTable"

cmd = New SqlCommand(query, MyConn)

MyConn.Open()
cmd.ExecuteNonQuery()
MyConn.Close()

You should consider the invoking the Dispose pattern for your SqlConnection by utilizing the Using statement, like this:

Dim strConn As String
strConn = "Server=(local);Database=dbAjout;Integrated Security=True"

Using MyConn As New SqlConnection(strConn)
    Dim cmd As SqlCommand
    Dim query As String = "EXEC CTable"

    cmd = New SqlCommand(query, MyConn)

    MyConn.Open()
    cmd.ExecuteNonQuery()
End Using

Note: The Using statement can be applied to any object that implements the IDisposable interface. In this case it will automatically close the SqlConnection object by calling the Close method for you. Behind the scenes, the VB.NET compiler will see the Using statement and wrap the Using block in a Try block and give it a Finally block, which is where the SqlConnection's Close method will be invoked on your behalf. This syntax makes the code easier to read in my opinion, easier to maintain as you can see everything that is contained within the connection object and removes the need to 'remember' to close the underlying database connection.

Upvotes: 2

Styxxy
Styxxy

Reputation: 7517

You open your connection twice. As per documentation (SqlConnection.Open()):

Exceptions
InvalidOperationException
- Cannot open a connection without specifying a data source or server.
- or
- The connection is already open.

That is (probably) what causes the InvalidOperationException to be thrown.

Upvotes: 4

Related Questions