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.


ljaquesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Craig YellickDatabase ArchitectCommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ljaquesAuthor Commented:
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
ljaquesAuthor Commented:
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
Craig YellickDatabase ArchitectCommented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.