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
|
|
|
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 |
|
|
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
|
|
|
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 |
|
|
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
|
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
|