Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
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.
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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?
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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.
Reply With Quote
  #7 (permalink)  
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!
Reply With Quote
  #8 (permalink)  
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.
Reply With Quote
  #9 (permalink)  
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!
Reply With Quote
  #10 (permalink)  
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?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.