asked on

Problem with unclosed Connection

ASP.NET framework 1.1 running against WinServer2003/SQL2K.

I have a page that make a number of different db calls all of them enclosed in a Try/Catch block. I'm getting an intermittant error condition that, after involved will bomb all future page hits with:

Not allowed to change the 'ConnectionString' property while the connection (state=Open).

The stack is pointing at:
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.InvalidOperationException: Not allowed to change the 'ConnectionString' property while the connection (state=Open).
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object o, Type& objType, String name, Object[] args, String[] paramnames, Boolean OptimisticSet, CallType UseCallType)
at Microsoft.VisualBasic.CompilerServices.LateBinding.LateSet(Object o, Type objType, String name, Object[] args, String[] paramnames)
at compliance4.loginGW.CreateTitles()
at compliance4.loginGW.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain()
--- End of inner exception stack trace ---
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain()
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.CallHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Which appears to point to this function:

Function CreateTitles()
Dim connStr As String = "workstation id=" & Request.ServerVariables("REMOTE_ADDR") & "; " & ConfigurationSettings.AppSettings("connStr")
p1.Value = 10159

cn.ConnectionString = connStr
Try
cn.Open()
ds = SqlHelper.ExecuteDataset(cn, CommandType.StoredProcedure, "dbo._login_GetFacilityTitles", TitleListParams)
Catch ex As SqlException
DisplaySqlErrors(ex)
Finally
cn.Close()
End Try
Return ds.Tables(0)
End Function

Any idea why the cn.Close wouldn't execute?

Raju Srivatsavaye

Try giving your connectionstring directly in the code instead of a reference

juststeve

ASKER

I have 2 different versions of what's essentially the same page - the only different being when the "CreateTitles" function is called or not. The page where it doesn't get called is the only one tossing this error.

Is there a downside to putting a test for an open connection at the beginning of the function? Closing it if it's found to be open? How would that test look?

compuken

Try cn.close() before the line cn.ConnectionString = connStr

juststeve

ASKER

Looking at this closer....

The function CreateTitles is in the Not IsPostBack section of the pageload. So it should only fire on the initial page load. ...

Can you decipher the trace stack to help me understand why there's a reference to CreateTitles at all?

Here's how it's called:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
...
lbTitle.DataSource = CreateTitles()
lbTitle.DataTextField = "strTitle"
lbTitle.DataValueField = "idTitle"
lbTitle.DataBind()
lbTitle.Items.Insert(0, "Please Choose One")
...

compuken

on the inital page load it calls CreateTitles as soon as it gets to lbTitle.DataSource = CreateTitles() line.
it looks like you should add one line to createtitles function: the cn.close function

Function CreateTitles()
Dim connStr As String = "workstation id=" & Request.ServerVariables("REMOTE_ADDR") & "; " & ConfigurationSettings.AppSettings("connStr")
p1.Value = 10159

'Close connection
cn.close()

cn.ConnectionString = connStr
Try
cn.Open()
ds = SqlHelper.ExecuteDataset(cn, CommandType.StoredProcedure, "dbo._login_GetFacilityTitles", TitleListParams)
Catch ex As SqlException
DisplaySqlErrors(ex)
Finally
cn.Close()
End Try
Return ds.Tables(0)
End Function

AGBrown

But ... if you have to close the connection at the beginning of CreateTitles, that means it was opened before CreateTitles was called, doesn't it? So my question would be, where is your connection declared, where is it instantiated (i.e. where is the line cn = new SqlConnection()) and where is it opened (where is the line cn.Open())

My second question is: as far as why CreateTitles is called. If that is the only place in your code that it is being used, then it would suggest that you are not posting back to the page each time. How are you getting to the page on the occasions that you expect it to be a postback?

Andy

juststeve

ASKER

I asked myself variations of both those...as of earlier this afternoon i thought i'd gotten to the bottom of the problem but now I wonder.

Above, i mentioned that there are 2 variations of this page that's malfunctioning. The second one's easy...it only needs to run once to populate the dropdown...after that the viewstate takes over. So yes, there's at least one postback and no, it doesn't need the CreateTitles after the initial.

In short, these are 2 login pages, one of the two present a dropdown with departmental titles (CreateTitles()) - the other functions essentially the same with without titles. After authenticating, recording the login and returning a loginID the page passing the relavent info to a Flash application. So from the perspective of ASP.NET this is really just a one page web application.

Except....

The folks that are entering thier titles have an extra button in the Flash app thats used to show them extra information depending on thier titles. If they click the 'Show Me Title Stuff' button (and only if) a very simple little popup aspx page listens to the titleID and generates a schedule for the user to review.

Turns out that I'd failed to issue the important 'cn.Close' in the Finally clause of that page. So obviously, that's the problem, right? I've been feeling relieved all afternoon.

But...

If failing to close the connection on that one page is really the cause of my problems, why wouldn't the error get tossed in the 'Non-Title' version of the page?

The connection is instantiated on each page in the Declarations as:

Public Shared cn = New System.Data.SqlClient.SqlConnection

Is it, in fact, an object shared _between_ pages? I wouldn't think so and if not, then how would failing to close the connection on one of the 3 pages effect the operation of one of the other pages ... much less; why effect only one and not both the other pages?

Point are not an object...get me to an understanding of what's going on and you can stake a claim to my first born if you want!

tx

ASKER CERTIFIED SOLUTION

AGBrown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AGBrown

PS. don't worry about points. You've given 500 points, and that's the maximum for a question - that's why I try to give a good enough answer to deserve 500 points :-)

juststeve

ASKER

And deserve you have. This it'd probably be the right point to disclose my affirmed childlessness as well. It seems very likely you've pinpointed the problem...the bit with setting the workstation was an attempt to display something akin to 'state' in SQL Profiler. I'm not at all surprised it's left teeth marks in my butt.

tx

AGBrown

*cough* I probably wouldn't know about it if I hadn't done it at some point in the past.