I have an events calendar. The events are in an MS Access database and I am using an ASP script to pull them through. The problem is that only one event will show for each day. How can I get multiple fields to show, eg more than one event for the same day?
Here is the code:
<%@ Language=VBScript %>
<!--#include virtual="adovbs.inc"-->
<%
'Determine the month name for a given month. Numeric value outputs month name
Function GetMonthName(iMonth)
Select Case iMonth
Case 1:
GetMonthName="January"
Case 2:
GetMonthName="February"
Case 3:
GetMonthName="March"
Case 4:
GetMonthName="April"
Case 5:
GetMonthName="May"
Case 6:
GetMonthName="June"
Case 7:
GetMonthName="July"
Case 8:
GetMonthName="August"
Case 9:
GetMonthName="September"
Case 10:
GetMonthName="October"
Case 11:
GetMonthName="November"
Case 12:
GetMonthName="December"
Case ELSE:
GetMonthName = "**INVALID MONTH**"
End Select
End Function
'line40
'Obtain the current date
Dim dbCurrentDate
dbCurrentDate = Request("Date")
If Len (dbCurrentDate) = 0 then
dbCurrentDate = Request.Cookies("CalendarDate")
End If
If Len(dbCurrentDate) = 0 then
dbCurrentDate = Date()
End If
Response.Cookies("CalendarDate").Expires = Now() + 30
'Create an array to store the 42 possible days of the month
Dim aCalendarDays(42)
'Into Calendardays place the days of the current month
Dim iFirstWeekday
iFirstWeekday = DatePart("w", DateSerial(Year(dbCurrentDate),_
Month(dbCurrentDate), 1))
'Loop from 1 to the number of days in the current month, populating the array aCalendarDays
Dim iDaysinMonth
iDaysinMonth = DatePart("d", DateSerial(Year(dbCurrentDate),_
Month(dbCurrentDate)+ 1, 1-1))
Dim iLoop
For iLoop = 1 to iDaysinMonth
aCalendarDays(iLoop + iFirstWeekday - 1) = iLoop
Next
'Display the populated array in calendar form. Create the calendar table
Dim iColumns, iRows
iColumns = 7
iRows = 6 - Int((42 - (iFirstWeekDay + iDaysinMonth)) / 7)
'calendar table
Dim strPrevMonth, strNextMonth
strPrevMonth = Server.URLEncode(DateAdd("m", -1, dbCurrentDate))
strNextMonth = Server.URLEncode(DateAdd("m", 1, dbCurrentDate))
%>
</head>
<div id="banner"><img src=http://www.htmlforums.com/archive/index.php/"css\banner5.jpg"></div>
<div id="header"><img src=http://www.htmlforums.com/archive/index.php/"css\newlogo2.gif"></div>
<p>
<br wp="br1">
<div id="body2">
<h1>EVENTS CALENDAR</h1><hr size="1">
<P>
<table align=center border="1" bordercolor="#666666" cellspacing=0 cellpadding="5" width=75% height=75%>
<tr>
<th>
<a href=http://www.htmlforums.com/archive/index.php/"Calendar.asp?Date=<%=strPrevMonth%>">
View the Previous Month</a>
</th>
<th colspan=5>
<font size=+2>
<%
'display the month and year 100 lines
Response.Write GetMonthName(Month(dbCurrentDate))
Response.Write "," & Year(dbCurrentDate)
%>
</font></th>
<th>
<a href=http://www.htmlforums.com/archive/index.php/"Calendar.asp?Date=<%=strNextMonth%>">
View the Next Month
</a>
</th>
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=C:\inetpub\wwwroot\search\calendar.mdb"
objConn.Open
Dim strSQL
strSQL = "SELECT Day, EventTitle FROM Event WHERE Month = "&_
Month(dbCurrentDate) & " AND Year = " & Year(dbCurrentDate) &_
" ORDER BY Day ASC"
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL,objConn
Dim iEventCount
iEventCount = 0
Dim iRowsLoop, iColumnsLoop
For iRowsLoop = 1 to iRows
Response.Write "<tr>"
For iColumnsLoop = 1 to iColumns
If aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) > 0 then
Response.Write "<td valign=top align=right width=14% " &_
"Height=" & FormatPercent(1/iRows,0) & ">"
Response.Write "<table cellspacing=0 cellpadding=0 border=0 " &_
"width=""100%"">"
Response.Write "<tr><td align=right><b>"
Response.Write aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "</b></td></tr>"
If Not objRS.EOF then
If objRS("Day") = _
aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) then
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
End If
End If
Response.Write "</table>"
Response.Write "</td>"
Else
Response.Write "<td bgcolor=black> </td>"
End If
Next
Response.Write "</tr>"
Next
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>
<p>
<Form Method=Get Action="News_events.asp">
<center><b>View a Specific Month</b><br>
<select size=1 name=Date>
<option value="<%=DateSerial(Year(dbCurrentDate),1, 1)%>"
<% If Month(dbCurrentDate) = 1 then Response.Write " Selected" %>>
January
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),2, 1)%>"
<% If Month(dbCurrentDate) = 2 then Response.Write " Selected" %>>
February
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),3, 1)%>"
<% If Month(dbCurrentDate) = 3 then Response.Write " Selected" %>>
March
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),4, 1)%>"
<% If Month(dbCurrentDate) = 4 then Response.Write " Selected" %>>
April
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),5, 1)%>"
<% If Month(dbCurrentDate) = 5 then Response.Write " Selected" %>>
May
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),6, 1)%>"
<% If Month(dbCurrentDate) = 6 then Response.Write " Selected" %>>
June
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),7, 1)%>"
<% If Month(dbCurrentDate) = 7 then Response.Write " Selected" %>>
July
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),8, 1)%>"
<% If Month(dbCurrentDate) = 8 then Response.Write " Selected" %>>
August
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),9, 1)%>"
<% If Month(dbCurrentDate) = 9 then Response.Write " Selected" %>>
September
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),10, 1)%>"
<% If Month(dbCurrentDate) = 10 then Response.Write " Selected" %>>
October
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),11, 1)%>"
<% If Month(dbCurrentDate) = 11 then Response.Write " Selected" %>>
November
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),12, 1)%>"
<% If Month(dbCurrentDate) = 12 then Response.Write " Selected" %>>
December
</option></select>
<br>
<input type=submit value="View Select Month">
</form>do until objRS.EOF
If objRS("Day") = _
aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) then
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
End If
loopam i doing something wrong, because when I put that code in none of my events display. Please help!Sorry, I must have left my brain out when I posted, this should do what you want :-If Not objRS.EOF then
'check if recordset day = loop day
do while objRS("Day") = CalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
'loop again if another record for same day
loop
End IfThanks for that, it worked! But.........now I have another problem arising form this one: error 800020009
Exception occurred in:
do while objRS("Day") = aCalendarDays ((iRowsLoop-1)*7 + iColumnsLoop)
Can you tell me what the error means?The error's caused because it's hitting EOF in the loop :-
If Not objRS.EOF then
'check if recordset day = loop day
do while objRS("Day") = CalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
'check for the end of the recordset
if objRs.Eof then exit do
'loop again if another record for same day
loop
End IfBrilliant! Thank-you.I would like to point out that your writing unneeded code. VB has a function for getting the month name.
MonthName(intMonth,blnAbbrv)Thank-you but as I have no previous programming experience I am learning things piecemeal as I go along
Probably not the best way, and very frustrating.......Thank-you but as I have no previous programming experience I am learning things piecemeal as I go along Probably not the best way, and very frustrating.......
Here is the code:
<%@ Language=VBScript %>
<!--#include virtual="adovbs.inc"-->
<%
'Determine the month name for a given month. Numeric value outputs month name
Function GetMonthName(iMonth)
Select Case iMonth
Case 1:
GetMonthName="January"
Case 2:
GetMonthName="February"
Case 3:
GetMonthName="March"
Case 4:
GetMonthName="April"
Case 5:
GetMonthName="May"
Case 6:
GetMonthName="June"
Case 7:
GetMonthName="July"
Case 8:
GetMonthName="August"
Case 9:
GetMonthName="September"
Case 10:
GetMonthName="October"
Case 11:
GetMonthName="November"
Case 12:
GetMonthName="December"
Case ELSE:
GetMonthName = "**INVALID MONTH**"
End Select
End Function
'line40
'Obtain the current date
Dim dbCurrentDate
dbCurrentDate = Request("Date")
If Len (dbCurrentDate) = 0 then
dbCurrentDate = Request.Cookies("CalendarDate")
End If
If Len(dbCurrentDate) = 0 then
dbCurrentDate = Date()
End If
Response.Cookies("CalendarDate").Expires = Now() + 30
'Create an array to store the 42 possible days of the month
Dim aCalendarDays(42)
'Into Calendardays place the days of the current month
Dim iFirstWeekday
iFirstWeekday = DatePart("w", DateSerial(Year(dbCurrentDate),_
Month(dbCurrentDate), 1))
'Loop from 1 to the number of days in the current month, populating the array aCalendarDays
Dim iDaysinMonth
iDaysinMonth = DatePart("d", DateSerial(Year(dbCurrentDate),_
Month(dbCurrentDate)+ 1, 1-1))
Dim iLoop
For iLoop = 1 to iDaysinMonth
aCalendarDays(iLoop + iFirstWeekday - 1) = iLoop
Next
'Display the populated array in calendar form. Create the calendar table
Dim iColumns, iRows
iColumns = 7
iRows = 6 - Int((42 - (iFirstWeekDay + iDaysinMonth)) / 7)
'calendar table
Dim strPrevMonth, strNextMonth
strPrevMonth = Server.URLEncode(DateAdd("m", -1, dbCurrentDate))
strNextMonth = Server.URLEncode(DateAdd("m", 1, dbCurrentDate))
%>
</head>
<div id="banner"><img src=http://www.htmlforums.com/archive/index.php/"css\banner5.jpg"></div>
<div id="header"><img src=http://www.htmlforums.com/archive/index.php/"css\newlogo2.gif"></div>
<p>
<br wp="br1">
<div id="body2">
<h1>EVENTS CALENDAR</h1><hr size="1">
<P>
<table align=center border="1" bordercolor="#666666" cellspacing=0 cellpadding="5" width=75% height=75%>
<tr>
<th>
<a href=http://www.htmlforums.com/archive/index.php/"Calendar.asp?Date=<%=strPrevMonth%>">
View the Previous Month</a>
</th>
<th colspan=5>
<font size=+2>
<%
'display the month and year 100 lines
Response.Write GetMonthName(Month(dbCurrentDate))
Response.Write "," & Year(dbCurrentDate)
%>
</font></th>
<th>
<a href=http://www.htmlforums.com/archive/index.php/"Calendar.asp?Date=<%=strNextMonth%>">
View the Next Month
</a>
</th>
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=C:\inetpub\wwwroot\search\calendar.mdb"
objConn.Open
Dim strSQL
strSQL = "SELECT Day, EventTitle FROM Event WHERE Month = "&_
Month(dbCurrentDate) & " AND Year = " & Year(dbCurrentDate) &_
" ORDER BY Day ASC"
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL,objConn
Dim iEventCount
iEventCount = 0
Dim iRowsLoop, iColumnsLoop
For iRowsLoop = 1 to iRows
Response.Write "<tr>"
For iColumnsLoop = 1 to iColumns
If aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) > 0 then
Response.Write "<td valign=top align=right width=14% " &_
"Height=" & FormatPercent(1/iRows,0) & ">"
Response.Write "<table cellspacing=0 cellpadding=0 border=0 " &_
"width=""100%"">"
Response.Write "<tr><td align=right><b>"
Response.Write aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "</b></td></tr>"
If Not objRS.EOF then
If objRS("Day") = _
aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) then
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
End If
End If
Response.Write "</table>"
Response.Write "</td>"
Else
Response.Write "<td bgcolor=black> </td>"
End If
Next
Response.Write "</tr>"
Next
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>
<p>
<Form Method=Get Action="News_events.asp">
<center><b>View a Specific Month</b><br>
<select size=1 name=Date>
<option value="<%=DateSerial(Year(dbCurrentDate),1, 1)%>"
<% If Month(dbCurrentDate) = 1 then Response.Write " Selected" %>>
January
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),2, 1)%>"
<% If Month(dbCurrentDate) = 2 then Response.Write " Selected" %>>
February
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),3, 1)%>"
<% If Month(dbCurrentDate) = 3 then Response.Write " Selected" %>>
March
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),4, 1)%>"
<% If Month(dbCurrentDate) = 4 then Response.Write " Selected" %>>
April
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),5, 1)%>"
<% If Month(dbCurrentDate) = 5 then Response.Write " Selected" %>>
May
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),6, 1)%>"
<% If Month(dbCurrentDate) = 6 then Response.Write " Selected" %>>
June
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),7, 1)%>"
<% If Month(dbCurrentDate) = 7 then Response.Write " Selected" %>>
July
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),8, 1)%>"
<% If Month(dbCurrentDate) = 8 then Response.Write " Selected" %>>
August
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),9, 1)%>"
<% If Month(dbCurrentDate) = 9 then Response.Write " Selected" %>>
September
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),10, 1)%>"
<% If Month(dbCurrentDate) = 10 then Response.Write " Selected" %>>
October
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),11, 1)%>"
<% If Month(dbCurrentDate) = 11 then Response.Write " Selected" %>>
November
</option>
<option value="<%=DateSerial(Year(dbCurrentDate),12, 1)%>"
<% If Month(dbCurrentDate) = 12 then Response.Write " Selected" %>>
December
</option></select>
<br>
<input type=submit value="View Select Month">
</form>do until objRS.EOF
If objRS("Day") = _
aCalendarDays((iRowsLoop-1)*7 + iColumnsLoop) then
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
End If
loopam i doing something wrong, because when I put that code in none of my events display. Please help!Sorry, I must have left my brain out when I posted, this should do what you want :-If Not objRS.EOF then
'check if recordset day = loop day
do while objRS("Day") = CalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
'loop again if another record for same day
loop
End IfThanks for that, it worked! But.........now I have another problem arising form this one: error 800020009
Exception occurred in:
do while objRS("Day") = aCalendarDays ((iRowsLoop-1)*7 + iColumnsLoop)
Can you tell me what the error means?The error's caused because it's hitting EOF in the loop :-
If Not objRS.EOF then
'check if recordset day = loop day
do while objRS("Day") = CalendarDays((iRowsLoop-1)*7 + iColumnsLoop)
Response.Write "<tr><td valign=top align=left>"
Response.Write "<font size=1>"
Response.Write objRS("EventTitle")
objRS.MoveNext
iEventCount = iEventCount + 1
Response.Write "</font>"
Response.Write "</td></tr>"
'check for the end of the recordset
if objRs.Eof then exit do
'loop again if another record for same day
loop
End IfBrilliant! Thank-you.I would like to point out that your writing unneeded code. VB has a function for getting the month name.
MonthName(intMonth,blnAbbrv)Thank-you but as I have no previous programming experience I am learning things piecemeal as I go along
Probably not the best way, and very frustrating.......Thank-you but as I have no previous programming experience I am learning things piecemeal as I go along Probably not the best way, and very frustrating.......