Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. 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 Databases 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 January 15th, 2008, 02:02 PM
Authorized User
Points: 136, Level: 2
Points: 136, Level: 2 Points: 136, Level: 2 Points: 136, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2006
Location: London Town, , United Kingdom.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Operation is not allowed when the object is closed

G'day..

I think this is a common problem with a simple solution, but I've no luck today.. Maybe someone here can help me?

SQL Stored Procedure -->

################################################## ####################

CREATE PROCEDURE [dbo].[sp_Search]
(
    @SearchString varchar(50),
    @from int,
    @to int
)

AS
BEGIN


-- Create Temp Table inc. Identity column

    CREATE TABLE #SearchResults(
        SR_ID int not null identity,
        The_Address varchar(150) null)

-- Do Search

    INSERT INTO #SearchResults (The_Address)
    SELECT DISTINCT Address
    FROM tbl_Addresses
    WHERE (tbl_Addresses.Address LIKE '%' + @SearchString + '%')
    ORDER BY tbl_Addresses.Address ASC

-- Select All Results using SR_ID for pagination etc.

    SELECT * FROM #SearchResults
    WHERE SR_ID > IsNull(@from,0) AND SR_ID < IsNull(@to, 25)
    ORDER BY SR_ID

END

################################################## ####################

ASP Code -->

################################################## ####################

Dim oDBConn, sQuery, oResults
Set oDBConn = Server.CreateObject("ADODB.Connection")
oDBConn.Open CONNECTION_STRING
sQuery = Trim(Request("SearchQuery")) & ""

Set oResults = oDBConn.Execute("EXEC sp_Search '" & sQuery & "', null, null;")

If Not oResults.EOF Then
...
End If

################################################## ####################

Just keep getting this error whenever I check if the oResults recordset is returning any result..?

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/search_results.asp, line 63

Line 63 is "If Not oResults.EOF Then"

I can execute:

EXEC sp_Search 'test', null, null;

in Query Analyser no problemo, it returns one result..

I have tried creating a Recordset ADODB object but is the problem that I am inserting into a temp table and then selecting from that? If so, what is the solution??

Hasta Luego..
KingRoon

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com
__________________
Hasta Luego..
KingRoon

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com
 
Old February 5th, 2008, 07:55 AM
Authorized User
 
Join Date: Jul 2004
Location: , , Ireland.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey there,

I think you might need to use oDBConn.OPEN instead of oDBConn.EXECUTE.

HTH!
J.

 
Old February 5th, 2008, 10:29 AM
Authorized User
Points: 136, Level: 2
Points: 136, Level: 2 Points: 136, Level: 2 Points: 136, Level: 2
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2006
Location: London Town, , United Kingdom.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Cheers for the reply.. I tried that too, I thught it might be a problem of INSERTing Data when a RecordSet is called for?

Hasta Luego..
KingRoon

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Operation is not allowed when obejct is closed zazo XSLT 19 July 25th, 2008 01:26 PM
Operation is not allowed when the object is closed lightmaker Classic ASP Basics 1 June 6th, 2008 12:29 PM
"OPERATION NOT ALLOWED AFTERRESULT SET IS CLOSED" picky JSP Basics 3 October 12th, 2006 03:26 PM
Operation is not allowed when the object is closed kah Javascript How-To 2 February 16th, 2005 07:20 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.