Re: sql connection errors as usage increases

From: Doug Stoltz (NoSpam_at_MyEmail.com)
Date: 08/30/04

  • Next message: cyberstrike: "RE: XP SP2 and MDAC 2.8" 
    Date: Mon, 30 Aug 2004 16:15:28 -0400

    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
    >
    >

  • Next message: cyberstrike: "RE: XP SP2 and MDAC 2.8"

    Relevant Pages

    • Re: VBA Project Password prompt on Exit
      ... > I create an ADO Connection Object that is opens a ... > connection to the current Excel file ... Dim ws As Excel.Worksheet ... ' Save copy of worksheet to temp workbook ...
      (microsoft.public.excel.programming)
    • Re: recordset does not open
      ... > have errors and actually you have expected result, but wasting recourses ... > Otherwise, as I know, you open another connection (command object takes ... > ConnectionString property of con object and opens its own connection based ... >> Dim newitem As Object ...
      (microsoft.public.vb.database)
    • Re: Using VB to look up and insert a value.
      ... > Dim db As New ADODB.Connection ... >> The rs.Open opens the Recordset, but the connection that you have defined ... >> Connection, I believe you can skip the Set Cnxn statement, but you'll ...
      (microsoft.public.access.formscoding)
    • Re: Find out who is connected to the back-end database.
      ... of the OpenSchema metod of the connection object as this ... >A Startup event could be anything from an Autoexec macro ... >could use a dummy, hidden form that opens at startup, ... >> Dim rstOC As New ADODB.Recordset ...
      (microsoft.public.access.modulesdaovba)
    • Re: Code looping through files
      ... Sub AllFolderFiles() ... Dim TheFile As String ... in a folder, opens each, does things, closes the file ...
      (microsoft.public.excel.programming)