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:
sqlCmd.CommandText="INSERT INTO MyProperty...; SELECT CAST(scope_identity() AS int)";
int newID = (int)sqlCmd.ExecuteScalar();
sqlCmd.CommandText = "INSERT INTO Rental ...";
int result = (int)sqlCmd.ExecuteNonQuery();
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.