error '80020009' - problem in loop (line 127)

[guineamation]

Hi all, i have a small error '80020009' problem.
searched the net, got a few ideas non of the worked.
i'm getting the error when retreivig data from record set.
it works fine until some point in the middle, then gives the error and continu.... wierd!
tried while, do while and do until... nothing
ideas someone?

....
r.open "SELECT NAME,FULLNAME,TYPE,FORMAT,REMARKS FROM machina WHERE SIZE='TOOLS' ORDER BY GROUP, FORMAT, NAME ASC;"
While Not r.EOF
newType = r.fields("type")
%>
<center>
<table border="1" width="100%" bgcolor="#C0C0C0">
<tr>
<td colspan=5><b><font size=2 face=Arial color=white><%=r.fields("type")%></b>&nbsp;</td>
</tr>
<tr width=100% align=left bgcolor=#002D40>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>NAME</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>FULLNAME</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>TYPE</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>FORMAT</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>DESCRIPTION</th>
</tr>
<%
Do Until (r.fields("type") <> newType) Or (r.EOF) '////////Line 127 !!!!!!!!!!!!
%>
<tr>
<td valign=top align=left><font size=1 face=Arial><a href=../indexes/desc.asp?p=<%=r.fields("name")%>><%=r.fields("name")%></a>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("fullname")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("type")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial color=red><%=r.fields("format")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("remarks")%>&nbsp;</td>
</tr>
<%
r.moveNext
Loop
WEnd
%>
</table>
<%

 

[a6m1n0]

Try:

r.open "SELECT NAME, FULLNAME, TYPE, FORMAT, REMARKS, SIZE FROM machina WHERE SIZE='TOOLS' ORDER BY GROUP, FORMAT, NAME ASC;"

if not r.eof then
  strType=r.fields("type")
%>
<center>
<table border="1" width="100%" bgcolor="#C0C0C0">
<tr>
<td colspan=5><b><font size=2 face=Arial color=white><%=strType%></b>&nbsp;</td>
</tr>
<tr width=100% align=left bgcolor=#002D40>
<%
for each field in r.fields
  if field.name<>"SIZE" then
%>
<th bgcolor="#002D40" align="left"><font color="white" face="Arial" size="2"><%=field.name%></th>
<%
  end if
next
%>
</tr>
<%
for each field in rs.fields
  if field.name<>"SIZE" then
%>
<tr>
<%
    if field.name="NAME" then
%>
<td valign=top align=left><font size=1 face=Arial>
<a href="../indexes/desc.asp?p=<%=r.fields(field)%>"><%=r.fields(field)%></a>&nbsp;</td>
<%
    else
%>
<td valign=top align=left><font size=1 face=Arial><%=r.fields(field)%>&nbsp;</td>
<%
    end if
%>
</tr>
<%
  end if
next
%>
</table>
<%else%>
There are no records to display.
<%end if%>

If you get an error on a line with r.fields(field) change it r.fields(field.name).

-a6m1n0

"Don't try to reinvent the wheel." -My HS FORTRAN Professor

 

[guineamation]

I run the SQL on the DB and saw that the script actually displays everything, the last TYPE contains only one record and the error appears right before it, i just want to find out why it happends so i can know for next time.

in other words, how do i change my owne code and not change everything, unless mine is totaly useless...

Thanks for the quick response

 

[FesterSXS]

OK - I see what you're trying to do. Try this...

<center>
<table border="1" width="100%" bgcolor="#C0C0C0">
<%
r.open "SELECT NAME,FULLNAME,TYPE,FORMAT,REMARKS FROM machina WHERE SIZE='TOOLS' ORDER BY GROUP, FORMAT, NAME ASC;"
While Not r.EOF
 newType = r.fields("type")

 If newType <> oldType Then
%>
<tr>
<td colspan=5><b><font size=2 face=Arial color=white><%=r.fields("type")%></b>&nbsp;</td>
</tr>
<tr width=100% align=left bgcolor=#002D40>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>NAME</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>FULLNAME</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>TYPE</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>FORMAT</th>
<th bgcolor=#002D40 align=left><font color=white face=Arial size=2>DESCRIPTION</th>
</tr>
<%
 End If
%>
<tr>
<td valign=top align=left><font size=1 face=Arial><a href=../indexes/desc.asp?p=<%=r.fields("name")%>><%=r.fields("name")%></a>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("fullname")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("type")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial color=red><%=r.fields("format")%>&nbsp;</td>
<td valign=top align=left><font size=1 face=Arial><%=r.fields("remarks")%>&nbsp;</td>
</tr>
<%
 oldType = newType
 r.moveNext
Wend
%>
</table>

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[guineamation]

Works like a charm!

Thanks.

 

[FesterSXS]

you're welcome

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[a6m1n0]

I'm tired and wrapped up into something else...sorry for butchering your code, lol. (Did you try the code and did it work? Just curious.)

-a6m1n0

"Don't try to reinvent the wheel." -My HS FORTRAN Professor

 

[guineamation]

i tryied the code but it didn't work,
it showed only one record and not in a TD (one after the other), but in TR (one under another)