Month and Year coding: Exception occured

[piersk]

Why oh why can't more information be given in this error:

Exception occurred.

/archive.asp, line 30

Anyway, here's my code:

<table width="75%" align="left" border="0">
<tr>
<td>&nbsp;</td>
</tr>
<% Dim rsArchive, dtLastEntry, dtFirstEntry, intMonth, intYear, i, conn, objRS
i = 0
set rsArchive = server.CreateObject("ADODB.Recordset")
rsArchive.activeconnection = "dsn=subseauk;"
rsArchive.source = "select * from news order by whenposted desc"
rsArchive.cursorlocation = 2
rsArchive.cursortype = 2
rsArchive.locktype = 1
rsArchive.open

if not(rsArchive.BOF and rsArchive.EOF) then
rsArchive.Movefirst
dtLastEntry = rsArchive("whenposted")
rsArchive.movelast
dtFirstEntry = rsArchive("whenposted")

rsArchive.movefirst

intMonth = month(dtLastEntry)
intYear = year(dtLastEntry)
i = 0

do while dtLastEntry >= dtFirstEntry

if datepart("m", rsArchive("whenposted")) = intMonth and datepart("yyyy", rsArchive("whenposted")) = intYear then
i = i + 1
'response.write "i = " & i & "<br />"
'response.write "intYear = " & intYear & "<br />"
'response.write "intMonth = " & intMonth & "<br />"


if rsArchive.EOF then exit do
rsArchive.movenext
else
%>
<tr>
<td align="left"><a href="listentries.asp?month=<%=intMonth%>&year=<%=intYear%>"><%=monthname(intMonth) & " " & intYear%></a> (<%=i%>)</td>
</tr>
<%
i = 0
dtLastEntry = dateadd("m",-1,dtLastEntry)
intMonth = datepart("m", dtLastEntry)
intYear = datepart("yyyy",dtLastEntry)
end if
loop
end if
%>
</table>

Line 30 is the one with the if datepart(... statement.

Anyone got any thoughts?

Yes, there is data in the database, and it always gives the error before the month count it written to the page (i.e. the "end of the month")

[CardboardHammer]

if rsArchive.EOF then exit do
rsArchive.movenext


I haven't done classic ADO in awhile, but I'm almost certain that you need to swap those 2 lines around. I think you're doing movenext and ending up at EOF which is causing it to crap itself on line 30.

[vbKing]

This might be a better way of doing that:
If Not rsArchive.EOF then rsArchive.movenext

[piersk]

Cheers guys! They both worked, but I went with the second one since it seemed more secure.

However, it's not picking up the last month. Anyone got any ideas why?

[CardboardHammer]

Maybe put this immediately before the closing tag for the table?

<tr>
<td align="left"><a href="listentries.asp?month=<%=intMonth%>&year=<%=intYear%>"><%=monthname(intMonth) & " " & intYear%></a> (<%=i%> )</td>
</tr>


Posting your revised code would be helpful, as I'm not sure exactly what all you changed... if you only did what it sounds like you're saying you did, you'd be risking an infinite loop.

[piersk]

I tried that, but it comes up with October (0), when it should be October (1).

New version of code (without the above change):<table width="75%" align="left" border="0">
<tr>
<td>&nbsp;</td>
</tr>
<% Dim rsArchive, dtLastEntry, dtFirstEntry, intMonth, intYear, i, conn, objRS
i = 0
set rsArchive = server.CreateObject("ADODB.Recordset")
rsArchive.activeconnection = "dsn=subseauk;"
rsArchive.source = "select * from news order by whenposted desc"
rsArchive.cursorlocation = 2
rsArchive.cursortype = 3
rsArchive.locktype = 1
rsArchive.open

if not(rsArchive.BOF and rsArchive.EOF) then
rsArchive.Movefirst
dtLastEntry = rsArchive("whenposted")
rsArchive.movelast
dtFirstEntry = rsArchive("whenposted")

rsArchive.movefirst

intMonth = month(dtLastEntry)
intYear = year(dtLastEntry)
i = 0

do while not(dtLastEntry < dtFirstEntry)

if cint(datepart("m", rsArchive("whenposted"))) = cint(intMonth) and cint(datepart("yyyy", rsArchive("whenposted"))) = cint(intYear) then
i = i + 1
'response.write isDate(rsArchive("whenposted")) & "<br />"

response.write "month = " & datepart("m", rsArchive("whenposted")) & "<br />"
response.write "year = " & datepart("yyyy", rsArchive("whenposted")) & "<br />"
if not rsArchive.EOF then
rsArchive.Movenext
end if

if rsArchive.EOF then
exit do
end if

else
%>
<tr>
<td align="left"><a href="listentries.asp?month=<%=intMonth%>&year=<%=intYear%>"><%=monthname(intMonth) & " " & intYear%></a> (<%=i%>)</td>
</tr>
<%
i = 0
dtLastEntry = dateadd("m",-1,dtLastEntry)
intMonth = datepart("m", dtLastEntry)
intYear = datepart("yyyy",dtLastEntry)
end if



loop

end if
%>

</table>

[CardboardHammer]

Hmmm... things would be a lot easier/faster for you if you rewrote your query.

Instead of:

select * from news order by whenposted desc


At least:

select distinct whenposted from news order by whenposted desc



And, if I understand what you're trying to do correctly...

Best would be something like (IDK what type of db you're using, so don't complain if the below doesn't work exactly as written... it's the general idea that counts):

select distinct postMonth = datepart("m", whenposted), postYear = datepart("yyyy", whenposted) from news order by whenposted desc


This last way would vastly simplify your code (plus you'd be able to use a forward only cursor, pull less data from the db, etc.):

if not(rsArchive.BOF and rsArchive.EOF) then
rsArchive.Movefirst
do while not(rsArchive.EOF)
intMonth = rsArchive(0)
intYear = rsArchive(1)
%>
<tr><td align="left"><a href="listentries.asp?month=<%=intMonth%>&year=<%=intYear%>"><%=monthname(intMonth) & " " & intYear%></a> (<%=i%> )</td></tr>
<%
rsArchive.Movenext
loop
end if
%>
</table>


Also note that jumping in and out of script decreases performance, so using Response.Write (especially in loops) is often a better idea.

"Select * ..." is bad performance-wise as well. You should specify exactly which fields you want.

Accessing fields in a recordset by index performs better than accessing by field name. rsArchive(0) vs. rsArchive("whenposted")

Coding with optimization in mind might not seem a big deal now, but if you don't, it can come back to bite you in the ass in the future. Where I work, the programmers were very lax and after a couple years of data building up, pages slowed to a crawl. Fixing the most problematic pages (I worked on that a couple years ago, now it's not my concern) had them running as if they were on a brand new server. Just something to keep in mind...


Disclaimer: I quit using old ASP/ADO in favor of .NET 1.5yrs. ago, so there may be minor errors in the details.

[piersk]

Thanks for the tips. Surely the distinct part of it is pointless since all the dates are going to be different anyway.

Also, the last bit of code you posted, would that go instead of the do while loop that I had in my code?

[CardboardHammer]

Well, if you only have at most one post per day (second query) or per month (last query), then the DISTINCT is indeed pointless. You are trying to get one link per month + year, right? That's what it looked like, at any rate.

Code (if you use the third query) replaces everything after "rsArchive.open" in your code (though you should probably change the area inside the loop that jumps in and out of script to use Response.write instead).