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.Clos e(). 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.ExecuteNonQuer y();
}
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;Initi al 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.
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
----
try
{
sqlCmd.CommandText="INSERT
sqlCmd.Connection.Open();
int newID = (int)sqlCmd.ExecuteScalar(
.
.
.
sqlCmd.CommandText = "INSERT INTO Rental ...";
int result = (int)sqlCmd.ExecuteNonQuer
}
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;Initi
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
Application["SqlConnection
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I was fiddling around and noticed that i was setting up the connection/command objects like such:
---
private SqlCommand CreateNewDbaseConnection(p arams 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?)
---
private SqlCommand CreateNewDbaseConnection(p
{
// Create a new SQL connection and command object
SqlConnection sqlConn = new SqlConnection("Data Source=MyServer;Initial Catalog=MyDbase;Integrated
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
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
ASKER
Odd.