Grouping data year wise and month wise in asp classic using access as database (1 Viewer)

dodevilz · May 22, 2014

Good wishes,
I have a problem i am using access database *.mdb to provide dynamic content to my website using asp classic as server side scripting.

I have a table named "circ"
Structure is ID autonumber, particulars text field, updatetime Date/time field
I have some entries in it I want to display it on my website Year/Month wise.
Eg.
2014
May
RECORD1
RECORD2
January
RECORD3
RECORD4
2013
December
RECORD5
RECORD6

At present I am using following code to create connection and fetch data from mdb file.
set rsd =server.CreateObject("ADODB.Recordset")
rsd.ActiveConnection = Con (Connection name)
Set rsd = Con.Execute("Select * from circ order by updatetime desc ")

if not(rsd.EOF or rsd.BOF) then
rscirc=rsd("Particulars")
rsupt=rsd("updatetime")
rsyear=year(rsupt)
rsmonth=(MonthName(month(rsupt)))
else
end if

and following code to display data but clueless about the syntax
<%
if not(rsd.EOF or rsd.BOF) then

While not rsd.Eof %>

<FONT ><%=rsyear%></FONT> <BR>
<% rsd.MoveNEXT
Wend

%>
<% else %>
Information coming soon...
<% end if %>

I am recieving the Year repeatedly.
Please help. Thanks in advance

CJ_London · May 22, 2014

It looks like your recordset consists of more than one row and this

Code:

While not rsd.Eof %>


<FONT ><%=rsyear%></FONT> <BR>
<% rsd.MoveNEXT 
Wend

is going through the entire recordset

Please also, when posting code, use the code tags and indent the code so that it is easier to read e.g.

Code:

While not rsd.Eof %>
    <FONT ><%=rsyear%></FONT> <BR>
    <% rsd.MoveNEXT 
Wend

dodevilz · May 22, 2014

Thanks for response. Here is the code.

Code:

<%    
   if not(rsd.EOF or rsd.BOF) then
     
    While not rsd.Eof %>
    <%=rsyear%><br>
    <%=rsmonth%>
    
<LI><%=rscirc%>.</B><BR>Updated at:<%=rsupt%></A></FONT><BR><BR>
<%  rsd.MoveNext 
                 Wend 
                
             %>
              <% else %>
                          No details till now.
                  <% end if %>

As you have mentioned I have around 100 records and this code is going through the entire recordset and showing

2014
Information coming soon... Information coming soon...

as result.

I have no idea as I am new to this.
Do I need to group data by year and month wise and then display the data.
Please help.

CJ_London · May 22, 2014

I thought the problem was with your recordset, but seems instead to be to with displaying the data. Unfortunately this is not my area of expertise so I don't think I can help.

I'm also confused because you seem to have changed your code and in your new code I cannot see 'Information coming soon... '

dodevilz · May 22, 2014

Sorry for that crap. this is the correct code.

Code:

<%    
   if not(rsd.EOF or rsd.BOF) then
     
    While not rsd.Eof %>

      
<%=rsyear%> <BR>
  <%  rsd.MoveNEXT 
                 Wend 
                
             %>
              <% else %>
                          Information coming soon...
                  <% end if %>
                  <%    
   if not(rsd.EOF or rsd.BOF) then
     
    While not rsd.Eof %>

      
<%=rsmonth%>
  <%  rsd.MoveNEXT 
                 Wend 
                
             %>
              <% else %>
                          Information coming soon...
                  <% end if %>
                  
<ul>
<%    
   if not(rsd.EOF or rsd.BOF) then
     
    While not rsd.Eof %>
<LI><%=rscirc%>.<BR><BR>
<%  rsd.MoveNext 
                 Wend 
                
             %>
              <% else %>
                          Information coming soon...
                  <% end if %>

and this is the code to connect to the recordset

Code:

set rsd =server.CreateObject("ADODB.Recordset")
        rsd.ActiveConnection = Con
        Set rsd = Con.Execute("Select * from circ order by updatetime desc ")
        
        
         if not(rsd.EOF or rsd.BOF) then
         rscirc=rsd("Particulars")
         rsupt=rsd("updatetime")
         rsyear=year(rsupt)
         rsmonth=(MonthName(month(rsupt)))
         else 
           
          end if

CJ_London · May 22, 2014

Still don't think I can help except to get your query right. To do this I need to understand the contents of your updatetime field - which of these is it?

dd/mm/yyyy hh:mm:ss
dd/mm/yyyy
hh:mm:ss

dodevilz · May 22, 2014

dd/mm/yyyy hh:mm:ss

CJ_London · May 22, 2014

OK, I think your sql needs to be more like this:

Code:

SELECT *, Year([updatetime]) AS UpdateYear, Month([updatetime]) AS UpdateMonth 
FROM circ 
ORDER BY Year([updatetime]), Month([updatetime])

This will list all your records, ordered by year and month

dodevilz · May 22, 2014

I have changed the sql and it has been executed without any error but still it is showing the same result.
how should I write the

Code:

<%        if not(rsd.EOF or rsd.BOF) then          
 While not rsd.Eof %>         
<%=rsyear%> <BR>   
<%  rsd.MoveNEXT                   
                Wend   %>               
<% else %> 
 Information coming soon...                   
<% end if %>                   
<%        if not(rsd.EOF or rsd.BOF) then           
While not rsd.Eof %>         
<%=rsmonth%>   
<%  rsd.MoveNEXT  
                Wend %>               
<% else %>                          
 Information coming soon...                   
<% end if %>                    
<ul> 
<%        if not(rsd.EOF or rsd.BOF) then           
While not rsd.Eof %> 
<LI><%=rscirc%>.<BR><BR> 
<%  rsd.MoveNext                   
Wend    %>               
<% else %>                           
Information coming soon...                  
 <% end if %>

part to get the desired output

CJ_London · May 22, 2014

As previously explain edI cannot help you with the code. In VBA I would expect it to be something like

Code:

While not rsd.EOF
    writeln rsd!UpdateYear
    writeln rsd!UpdateMonth
    writeln rsd!NextField
    ...etc
    rsd.MoveNext
Wend

But you have many more loops

Grouping data year wise and month wise in asp classic using access as database (1 Viewer)

dodevilz

New member

CJ_London

Super Moderator

dodevilz

New member

CJ_London

Super Moderator

dodevilz

New member

CJ_London

Super Moderator

dodevilz

New member

CJ_London

Super Moderator

dodevilz

New member

CJ_London

Super Moderator

Users who are viewing this thread