Help with error '80020009' exception occured ASP code to display records order by month and day descending

I have the following code which pulls links from a database and displays them with month and day descending...grouped by month.  The output looks something like this

December

2003-12-11
some link here

2003-12-05
some link here

November
.....etc

The code is working but breaks once the last record is reached...this is when I get the error code...

error '80020009'
Exception occurred.

the error refers to this particular line of code

      Do while objChronoRS("month") = lTempMonth


Anyways here is the code...
objConn.Open
                  DIM myChrono
                  Set objChronoRS= Server.CreateObject("ADODB.Recordset")
                  myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"
                  objChronoRS.Open myChrono, objConn, 1, 3

                 Dim lTempMonth
                 lTempMonth = 0
     
                 Do while not objChronoRS.eof
                        lTempMonth = objChronoRS("month")  
                               
                              if objChronoRS("month")= 12 then
                                    response.write("<h2>")
                                    response.write("December")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 11 then
                                    response.write("<h2>")
                                    response.write("November")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 10 then
                                    response.write("<h2>")
                                    response.write("October")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 9 then
                                    response.write("<h2>")
                                    response.write("September")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 8 then
                                    response.write("<h2>")
                                    response.write("August")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 7 then
                                    response.write("<h2>")
                                    response.write("July")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 6 then
                                    response.write("<h2>")
                                    response.write("June")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 5 then
                                    response.write("<h2>")
                                    response.write("May")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 4 then
                                    response.write("<h2>")
                                    response.write("April")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 3 then
                                    response.write("<h2>")
                                    response.write("March")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 2 then
                                    response.write("<h2>")
                                    response.write("February")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 1 then
                                    response.write("<h2>")
                                    response.write("January")
                                    response.write("</h2>")
                              end if
                                          
                        Do while objChronoRS("month") = lTempMonth
                              'print other details for that month here
                              vMonth=objChronoRS("month")
                              vYear=objChronoRS("year")
                              link=objChronoRS("link")
                              vDay=objChronoRS("day")
                              response.write("<p>")
                              response.write(vYear)
                              response.write("-")
                              if vMonth < 10 then
                              response.write("0")
                              response.write(vMonth)
                              else
                              response.write(vMonth)
                              end if
                              response.write("-")
                              if vDay < 10 then
                              response.write("0")
                              response.write(vDay)
                              else
                              response.write(vDay)
                              end if
                              response.write("<br>")
                              response.write(link)
                              response.write("<br>")
                              objChronoRS.movenext()
                      Loop
                 Loop

                  objChronoRS.Close
                  Set objChronoRS = Nothing
                  objConn.Close
                  end if

thanks groovymonkey
groovymonkeyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NowaYCommented:
You are not checking for objChronoRS.Eof on the second loop
kingsfan76Commented:
>>Do while objChronoRS("month") = lTempMonth

you need to check for EOF as well:

Do while objChronoRS("month") = lTempMonth and Not objChronoRS.EOF


but why are you having a nested loop with the same recordset??

Do while not objChronoRS.eof
     Do while objChronoRS("month") = lTempMonth and Not objChronoRS.EOF
         objChronoRS.movenext()
     Loop
Loop
---------
the outer loop will only loop once.  maybe this is what you meant to do??
--------------------------
if not objChronoRS.eof
     lTempMonth = objChronoRS("month")  
     Do while objChronoRS("month") = lTempMonth and Not objChronoRS.EOF
         objChronoRS.movenext()
     Loop
end if
-------------------

kingsfan


kingsfan76Commented:
I'm not sure what you are trying to do, but it seems you are trying to loop to compare the month, and print out some details for the month until the month change.  then you do it for the next month, and so forth, until the end of the recordset.  

If that's the case, the above logic will not work.  try the following logic:

if not  objChronoRS.eof then
            PrevMonth = objChronoRS("month")     'initalize the previous month
            'initially print the name of the month
            ' put the print name of month codes here.........
      Do while not objChronoRS.eof

               lTempMonth = objChronoRS("month")   'the current month

               if lTempMonth <> PrevMonth then    'a new month, print out the name of the month
                     PrevMonth = ITempMonth           're-assign previous month
                     'codes for printing the name of the month
                     'codes for printing details for the month for the current record

               else        'the same month
                     'print detail for the month
                     'print detail of month codes here..........
               end if

            objChronoRS.movenext()
     Loop
end if


hope I'm making some sense....  :-)
p.s. I suggest putting the codes for printing the name of the month in a function so you don't have to write it every multiple times

kingsfan
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

groovymonkeyAuthor Commented:
Makes sense...right now when I insert my info into your suggested code...it is writing the month everytime...I have obviously missed something here...also how would I put the month check code into a function?  I have upped the value of the que to 500 points...the question is also part of another que for 500 points at http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20822214.html .  There is a chance to get 1000 points here so I appreciate any help...here is my code...


      objConn.Open
                  DIM myChrono
                  Set objChronoRS= Server.CreateObject("ADODB.Recordset")
                  myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"
                  objChronoRS.Open myChrono, objConn, 1, 3

                 if not  objChronoRS.eof then
            PrevMonth = objChronoRS("month")     'initalize the previous month
            'initially print the name of the month
            ' put the print name of month codes here
                              if objChronoRS("month")= 12 then
                                    response.write("<h2>")
                                    response.write("December")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 11 then
                                    response.write("<h2>")
                                    response.write("November")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 10 then
                                    response.write("<h2>")
                                    response.write("October")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 9 then
                                    response.write("<h2>")
                                    response.write("September")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 8 then
                                    response.write("<h2>")
                                    response.write("August")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 7 then
                                    response.write("<h2>")
                                    response.write("July")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 6 then
                                    response.write("<h2>")
                                    response.write("June")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 5 then
                                    response.write("<h2>")
                                    response.write("May")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 4 then
                                    response.write("<h2>")
                                    response.write("April")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 3 then
                                    response.write("<h2>")
                                    response.write("March")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 2 then
                                    response.write("<h2>")
                                    response.write("February")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 1 then
                                    response.write("<h2>")
                                    response.write("January")
                                    response.write("</h2>")
                              end if
                  
                  Do while not objChronoRS.eof

               lTempMonth = objChronoRS("month")   'the current month

               if lTempMonth <> PrevMonth then    'a new month, print out the name of the month
                     PrevMonth = ITempMonth           're-assign previous month
                     'codes for printing the name of the month
                                     if objChronoRS("month")= 12 then
                                    response.write("<h2>")
                                    response.write("December")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 11 then
                                    response.write("<h2>")
                                    response.write("November")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 10 then
                                    response.write("<h2>")
                                    response.write("October")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 9 then
                                    response.write("<h2>")
                                    response.write("September")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 8 then
                                    response.write("<h2>")
                                    response.write("August")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 7 then
                                    response.write("<h2>")
                                    response.write("July")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 6 then
                                    response.write("<h2>")
                                    response.write("June")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 5 then
                                    response.write("<h2>")
                                    response.write("May")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 4 then
                                    response.write("<h2>")
                                    response.write("April")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 3 then
                                    response.write("<h2>")
                                    response.write("March")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 2 then
                                    response.write("<h2>")
                                    response.write("February")
                                    response.write("</h2>")
                              elseif objChronoRS("month")= 1 then
                                    response.write("<h2>")
                                    response.write("January")
                                    response.write("</h2>")
                              end if
                              
                     'codes for printing details for the month for the current record
                              vMonth=objChronoRS("month")
                              vYear=objChronoRS("year")
                              link=objChronoRS("link")
                              vDay=objChronoRS("day")
                              response.write("<p>")
                              response.write(vYear)
                              response.write("-")
                              if vMonth < 10 then
                              response.write("0")
                              response.write(vMonth)
                              else
                              response.write(vMonth)
                              end if
                              response.write("-")
                              if vDay < 10 then
                              response.write("0")
                              response.write(vDay)
                              else
                              response.write(vDay)
                              end if
                              response.write("<br>")
                              response.write(link)
                              response.write("<br>")      
                              

               else        'the same month
                     'print detail for the month
                     'print detail of month codes here
                              vMonth=objChronoRS("month")
                              vYear=objChronoRS("year")
                              link=objChronoRS("link")
                              vDay=objChronoRS("day")
                              response.write("<p>")
                              response.write(vYear)
                              response.write("-")
                              if vMonth < 10 then
                              response.write("0")
                              response.write(vMonth)
                              else
                              response.write(vMonth)
                              end if
                              response.write("-")
                              if vDay < 10 then
                              response.write("0")
                              response.write(vDay)
                              else
                              response.write(vDay)
                              end if
                              response.write("<br>")
                              response.write(link)
                              response.write("<br>")
                              
               end if

            objChronoRS.movenext()
                 Loop
                  end if

                  objChronoRS.Close
                  Set objChronoRS = Nothing
                  objConn.Close
                  end if
kingsfan76Commented:
before modifying your codes, I have a suggestion for your month name codes.
There's no need to write it in a function because VBscript has a built-in function for doing that:
just replace all the month name code with this line:

response.write("<h2>" & MonthName(objChronoRS("month")) & "</h2>)

MonthName() is a built-in function and takes integer 1 to 12 as arguments
this should clean up the codes significantly

kingsfan76Commented:
Ok I tested  with your codes.  You did used my codes and my logic correctly.  there's a tiny error that makes it not work.  You mis-spelled one variable.
ITempMonth and lTempMonth   (the "i" and "L")
kind of a silly mistake isn't it.
Well for the revised code i'll just name it TempMonth so it won't cause any confusion.
here's the code that should work:
--------------------------------------
DIM myChrono
Set objChronoRS= Server.CreateObject("ADODB.Recordset")
myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"
objChronoRS.Open myChrono, objConn, 1, 3

if not objChronoRS.eof then
   PrevMonth = objChronoRS("month")     'initalize the previous month
   'write the month name for the first record
   Response.Write("<h2>" & MonthName(objChronoRS("month")) & "</h2>")
             
   Do while not objChronoRS.eof
       TempMonth = objChronoRS("month")   'the current month

       if TempMonth <> PrevMonth then    'a new month, print out the name of the month
            PrevMonth = TempMonth           're-assign previous month
            Response.Write("<h2>" & MonthName(objChronoRS("month")) & "</h2>")
       end if                
           
            'this part you'll need to print no matter it is a new month or same month
            vMonth=objChronoRS("month")
            vYear=objChronoRS("year")
            link=objChronoRS("link")
            vDay=objChronoRS("day")
            response.write("<p>")
            response.write(vYear)
            response.write("-")
            if vMonth < 10 then
               response.write("0")
               response.write(vMonth)
            else
               response.write(vMonth)
            end if
            response.write("-")
            if vDay < 10 then
               response.write("0")
               response.write(vDay)
            else
               response.write(vDay)
            end if
            response.write("<br>")
            response.write(link)
            response.write("<br>")

        objChronoRS.movenext()
     Loop
end if

              objChronoRS.Close
              Set objChronoRS = Nothing
              objConn.Close

---------------------------------

i modify your code to test and then modify back, hope i didn't mis-spelled any of the database field names

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ap_sajithCommented:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q175/2/39.asp&NoWebContent=1

Also, what is the datatype of objChronoRS("month")? . Is it numeric?

Then change your code to..

lTempMonth = Cint(objChronoRS("month"))

AND..

Do while Cint(objChronoRS("month")) = lTempMonth

Cheers!!
groovymonkeyAuthor Commented:
kingsfan76...I tried the code and it is not pulling ANY records out (blank screen is shown)...any ideas why

<%
                  vYear = request.querystring("year")
                  search = request.querystring("search")
                  vClass = request.querystring("class")
                  vOffice = request.querystring("office")
                  response.write(vClass)
                  response.write(vOffice)
                  
                  Dim objConn
                  Set objConn = Server.CreateObject("ADODB.Connection")
                  objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                  Server.MapPath ("mr.mdb") & ";"
                  
                  objConn.Open
DIM myChrono
Set objChronoRS= Server.CreateObject("ADODB.Recordset")
myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"
objChronoRS.Open myChrono, objConn, 1, 3

if not objChronoRS.eof then
   PrevMonth = objChronoRS("month")     'initalize the previous month
   'write the month name for the first record
   Response.Write("<h2>" & MonthName(objChronoRS("month")) & "</h2>")
             
   Do while not objChronoRS.eof
       TempMonth = objChronoRS("month")   'the current month

       if TempMonth <> PrevMonth then    'a new month, print out the name of the month
            PrevMonth = TempMonth           're-assign previous month
            Response.Write("<h2>" & MonthName(objChronoRS("month")) & "</h2>")
       end if                
           
            'this part you'll need to print no matter it is a new month or same month
            vMonth=objChronoRS("month")
            vYear=objChronoRS("year")
            link=objChronoRS("link")
            vDay=objChronoRS("day")
            response.write("<p>")
            response.write(vYear)
            response.write("-")
            if vMonth < 10 then
               response.write("0")
               response.write(vMonth)
            else
               response.write(vMonth)
            end if
            response.write("-")
            if vDay < 10 then
               response.write("0")
               response.write(vDay)
            else
               response.write(vDay)
            end if
            response.write("<br>")
            response.write(link)
            response.write("<br>")

        objChronoRS.movenext()
     Loop
end if

              objChronoRS.Close
              Set objChronoRS = Nothing
              objConn.Close

                    
                    %>
kingsfan76Commented:
@groovymoney,

are you still having problem with your codes above?  since you accepted the answer so i'm not sure if you got it working already.  let me knwo if you still have error with the above code, we can insert some debugging statements to find out what is causing it.

kingsfan
pegasysIT, System Admin, Development and Stack DevelopmentCommented:
myChrono = "set dateformat ddMMyyyy SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"

u can change the dateformat. Look up it's synta :)

regards

pgx();
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.