asked on

Trouble displaying recordset from access database using group and order

Hello,
I am currently retrieving info successfully from a db...what I want to do is group the records by month have a bolded heading for the month and have them in order by day descending...something like this....

OCTOBER

october 12 some info
october 11 some info

SEPTEMBER

sept 22 some info
sept 4 some info

....etc

Right now it is displaying by month descending...it is not grouping by month or displaying by day descending....obviously I am not sure what to do with my sql statement "myChrono"

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"
                  objChronoRS.Open myChrono, objConn, 1, 3
                  Do While not objChronoRS.EOF
                              vMonth=objChronoRS("month")
                              vYear=objChronoRS("year")
                              link=objChronoRS("link")
                              vDay=objChronoRS("day")
                              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
                  objChronoRS.Close
                  Set objChronoRS = Nothing
                  objConn.Close
thanks for all help!
groovymonkey

D_M_D

Try this SQL statement...

myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' GROUP BY DAY ORDER BY month DESC"

--------
D_M_D

AntChen

myChrono = "SELECT * From mediaroom WHERE year="+vYear+" AND class='" & vClass & "' order by month DESC,day DESC"

Use this you can desc by month and also desc by day.
Then you can use below show the result. It is not the best but it can solve your question.

<% do while not rs.eof
if rs("month")= OCTOBER then
for i=0 to rs.fields.count-1
response.write("OCTOBER")%>
<% rs(i)&"  "%>
<% next
end if%>
<br>
<%rs.movenext
loop%>

<% rs.movefirst
do while not rs.eof
if rs("month")= SEPTEMBER then
for i=0 to rs.fields.count-1
response.write("SEPTEMBER")%>
<%=rs(i)&"  "%>
<% next
end if%>
<br>
<%rs.movenext
loop%>
:
<% rs.close
conn.close
set rs=nothing
set conn=nothing%>

groovymonkey

ASKER

Okay it is grouping the way I want it to but I do not fully understand the code to group it so that I have a single heading for each month with the appropriate links displayed under it...here is my thought process/pseudocode to show you what variables I am working with...

if objChronoRS("month")= 12 then
response.write("<h2>")
response.write("December")
response.write("</h2>")

//loop here and display all links for the month of december
response.write(link)
response.write("<br>")
//end of loop
//do same for all remaining months

Thanks for the help...I really need to get this working ASAP with nice clean efficient code...I have raised the value to 500 points.
thanks groovymonkey

zeetin

myChrono = "SELECT * From mediaroom WHERE [year]="+vYear+" AND class='" & vClass & "' order by [month] DESC, [day] DESC"

The codes below should help you to get the names of the months that you wanted, and display all the other information that you want to show for that month

<%
      Dim lTempMonth
      lTempMonth = 0

      Do while not objChronoRS.eof
            lTempMonth = objChronoRS("month")
            Response.Write "<h2>" & monthname(lTempMonth) & "</h2>"

            Do while objChronoRS("month") = lTempMonth
                  'print other details for that month here

                  rs.movenext()
            Loop
      Loop
%>

Hope this helps :-)

groovymonkey

ASKER

zeetin...almost there

here is my code

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

groovymonkey

ASKER

...sorry not done comment....the above code is putting out the info rouped by months and headings as wanted...it is breaking at the end and I am getting the following error message...

error '80020009'
Exception occurred.

referring to this line of code

Do while objChronoRS("month") = lTempMonth

ASKER CERTIFIED SOLUTION

zeetin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zeetin

opss... sorry.. the indentation of the codes didn't came out as i expected.

Anyway, I won't have access to the internet for the next 3 days so hopefully my suggestion above would have helped you solve your problem by then. Best regards :).

ap_sajith

As mentioned in the other post, try changing this line..

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

Cheers!!

zeetin

Hi groovymonkey,

How are things going?