error '80020009' exception error

[Earl_CG]

Hello

Got a problem which has had me scratching my head for an age now, and has defeated both my Computing Teacher & my ultra techie friend.

The following code is working perfectly till the last cell of the table it produces:

set rs=conn.execute("SELECT Predictions.[Match ID], Users.Username, Predictions.Week, Predictions.HPred, Predictions.APred, Predictions.Mark FROM Weeks INNER JOIN (Users INNER JOIN (Matches INNER JOIN Predictions ON Matches.[Match ID] = Predictions.[Match ID]) ON Users.Username = Predictions.User) ON (Weeks.Week = Predictions.Week) AND (Weeks.Week = Matches.Week) WHERE (((Predictions.Week)='" & week & "')) ORDER BY Users.Username, Predictions.[Match ID];")

rs.movefirst
total = 0
	do until rs.eof = true
   	username = rs("username")
   	response.write "<tr><td>" & username & "</td>"
   	ttotal = total + ttotal 
   	total= 0
		do until rs("username") <> username or rs.eof = true
		mark = rs("mark")
		total = mark + total
		response.write "<td align=center>" & rs("HPred") & " - " & rs("APred") & "</td><td align=center>" & (mark) & "</td>"
   		rs.movenext
   		loop
	response.write "<td align=right>" & total & "</td></tr>"
	loop
response.write"</table>"
rs.close

It is the last "<td align=right>" & total & "</td></tr>" which isn't being written

This is my logic:

After the inner loop has written the final record set it should "movenext" onto a non-existant RS. This should mean that rs.eof = true and without there being a username to call on, a null value should not equal the value in the variable. Thus the inner loop should stop.

However the error in the title is instead thrown up regarding the "do until" line of the inner loop.

Any suggestion are most appreciated as this one has me flummoxed.

[dexterz]

I thought ASP Web Dev teacher should know ASP and SQL bec they got paid good money. Anyway, I changed the coding structure not to use response.write, instead use block so that its easy to read.

Here's what I came up:

<%
Dim username, total, mark
set rs=conn.execute("SELECT Predictions.[Match ID], Users.Username, Predictions.Week, Predictions.HPred, Predictions.APred, Predictions.Mark FROM Weeks INNER JOIN (Users INNER JOIN (Matches INNER JOIN Predictions ON Matches.[Match ID] = Predictions.[Match ID]) ON Users.Username = Predictions.User) ON (Weeks.Week = Predictions.Week) AND (Weeks.Week = Matches.Week) WHERE (((Predictions.Week)='" & week & "')) ORDER BY Users.Username, Predictions.[Match ID];")

rs.movefirst
total = 0
do until rs.eof
username = rs("username")
ttotal = total + ttotal
total= 0
%>
<table><tr><td><%=username%></td>
<%
If rs("username") <> username or rs.eof Then
mark = rs("mark")
total = mark + total
end if
%>
<%
<td align=center><%=rs("HPred")%> - <%=rs("APred")%></td><td align=center>(<%=mark%>)</td>
<td align=right><%=total%></td></tr>
</table>
<%
rs.movenext
loop
%>
<%
rs.close
Set rs = Nothing
%>

www.ex-designz.net
Dexter

[Earl_CG]

I'm sorry but that's not suitable for what I'm trying to do. Your code simply writes each record in its own row. It also bizzarely opens and closes a table in every loop, so in fact each record set has a table of it's own!

The inner loop is there because for each row (or username) four record sets must be written.

As each record set is written the mark field is totaled. Once the four records are written the total is the put into the final cell of that row, which is then closed.

The majority of the code works fine. It's just the browser seems to decide that is finds a loop hole in the inner "do until" statement once it has "moved next" onto a non-existant record set. This prevents it from writing the final total cell at the end of the final row.

[william_stam]

I'm sorry but that's not suitable for what I'm trying to do. Your code simply writes each record in its own row. It also bizzarely opens and closes a table in every loop, so in fact each record set has a table of it's own!

The inner loop is there because for each row (or username) four record sets must be written.

As each record set is written the mark field is totaled. Once the four records are written the total is the put into the final cell of that row, which is then closed.

The majority of the code works fine. It's just the browser seems to decide that is finds a loop hole in the inner "do until" statement once it has "moved next" onto a non-existant record set. This prevents it from writing the final total cell at the end of the final row.

move the <table> and </Table> tags outsidt the loop. loop only from <tr> and </tr> (same table but looped table rows) i use this way makes it easier to debug later (tag colouring still works as well - depends on program)

[Earl_CG]

Thanks for that but with some help from a friend I worked out and solved the problem.

The exception was due to it checking the whole conditional statement despite it being end of file. Thus it tried to compare a non-existing record to the variable.

The solution was to add an interrupt to the loop:

rs.movefirst
total = 0
	do until rs.eof
   	username = rs("username")
   	response.write "<tr><td>" & username & "</td>"
   	ttotal = total + ttotal 
   	total = 0
		do until rs("username") <> username
		mark = rs("mark")
		total = mark + total
		response.write "<td align=center>" & rs("HPred") & " - " & rs("APred") & "</td><td align=center>" & (mark) & "</td>"
   		rs.movenext
   		If rs.eof Then Exit Do
   		loop
	response.write "<td align=right>" & total & "</td></tr>"
	loop
response.write "</table></center><br />"
rs.close

Cheers for your efforts though.

[andrew23chan]

Hi
Can somebody help me add/create an error messege so that it will redirect into a more user friendly error instead of getting this messgge:
"error '80020009' "

Thanks,
Andrew

[redRemedy]

It is better to solve the problem rather than hiding it or working around it.
Take a look at this page: http://www.aspfaq.com/show.asp?id=2246

// redRemedy

Hi
Can somebody help me add/create an error messege so that it will redirect into a more user friendly error instead of getting this messgge:
"error '80020009' "

Thanks,
Andrew

[Jonica]

I'm having the same error but I'm not cycling through a recordset with a loop, I'm simply doing

strSQL = select field from table where condition
   recordset.Open strSQL

straight after which I get a problem. any ideas? is it just because the recordset is empty? I don't get the error in all cases and am having trouble recreating it so I can't test for sure if that's it.

[j03d4d4]

ADODB.Field error '80020009'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/freeupdates.asp, line 0

This is the error I am getting and it's debugging process crashes visual studio 2008 and momentarily stalls IIS.

Normally the /freeupdates.asp get's directed to as freeupdates.asp?set=<% Response.write (rs("setid")) %> however when you go directly to freeupdates.asp without adding ?set=39650B the ADODB.Field error '80020009' error is produced. This stops all IIS functions as the error is caught by debugging and you must choose YES or NO to debug.

I need a way to make it so there is no way for visitors to get acess to /freeupdates.asp unless they are coming directly from a specific link which forces ?set=<% Response.write (rs("setid")) %>.

Any ideas?

[j03d4d4]

I figured it out. I just allow for only localhost read permissions on freeupdates.asp. The code is executedly properly from localhost. This is just to prevent people from causing denial of service in crappy methods of coding.

[j03d4d4]

actually no that didn't work like I thought it would.