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
Operation is not allowed when the object is closed
