Requirements
I was asked to make an online calendar for summer cottage rentals, with the following feaures:
- month per page,
- if one or more units is rented for a certain day, show all units in the list for that day,
- ability to control the display order of rental units and add/subtract from the rental list,
- color-coded entries lining up across the page,
- MS Access database backend, and
- password protected admin pages.
Background
Hoping to find a shareware to meet my requirements, I found Paul Apostolos' excellent article, "Building an Event Calendar Web Application". This became my starting point.
Database
In the tbl_place table, the place
field holds the full rental unit name used in the dropdown listbox on the New and EditDel page. The abbre
field is the rental header abbreviation (two letters) used for display. In the Datasheet view, put your own place
and abbre
values. dsplOrder
is the display order of the cottages for each day. Change the numbering here to determine the order on the calendar. Leave blank (null) to omit that unit from being displayed.
Using the code
As Paul's article points out, each day on the calendar is rendered separately, so it's better to store whatever you need from the database once for the page you are displaying. The myCollection
and MyDates
are from Paul's article. I have added the last three arrays to keep track of the display requirements. These stores are filled and emptied for each day being displayed in Calendar1_DayRender()
.
public ArrayList myCollection;
public struct MyDates
{
public int _ID;
public DateTime _date;
public string _renter;
public long _place;
public long _dsplOdr;
}
public ArrayList rentalHeaders;
public ArrayList placeDisplayOrder;
public ArrayList placeID;
EditDel.aspx
Calendar1_DayRender()
: first, GetDBItems()
fills the myCollection
array with MyDates
structs (database records) for the month we are displaying. ORDER BY
tbl_events.date
and tbl_place.dsplOrder
so a DataReader
can make one pass and get each day with the correct cottage display order. Get the two digit rental headers. In the foreach
loop, we check the date. Now, build an HTML line for that day.
private void Calendar1_DayRender(object sender,
System.Web.UI.WebControls.DayRenderEventArgs e)
{
DateTime dayHold;
dayHold=Calendar1.TodaysDate.AddYears(-40);
bool dayTextHasChanged = false;
temp = new StringBuilder();
placeDisplayOrder = new ArrayList();
if(myCollection==null)
{
GetDBItems();
GetRentalHeaders();
}
foreach(MyDates Item in myCollection)
{
if(dayHold.CompareTo(Item._date)==0)
{
if(dayTextHasChanged==true)
{
return;
}
dayHold=Item._date;
}
if(e.Day.Date.ToShortDateString()==Item._date.ToString("d"))
{
placeDisplayOrder.Add((int)Item._dsplOdr);
temp.Append("<span style='font-family:Arial; " +
"font-weight:bold;font-size:12px;'>");
Display(sequence, (int)Item._dsplOdr-1, Item._renter);
sequence++;
dayTextHasChanged = true;
}
}
if(dayTextHasChanged == true)
{
DisplayLast(sequence, (int)placeD
isplayOrder[placeDisplayOrder.Count-1]);
temp.Append("</font>");
temp.Append("</span>");
e.Cell.Controls.Add(new LiteralControl(temp.ToString()));
sequence=0;
}
}
Display()
: adds to the HTML line we are building for the current day. The DataReader
is going row by row through the database. When we start a new day, the value of sequence
will be 0. pl
is the cottage display number. Suppose that number is 3. The first for
loop prints the rental headers 0, 1, and 2. When i
hits 3, we print the renter. If it's not the first cottage with a renter for the current day, we go to the else
statement. In the placeDisplayOrder
array, if the last number minus the second to last number in the array isn't equal to 1, then we have to print the rentalHeaders
in between those two cottages.
void Display(int sq, int pl, string renter)
{
string textColor;
if(sq==0)
{
for(int i=sq; i<=pl; i++)
{
textColor = GetTextColor(i);
temp.Append("<font color='"+textColor+"'>");
temp.Append(rentalHeaders[i]);
if(i==pl)
{
temp.Append(renter);
}
temp.Append("</font>");
}
}
else
{
if((int)placeDisplayOrder[sq]-(int)placeDisplayOrder[sq-1]!=1)
{
for(int i=(int)placeDisplayOrder[sq-1]; i<=pl; i++)
{
textColor = GetTextColor(i);
temp.Append("<font color='"+textColor+"'>");
temp.Append(rentalHeaders[i]);
if(i==pl)
{
temp.Append(renter);
}
temp.Append("</font>");
}
}
else
{
textColor = GetTextColor((int)placeDisplayOrder[sq-1]);
temp.Append("<font color='"+textColor+"'>");
temp.Append(rentalHeaders[(int)placeDisplayOrder[sq-1]]);
temp.Append(renter);
temp.Append("</font>");
}
}
}
When the foreach
loop in Calendar1_DayRender()
hits a new date, we use DisplayLast()
to check for any remaining rentalHeaders
, and add them to the HTML string for that day. In other words, if there are 6 cottages for rent but the sixth cottage has no renter, we still want to display the rentalHeader
for the last cottage.
void DisplayLast(int sq, int pl)
{
int remainingHeaders = (int)Session["numRentals"]-pl;
int i=0;
while(i<remainingHeaders)
{
string txtcolor = GetTextColor(pl+i);
temp.Append("<font color='"+txtcolor+"'>");
temp.Append(rentalHeaders[pl+i]);
i++;
}
}
In the calendar, day numbers are hyperlinks. When you click on one, the Calendar1_SelectionChanged()
is called. First, grab all the bookings from that day into a DataReader
. Build a hyperlink with a query string of the ID, cottage, and renter. Using the HtmlGenericControl
class lets you run regular HTML tags from the server. The selectedday
<span>
tag is used to display the date. The daydetail_render
<span>
tag is used to display the hyperlink. The daydetail
<div>
tag encloses the other two tags, and is used to control when the other two are visible. Here's the HTML code inside the <form>
tag:
<div id="daydetail" runat="server" Visible="False">
<h3>Update or Delete:
<span id="selectedday" runat="server"></span>
</h3>
<span id="daydetail_render" runat="server"></span>
</div>
Here's the code-behind:
private void Calendar1_SelectionChanged(object sender, System.EventArgs e)
{
string temp="";
lblMsg.Text = "";
string selDate = "#"+Calendar1.SelectedDate.ToShortDateString()+"#";
string sql = "SELECT tbl_events.ID, tbl_events.date," +
" tbl_events.cust, tbl_place.place " +
"FROM tbl_events INNER JOIN tbl_place" +
" ON tbl_events.place_ID = tbl_place.ID" +
"WHERE [date] = "+selDate+" ORDER BY tbl_place.ID";
conn = new OleDbConnection(strConn);
try
{
conn.Open();
cmd = new OleDbCommand(sql, conn);
dr = cmd.ExecuteReader();
int i=0;
if(dr.HasRows)
{
while(dr.Read())
{
temp += "<a href='EditDel.aspx?ID=" +dr["ID"]+"'>" +
dr["place"]+": "+dr["cust"]+ "</a><br>";
i++;
}
daydetail.Visible = true;
daydetail_render.InnerHtml = temp;
selectedday.InnerHtml =
Calendar1.SelectedDate.ToString("MMM d, yyyy");
dr.Close();
conn.Close();
Calendar1.Attributes.Add("style", calendarPos(i));
}
}
catch
{
conn.Close();
}
}
All the bookings for a selected day appear above the calendar as hyperlinks. In Calendar1.Attributes.Add("style", calendarPos(i));
, i
is the number of lines of hyperlinks. calendarPos(i)
changes the absolute position of the calendar down i
times so you can see the hyperlinks.
Here's the display after choosing a day:
Clicking on a hyperlink (School House) tells the server to open the EditDel.aspx (same page you're on), only this time there's a query string attached. Bind()
sees the query string: if(Request.QueryString["ID"]!= null)
makes visible and populates another calendar control, a drop down box, and a text box with data from the record based on the ID. Calendar1.SelectionMode = (CalendarSelectionMode)0;
turns off the day number hyperlinks on the main calendar to prevent the user from choosing another date before either updating or deleting the currently selected day or using the back button (which is the same as cancel). Here's the display after choosing a hyperlink.
When the Update button is clicked, IsAvailable()
checks for double bookings and then displays a message.
bool IsAvailable(DateTime date, int place, int id)
{
bool isAvail = true;
if(myCollection==null)
{
GetDBItems();
}
foreach(MyDates Item in myCollection)
{
if(id != Item._ID)
{
if(Item._date == date && Item._place == place)
{
isAvail=false;
}
}
}
if(isAvail==false)
{
lblMsg.Text = "<span style='font-family:Arial; " +
"font-weight:bold;font-size:16px; color:red;'>" +
"This date and cottage is " +
"already booked.</span>";
return false;
}
else
{
lblMsg.Text = "<span style='font-family:Arial; " +
"font-weight:bold;font-size:16px; color:green;'>" +
"Record updated.</span>";
return true;
}
}
Folders
Here is the application in my inetpub/wwwroot/. All the public pages are in the cottrent root folder and don't require login. The admin folder holds the Add.aspx, EditDel.aspx, login.aspx, and the link page admin.aspx. The db folder holds the cottrent.mdb Access database file.
Web.config
The Web.config file in the root folder keeps the connection strings, one for local and one for remote. The local version stays on my machine while the remote version is on the Windows host server. This way, I don't have to change any code or recompile between testing on the local server and uploading to the remote server. The remote string is commented out so this config file is used on my local machine. The code goes between <configuration>
and <system.web>
:
<appSettings>
<add key="AccessConnStr" value="Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\inetpub\\wwwroot\
\cottrent\\db\\cottrent.mdb;" />
</appSettings>
In my C# code, I declare the string.
protected static string strConn =
ConfigurationSettings.AppSettings["AccessConnStr"];
Also, in the root Web.config is the forms login. Set your username and password here.
<authentication mode="Forms" >
<forms loginUrl="admin/login.aspx" >
<credentials passwordFormat="Clear">
<user name="user" password="password"/>
</credentials>
</forms>
</authentication >
<authorization>
<allow users="*" />
</authorization>
Another Web.config in the admin folder allows the user named "user" and calls the login form. Everyone else is denied.
<authorization>
<allow users="user" />
<deny users="*" />
</authorization>
Printer friendly
Here's the HTML for dayrental.aspx generated by Visual Studio. The text in bold (see first page) is removed and put in the style
section (see text in bold on the second snippet).
<HTML>
<HEAD>
<title>dayrental</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Calendar id="Calendar1"
style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px"
runat="server" Width="910px"
BorderColor="Black" SelectionMode="None"
PrevMonthText="<<Back"
NextMonthText="Next>>">
<DayStyle Font-Size="12px" Font-Names="Verdana"
Font-Bold="True" Wrap="False" HorizontalAlign="Left"
Height="100px" BorderWidth="1px"
BorderStyle="Solid" Width="120px"
VerticalAlign="Top"></DayStyle>
<NextPrevStyle Font-Size="14px"></NextPrevStyle>
<DayHeaderStyle Font-Names="Verdana"></DayHeaderStyle>
<SelectedDayStyle ForeColor="Black"
BackColor="White"></SelectedDayStyle>
<TitleStyle Font-Size="18px"
Font-Names="Verdana" Font-Bold="True"></TitleStyle>
<OtherMonthDayStyle ForeColor="DarkGray">
</OtherMonthDayStyle>
</asp:Calendar>
</form>r>
</body>
</HTML>
To make the calendar print correctly, take the properties out of the <form>
section and put them into the <style>
section in <head>
. Make one version for the screen, and one for print, using the CSS @media
tag.
<HTML>
<HEAD>
<title>dayrental</title>
<meta name="GENERATOR"
Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="http://schemas.microsoft.com/intellisense/ie5">
<style type="text/css">
@media Screen { .dayrentcal { WIDTH: 910px; }
.caldaystyle { FONT-SIZE: 12px; Height: 100px; }}
@media Print { .dayrentcal { WIDTH: 460pt; }
.caldaystyle { FONT-SIZE: 7.5pt; Height: 90px; }}
</style>
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Calendar id="Calendar1"
style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px"
runat="server" BorderColor="Black" SelectionMode="None"
PrevMonthText="<<Back"
NextMonthText="Next>>" CssClass="dayrentcal">
<DayStyle Font-Names="Verdana" Font-Bold="True" Wrap="False"
HorizontalAlign="Left" BorderWidth="1px" BorderStyle="Solid"
CssClass="caldaystyle" VerticalAlign="Top"></DayStyle>
<NextPrevStyle Font-Size="14px"></NextPrevStyle>
<DayHeaderStyle Font-Names="Verdana"></DayHeaderStyle>
<SelectedDayStyle ForeColor="Black" BackColor="White">
</SelectedDayStyle>
<TitleStyle Font-Size="18px" Font-Names="Verdana" Font-Bold="True">
</TitleStyle>
<OtherMonthDayStyle ForeColor="DarkGray"></OtherMonthDayStyle>
</asp:Calendar>
</form>
</body>
</HTML>
Where did the .dayrentcal
in the <style>
section come from? That is set in Visual Studio in the Properties window of the calendar. The property is called CssClass
. Likewise, .caldaystyle
is set in the same Properties window under DayStyle
, and then CssClass
. That's it. Now, when the user clicks the browser's Window menu or toolbox Print button, the calendar will fit on the page.
Installing locally
Unzip into inetpub/wwwroot. Create a virtual directory in IIS. Open with Visual Studio 2003 or above. Open Access and change the tbl_place table to suit your needs.
Installing remotely
Put the demo project download on your website.
- Create a folder and set it to "Set as an application directory" and "Execute ASP/ASP.NET/PHP Scripts".
- In application directory/Web.config - set the database connection string, username, and password.
- In admin/Web.config - set the username the same as other Web.config.
- Upload the file dayrental.aspx, denied.html, and Web.config into your application directory.
- Create three directories inside your application directory: admin, bin, and db.
- Put Add.aspx, admin.aspx, EditDel.aspx, login.aspx, and Web.config into the admin directory.
- Put cottrent.dll into the bin directory.
- Open Access and change the tbl_place table to suit your needs.
- Put cottrent.mdb into the db directory.
- Set the db directory to "Lock directory from web browsing (still allows access via website scripts)".
- Set permissions to modify.
Senior Web Programmer for the Shubert Organization; I was also the Trombonist/Arranger for Black 47 for 25 years.