Database Looping and Error "80020009"

Okay, here's my problem. I must loop through a recordset until I get through all the records, but I need to run a second loop that tests for groups of time periods. Here's the code:

intTimeTot = 0
do while not rs.eof
dtmCheckTime = rs("time_start")
intChoices = 0
do while dtmCheckTime = rs("time_start")
intChoices = intChoices + 1
rs.movenext
loop
intTimeTot = intTimeTot + 1

{other code}...

loop

I get the error "error '80020009' Exception occurred." at the "do while dtmCheckTime = rs("time_start")" line, and it happens when it gets to the end of the recordset.

Basically, I need that section of code to count as many recordsets at that point that have the same time period. If there is a better way to do it, please let me know.

Thanks in advance

You should do all that kinda of stuff in the DB. There's no need to spent time writing more code when SQL can do it all for you! :P

SELECT COUNT(time_start) FROM table GROUP BY time_start

That would be great, but there might be times that will reoccur, but on different days. There is a DB field called 'dat' that holds the date of the event. I'm still a little bit new on SQL. Can an aggregate be structured so it would group the times by their dates, and return the same count?

Note I assume you're using SQLServer.

You can aggregate by any part of the datetime field. Using:

SELECT 108, CONVERT(VARCHAR,datetimefieldname,108)

Will return only the time section.

This article will help you out in any future issues.

Thanks! That's a good article.

I have a similar problem, except I am trying to build a table that lists the different down times of different applications according to 1 of 4 different codes. When an application has no listing for such a procedure, it should put up a blank space in the cell where the info should have been. Whenever this runs, I hit an error on the first point in which data is not found in the field for that recordset. I tried to correct it by writing "if then" statements, but it is not working to place a blank space in if the field is null. instead, it puts the error code and message in that cell in the table. Any help would be appreciated. Here is the code:




if obj1rs.eof then
'Do nothing, end the table
response.write("<tr><td> There are no Application Objectives associated with this application</td></tr>")
else
'Add the records
response.write("</td></tr>")
response.write("<tr><td><table border='2' style='border-collapse: collapse' width='100%'>")
response.write("<tr BGCOLOR='ffffe0'><td align='center'><b>RTO</b></td>")
response.write("<td align='center'><b>RPO</b></td><td align='center'><b>SRTO</b></td><td align='center'><b>SRPO</b></td></tr>")

do until obj1rs.eof
response.write ("<tr><td align='center'>")
if obj1rs("goal_hrs")="" then
response.write ("&nbsp;</td>")
else
response.write (obj1rs("goal_hrs") & "</td>")
end if

response.write("<td align='center'>")
if obj2rs("goal_hrs")="" then
response.write ("&nbsp;</td>")
else
response.write (obj2rs("goal_hrs") & "</td>")
end if

response.write("<td align='center'>")
if obj3rs("goal_hrs")="" then
response.write ("&nbsp;</td>")
else
response.write (obj3rs("goal_hrs") & "</td>")
end if

response.write("<td align='center'>")
if obj4rs("goal_hrs")="" then
response.write ("&nbsp;</td>")
else
response.write (obj4rs("goal_hrs") & "</td></tr>")
end if
obj1rs.movenext
loop
end if

response.write("</table></td></tr>")