|
 |
asp_databases thread: RE: Recordset closed?
Message #1 by "Edward Hunter" <erhunter@c...> on Mon, 13 May 2002 20:24:46
|
|
strConnect gets set in an include file, and I am able to connect using it -
the problem seems to have something to do with the stored proc. the proc
returns an almost totally dynamic table where even the field names are
dynamic.
--Edward
> Where is strConnect getting set? I see the Dim for ObjConn and later
strConnect
set objConn =3D Server.CreateObject("ADODB.Connection")
objComm.ActiveConnection =3D objConn
-----Original Message-----
From: Edward Hunter [mailto:erhunter@c...]
Sent: Monday, May 13, 2002 3:13 PM
To: ASP Databases
Subject: [asp_databases] Recordset closed?
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/QA/protoQA.asp, line 54
I'm using ASP to access a stored procedure, then trying to get the data
back into a recordset object. The SP works with the parameters when
executed in enterprise manager, but when I execute *this* asp code i get
the above error?
<%
Dim ObjConn, ObjRS, objComm, objParam, strBeginMonth, strEndMonth,
strReportType, Fields
Set objComm =3D Server.CreateObject("ADODB.Command")
Set objRS =3D Server.CreateObject("ADODB.Recordset")
objComm.ActiveConnection =3D strConnect
objComm.CommandText =3D "spQAFact"
objComm.CommandType =3D adCmdStoredProc
set objParam =3D objComm.CreateParameter("vTargetCurrentMonth",
adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam =3D objComm.CreateParameter("vTargetLastMonth", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam =3D objComm.CreateParameter("vReportType", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
strBeginMonth =3D "April"
strEndMonth =3D "March"
strReportType =3D "Traffic"
objComm.Parameters("vTargetCurrentMonth") =3D strBeginMonth
objComm.Parameters("vTargetLastMonth") =3D strEndMonth
objComm.Parameters("vReportType") =3D strReportType
Set objRS =3D objComm.Execute
Response.Write "Count: " & objComm.Parameters.Count Response.Write
objComm.Parameters("vTargetCurrentMonth")
Response.Write objComm.Parameters("vTargetLastMonth")
Response.Write objComm.Parameters("vReportType")
while not objRS.EOF
Response.Write("Name")
Response.Write("UV_Rank")
Wend
Message #2 by "Drew, Ron" <RDrew@B...> on Mon, 13 May 2002 17:27:26 -0400
|
|
Test to make sure it is not timeing out...replace my code with your
names
adoCmd.CommandText =3D "sproc_RecalculateData"
adoCmd.CommandType =3D adCmdStoredProc
adoCmd.Execute
Set colErrs=3DadoConn.Errors
If adoConn.Errors.Count <> 0 then
For Each objError In colErrs
'This is the error number for a timeout.
If objError.Number=3D-2147217871 Then
adoConn.RollbackTrans
Response.Write "The query timed out before finishing.
Please try again."
timeout=3DTrue
adoConn.Errors.Clear
Exit For
End If
Next
End If
Set adoCmd =3D Nothing
If Not timeout Then
adoConn.CommitTrans
End If
adoConn.Close
Set adoConn =3D Nothing
-----Original Message-----
From: Edward Hunter [mailto:erhunter@c...]
Sent: Monday, May 13, 2002 4:25 PM
To: ASP Databases
Subject: [asp_databases] RE: Recordset closed?
strConnect gets set in an include file, and I am able to connect using
it - the problem seems to have something to do with the stored proc.
the proc
returns an almost totally dynamic table where even the field names are
dynamic.
--Edward
> Where is strConnect getting set? I see the Dim for ObjConn and later
strConnect
set objConn =3D3D Server.CreateObject("ADODB.Connection")
objComm.ActiveConnection =3D3D objConn
-----Original Message-----
From: Edward Hunter [mailto:erhunter@c...]=3D20
Sent: Monday, May 13, 2002 3:13 PM
To: ASP Databases
Subject: [asp_databases] Recordset closed?
ADODB.Recordset error '800a0e78'=3D20
Operation is not allowed when the object is closed.=3D20
/QA/protoQA.asp, line 54=3D20
I'm using ASP to access a stored procedure, then trying to get the
data=3D20 back into a recordset object. The SP works with the parameters
when=3D20 executed in enterprise manager, but when I execute *this* asp
code i get
the above error?
<%=3D20
Dim ObjConn, ObjRS, objComm, objParam, strBeginMonth, strEndMonth,=3D20
strReportType, Fields
Set objComm =3D3D Server.CreateObject("ADODB.Command")
Set objRS =3D3D Server.CreateObject("ADODB.Recordset")
objComm.ActiveConnection =3D3D strConnect
objComm.CommandText =3D3D "spQAFact"
objComm.CommandType =3D3D adCmdStoredProc
set objParam =3D3D objComm.CreateParameter("vTargetCurrentMonth", =3D
adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam =3D3D objComm.CreateParameter("vTargetLastMonth",
adVarChar,=3D20 adParamInput, 20) objComm.Parameters.Append objParam
set objParam =3D3D objComm.CreateParameter("vReportType",
adVarChar,=3D20
adParamInput, 20) objComm.Parameters.Append objParam
strBeginMonth =3D3D "April"
strEndMonth =3D3D "March"
strReportType =3D3D "Traffic"
objComm.Parameters("vTargetCurrentMonth") =3D3D strBeginMonth
objComm.Parameters("vTargetLastMonth") =3D3D strEndMonth
objComm.Parameters("vReportType") =3D3D strReportType
Set objRS =3D3D objComm.Execute
Response.Write "Count: " & objComm.Parameters.Count Response.Write
objComm.Parameters("vTargetCurrentMonth")
Response.Write objComm.Parameters("vTargetLastMonth")
Response.Write objComm.Parameters("vReportType")
while not objRS.EOF
Response.Write("Name")
Response.Write("UV_Rank")
Wend
---
Change your mail options at http://p2p.wrox.com/manager.asp or=3D20 to
unsubscribe send a blank email to
Message #3 by "Drew, Ron" <RDrew@B...> on Mon, 13 May 2002 15:02:54 -0400
|
|
Where is strConnect getting set? I see the Dim for ObjConn and later
strConnect
set objConn =3D Server.CreateObject("ADODB.Connection")
objComm.ActiveConnection =3D objConn
-----Original Message-----
From: Edward Hunter [mailto:erhunter@c...]
Sent: Monday, May 13, 2002 3:13 PM
To: ASP Databases
Subject: [asp_databases] Recordset closed?
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/QA/protoQA.asp, line 54
I'm using ASP to access a stored procedure, then trying to get the data
back into a recordset object. The SP works with the parameters when
executed in enterprise manager, but when I execute *this* asp code i get
the above error?
<%
Dim ObjConn, ObjRS, objComm, objParam, strBeginMonth, strEndMonth,
strReportType, Fields
Set objComm =3D Server.CreateObject("ADODB.Command")
Set objRS =3D Server.CreateObject("ADODB.Recordset")
objComm.ActiveConnection =3D strConnect
objComm.CommandText =3D "spQAFact"
objComm.CommandType =3D adCmdStoredProc
set objParam =3D objComm.CreateParameter("vTargetCurrentMonth",
adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam =3D objComm.CreateParameter("vTargetLastMonth", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam =3D objComm.CreateParameter("vReportType", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
strBeginMonth =3D "April"
strEndMonth =3D "March"
strReportType =3D "Traffic"
objComm.Parameters("vTargetCurrentMonth") =3D strBeginMonth
objComm.Parameters("vTargetLastMonth") =3D strEndMonth
objComm.Parameters("vReportType") =3D strReportType
Set objRS =3D objComm.Execute
Response.Write "Count: " & objComm.Parameters.Count Response.Write
objComm.Parameters("vTargetCurrentMonth")
Response.Write objComm.Parameters("vTargetLastMonth")
Response.Write objComm.Parameters("vReportType")
while not objRS.EOF
Response.Write("Name")
Response.Write("UV_Rank")
Wend
Message #4 by "Edward Hunter" <erhunter@c...> on Mon, 13 May 2002 20:10:37
|
|
Gah...sorry...
Its *the* line, *any* line (as I am finding out) that invokes the
recordset EOF... or any RS method for that matter. In the example you are
seeing, it is;
while not objRS.EOF
Hope that helps.
Warm Regards,
Edward Hunter
> Edward,
Which line is line 54?
--- Edward Hunter <erhunter@c...> wrote:
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> /QA/protoQA.asp, line 54
>
> I'm using ASP to access a stored procedure, then
> trying to get the data
> back into a recordset object. The SP works with the
> parameters when
> executed in enterprise manager, but when I execute
> *this* asp code i get
> the above error?
>
> <%
>
> Dim ObjConn, ObjRS, objComm, objParam,
> strBeginMonth, strEndMonth,
> strReportType, Fields
>
> Set objComm = Server.CreateObject("ADODB.Command")
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> objComm.ActiveConnection = strConnect
>
> objComm.CommandText = "spQAFact"
> objComm.CommandType = adCmdStoredProc
> set objParam
> objComm.CreateParameter("vTargetCurrentMonth",
> adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> set objParam
> objComm.CreateParameter("vTargetLastMonth",
> adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> set objParam
> objComm.CreateParameter("vReportType", adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> strBeginMonth = "April"
> strEndMonth = "March"
> strReportType = "Traffic"
>
> objComm.Parameters("vTargetCurrentMonth")
> strBeginMonth
> objComm.Parameters("vTargetLastMonth") = strEndMonth
> objComm.Parameters("vReportType") = strReportType
>
>
> Set objRS = objComm.Execute
>
>
> Response.Write "Count: " & objComm.Parameters.Count
> Response.Write
> objComm.Parameters("vTargetCurrentMonth")
> Response.Write
> objComm.Parameters("vTargetLastMonth")
> Response.Write objComm.Parameters("vReportType")
>
>
> while not objRS.EOF
> Response.Write("Name")
> Response.Write("UV_Rank")
> Wend
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
Message #5 by Mark Eckeard <meckeard2000@y...> on Mon, 13 May 2002 11:30:39 -0700 (PDT)
|
|
Edward,
Which line is line 54?
--- Edward Hunter <erhunter@c...> wrote:
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> /QA/protoQA.asp, line 54
>
> I'm using ASP to access a stored procedure, then
> trying to get the data
> back into a recordset object. The SP works with the
> parameters when
> executed in enterprise manager, but when I execute
> *this* asp code i get
> the above error?
>
> <%
>
> Dim ObjConn, ObjRS, objComm, objParam,
> strBeginMonth, strEndMonth,
> strReportType, Fields
>
> Set objComm = Server.CreateObject("ADODB.Command")
> Set objRS = Server.CreateObject("ADODB.Recordset")
>
> objComm.ActiveConnection = strConnect
>
> objComm.CommandText = "spQAFact"
> objComm.CommandType = adCmdStoredProc
> set objParam
> objComm.CreateParameter("vTargetCurrentMonth",
> adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> set objParam
> objComm.CreateParameter("vTargetLastMonth",
> adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> set objParam
> objComm.CreateParameter("vReportType", adVarChar,
> adParamInput, 20)
> objComm.Parameters.Append objParam
>
> strBeginMonth = "April"
> strEndMonth = "March"
> strReportType = "Traffic"
>
> objComm.Parameters("vTargetCurrentMonth")
> strBeginMonth
> objComm.Parameters("vTargetLastMonth") = strEndMonth
> objComm.Parameters("vReportType") = strReportType
>
>
> Set objRS = objComm.Execute
>
>
> Response.Write "Count: " & objComm.Parameters.Count
> Response.Write
> objComm.Parameters("vTargetCurrentMonth")
> Response.Write
> objComm.Parameters("vTargetLastMonth")
> Response.Write objComm.Parameters("vReportType")
>
>
> while not objRS.EOF
> Response.Write("Name")
> Response.Write("UV_Rank")
> Wend
__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
Message #6 by "Edward Hunter" <erhunter@c...> on Mon, 13 May 2002 19:12:52
|
|
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/QA/protoQA.asp, line 54
I'm using ASP to access a stored procedure, then trying to get the data
back into a recordset object. The SP works with the parameters when
executed in enterprise manager, but when I execute *this* asp code i get
the above error?
<%
Dim ObjConn, ObjRS, objComm, objParam, strBeginMonth, strEndMonth,
strReportType, Fields
Set objComm = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.Recordset")
objComm.ActiveConnection = strConnect
objComm.CommandText = "spQAFact"
objComm.CommandType = adCmdStoredProc
set objParam = objComm.CreateParameter("vTargetCurrentMonth", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam = objComm.CreateParameter("vTargetLastMonth", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
set objParam = objComm.CreateParameter("vReportType", adVarChar,
adParamInput, 20)
objComm.Parameters.Append objParam
strBeginMonth = "April"
strEndMonth = "March"
strReportType = "Traffic"
objComm.Parameters("vTargetCurrentMonth") = strBeginMonth
objComm.Parameters("vTargetLastMonth") = strEndMonth
objComm.Parameters("vReportType") = strReportType
Set objRS = objComm.Execute
Response.Write "Count: " & objComm.Parameters.Count
Response.Write objComm.Parameters("vTargetCurrentMonth")
Response.Write objComm.Parameters("vTargetLastMonth")
Response.Write objComm.Parameters("vReportType")
while not objRS.EOF
Response.Write("Name")
Response.Write("UV_Rank")
Wend
Message #7 by "Ken Schaefer" <ken@a...> on Tue, 14 May 2002 13:47:24 +1000
|
|
Do you have SET NOCOUNT ON in your sproc? If you are doing an INSERT, or an
UPDATE (etc) before you do your SELECT, then SQL Server will be returning
"xx records affected" which is being placed into the recordset *before* your
results are.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Edward Hunter" <erhunter@c...>
Subject: [asp_databases] Recordset closed?
: ADODB.Recordset error '800a0e78'
:
: Operation is not allowed when the object is closed.
:
: /QA/protoQA.asp, line 54
:
: I'm using ASP to access a stored procedure, then trying to get the data
: back into a recordset object. The SP works with the parameters when
: executed in enterprise manager, but when I execute *this* asp code i get
: the above error?
:
: <%
:
: Dim ObjConn, ObjRS, objComm, objParam, strBeginMonth, strEndMonth,
: strReportType, Fields
:
: Set objComm = Server.CreateObject("ADODB.Command")
: Set objRS = Server.CreateObject("ADODB.Recordset")
:
: objComm.ActiveConnection = strConnect
:
: objComm.CommandText = "spQAFact"
: objComm.CommandType = adCmdStoredProc
: set objParam = objComm.CreateParameter("vTargetCurrentMonth", adVarChar,
: adParamInput, 20)
: objComm.Parameters.Append objParam
:
: set objParam = objComm.CreateParameter("vTargetLastMonth", adVarChar,
: adParamInput, 20)
: objComm.Parameters.Append objParam
:
: set objParam = objComm.CreateParameter("vReportType", adVarChar,
: adParamInput, 20)
: objComm.Parameters.Append objParam
:
: strBeginMonth = "April"
: strEndMonth = "March"
: strReportType = "Traffic"
:
: objComm.Parameters("vTargetCurrentMonth") = strBeginMonth
: objComm.Parameters("vTargetLastMonth") = strEndMonth
: objComm.Parameters("vReportType") = strReportType
:
:
: Set objRS = objComm.Execute
:
:
: Response.Write "Count: " & objComm.Parameters.Count
: Response.Write objComm.Parameters("vTargetCurrentMonth")
: Response.Write objComm.Parameters("vTargetLastMonth")
: Response.Write objComm.Parameters("vReportType")
:
:
: while not objRS.EOF
: Response.Write("Name")
: Response.Write("UV_Rank")
: Wend
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
View Cart
