Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 22nd, 2010, 09:05 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default Operation is not allowed when the object is closed.

I'm getting a " Operation is not allowed when the object is closed. " error when trying to execute my ASP code. Could it be related to the stored procedure I"m calling? Code follows:

Code:
Dim rsqdb
Dim strConnection, conn
Dim raction_summary
set Conn=Server.CreateObject("ADODB.Connection")
set rsqdb = server.CreateObject("ADODB.Recordset")
set raction_summary =server.CreateObject("ADODB.Recordset")
Conn.open "Provider=sqloledb;Server=myservers;Initial Catalog=nqccdb;UID=test;PWD="
set rsqdb = conn.Execute ("CREATE TABLE #temp  " _
& "     (  " _
& "       tid INT IDENTITY(1, 1),  " _
& "       mgname NVARCHAR(255),  " _
& "       ksname NVARCHAR(255),  " _
& "       tablename NVARCHAR(255)  " _
& "     )  " _
& " INSERT  #temp  " _
& "         SELECT  M.Name,  " _
& "                 P.KSGName,  " _
& "                 M.ServerTableName  " _
& "         FROM    dbo.ManagementGroup M  " _
& "                 INNER JOIN dbo.MPDefinition P ON P.ManagementGroupID = M.ManagementGroupID  " _
& "         ORDER BY M.Name,  " _
& "                 P.KSGName  " _
& " DECLARE @sql NVARCHAR(4000)  " _
& " DECLARE @ctr INT,  " _
& "     @max INT  " _
& " DECLARE @mgname NVARCHAR(255),  " _
& "     @ksname NVARCHAR(255),  " _
& "     @table NVARCHAR(255)  " _
& " CREATE TABLE #results  " _
& "     (  " _
& "       [Management_Group] NVARCHAR(255),  " _
& "       [KSG] NVARCHAR(255),  " _
& "       [Server] NVARCHAR(128)  " _
& "     )  " _
& " SELECT  @ctr = 1,  " _
& "         @max = MAX(tid)  " _
& " FROM    #temp  " _
& " WHILE @ctr <= @max  " _
& "     BEGIN  " _
& "         SELECT  @mgname = mgname,  " _
& "                 @ksname = ksname,  " _
& "                 @table = tablename  " _
& "         FROM    #temp  " _
& "         WHERE   tid = @ctr  " _
& "         SELECT  @sql = N'  " _
& "    insert #results  " _
& "     select distinct [Management_Group] = @my_mgname, [KSG] = @my_ksname, [Server] = C.Computer  " _
& "     from ' + @table + N' C  " _
& "     order by [Management_Group], [KSG], C.Computer'  " _
& "         EXEC sp_executesql @sql,  " _
& "             N'@my_mgname nvarchar(255), @my_ksname nvarchar(255)',  " _
& "             @my_mgname = @mgname, @my_ksname = @ksname  " _
& "         SELECT  @ctr = @ctr + 1  " _
& "     END  " _
& " DROP TABLE #results  " _
& " DROP TABLE #temp ")
if rsqdb.EOF then
 Response.Write "There is no data"
 Response.End
end if
Thanks!
Dale
 
Old July 22nd, 2010, 03:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Pure guess: Because you forgot to put a
Code:
SET NOCOUNT ON
line into your SQL Server code, your query is actually returning *several* recordset objects, but most of them are, indeed, closed.

Try putting SET NOCOUNT ON as the first line of the SQL code.
 
Old July 22nd, 2010, 04:16 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Added the SETNOCOUNT ON, but still getting the same error. Any other suggestions?

Thanks!
Dale
 
Old July 22nd, 2010, 05:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

What happens if you execute this same query in a query tool? (MMS, etc.)?

I'm *guessing* that you are getting multiple recordsets, but I sure don't know that.

You could try doing this, in place of
if rsqdb.EOF then

Code:
On Error Resume Next
    Do
        Set temp = rsqdb
        Set rsqdb = rsqdb.NextRecordset
    Loop Until rsqdb IS Nothing
    Set rsqdb = temp
On Error GoTo 0
If rsqdb.EOF Then
...
Something like that?
 
Old July 23rd, 2010, 12:52 PM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Old Pendant,

Thanks for the tip, it worked! One more question. I'm getting the following error now:



Microsoft VBScript runtime error '800a000d'
Type mismatch /det2/show_afnet_mgs.asp, line 181

Which points to this line:

<%response.write rsqdb("Management_Group")%>

I've tried [Management_Group], and @my_mgname, both generate the same error.

Dale
 
Old July 23rd, 2010, 05:21 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You sure it's that line??

Sometimes, ASP error line numbers are one or two off.

You could try this for debugging:
Code:
    Set fld = rsqdb("Management_Group")
    Response.Write "field type is " & fld.Type & "<br/>"
    Response.Write "typename is " & TypeName(fld.value) & "<br/>"
See what that tells you.

But show more code. Show from line 170 to 190 or so.
 
Old July 26th, 2010, 09:34 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Okay, I'm dense. I don't know where to put the Set fld = rsqdb("Management_Group") line. I've tried all over the script I keep getting a Variable is undefined: 'fld' error.

Suggestions?
Thanks!
 
Old July 26th, 2010, 03:14 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Originally Posted by dalezjc View Post
Okay, I'm dense. I don't know where to put the Set fld = rsqdb("Management_Group") line. I've tried all over the script I keep getting a Variable is undefined: 'fld' error.

Suggestions?
Thanks!
My code was intended to REPLACE the line you showed:
Code:
<%response.write rsqdb("Management_Group")%>
You apparently are using OPTION EXPLICIT, so you just need to DIM the variable, thus:
Code:
<%
Dim fld
Set fld = rsqdb("Management_Group")
Response.Write "field type is " & fld.Type & "<br/>"
Response.Write "typename is " & TypeName(fld.value) & "<br/>"
%>
to replace the problem line. NOTE: THIS IS TEMPORARY, just to diagnose the problem.
 
Old August 9th, 2010, 11:33 AM
Authorized User
Points: 287, Level: 5
Points: 287, Level: 5 Points: 287, Level: 5 Points: 287, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2006
Location: Colorado Springs, CO
Posts: 60
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Got it. Thanks!
 
Old October 4th, 2010, 04:51 AM
Registered User
 
Join Date: Oct 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Old Pedant, I was breaking my head over a similar issue and your tip helped me tremendously so just wanted to say thanks.
Jake

Quote:
Originally Posted by Old Pedant View Post
What happens if you execute this same query in a query tool? (MMS, etc.)?

I'm *guessing* that you are getting multiple recordsets, but I sure don't know that.

You could try doing this, in place of
if rsqdb.EOF then

Code:
On Error Resume Next
    Do
        Set temp = rsqdb
        Set rsqdb = rsqdb.NextRecordset
    Loop Until rsqdb IS Nothing
    Set rsqdb = temp
On Error GoTo 0
If rsqdb.EOF Then
...
Something like that?




Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation is not allowed when the object is closed. Hannibal Classic ASP Basics 0 April 7th, 2010 10:45 AM
Operation is not allowed when the object is closed. davfergus Classic ASP Databases 2 January 29th, 2010 05:29 PM
Operation is not allowed when the object is closed lightmaker Classic ASP Basics 1 June 6th, 2008 12:29 PM
Operation is not allowed when the object is closed kingroon Classic ASP Databases 2 February 5th, 2008 10:29 AM
operation is not allowed when object is closed shoakat Classic ASP Databases 1 November 26th, 2004 12:17 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.