Solved

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

Posted on 2003-12-11
10
74,764 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:groovymonkey
10 Comments
 
LVL 8

Expert Comment

by:NowaY
ID: 9921795
You are not checking for objChronoRS.Eof on the second loop
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9921821
>>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


0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9922028
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
0
 

Author Comment

by:groovymonkey
ID: 9922618
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
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9922802
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

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 12

Accepted Solution

by:
kingsfan76 earned 500 total points
ID: 9923210
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
0
 
LVL 21

Expert Comment

by:ap_sajith
ID: 9927161
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!!
0
 

Author Comment

by:groovymonkey
ID: 9948824
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

                    
                    %>
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9951651
@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
0
 
LVL 7

Expert Comment

by:pegasys
ID: 12326400
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();
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now