Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO.NET - Connection Already Open?

Posted on 2006-04-02
4
Medium Priority
?
417 Views
Last Modified: 2012-06-27
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.


0
Comment
Question by:ljaques
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 400 total points
ID: 16355276
>> Global connection, global command

The connection pooling subsystem (which is enabled by default) will take care of connection management for you. So while you should have a single, global connection STRING you should not try to recycle connection/command objects themselves.

Create a new connection and command object whenever you need one, using the most restrictive scope possible, and explicitly close them as soon as possible.  Otherwise you run the risk of getting them entangled as you are experiencing.  Connection pooling will take care of the physical connection, so you're actually only creating/disposing logical connections.

-- Craig
0
 

Author Comment

by:ljaques
ID: 16355412
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.
0
 

Author Comment

by:ljaques
ID: 16355784
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?)
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16356005
>> 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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question