ASP and ADO Recordset Object

Hello,

I have an asp application that uses Sybase ASE as a backend DB. I have been
noticing ADO errors saying "Out of Memory" or
"Catastrophic Error" and then the web server will just hang up. I have
checked all my DB connections and they are all being
explictly closed.

I am using a separate include file to handle all of my DB work to return the
data. So basically, when I need to display
some data to the page I will call a function that will return an ADO
recordset from the function to the calling asp page. I am not
able to close the recordset from the function. I am also not sure if the
code after the return statement is being executed. Does anyone know?
Could it be possible that having enough of these recordsets open would
cause some
memory issues? Below is an example:

This is the code from the calling page:

Set objRsAffiliatedHosp = Select_Affiliated_Hospitals(ProviderId)

------------------------------------------------------------ ----------------
----------------------------
This is the function in the include file:

Function Select_Affiliated_Hospitals(inProviderId)
On Error Resume Next

functionName = "Select_Affiliated_Hospitals"
Set objConn = Server.CreateObject("Adodb.Connection")
myDSN="DSN=" & trim(my_site_facetsdsn) & ";uid=" &
trim(my_site_facetsuid) & ";pwd=" & trim(my_site_facetspwd)

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = myDSN
objCmd.CommandType= adCmdStoredProc
objCmd.CommandText = "EXTSP_EBNT_RTPD_AFFIL_HOSP"

Set objParameter = objCmd.CreateParameter("@In_Provider_Id",
adVarChar,AdParamInput, 15)
objCmd.Parameters.Append objParameter

objCmd.Parameters("@In_Provider_Id") = inProviderId

Set objRsHospitals = Server.CreateObject("ADODB.Recordset")
Set objRsHospitals = objCmd.Execute

If Err.number <> 0 then
Handle_Error Err, functionName
End If

Set Select_Affiliated_Hospitals = objRsHospitals
'objRsHospitals.close
'objConn.Close
'Set objConn = Nothing

'Set objRsHospitals = Nothing

End Function

----------------------------------------------------------

Thanks.

Re: ASP and ADO Recordset Object

Craig wrote:
> Hello,
>
> I have an asp application that uses Sybase ASE as a backend DB. I
> have been noticing ADO errors saying "Out of Memory" or
> "Catastrophic Error" and then the web server will just hang up. I
> have checked all my DB connections and they are all being
> explictly closed.

Actually, they aren't. See below.

>
> I am using a separate include file to handle all of my DB work to
> return the data. So basically, when I need to display
> some data to the page I will call a function that will return an ADO
> recordset from the function to the calling asp page. I am not
> able to close the recordset from the function.

I would disconnect the recordset so I could close the connection within
the function. See below:

> I am also not sure if
> the code after the return statement is being executed. Does anyone
> know?
> Could it be possible that having enough of these recordsets open
> would cause some
> memory issues? Below is an example:
>
> This is the code from the calling page:
>
> Set objRsAffiliatedHosp = Select_Affiliated_Hospitals(ProviderId)
>
> ------------------------------------------------------------ ----------
------
> ----------------------------
> This is the function in the include file:
>
> Function Select_Affiliated_Hospitals(inProviderId)
> On Error Resume Next
>
> functionName = "Select_Affiliated_Hospitals"
> Set objConn = Server.CreateObject("Adodb.Connection")
> myDSN="DSN=" & trim(my_site_facetsdsn) & ";uid=" &
> trim(my_site_facetsuid) & ";pwd=" & trim(my_site_facetspwd)
>
> Set objCmd = Server.CreateObject("ADODB.Command")
> objCmd.ActiveConnection = myDSN

It's a bad idea to use an implicit connection here (this practice can
disable connection pooling which can lead to an excessive number of
connection being opened and left open on your server, leading to the
out-of-memory problem). This may be the source of your error. ALWAYS
create and use an explicit connection object:

Set cn=CreateObject("ADODB.Connection")
cn.Open myDSN
'you might want to handle an error here ...
Set objCmd.ActiveConnection = cn


> objCmd.CommandType= adCmdStoredProc
> objCmd.CommandText = "EXTSP_EBNT_RTPD_AFFIL_HOSP"

Really?? Yuck! ;-)

>
> Set objParameter = objCmd.CreateParameter("@In_Provider_Id",
> adVarChar,AdParamInput, 15)
> objCmd.Parameters.Append objParameter
>
> objCmd.Parameters("@In_Provider_Id") = inProviderId
>
> Set objRsHospitals = Server.CreateObject("ADODB.Recordset")

objRsHospitals.CursorLocation = adUseClient

> Set objRsHospitals = objCmd.Execute
No, no, no - do this instead:
objRsHospitals.Open objCmd,,adLockOptimistic


>
> If Err.number <> 0 then
> Handle_Error Err, functionName

'now we see the value of using the explicit connection object:
cn.close:set cn=nothing
Set Select_Affiliated_Hospitals = nothing
Else
'disconnect the recordset:
set 'objRsHospitals.ActiveConnection = Nothing
cn.close: set cn=nothing
Set Select_Affiliated_Hospitals = objRsHospitals
'Don't destroy the recordset here - do it in the calling
procedure
> End If
>
'discard the rest of this function:

> Set Select_Affiliated_Hospitals = objRsHospitals
> 'objRsHospitals.close
> 'objConn.Close
> 'Set objConn = Nothing
>
> 'Set objRsHospitals = Nothing
>
> End Function
>
> ----------------------------------------------------------
>
> Thanks.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ASP and ADO Recordset Object

Thank you so much.

Craig

"Bob Barrows [MVP]" wrote in message
news:OTYgFZdfGHA.1324@TK2MSFTNGP04.phx.gbl...
> Craig wrote:
> > Hello,
> >
> > I have an asp application that uses Sybase ASE as a backend DB. I
> > have been noticing ADO errors saying "Out of Memory" or
> > "Catastrophic Error" and then the web server will just hang up. I
> > have checked all my DB connections and they are all being
> > explictly closed.
>
> Actually, they aren't. See below.
>
> >
> > I am using a separate include file to handle all of my DB work to
> > return the data. So basically, when I need to display
> > some data to the page I will call a function that will return an ADO
> > recordset from the function to the calling asp page. I am not
> > able to close the recordset from the function.
>
> I would disconnect the recordset so I could close the connection within
> the function. See below:
>
> > I am also not sure if
> > the code after the return statement is being executed. Does anyone
> > know?
> > Could it be possible that having enough of these recordsets open
> > would cause some
> > memory issues? Below is an example:
> >
> > This is the code from the calling page:
> >
> > Set objRsAffiliatedHosp = Select_Affiliated_Hospitals(ProviderId)
> >
> > ------------------------------------------------------------ ----------
> ------
> > ----------------------------
> > This is the function in the include file:
> >
> > Function Select_Affiliated_Hospitals(inProviderId)
> > On Error Resume Next
> >
> > functionName = "Select_Affiliated_Hospitals"
> > Set objConn = Server.CreateObject("Adodb.Connection")
> > myDSN="DSN=" & trim(my_site_facetsdsn) & ";uid=" &
> > trim(my_site_facetsuid) & ";pwd=" & trim(my_site_facetspwd)
> >
> > Set objCmd = Server.CreateObject("ADODB.Command")
> > objCmd.ActiveConnection = myDSN
>
> It's a bad idea to use an implicit connection here (this practice can
> disable connection pooling which can lead to an excessive number of
> connection being opened and left open on your server, leading to the
> out-of-memory problem). This may be the source of your error. ALWAYS
> create and use an explicit connection object:
>
> Set cn=CreateObject("ADODB.Connection")
> cn.Open myDSN
> 'you might want to handle an error here ...
> Set objCmd.ActiveConnection = cn
>
>
> > objCmd.CommandType= adCmdStoredProc
> > objCmd.CommandText = "EXTSP_EBNT_RTPD_AFFIL_HOSP"
>
> Really?? Yuck! ;-)
>
> >
> > Set objParameter = objCmd.CreateParameter("@In_Provider_Id",
> > adVarChar,AdParamInput, 15)
> > objCmd.Parameters.Append objParameter
> >
> > objCmd.Parameters("@In_Provider_Id") = inProviderId
> >
> > Set objRsHospitals = Server.CreateObject("ADODB.Recordset")
>
> objRsHospitals.CursorLocation = adUseClient
>
> > Set objRsHospitals = objCmd.Execute
> No, no, no - do this instead:
> objRsHospitals.Open objCmd,,adLockOptimistic
>
>
> >
> > If Err.number <> 0 then
> > Handle_Error Err, functionName
>
> 'now we see the value of using the explicit connection object:
> cn.close:set cn=nothing
> Set Select_Affiliated_Hospitals = nothing
> Else
> 'disconnect the recordset:
> set 'objRsHospitals.ActiveConnection = Nothing
> cn.close: set cn=nothing
> Set Select_Affiliated_Hospitals = objRsHospitals
> 'Don't destroy the recordset here - do it in the calling
> procedure
> > End If
> >
> 'discard the rest of this function:
>
> > Set Select_Affiliated_Hospitals = objRsHospitals
> > 'objRsHospitals.close
> > 'objConn.Close
> > 'Set objConn = Nothing
> >
> > 'Set objRsHospitals = Nothing
> >
> > End Function
> >
> > ----------------------------------------------------------
> >
> > Thanks.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>