Microsoft KB Archive/311544

= Error message occurs when you run commands on a command object: &quot;Unhandled exception of type 'System.InvalidOperationException'&quot; =

Article ID: 311544

Article Last Modified on 5/13/2007

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q311544



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.OleDb
 * System.Data.SqlClient



SYMPTOMS
If you run commands or call methods of the SqlCommand or OleDbCommand object, you receive the following error message if a connection is not open:

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

Additional information: ExecuteReader requires an open and available Connection (state=Closed).



MORE INFORMATION
The DataAdapter object does not require that you explicitly open a connection to run some of its methods. Therefore, you can call the Update or Fill method of the DataAdapter object when the connection is closed. The Connection object that is associated with the SELECT statement must be valid, but it does not need to be open. If you close the connection before you call Fill, the connection is opened to retrieve the data and then closed. If the connection is open before you call Fill, it remains open.

Steps to Reproduce the Behavior
 Start Microsoft Visual Studio .NET. Create a new Windows Application project in Visual Basic .NET. Form1 is added to the project by default. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not. Place two Button controls and one DataGrid control on Form1. Button1, Button2, and DataGrid1 are created by default. Change the Name property of Button1 to btnDataAdapter and the Text property to DataAdapter.

Change the Name property of Button2 to btnCommand and the Text property to Command .  Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the &quot;General Declarations&quot; section of Form1: Imports System Imports System.Data.OleDb Imports System.Data.SqlClient </li>  In the Code window, copy and paste the following code after the &quot;Windows Form Designer generated code&quot; region: Private Sub btnDataAdapter(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnDataAdapter.Click Dim myConnString As String = _ &quot;User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=myServer&quot; Dim mySelectQuery As String = _ &quot;Select * From Customers Where CustomerID Like 'A%'&quot; Dim con As New SqlConnection(myConnString) 'The code works fine even if you comment out the next line (to open the connection). con.Open Dim daCust As New SqlDataAdapter(mySelectQuery, con) Dim ds As New DataSet daCust.Fill(ds, &quot;Cust&quot;) DataGrid1.DataSource = ds   DataGrid1.DataMember = &quot;Cust&quot; End Sub

Private Sub btnCommand(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnCommand.Click Dim myConnString As String = _ &quot;User ID=sa;password=sa;Initial Catalog=Northwind;Data Source=myServer&quot; Dim mySelectQuery As String = _ &quot;Select * From Customers Where CustomerID Like 'A%'&quot; Dim con As New SqlConnection(myConnString) Dim myCommand As New SqlCommand(mySelectQuery, con) 'An exception is thrown if you comment out the next line (to open the connection). con.Open Dim myReader As SqlDataReader = myCommand.ExecuteReader While myReader.Read 'Process data. End While myReader.Close con.Close End Sub </li> Modify the connection string (myConnString) as appropriate for your environment.</li> Save your project. On the Debug menu, click Start to run your project.</li> Comment out the line of code that opens the connection. Notice that DataAdapter.Fill works as expected, but Command.ExecuteReader fails with the above-mentioned exception.</li></ol>

<div class="references_section">