Sporadic db connection error - only occurrs on live environment

[Shaun OGrady]

I get the following error message very sporadically: "The connection
cannot be used to perform this operation. It is either closed or
invalid in this context."

This error does not occur in the development environment, only the
live environment.

It will happen once or twice every hundred times when I update the
same info. And when it does occur, all I have to do is refresh the
page and the error disappears and the information gets updated, as it
should.

I've trapped the error and was able to identify that it happens at
line 5 (set cmd.ActiveConnection = conn) of the following function.

Function GetSubs(intParentId)
On error Resume Next
dim cmd
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = conn
cmd.CommandText = "SP_GET_CHILD_SUBS"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("PARENT_ID", adInteger,
adParamInput, , intParentId)
Set GetSubsections = cmd.execute
If err.number <> 0 Then
Call ReportError("GetSubsections", err.number, err.description)
End If
set cmd = nothing
On Error Goto 0
End Function

I've tested the db connection state (conn.State) when the error is
raised and the connection is open.

Does anyone have a clue as to what's going on?

[Tammy B.]

I'm not sure that you should use "set" on that statement.
set cmd.ActiveConnection = conn

when you "set" the other properties, of cmd, you don't use "set":
cmd.CommandText = "SP_GET_CHILD_SUBS"

try not using "set"

or wait, and see what ray at home has to say.


"Shaun OGrady" <shaun@greenbutton.ca> wrote in message
news:2d805e52.0307230931.4bde94d4@posting.google.c om...

> I get the following error message very sporadically: "The connection
> cannot be used to perform this operation. It is either closed or
> invalid in this context."
>
> This error does not occur in the development environment, only the
> live environment.
>
> It will happen once or twice every hundred times when I update the
> same info. And when it does occur, all I have to do is refresh the
> page and the error disappears and the information gets updated, as it
> should.
>
> I've trapped the error and was able to identify that it happens at
> line 5 (set cmd.ActiveConnection = conn) of the following function.
>
> Function GetSubs(intParentId)
> On error Resume Next
> dim cmd
> set cmd = Server.CreateObject("ADODB.Command")
> set cmd.ActiveConnection = conn
> cmd.CommandText = "SP_GET_CHILD_SUBS"
> cmd.CommandType = adCmdStoredProc
> cmd.Parameters.Append cmd.CreateParameter("PARENT_ID", adInteger,
> adParamInput, , intParentId)
> Set GetSubsections = cmd.execute
> If err.number <> 0 Then
> Call ReportError("GetSubsections", err.number, err.description)
> End If
> set cmd = nothing
> On Error Goto 0
> End Function
>
> I've tested the db connection state (conn.State) when the error is
> raised and the connection is open.
>
> Does anyone have a clue as to what's going on?

[Shaun OGrady]

Bob and Tammy, thank you for your replies.

I've tested the data connection state at the time that the error is
raised and the connection state is open.

I believe that the problem lies in a discrepancy in the IIS and/or SQL
Server settings between the production and development environment
because (as aforementioned) the error only occurrs on the production
environment.

Shaun O

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<#T0tJKdUDHA.2076@TK2MSFTNGP11.phx.gbl>...

> There is no problem using Set here, since you are setting a property that
> contains an object to another object. In this case the "set" is not
> necessary, but it is not hurting either. It definitely hasn't hurt any of
> the applications in which I've used it.
>
> The problem is the conn variable. He should check its State property before
> using it here. He may have closed it somewhere else in his code. I'm not
> even sure where it was instantiated or opened ... it must have been because
> he says it is a sporadic error.
>
> Bob Barrows
>
>
> Tammy B. wrote:

> > I'm not sure that you should use "set" on that statement.
> > set cmd.ActiveConnection = conn
> >
> > when you "set" the other properties, of cmd, you don't use "set":
> > cmd.CommandText = "SP_GET_CHILD_SUBS"
> >
> > try not using "set"
> >
> > or wait, and see what ray at home has to say.
> >
> >
> > "Shaun OGrady" <shaun@greenbutton.ca> wrote in message
> > news:2d805e52.0307230931.4bde94d4@posting.google.c om...

> >> I get the following error message very sporadically: "The connection
> >> cannot be used to perform this operation. It is either closed or
> >> invalid in this context."
> >>
> >> This error does not occur in the development environment, only the
> >> live environment.
> >>
> >> It will happen once or twice every hundred times when I update the
> >> same info. And when it does occur, all I have to do is refresh the
> >> page and the error disappears and the information gets updated, as it
> >> should.
> >>
> >> I've trapped the error and was able to identify that it happens at
> >> line 5 (set cmd.ActiveConnection = conn) of the following function.
> >>
> >> Function GetSubs(intParentId)
> >> On error Resume Next
> >> dim cmd
> >> set cmd = Server.CreateObject("ADODB.Command")
> >> set cmd.ActiveConnection = conn
> >> cmd.CommandText = "SP_GET_CHILD_SUBS"
> >> cmd.CommandType = adCmdStoredProc
> >> cmd.Parameters.Append cmd.CreateParameter("PARENT_ID", adInteger,
> >> adParamInput, , intParentId)
> >> Set GetSubsections = cmd.execute
> >> If err.number <> 0 Then
> >> Call ReportError("GetSubsections", err.number, err.description)
> >> End If
> >> set cmd = nothing
> >> On Error Goto 0
> >> End Function
> >>
> >> I've tested the db connection state (conn.State) when the error is
> >> raised and the connection is open.
> >>
> >> Does anyone have a clue as to what's going on?