Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-12-11
10
Medium Priority
?
74,923 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 12

Accepted Solution

by:
kingsfan76 earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

926 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