Morning All!
I have been creating a website for a sports group at work who which to advertise what events are coming up this month next month and thought that couldn't be to hard, i created a SQL 2005 database with events and created a page that calls the database lists all events where variable month matches the date field. and have finally got it all working and was testing the display when i hit December and got an error as there is no 13th month the same with January.
Is there a better way to be displaying my results than what i have below? Currently i have put a work around in so that it loops through all 12 months but noticed that when i get round to Sep 2010 it'll display events from 2009 and 2010.
would a better filter resolve this problem?
Database Layout
intEventID (PK)
dteEventDate
intCost
IntRating
strTitle
strDescription
Database Connection String
MM_ConnDatabase_STRING = "Provider=SQLOLEDB; Server=eh-sql-v02\sql_instance_2; Initial Catalog=ExtremeSports; User Id=ExtremeUsers; Password=ExtremeUsers"
Session.asp
<% Session("intMonth")=Month(now)
Session("Month")=MonthName(Session("intMonth"))
Session("ShortMonth") = Left(Session("Month"),3)
%> Events.asp
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/ConnDatabase.asp" -->
<!--#include file="includes/session.asp" --> <% Function functionShortName2Number(variable)
Select Case variable
case "Jan"
functionShortName2Number = 1
case "Feb"
functionShortName2Number = 2
case "Mar"
functionShortName2Number = 3
case "Apr"
functionShortName2Number = 4
case "May"
functionShortName2Number = 5
case "Jun"
functionShortName2Number = 6
case "Jul"
functionShortName2Number = 7
case "Aug"
functionShortName2Number = 8
case "Sep"
functionShortName2Number = 9
case "Oct"
functionShortName2Number = 10
case "Nov"
functionShortName2Number = 11
case "Dec"
functionShortName2Number = 12
end select End Function
%>
<%
'Get Variable from Address Bar for Record Set Month
Dim RSEvents__MMColParam
RSEvents__MMColParam = "%"
If (Request.QueryString("Month") <> "") Then
RSEvents__MMColParam = Request.QueryString("Month")
else 'If nothing is in the QueryString use the current short month from the session variable.
RSEvents__MMColParam = Session("ShortMonth")
End If
%>
<%
'Create RecordSet
Dim RSEvents
Dim RSEvents_cmd
Dim RSEvents_numRows Set RSEvents_cmd = Server.CreateObject ("ADODB.Command")
RSEvents_cmd.ActiveConnection = MM_ConnDatabase_STRING RSEvents_cmd.CommandText = "SELECT StrEventTitle, StrOrganiser, DteEventDate, intCost, IntRating FROM dbo.tblEvents WHERE DteEventDate LIKE '%" + RSEvents__MMColParam + "%' ORDER BY DteEventDate ASC" ' 'Dreamweaver CS4 added these additional lines but i commented them out as it does not run correctly and the above does which was taken from an old site 'done in CS3. ' 'RSEvents_cmd.Prepared = true
'RSEvents_cmd.Parameters.Append RSEvents_cmd.CreateParameter("param1", 135, 1, -1, "%" + RSEvents__MMColParam + "%") ' adDBTimeStamp Set RSEvents = RSEvents_cmd.Execute
RSEvents_numRows = 0
%>
<%
'Start Repeat Region
Dim Repeat1__numRows
Dim Repeat1__index Repeat1__numRows = -1
Repeat1__index = 0
RSEvents_numRows = RSEvents_numRows + Repeat1__numRows
%> <%
'Declare any variables here
'Get Page Name
arrvar = Request.ServerVariables("URL")
'/cs/extreme_sports/default.asp
arrvar2 = split(arrvar,"/")
arrvar3 = arrvar2(3)
arrvar4 = split(arrvar3,".")
arrvar5 = arrvar4(0)
' Rename Page depending on...
if arrvar5 = "Default" then
arrvar5 = "Home"
end if
if instr(arrvar5,"_") then
arrvar5 = Replace(arrvar5, "_", " ")
end if
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<!-- *** TITLE *** -->
<title>IS Extreme Sports - <% response.write(arrvar5) %></title> <link href="MyStyles.css" rel="stylesheet" type="text/css" />
</head>
<body>
<map name="Map" id="Map">
<!-- *** IMAGE MAP CHANGE HREF TO LOCAL HOME PAGE *** -->
<area shape="rect" coords="5,38,185,59" href="http://intranet/cs/Extreme_Sports/default.asp" />
<area shape="circle" coords="231,33,26" href="http://intranet/corp" />
</map>
<!-- *** HEADER *** -->
<table border="0" cellpadding="0" cellspacing="0" width="760" height="453">
<tr>
<td width="757" colspan="3" height="1">
<!-- #include virtual = "/cs/Extreme_Sports/includes/banner.ASP" -->
</td>
</tr>
<tr>
<td height="23"></td>
<td height="23">
<img border="0" src="images/spacer540.gif"></td>
<td height="23"></td>
</tr>
<tr>
<td style="border-right: 1px solid #D9D9DA;" width="223" valign="top" height="356">
<!-- *** LEFT COL *** -->
<!--#include virtual="/cs/Extreme_Sports/includes/general.asp"-->
</td>
<td valign="top" height="356">
<!-- *** CENTRE COL *** -->
<!-- *** CONTENTS TABLE *** -->
<table width="100%" height="386" border="0" align="left" cellpadding="0" cellspacing="0">
<tr>
<td width="6" height="48">
<img border="0" src="images/spacer6x6.gif"></td>
<td width="524" height="48" valign="top">
<!-- *** SECTION HEAD TABLE *** -->
<table border="0" cellpadding="0" cellspacing="0" width="532" height="38" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<!-- CELL BACKGROUND CHANGE IMG SRC -->
<td width="523" height="38" background="images/orange528W.jpg" class="Title"> Extreme Sports - <% response.write(arrvar5)%></td>
</tr>
</table>
<div class="Footer"></div>
</td>
</tr>
<!-- ***CONTENTS GOES HERE*** -->
<tr>
<td width="6" height="338"> </td>
<td height="338" valign="top">
<p>
<%
'Get session variables and work out the next month etc
If (Request.QueryString("Month") = "") Then
intCurrentMonth = Session("intMonth")
else
intCurrentMonth = functionShortName2Number(Request.QueryString("Month"))
End If intPreviousMonth = intCurrentMonth-1
intNextMonth = intCurrentMonth+1
strCurrentMonth = MonthName(intCurrentMonth) if intCurrentMonth = 1 then
strPreviousMonth = "Dec Last Year"
else
strPreviousMonth = MonthName(intPreviousMonth)
End If if intCurrentMonth = 12 then
strNextMonth = "Jan Next Year"
else
strNextMonth = MonthName(intNextMonth)
End If
str_Short_PreviousMonth = Left(strPreviousMonth,3)
str_Short_NextMonth = Left(strNextMonth,3)
%>
<div id="EventNavigation">
<table border="0" class="EventNav">
<tr>
<td class="TDEventNav"><a href="Events.asp?Month=<%=str_Short_PreviousMonth%>">Previous Month</a></td>
<td class="TDEventNav"><% Response.Write(strCurrentMonth)%></td>
<td class="TDEventNav"><a href="Events.asp?Month=<%=Str_Short_NextMonth%>">Next Month</a></td>
</tr>
</table>
</div>
<% If RSEvents.EOF And RSEvents.BOF Then %>
<p>Sorry there are no Events Currently for <% Response.Write(strCurrentMonth)%></p>
<%Else%> <table border="0" class="EventTable">
<tr>
<td>Event Name</td>
<td>Event Organiser</td>
<td>Date</td>
<td>Cost</td>
<td>Extreme Rating</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT RSEvents.EOF)) %>
<tr>
<td><%=(RSEvents.Fields.Item("StrEventTitle").Value)%></td>
<td><%=(RSEvents.Fields.Item("StrOrganiser").Value)%></td>
<td><%=(RSEvents.Fields.Item("DteEventDate").Value)%></td>
<td><%=(RSEvents.Fields.Item("intCost").Value)%></td>
<td><%=(RSEvents.Fields.Item("IntRating").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RSEvents.MoveNext()
Wend
%>
</table>
<% End If %>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" height="43" width="920" style="border-top: 1px solid #D9D9DA;" colspan="3">
<p> </td>
</tr>
</table> </body>
</html>
<%
RSEvents.Close()
Set RSEvents = Nothing
%>