CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

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

Print topic Send  topic

Author Message
cmilkosk

Posts: 6
Posted: 08/26/2008, 6:14 AM

All,

I'm trying to create a search page with a grid in CC4 using ASP that connects to a SQL Server 2000 database via an ODBC connection. I don't have anything fancy or custom in it, it was created with the Search Builder. I tested the ODBC connections from my desktop and the server I published the page to and they tested successfully from both.

After publishing the page, I navigate to it with Mozilla or IE7 and get the Search form. I enter some search criteria, and it churns for a bit (a minute or so), and then returns the following:

ADODB.Recordset error '800a0e78'  
  
Operation is not allowed when the object is closed.  
  
/capacity_tools/custinfo/Classes.asp, line 3373

Line 3373 in Classes.asp =

        If Not IsEmpty(mCountSQL) And Len(CountSQL) = 0  And NOT DataSource.Recordset.EOF Then

and it is part of a larger case statement on Classes.asp that begins with:

    Select Case CommandType  
      Case dsTable, dsSQL  
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeBuildSelect", Me)  
        If  InStr(SQL, "{SQL_Where}") > 0 Or InStr(SQL,"{SQL_OrderBy}") > 0 Then  
          SQL = Replace(SQL, "{SQL_Where}", IIf(Len(Where) > 0, " WHERE " & Where, ""))  
          If InStr(SQL,"{SQL_OrderBy}") > 0 Then   
             SQL = Replace(SQL, "{SQL_OrderBy}", IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, ""))  
          Else   
             SQL = SQL & IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, "")  
          End If  
          builtSQL = ParseParams(SQL, WhereParameters)  
        Else   
          builtSQL = ParseParams(SQL & IIf(Len(Where) > 0, " WHERE " & Where, "") & IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, ""), WhereParameters)  
        End If  
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeExecuteSelect", Me)  
        Set DataSource.Recordset = OpenRecordset(builtSQL,False)  
        If ActivePage > 0 And IsNeedMoveToPage Then   
          DataSource.MoveToPage ActivePage  
        End If  
        If Not IsEmpty(mCountSQL) And Len(CountSQL) = 0  And NOT DataSource.Recordset.EOF Then  
          If DataSource.Recordset.State = adStateOpen Then  
            Dim Counter : Counter = 0  
            While NOT DataSource.Recordset.EOF AND Counter < mPageSize+1  
               Counter = Counter + 1  
               DataSource.Recordset.MoveNext  
            Wend  
            DataSource.RecordCount = IIF(ActivePage>0,(ActivePage - 1) * mPageSize, 0) + Counter  
          End If   
          If ActivePage > 0 Then  
            DataSource.Recordset.MoveFirst  
            DataSource.MoveToPage ActivePage  
          End If  
    End If   

Any idea why this error is happening? Any further explanation as to what it means?

Thanks for any assistance in this.

Chris
View profile  Send private message
Oper


Posts: 1195
Posted: 08/26/2008, 2:38 PM

1) Same Database. I Mean during design you are using Same Database as the one runing and giving you the error? (Dont reply they are identical, just if are teh same)
ex: if one database in your computer and teh other in another server then they are not the same.


2) Do you have any other stufff reading ok like listbox, form, etc? any other thing that said that actualy you are reading teh database?

AS A TEST do you see the line that got the error: in Classes.asp
If Not IsEmpty(mCountSQL) And Len(CountSQL) = 0 And NOT DataSource.Recordset.EOF Then

put this above sentence
Response.write BuildSQL & "----------------<br>"

you will find the error. post what the response.write display in case not solved
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)

http://www.PremiumWebTemplate.com
Affiliation Web Site Templates

Please do backup first
View profile  Send private message
cmilkosk

Posts: 6
Posted: 08/27/2008, 6:04 AM

Thanks for responding Oper!

I have a correction to something I wrote earlier. I wrote that I used the Search Builder to make the page, and in fact I used the Application Builder. I don't know if that changes anything. Sorry about that.

Quote :
1) Same Database. I Mean during design you are using Same Database as the one runing and giving you the error? (Dont reply they are identical, just if are teh same)
ex: if one database in your computer and teh other in another server then they are not the same.

The ODBC data source I used on the designing PC and the server I published to were configured as a System DSN. They both point to the same database server.

Quote :
2) Do you have any other stufff reading ok like listbox, form, etc? any other thing that said that actualy you are reading teh database?

I didn't add anything to the page, I just created it with the Application Builder.

Quote :
AS A TEST do you see the line that got the error: in Classes.asp
If Not IsEmpty(mCountSQL) And Len(CountSQL) = 0 And NOT DataSource.Recordset.EOF Then

put this above sentence
Response.write BuildSQL & "----------------<br>"

you will find the error. post what the response.write display in case not solved

I changed that to

Response.Write "SQL :" & builtSQL & "<BR>" 

and received the following when I navigated to the page (I substituted the column names and database name with generic names):

SQL:SELECT TOP 20 column1, column2, column3 FROM table1 ORDER BY column1  
  
ADODB.Recordset error '800a0e78'  
  
Operation is not allowed when the object is closed.  
  
/pathname/subdir/Classes.asp, line 3380 

That query runs correctly when entered in the SQL Server Management Studio.


Thanks,

Chris
View profile  Send private message
Oper


Posts: 1195
Posted: 08/27/2008, 3:24 PM

so you dont have a SQL Database in your computer?

Database is Empty?
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)

http://www.PremiumWebTemplate.com
Affiliation Web Site Templates

Please do backup first
View profile  Send private message
cmilkosk

Posts: 6
Posted: 08/28/2008, 9:52 AM

Quote Oper:
so you dont have a SQL Database in your computer?

Database is Empty?

Oper,

The SQLServer 2000 database is a 14 million record db located on a separate server. So, there are 3 machines involved:

1) The development machine - my PC. There is no SQL database on this computer.
2) The IIS Server to serve up the web pages. There is a SQL database on this computer, but not the one I am querying in the form.
3) The SQLServer 2000 database - there is a SQL database on this computer and it is the one I'm looking to query with my form...

Maybe I should ask this a different way. What does the error mean? Why would it be telling me that an operation is not allowed when the object is closed? Is it that it isn't opening to begin with and I'm not seeing an error for when it attempts to open?

Thanks,

Chris
View profile  Send private message
Oper


Posts: 1195
Posted: 08/28/2008, 6:21 PM

Look like is not what i was thinking,

somtime when you using a Design Database and a Production Database
(not your case loo like) you get this error
cuase one of the field used during design do not exist on the table you used on production

But look like your isue is other.

ZIp your project and open a ticket.

also try this.
response.write ccdlookup("Column1","TABLE1","ANYID=###",DBConnection1)

note user real FiledID and value and your Connection Name
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)

http://www.PremiumWebTemplate.com
Affiliation Web Site Templates

Please do backup first
View profile  Send private message
Oper


Posts: 1195
Posted: 08/28/2008, 7:00 PM

here you will get real error:

substitub that select case CommandType you see in Classes.asp
for this:

Do Backup
AT your Own Risk
Never modify those Files


  
    Select Case CommandType  
      Case dsTable, dsSQL  
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeBuildSelect", Me)  
        If  InStr(SQL, "{SQL_Where}") > 0 Or InStr(SQL,"{SQL_OrderBy}") > 0 Then  
          SQL = Replace(SQL, "{SQL_Where}", IIf(Len(Where) > 0, " WHERE " & Where, ""))  
          If InStr(SQL,"{SQL_OrderBy}") > 0 Then   
             SQL = Replace(SQL, "{SQL_OrderBy}", IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, ""))  
          Else   
             SQL = SQL & IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, "")  
          End If  
          builtSQL = ParseParams(SQL, WhereParameters)  
        Else   
          builtSQL = ParseParams(SQL & IIf(Len(Where) > 0, " WHERE " & Where, "") & IIf(Len(OrderBy) > 0, " ORDER BY " & OrderBy, ""), WhereParameters)  
        End If  
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeExecuteSelect", Me)  
        Set DataSource.Recordset = OpenRecordset(builtSQL,False)  
        If Errors.Count = 0 Then     
          If ActivePage > 0 And IsNeedMoveToPage Then   
            DataSource.MoveToPage ActivePage  
          End If  
          If Not IsEmpty(mCountSQL) And Len(CountSQL) = 0  And NOT DataSource.Recordset.EOF Then  
            If DataSource.Recordset.State = adStateOpen Then  
              Dim Counter : Counter = 0  
              While NOT DataSource.Recordset.EOF AND Counter < mPageSize+1  
                 Counter = Counter + 1  
                 DataSource.Recordset.MoveNext  
              Wend  
              DataSource.RecordCount = IIF(ActivePage>0,(ActivePage - 1) * mPageSize, 0) + Counter  
            End If   
            If ActivePage > 0 Then  
              DataSource.Recordset.MoveFirst  
              DataSource.MoveToPage ActivePage  
            End If  
          End If   
        End If  
  
        CCSEventResult = CCRaiseEvent(CCSEvents, "AfterExecuteSelect", Me)  
        If Errors.Count > 0 Then   
          DataSource.Errors.AddErrors Errors  
        End If  
        Set DoOpen = DataSource  


_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)

http://www.PremiumWebTemplate.com
Affiliation Web Site Templates

Please do backup first
View profile  Send private message
Trigger
Posted: 08/28/2008, 7:26 PM

Sorry I haven't been following this thread but you can look for connection
information here:

http://www.connectionstrings.com/

If your connection has timed out and then your application tries to do
something with the result
set (that it doesn't have because the connection timed out), you get the
operation not allowed message
because it wouldn't make sense to do anything with the invalid result set.
Sometimes you will get a
function sequence error. Same thing, you didn't get a valid result set, so
trying to read it without a valid
result set is an error and wont return useful or meaningful results.

If you are timing out at the server, sometimes increasing the timeout
parameter can help but it is usually
an indication of a more general problem. If you are using a stored procedure
or view to return the data
make sure that you have all of the necessary indexes to join tables. If you
are trying to return too much data,
set up a filter that will return less data. Usually, people will not hang
around on a website if it takes more that
20 seconds or so to return some data. If you are developing an application,
get prepared to get a lot of complaints
about how slow that application is.

Tim

"cmilkosk" <cmilkosk@forum.codecharge> wrote in message
news:648b6d7b804dfd@news.codecharge.com...
>
Quote Oper:
> so you dont have a SQL Database in your computer?
>
> Database is Empty?
>
>
> Oper,
>
> The SQLServer 2000 database is a 14 million record db located on a
> separate
> server. So, there are 3 machines involved:
>
> 1) The development machine - my PC. There is no SQL database on this
> computer.
> 2) The IIS Server to serve up the web pages. There is a SQL database on
> this
> computer, but not the one I am querying in the form.
> 3) The SQLServer 2000 database - there is a SQL database on this computer
> and
> it is the one I'm looking to query with my form...
>
> Maybe I should ask this a different way. What does the error mean? Why
> would
> it be telling me that an operation is not allowed when the object is
> closed? Is
> it that it isn't opening to begin with and I'm not seeing an error for
> when it
> attempts to open?
>
> Thanks,
>
> Chris
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>

GeorgeS

Posts: 206
Posted: 09/09/2008, 3:08 PM

I'd check IUSR permissions for the directory on the server.
_________________
GeorgeS
View profile  Send private message
Oper


Posts: 1195
Posted: 09/10/2008, 7:14 AM

if he substitud the code i inserted, the real error will appear.



_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)

http://www.PremiumWebTemplate.com
Affiliation Web Site Templates

Please do backup first
View profile  Send private message
bburnett

Posts: 22
Posted: 11/07/2008, 8:10 AM

Oper's code is very helpful. Yes should replace the existing code with it, as it returns the actual error.

I've found that this error tends to happen when you are missing a table or view for a listbox or grid or your SQL statement is timing out. You can add ;ConnectionTimout=120 to your OLEDB connection string to increase the timeout for your whole project. Unfortunately the CCS DBConnection class doesn't implement the CommandTimeout property so that we can change this value on a grid by grid basis.
_________________
Brandon Burnett
New Media Architect
REL Productions
West Des Moines, IA, USA
www.relonline.com
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.