asked on

ADODB.Recordset error '800a0e78' - Operation is not allowed when the object is closed.

Hello,

I have an ASP VBScript web page that calls an MS SQL Stored Procedure, which queries the database..

When I test for values that I know don't exist I get --

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/test23.asp, line 20

Where line 20 is --

if not RSContacts.eof then

Open in new window

Of -

<%
if(Request("search") <> "") then

Dim CMDContactSearch__search
CMDContactSearch__search = NULL
if(Request("search") <> "") then CMDContactSearch__search = Request("search")

set CMDContactSearch = Server.CreateObject("ADODB.Command")
CMDContactSearch.ActiveConnection = MM_GolfConnection_STRING
CMDContactSearch.CommandText = "dbo.ContactSearchv2"
CMDContactSearch.CommandType = 4
CMDContactSearch.CommandTimeout = 0
CMDContactSearch.Prepared = true
CMDContactSearch.Parameters.Append CMDContactSearch.CreateParameter("@RETURN_VALUE", 3, 4)
CMDContactSearch.Parameters.Append CMDContactSearch.CreateParameter("@search", 200, 1,500,CMDContactSearch__search)
set RSContacts = CMDContactSearch.Execute
RSContacts_numRows = 0

if not RSContacts.eof then
Dim ARRContacts
ARRContacts = RSContacts.GetRows()

RSContacts.Close()
Set RSContacts = Nothing

End if
End if
%>

Open in new window

I've tried changing the if statement to

if IsObject(RSContacts) then

Open in new window

but that just moves the problem --

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/test23.asp, line 22

Line 22 is

ARRContacts = RSContacts.GetRows()

Open in new window

Help !!

What is the best route here?

Thanks

Meir Rivkin

where's the code which instance ADODB.Connection?
replace this lines:

set CMDContactSearch = Server.CreateObject("ADODB.Command")
CMDContactSearch.ActiveConnection = MM_GolfConnection_STRING

Open in new window

with this lines:

set CMDContactSearch = Server.CreateObject("ADODB.Command")
set ConContactSearch = Server.CreateObject("ADODB.Connection")
ConContactSearch.Open(MM_GolfConnection_STRING)
CMDContactSearch.ActiveConnection = ConContactSearch

Open in new window

garethtnash

ASKER

Hi Sedgwick,

That gave me --

Microsoft VBScript runtime error '800a01a8'

Object required: 'ConContactSearchCMDContactSearch'

/test23.asp, line 12

Any thoughts?

Meir Rivkin

can u post the whole code again?

garethtnash

ASKER

Sure ,

I've added a section of code (commented out), which if you remove the offending section and run results in 'Object Exists' being written to the page --

<%
if(Request("search") <> "") then

Dim CMDContactSearch__search
CMDContactSearch__search = NULL
if(Request("search") <> "") then CMDContactSearch__search = Request("search")

set CMDContactSearch = Server.CreateObject("ADODB.Command")
CMDContactSearch.ActiveConnection = MM_GolfConnection_STRING
CMDContactSearch.CommandText = "dbo.ContactSearchv2"
CMDContactSearch.CommandType = 4
CMDContactSearch.CommandTimeout = 0
CMDContactSearch.Prepared = true
CMDContactSearch.Parameters.Append CMDContactSearch.CreateParameter("@RETURN_VALUE", 3, 4)
CMDContactSearch.Parameters.Append CMDContactSearch.CreateParameter("@search", 200, 1,500,CMDContactSearch__search)
set RSContacts = CMDContactSearch.Execute
RSContacts_numRows = 0

'if IsObject(RSContacts) then
'Dim resp
'resp = "Object Exists"
'End if

if not RSContacts.eof then
Dim ARRContacts
ARRContacts = RSContacts.GetRows()

RSContacts.Close()
Set RSContacts = Nothing

End if
End if
%>

Open in new window

I guess this means that the object exists.... Even though the recordset is empty

Appreciate your help..

thanks

ASKER CERTIFIED SOLUTION

Meir Rivkin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

garethtnash

ASKER

Thanks Sedgwick