Error message when you execute the ChangeDatabase method of a Connection object: "ChangeDatabase requires an open Connection" (318137)


The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework)
  • Microsoft ADO.Net 2.0
  • Microsoft Visual Studio .NET (2002), Professional Edition
This article was previously published under Q318137

SYMPTOMS

When you execute the ChangeDatabase method of a Connection object, you may receive the following error message:
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ChangeDatabase requires an open Connection. The connection's current state is Open.
If you are using Microsoft ADO.NET 2.0 in Microsoft Visual Studio 2005, you receive the following error message:

There is already an open DataReader associated with this Command which must be closed first.

CAUSE

This error occurs because the DataReader object uses the Connection object exclusively. If the DataReader is still open, you cannot carry out any commands for the connection, including the ChangeDatabase method.

RESOLUTION

To resolve this problem, make sure that you close the DataReader before you call the ChangeDatabase method.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to reproduce the behavior

  1. Start Visual Studio .NET.
  2. Create a new Windows Application project in Visual Basic .NET. Form1 is added to the project by default.
  3. Place a Button control on Form1. Change the Name property of the button to btnTest.
  4. Add the following code to the "General Declarations" section of Form1 so that you are not required to qualify declarations in this namespace later in your code:
    Imports System.Data.SqlClient
  5. Add the following code in the btnTest_Click event:
            Dim dr As SqlDataReader
        Dim con As New SqlConnection("server=myServer;user id=myUID;" & _
                                     "password=myPWD;database=northwind")
        con.Open()
        Dim cmd As New SqlCommand("select * from customers", con)
        dr = cmd.ExecuteReader()
        While dr.Read()
            'process data
        End While
        con.ChangeDatabase("pubs")
        dr.Close()
        con.Close()
  6. Modify the connection string as appropriate to connect to your server.
  7. Save and then run the project.
  8. Click btnTest. Notice that you receive the error message that is listed in the "Symptoms" section.

RESOLUTION

  1. Locate the following code in Form1: 
            con.ChangeDatabase("pubs")
        dr.Close()
    Reverse the order of these lines of code as follows:
            dr.Close()
        con.ChangeDatabase("pubs")
  2. Save and then run the project.
  3. Click btnTest. Notice that you do not receive the error message.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

308051 PRB: Output parameters are not returned when you run an ADO.NET command in Visual Basic .NET

Modification Type:MajorLast Reviewed:3/13/2006
Keywords:kbprb KB318137 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.