Link to home
Start Free TrialLog in
Avatar of ljaques
ljaques

asked on

ADO.NET - Connection Already Open?

Very strange this is.

Im using ASP.NET C# connecting to MS SQL Server 2000 database (MSDE) with the SqlConnection/Command statements.  Earlier I was able to make connections to the database to read what I needed via ExecuteScalar and ExecuteReader.  Now I've been trying to write information to the database with ExecuteScalar and ExecuteNonQuery.  SQL statments involve the INSERT INTO.

The problem Im experiencing is the error "The connection is already Open (state=Open)."  I normally setup the SqlCommand.CommandText then SqlCommand.Connection.Open() and perform the ExecuteNonQuery/Scalar then within a Try/Finally I close the connection SqlCommand.Connection.Close().  Recently however, I had to make 2 seperate subsequent queries to the database:
----
try
{
      sqlCmd.CommandText="INSERT INTO MyProperty...; SELECT CAST(scope_identity() AS int)";
      sqlCmd.Connection.Open();
      int newID = (int)sqlCmd.ExecuteScalar();
      .
      .
      .
      sqlCmd.CommandText = "INSERT INTO Rental ...";
      int result = (int)sqlCmd.ExecuteNonQuery();
}
finally
{sqlCmd.Connection.Close();}
---

Notice that i didnt bother closing the previous connection before making the ExecuteNonQuery().  I just figured the connection was open so just use it.  There is a single .Close() statement in the finally.

Besides the error message Im now getting (The connection is already open) when I try to perform a .Open(), when I check the "MyProperty" table it contains the inserted entry already.  Sometimes the entry is repeatedly inserted 5 times or more.  I can step through my code have it execute the sqlCmd.CommandText statement (I skip the .Open() statement since it cant run anyway) then have it abort from where it is or .Close() the connection, and still one or more new entries will appear in the table.

Whats going on here?  I've tried restarting WinXP, IIS, and SQL Server but the same errors appear from the get go.

BTW within my ASP.NET code I have the following added to my Global.asax file:
---
protected void Application_Start(Object sender, EventArgs e)
{
  SqlConnection sqlConn = new SqlConnection("Data Source=MyLocalServer;Initial Catalog=MyDbase;Integrated Security=SSPI;");
  SqlCommand sqlCmd = new SqlCommand();
  sqlCmd.Connection = sqlConn;

  Application["SqlConnection"]  = sqlConn;
  Application["SqlCommand"]     = sqlCmd;
}
---

When the Page_Load() function is called I create a new instance of my database class.  Within its constructor it extracts the  Application["SqlConnection"]  &   Application["SqlCommand"]  values and stores them into member variables.  As you can see I never create a new SqlConnection or SqlCommand for all new sessions coming in.  They are the same objects Application-wide.  Could this be a problem?

Thanks for any solutions.  Oh, I do I SQL Server 2000's Enterprise Manager installed if this will help you help me.

Cheers.


ASKER CERTIFIED SOLUTION
Avatar of Craig Yellick
Craig Yellick
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ljaques
ljaques

ASKER

Thanks for the info.  Will do.  But as it stands with my "entangled" connections, how do i go about untangling it?  I've tried rebooting XP and restarting IIS and SQL Server 2000 and still i get the same error message.  I've tried creating a new connection and command object within the local scope, as you said, but the error message keeps appearing.

Odd.
Avatar of ljaques

ASKER

Ok I was fiddling around and noticed that i was setting up the connection/command objects like such:
---
private SqlCommand CreateNewDbaseConnection(params string[] QueryString)
{
      // Create a new SQL connection and command object
      SqlConnection sqlConn = new SqlConnection("Data Source=MyServer;Initial Catalog=MyDbase;Integrated Security=SSPI;");
      SqlCommand sqlCmd = new SqlCommand();
      sqlCmd.Connection = sqlConn;

      if (QueryString.Length > 0)
            sqlCmd.CommandText = QueryString[0];

      return sqlCmd;
}
---
Stepping through I noticed that when the "sqlCmd.Connection = sqlConn" line is executed, the connection is automatically opened.  I wasn't expecting that to happen until i explicitly called sqlCmd.Connection.Open().  I modified my code so that the connection and command objects don't intermiggle as such.  All works now.

Is the above a bug in ADO.NET?  (sqlCmd.Connection = sqlConn and it auto-opening?)
>> Auto-opening sqlConnection

Hmm. That has not been my experience. The only way to know for sure is to create a quickie test web site with the minimal lines of code to duplicate this behavior. I will guess that in a super-simple context, the connection is not opened. The interplay between your pages and the Global.asax must be responsible.

If the connection IS opened on assignment to a command in your quickie test then, er, uhm, I dunno what to say! That's not what I have experienced.

-- Craig