Re: sql connection errors as usage increases

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 16:47:17 -0700

I knew this was true for VB6, but I'm not so sure for .NET. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Doug Stoltz" <NoSpam@MyEmail.com> wrote in message 
news:eeSYf0sjEHA.3968@TK2MSFTNGP11.phx.gbl...
> You can verify Bills theory by removing the NEW operator on the DIM
> statement and breaking this into 2 statements, like this. Change you code
> from this:
> Dim xxxx as new xxxx
> to this:
> Dim xxxx as xxxx
> Set xxxx = new xxxx
>
> With your current logic, you don't get an error when you do something like
> this:
> dim xxxx as new xxxx
> ...
> set xxxx = nothing             ' destroys an instance
> if xxx.propertyX = 5 then     ' VB instantiates new object here
>
> **
>
> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
> news:u7MgUKiiEHA.2908@TK2MSFTNGP10.phx.gbl...
>> I suspect that you have a hole in your logic that permits a connection to
> be
>> opened but not closed as you suspect.
>>
>> -- 
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>>
>> "kkres" <kkres@discussions.microsoft.com> wrote in message
>> news:9F344E8F-A6E8-46D1-BEDF-B33A97590C47@microsoft.com...
>> >I own an ASP .NET application that uses ADO.NET to manage SQLConnections
> to
>> >a
>> > SQL Srvr 2000 database. Until recently the app has been working fine
>> > (with a very small number of users). But in recent weeks number of 
>> > users
>> > has
>> > dramatically increased, and now the site is reporting this frequent
> error:
>> >
>> > System.InvalidOperationException: Not allowed to change the
>> > 'ConnectionString'
>> > property while the connection (state=Open). at
>> > System.Data.SqlClient.SqlConnection.set_ConnectionString(Stringvalue)
>> > at ... in ...basApplication.vb:line 89
>> >
>> > It occurs as the user login and the app is creating 2 connections
> strings
>> > for use during the user's session. The code in question is essentially:
>> >
>> >    Public gobjConn As New
>> > SqlClient.SqlConnection(ConfigurationSettings.AppSettings("dsn"))
>> >    Public gobjConn2 As New
>> > SqlClient.SqlConnection(ConfigurationSettings.AppSettings("dsn"))
>> > -
>> > -
>> >    gobjConn.Close()
>> >    gobjConn2.Close()
>> > -
>> > -
>> >    gobjConn.ConnectionString = strDataSource & strDataBase & 
>> > "UID=fred;"
> &
>> > "PWD=xyz;"
>> >    gobjConn2.ConnectionString = strDataSource & strDataBase &
> "UID=fred;"
>> > &
>> > "PWD=xyz;"   (line 89)
>> >
>> >    gobjConn.Open()
>> >    gobjConn2.Open()
>> >
>> > The connection strings are identical (same server and db) and exactly
> the
>> > same (same login and pw) for all users.
>> >
>> > I had thought that the Close operations before the Opens would always
>> > assure
>> > that no connection would ever be left open.
>> >
>> > This site has been able to live with the problem by regularly 
>> > restarting
>> > IIS.
>> >
>> > What am I missing?
>> >
>> > Thanks,
>> > -- 
>> > kkres
>>
>>
>
>

Relevant Pages

  • Re: What is the difference between Dispose and Close
    ... All you have to do is look at the reflected code, dispose calls ... Dispose and Close both return the connection to the pool. ... >> William (Bill) Vaughn ... >> Please reply only to the newsgroup so that others can benefit. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Are Close and Dispose on connection objects equivilent?
    ... When I saw that my opens failed post dispose I took it to ... William (Bill) Vaughn ... >>subsequently inspect Connection properties and use the Open method again. ... >>Please reply only to the newsgroup so that others can benefit. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Updating Access data using SQL / refresh time question
    ... As a test today, for one poarticular recordset, I changed from DAO to ADO to ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ...
    (microsoft.public.vb.database)
  • Suggested filter rule for the flood of 2007-07-27 10:51 GMT
    ... preservation sweeps kinds in charge of Penny's current assembly. ... What did Karim bother the japanese underneath the unnecessary ... Better wonder revisions now or Bill will ... Betty, below movements shared and greek, hosts in connection with it, ...
    (sci.crypt)
  • Re: Updating Access data using SQL / refresh time question
    ... > I forgot to mention that the expense of repeated connection open and close ... > dim oconn as new adodb.connection ... > 'Now load the listview by looping through each RS row ... > ' Set the recordset object each time we load the listview ...
    (microsoft.public.vb.database)