• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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?
0
juststeve
Asked:
juststeve
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Raju SrivatsavayeSoftware EngineerCommented:
Try giving your connectionstring directly in the code instead of a reference
0
 
juststeveAuthor Commented:
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?
0
 
compukenCommented:
Try cn.close() before the line cn.ConnectionString = connStr
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
juststeveAuthor Commented:
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")
...
0
 
compukenCommented:
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
0
 
AGBrownCommented:
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
0
 
juststeveAuthor Commented:
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
0
 
AGBrownCommented:
Rofl. I think I'll leave your firstborn with you, I have enough on my hands nannying a working .NET application.

You must, must, must, must, must always always always always always always (enough emphasis) ensure your connections are closed before they go out of scope. The default behaviour for a database connection is that it is pooled. If you let an open connection go out of scope then your pool will fill up with useless connections.

So it looks like that was possibly your problem.

I would suggest that, in development applications, you enforce a small maximum connection pool size (say 3) as it may help show up problems like this during development. You can do this with the extra argument ";Max Pool Size=3" at the end of your connection string.

To make sure that you understand _why_ you must close the connection, have a look at:
(.NET 2.0) http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.aspx
(.NET 1.1) http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassTopic.asp

The key paragraph for .NET 2.0 is:
"If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose. They are functionally equivalent. If the connection pooling value Pooling is set to true or yes, this also releases the physical connection. You can also open the connection inside of a using block, which ensures that the connection is closed when the code exits the using block. "

There is a similar one for .NET 1.1

I think that understanding pooling would help your application performance a little as well. A connection pool is set up for every single connection string that you use. Therefore if, for example, the workstation id is different for two workstations, then that will create a new connection pool for two different workstations. I pick on workstation id as you set the workstation id to the value of the REMOTE_ADDR server variable. This will create a new connection pool for each variation on the REMOTE_ADDR variable - great if the db admin wants to know which REMOTE_ADDR people are connected, bad for performance. I set workstation id to the name of the server; there's no reason to use anything else really as far as I can see.

So your problem would have been intermittent. It would only have shown up once a given REMOTE_ADDR managed to pass across the piece of code that was missing the cn.Close() enough times to screw up your connection pool.

After all that you are going to tell me that you have set pooling to false in your connection string, aren't you ;)

A further note about pooling and connections. Instantiating the connection (cn = new SqlConnection()) does not get a connection from the pool, that only happens on cn.Open(). It is returned on cn.Close(). For performance, therefore, you should generally make sure that your Open/Close is as close to the actual execution of the database command as possible.

Andy
0
 
AGBrownCommented:
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 :-)
0
 
juststeveAuthor Commented:
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
0
 
AGBrownCommented:
*cough* I probably wouldn't know about it if I hadn't done it at some point in the past.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now