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

Hey there,

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

HTH!
J.

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