• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

ADODB.Recordset error '800a0e78'

Quick question, hopefully a quick answer. I have this code on an .asp page, most of the time it works and returns the data from the database, but occasionally i get an error

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

If i refresh the brower it usually goes away, any idea why i get it in the first place. I can only assume it find no data for some reason.

ZIP is a session variable. I have tried without the variable and used the sql line:

   sql = "SELECT DISTINCTROW *  FROM Teams  WHERE Teams.[Zip]=10"

and get the same error, but it's not all the time it happens.

The jist of the code below (hopefully i copied all the relevant bits!). Can anyone see why?

<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")

  Dim connstr
  'Using the Microsoft Access driver
  connstr = "DRIVER={Microsoft Access Driver (*.mdb)};" _
  & "DBQ=" & ("c:\Path-to-database\database.mdb") _
  & ";PWD=" & pword & ";"

    conn.Open connstr
%>          


<%
    sql = "SELECT DISTINCTROW *  FROM Teams  WHERE Teams.[Zip]=" & ZIP
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<%
On Error Goto 0
rs.MoveFirst
do while Not rs.eof
 %>

<Table>
<TR><TD>
<%=Server.HTMLEncode(rs.Fields("TeamName").Value)%>
</TD></TR>
</Table>

<%
rs.MoveNext
loop%>
0
jeffw1
Asked:
jeffw1
  • 4
  • 4
  • 4
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>On Error Goto 0
looks to me that somewhere above, you have something like:
On Error resume next

of that is the case, you get some error in the code above, but do neither "see" nor "handle" it.

if could be that the query is taking too long, or the connection fails to open...
0
 
jeffw1Author Commented:

So you think maybe i should add in some error handling code for when this occurs?

Any suggestions....... :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So you think maybe i should add in some error handling code for when this occurs?
yep

>Any suggestions....... :)
the "shortest" possible would be to have after each statement that is subject to fail:

if err.number <> 0 then
  response.write err.description
  response.end
end if


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ddelhezCommented:
the error is
rs.MoveFirst

You cannot use MoveFirst on an empty recordset...

so your MoveFirst must be in something like this

On Error Goto 0
if not rs.Eof then
  rs.MoveFirst
  do while Not rs.eof
       your loop
  end of your loop
end if
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ddelhez is actually right!
now, normally, you don't need movefirst at all anyhow, as the recordset.open is on the first record (if any is there) anyhow...
0
 
ddelhezCommented:
True, personally , I never use Movefirst to start browsing a recordset result
0
 
jeffw1Author Commented:
Ok, i've added the error handling code and when i do get the error its:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I've also now added the code to check files not empty.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you get that error, you have an error in the query.
for example, when the ZIP variable is not filled in at all...

this version might hide the problem:
    sql = "SELECT DISTINCTROW *  FROM Teams  WHERE Teams.[Zip]= 0" & ZIP
0
 
ddelhezCommented:
This error happens when you
- use a non existing column in the where clause
- refer to non existing column in the recordset

so in your case , either the Zip column, either the TeamName column

Most of the time, it's a mistype of a column
0
 
jeffw1Author Commented:
I tried without the session variable and added the parameter into the sql statement

sql = "SELECT DISTINCTROW *  FROM Teams  WHERE Teams.[Zip]=10"

I'm now running as

sql = "SELECT DISTINCTROW [Team Name]  FROM Teams  WHERE Teams.[Zip]=10"

The error still occurs but intermittently as usual, i also tried with different fields from the table, always the same........... it can load 10 times then fail once or load 5 times and fail twice.

If no other ideas, i'll use the error handling to redirect to a failed to load page  and get them to refresh the browser until it works, or reload the current page until it works!! :(



0
 
ddelhezCommented:
Just a question, about what your code

in your new sql, I see that your field is [Team Name] with a space between Team and Name...

but in your asp code
Server.HTMLEncode(rs.Fields("TeamName").Value)

there is no space...

0
 
jeffw1Author Commented:
Just the way i typed it, it's correct in the code.

I've gone with the redirect to the same page which seem to work ok, it's now ideal but as the error occurs every now and then in theory one of the times the page will load :)

I've split the points between you both as you gave me a few ideas.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now