Export (0) Print
Expand All
21 out of 684 rated this helpful - Rate this topic

Building a Web-based Event Calendar

 

Scott Semyan
Microsoft Corporation

July 2000

Summary: This article describes how to build a calendar using ASP, Internet Information Services, and Microsoft SQL Server. (18 printed pages)


Contents

Introduction
SQL Back End
ASP Front End
Performance Issues
Conclusion

Introduction

In this article I will demonstrate how to build a Web-based calendar. This article provides a good introduction to data-driven Web sites for developers new to Active Server Pages (ASP), Structured Query Language (SQL), and ActiveX® Data Objects (ADO). For more experienced developers, I also provide tips on scalability.

Figure 1. A Web-based event calendar

What Are Web-based Calendars?

Web-based calendars have become quite popular recently. Originally people kept their schedules in personal information managers, such as Microsoft® Outlook®. The problem with these calendars is that, to put it simply, they are personal and it is difficult to share the calendar out for others to see or use. What was needed was a way to either make your personal calendar available for others to see, or to have a group calendar that many people could use at the same time. With the rise of the Internet—available anytime, anywhere—Web-based calendars became feasible.

What Can You Do with Web-based Calendars?

A Web-based calendar allows you to share your schedule with others or manage a calendar for a team of people. Sharing your calendar is useful if you want people to have access to your schedule. Team calendars, on the other hand, are useful for showing major events, such as deadlines or milestones, and also for showing when people will be on vacation.

What Is Presented Here?

At my company we have an intranet site where we consolidate all the information relevant to our team, including scheduled vacations, milestones, and holidays. To do this I built a simple Web-based calendar that allowed anyone to view, add, and/or remove events (see Figure 1).

In this article I describe how to build a very simple Web-based calendar using ASP pages within Microsoft Internet Information Services (IIS) and Microsoft SQL Server™. At the conclusion of this article I will discuss the performance implications that must be considered when scaling this application up to many users. For people new to IIS and ASP, this article can be considered a good tutorial for building data-driven Web sites.

SQL Back End

What Data to Save?

The first question is what kind of data do you want to save for each date? For my calendar, I simply needed to save a string of text, up to 100 characters long, that identified the nature of the event. One could easily extend this data model to include much more then just a text string.

Schema

Calendar.sql

The SQL code is contained in the following code snippet:

--------------------------------------------------
-- Tables
--------------------------------------------------

create table Schedule
(
   idSchedule   smallint identity primary key,
   dtDate      smalldatetime not null,
   vcEvent      varchar(100) not null
)
go

--------------------------------------------------
-- Stored Procedures
--------------------------------------------------

create procedure GetSchedule (@nMonth tinyint, @nYear smallint)
as
   select idSchedule, convert(varchar, datepart(dd, dtDate)) 'nDay', vcEvent
   from Schedule
   where datepart(yy, dtDate) = @nYear and datepart(mm, dtDate) = @nMonth
   order by datepart(dd, dtDate)
go

create procedure AddEvent (@vcDate varchar(20), @vcEvent varchar(100))
as
   insert Schedule
   select @vcDate, @vcEvent
go

create procedure DeleteEvent (@idSchedule smallint)
as
   delete Schedule where idSchedule = @idSchedule
go

For my schedule table, I use an identity key (self incrementing) as the primary key so I don't have to make a unique key myself. I choose a smallint for idSchedule because, for my needs, I didn't expect to ever have more than 32,000 different events. For a larger-scale application, you would probably want to use a long integer to give you up to 2 billion records. I save the date as a smalldatetime to save space because I don't need to record time down to the second. A normal datetime, which saves times accurately down to milliseconds, uses twice the space of a smalldatetime, which is accurate down to a minute. Larger-scale applications might want to examine other ways to record dates that take up even less space (for example, an integer showing the number of days after a certain day, such as Jan 1, 1970).

For my data I choose a variable length character string (varchar) up to 100 characters long. In SQL Server 7.0 you could use a nvarchar, which would hold Unicode characters, making your application worldwide capable. There are, however, issues with creating Web pages for an international audience that are beyond the scope of this discussion.

Stored Procedures

A quick note on stored procedures in SQL: Stored procedures are better than hard-coded SQL in the ASP code for two primary reasons. First, keeping the data access code separate from the presentation code ensures the independence of the data. This allows you to change the data structure (and its accompanying stored procedures) without having to alter the ASP pages. This is classic Microsoft Windows® DNA n-tier architecture. The second reason why stored procedures are preferred is that they will run faster than a text query because they are parsed the first time they are called. Therefore, subsequent calls do not have to parse the procedure again.

I need three stored procedures to access and manipulate the data in this table: GetSchedule, AddEvent and DeleteEvent.

For the GetSchedule procedure I pass in the month and year of the data I want back. Next, I use the Datepart() function to get the numerical day of the event. I will then use this number to match up with the day of the month as I am building the calendar. It is important to order the results by day, as we will see later.

The AddEvent procedure simply adds a text event to a given date. I do not worry about adding duplicate events because all the events are marked with a unique schedule ID.

The unique schedule ID is all that is needed to remove an event from the schedule. Passing this into the DeleteEvent procedure removes the event.

ASP Front End

Basic UI

Figure 1 shows the main user interface (UI). The UI is set up like a wall calendar. My week starts on Sunday and includes 7 days. For the sake of simplicity, I have refrained from doing heavy formatting, but it would be easy to use style sheets to make this calendar look much more professional. From this main page, the user can see what events have been scheduled. In addition, they can move forward and backward in the calendar by month using the links at the bottom, or they can select a specific month and year using the drop-down list boxes in the upper-left corner of the page. The link to the Add/Remove Events page is directly below the calendar.

ASP Code Basics

Header.asp

<%@ LANGUAGE="VBSCRIPT"
   ENABLESESSIONSTATE = False %>
<%
'********************************************************************
' Name: Header.asp
'
' Purpose: Header include file used to start all pages
'   also includes global functions
'
'********************************************************************

Option Explicit
Response.Buffer = True
Response.Expires = 0

sub DoHeader(strTitle)
%>
   <html>
   <head>
   <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso8859-1">
   <title>Event Calendar - <%= strTitle %></title>
   </head>

   <body bgcolor="white" link="blue" alink="blue" vlink="blue">
   <basefont face="Verdana, Arial">

   <center><h1>Event Calendar</h1>
   <h3><%= strTitle %></h3>
<%
end sub

sub DoFooter(strTitle)
%>
   </center>
   </body></html>
<%
end sub

function GetDataConnection()
   dim oConn, strConn
   Set oConn = Server.CreateObject("ADODB.Connection")
   strConn = "Provider=SQLOLEDB; Data Source=adspm; Initial Catalog=TeamWeb; "
   strConn = strConn & "User Id=TeamWeb; Password=x"
   oConn.Open strConn
   set GetDataConnection = oConn
end function
%>

When I code a Web site in ASP I like to have a single header page for the entire site. This allows me to have one page on which to put commonly used functions, styles, Microsoft JScript®, etc. For this site, I have set up a header page (see the preceding code example) that sets some ASP settings for the entire site and contains three functions that I use in all other pages of the site: an HTML header and footer, and my data connection code.

The first thing I do is set the language to Microsoft Visual Basic® Scripting Edition (VBScript) and disable session state. Setting the language is not necessary but is a good idea. Also, this site makes no use of ASP session state, which I explicitly tell IIS, thus improving performance slightly. My other coding conventions are to set Option Explicit (meaning I must dim variables before using them), buffered output (IIS will hold output and then spit it all out at once-this improves performance), and to set the page to expire immediately (so that users will always get fresh pages).

I use a DoHeader function that handles all of my HTML header code. I pass in the name of the page, which is displayed both in the HTML title and the H3 tag at the top of the page. This gives me a single place to edit HTML BODY attributes, styles, etc. Similar to the DoHeader function, I like to have a footer function in case I need to add a common footer (with contact information, etc.).

ADO lets me connect my ASP page with my SQL database very easily. First I create a connection to the database. To get a recordset I call the Execute method of the Connection object passing in a text string of the command I wish to perform. Once I have the recordset, I can loop through it. Header.asp contains the function to get my data connections. This means I only have one place where I need to edit connection information (server, user, password) if my data source changes. Note that at the end of the function I must use the set command to pass the new connection out as a result.

How It Works

Calendar.asp

<!--#includes file="Header.asp"-->
<%
'********************************************************************
' Name: Calendar.asp
'
' Purpose: Main calendar page that shows events in a calendar format
'
'********************************************************************

dim dbConn, rs, nDex, nMonth, nYear, dtDate

' Get the current date
dtDate = Now()

' Set the Month and Year
nMonth = Request.QueryString("nMonth")
nYear = Request.QueryString("nYear")
if nMonth = "" then nMonth = Month(dtDate)
if nYear = "" then nYear = Year(dtDate)

' Set the date to the first of the current month
dtDate = DateSerial(nYear, nMonth, 1)

Set dbConn = GetDataConnection
Set rs = dbConn.Execute ("GetSchedule " & nMonth & ", " & nYear)

DoHeader(MonthName(Month(dtDate)) & "&nbsp;&nbsp;" & nYear)
%>
<form method="get" name="DateSelect" action="Calendar.asp">

<table width=700>
<tr><td colspan=2><select name="nMonth" onChange="DateSelect.submit();"><%
      for nDex = 1 to 12
         Response.Write "<option value=""" & nDex & """"
         if MonthName(nDex) = MonthName(nMonth) then
            Response.Write " selected"
         end if
         Response.Write ">" & MonthName(nDex)
      next %></select>&nbsp;
   <select name="nYear" onChange="DateSelect.submit();"><%
      ' Note: I have set the year to be between 1999 and 2000
      for nDex = 1999 to 2002
         Response.Write "<option value=""" & nDex & """"
         if nDex = CInt(nYear) then Response.Write " selected"
         Response.Write ">" & nDex
      next %></select></td></tr>

<tr><td colspan=2>
   <table border=1 bgcolor="gray" cellpadding=3>
   <tr bgcolor="Blue"><td width=90><font color="white"><b>Sunday</b></font></td>
      <td width=90><font color="white"><b>Monday</b></font></td>
      <td width=90><font color="white"><b>Tuesday</b></font></td>
      <td width=90><font color="white"><b>Wednesday</b></font></td>
      <td width=90><font color="white"><b>Thursday</b></font></td>
      <td width=90><font color="white"><b>Friday</b></font></td>
      <td width=90><font color="white"><b>Saturday</b></font></td></tr>
   <tr bgcolor="#ffffc0">
      <%
      ' Add blank cells until the proper day
      for nDex = 1 to Weekday(dtDate) - 1
         Response.Write "<td bgcolor=""#c0c0c0"">&nbsp;</td>"
      next

      do
         Response.Write "<td valign=""top"">" & Day(dtDate) & "<br>&nbsp;<br>"

         if not rs.EOF then
            do
               if CInt(rs("nDay")) <> CInt(Day(dtDate)) then exit do

               Response.Write "<font size=""-1""><b>" & rs("vcEvent")
               Response.Write "</b></font><br>"
               rs.MoveNext

               if rs.EOF then exit do
            loop
         end if

            Response.Write "</td>"

            if WeekDay(dtDate) = 7 then
               Response.Write "</tr>" & vbCrLf & "<tr  bgcolor=""#ffffc0"">"
            end if
            dtDate = DateAdd("d", 1, dtDate)
         loop until (Month(dtDate) <> CInt(nMonth))

         ' Add blank cells to fill out the rest of the month if needed
         if Weekday(dtDate) <> 1 then
            for nDex = Weekday(dtDate) to 7
               Response.Write "<td bgcolor=""#C0C0C0"">&nbsp;</td>"
            next
         end if
         %>
      </tr>
   </table></td></tr>

<tr><td colspan=2 align="center"><b><a
   href="Events.asp?nMonth=<%= nMonth %>&nYear=<%= nYear %>"
   >Add/Remove Events</a></td></tr>

<tr><td><a href="Calendar.asp?nMonth=<%
            if nMonth = 1 then
               Response.Write "12&nYear=" & nYear - 1
            else
               Response.Write nMonth - 1 & "&nYear=" & nYear
            end if %>"><b>&lt;- Previous Month</b></a></td>
      <td align=right><a href="Calendar.asp?nMonth=<%
         if nMonth = 12 then
            Response.Write "1&nYear=" & nYear + 1
         else
            Response.Write nMonth + 1 & "&nYear=" & nYear
         end if %>"><b>Next Month - &gt;</b></a>
      </td>
   </tr>
</table>

</form>

<%
DoFooter("Home")
%>

The most challenging part of this project was building an HTML calendar that was accurate. There are many algorithms to determine-for example, how many days are in each month, whether a year is a leap year, etc. Rather than work through all that, I used the date object within Visual Basic to build my calendar.

To lay out the calendar for a given month, I first create a date object for the first day of that month and year. I then simply increment the date forward a day at a time using the DateAdd() function. I can then use the built-in methods to show what day of the week it is, and when the month ends. I also know the day of the month and use this to synchronize with my schedule information. This allows me to build a table that shows a calendar.

The Event Calendar page is built dynamically using the date and year passed in the query string. If no month or year is passed in, the current month and year are used.

This makes it easy to build UI that can move between months. The link to go back a month is created by calling Calendar.asp and supplying a query string that indicates the previous month (and decrementing the year if we are in January). Moving forward a month is just as easy (first checking to see if we are in December). Moving to a specific month and year is easier still. All we do is set up a form with Calendar.asp as the target, fill some drop-down list boxes with the months and years we want to cover, and then attach a submit event to the onChange() event for the drop-downs lists. This allows the page to update automatically when the user picks a new month or year. Notice how I use the MonthName() function to list the months.

Showing events is quite easy. First I pull out the events for the given month using ADO to connect to my SQL database. I call my GetSchedule stored procedure and pass in the month and year in question. This gives me a recordset containing the list of events, along with the day in which they occur (sorted by day).

Now, while I am building the calendar, I simply show the events on the day in which they occur. I can do this because I know my events are sorted by day.

Figure 2. The Add/Remove Events page

The Add/Remove Events page allows a user to either add a new event or remove an existing event. The page is accessed by first moving the calendar to the proper month and then clicking the Add/Remove Event link. This link is built to include a query string that indicates what month and year to add/remove events.

The list of current events for the month is generated using the same GetSchedule stored procedure as Calendar.asp. This time all we need to do is simply cycle through the recordset to show all the events for the month.

Events.asp

<!--#includes file="Header.asp"-->
<%
   '********************************************************************
   ' Name: Events.asp
   '
   ' Purpose: Used to add and remove events from the event database.
   '
   '********************************************************************

   dim dbConn, rs, nDex, nMonth, nYear, dtDate, dtTemp

   ' Get the current date
   dtDate = Now()

   ' Set the Month and Year
   nMonth = Request("nMonth")
   nYear = Request("nYear")
   if nMonth = "" then nMonth = Month(dtDate)
   if nYear = "" then nYear = Year(dtDate)

   Set dbConn = GetDataConnection
   Set rs = dbConn.Execute ("GetSchedule " & nMonth & ", " & nYear)

   DoHeader("Add/Remove Events")
%>

   <form method="get" action="AddEvent.asp">

   <br>Currently Scheduled Events for <b><%
    Response.Write MonthName(nMonth) & "&nbsp;&nbsp;" & nYear
    %></b>:<p>
   <table bgcolor="gray" border=1 cellpadding=3>
   <tr bgcolor="Blue"><td><font color="white"><b>Day</b></font></td>
      <td colspan=2><font color="white"><b>Event</b></font></td></tr>

   <% if rs.EOF then
      Response.Write "<tr><td colspan=3 bgcolor=""#ffffc0"">No events listed</td></tr>"
   end if

   while not rs.EOF
      Response.Write "<tr bgcolor=""#ffffc0""><td>" & rs("nDay") & "</td><td>"
      Response.Write rs("vcEvent") & "</td><td><input type=""button"" value=""Remove"""
      Response.Write " onClick=""window.location.href='RemoveEvent.asp?nMonth=" & nMonth
      Response.Write "&nYear=" & nYear & "&idSchedule=" & rs("idSchedule") & "'""></td></tr>"
      rs.MoveNext
   wend
   %></table>

   <p><br>
   <table bgcolor="gray" border=1 cellpadding=3>
      <tr bgcolor="Blue"><td><font color="white"><b>Add New Event:</b></font></td></tr>
      <tr bgcolor="#ffffc0"><td>Event:
         <input type="text" size=30 maxlength=100 name="Event"> Day: <select name="nDay"><%
         ' Set the date to the first of the current month
         dtDate = DateSerial(nYear, nMonth, 1)

         dtTemp = dtDate
         do
            Response.Write "<option value=""" & Day(dtTemp) & """>" & Day(dtTemp)
            dtTemp = DateAdd("d", 1, dtTemp)
         loop until (Month(dtTemp) <> CInt(nMonth)) %></select>
         <input type="hidden" name="nMonth" value="<%= nMonth %>">
         <input type="hidden" name="nYear" value="<%= nYear %>">
         <input type="Submit" value="Add Event"></td></tr>
   </table>
   </form>

   <p><a href="Calendar.asp?nMonth=<%= nMonth %>&nYear=<%= nYear %>">Back to Calendar</a>

<%
   DoFooter("Home")
%>

To add a new event the user simply types their event into the form and then clicks the Submit button. The form element is limited to 100 characters to match the event field in the Schedule table using the maxlength attribute.

To remove an event the user clicks a button next to the event they wish to remove. This button calls RemoveEvent.asp, passing in the idSchedule of the event along with the current month and year so that the link back to Events.asp can be built.

AddEvent.asp

<!--#includes file="Header.asp"-->
<%
   '********************************************************************
   ' Name: AddEvent.asp
   '
   ' Purpose: Used to add an event to the event database.
   '
   '********************************************************************

   dim dbConn, strSQL

   Set dbConn = GetDataConnection
   strSQL = "AddEvent '" & Request("nMonth") & "/" & Request("nDay")
   strSQL = strSQL & "/" & Request("nYear") & "', '"
   strSQL = strSQL & Replace(Request("Event"), "'", "''") & "'"

   dbConn.Execute (strSQL)

   Response.Redirect("Events.asp?nMonth=" & Request("nMonth") & "&nYear=" & Request("nYear"))
%>

The AddEvent.asp page takes the information passed in by the form and inserts it into the database using the AddEvent stored procedure. The page then redirects back to the Add/Remove Events page so the user immediately sees their new event. Notice how I use the Replace() function to change single quotes (') to double single quotes (''), which SQL interprets as single quotes. If you don't do this you will get an error when a user tries to enter an event like "All Saint's Day."

RemoveEvent.asp

<!--#includes file="Header.asp"-->
<%
   '********************************************************************
   ' Name: RemoveEvent.asp
   '
   ' Purpose: Used to remove an event from the event database.
   '
   '********************************************************************

   dim dbConn

   Set dbConn = GetDataConnection
   dbConn.Execute ("DeleteEvent " & Request("idSchedule") )

   Response.Redirect("Events.asp?nMonth=" & Request("nMonth") & "&nYear=" & Request("nYear"))
%>

The RemoveEvent.asp page uses the DeleteEvent stored procedure to remove the given event ID, and then immediately sends the user back to the Events page.

Performance Issues

Scaling

The beauty of ASP is that it makes building Web pages very easy. However, if you want to build a site that can scale to a high volume of users, you need to code carefully. In this section I will discuss various methods to increase the scalability of the Web-based calendar I have presented here. Although specific to this application, the methods I describe can be used to improve performance on any ASP Web site.

SQL Optimizations

An easy way to improve the performance of the site is to add an index on the date field of the Schedule table. This will speed up the GetEvents stored procedure because it looks for events between given dates.

For small sites, SQL can be installed on the same server as IIS. Once the site starts to grow, you will want to move SQL to its own server. As the site grows further still, you may want to add multiple IIS servers that all point to the same SQL server. If traffic to the SQL server grows excessively, you can partition data onto different servers. You might want to set odd-numbered months to go to one server and even-numbered months to go to another server. Of course, you will want to alter your GetDataConnection in Header.asp so it will give you the proper connection based on the month.

ASP Optimizations

The main optimization for the ASP rendering would be caching pages so they don't have to be rendered each time. The easiest way to do this is through the ASP Application object. To do this, you simply save the page HTML into an application variable that has the name of the month and year (for example, Calendar07-2000). Then, when displaying the Event Calendar page, you first check to see if the calendar has already been saved in an application variable and, if so, simply retrieve it. This saves a trip to the SQL box. Here is some pseudo code that shows how this would work:

<<do header>>

ShowCalendar(nMonth, nYear)

<<do Footer>>

sub ShowCalendar(nMonth, nYear)

   if Application("Calendar" & nMonth & "-" & nYear) = "" then
      <<Build Calendar>>
      Application("Calendar" & nMonth & "-" & nYear) = <<Calendar>>
   End if

   Response.Write Application("Calendar" & nMonth & "-" & nYear)
End sub

Of course, when the Events.asp page changes a month's events you will need to empty the application variable for that month so the changes are reflected.

Security

There are several ways to implement security on this site. For an intranet site, Microsoft Windows NT®-based authentication is the easiest to set up because your users will most likely already be logged on to the network. You may wish to let all users view the Event Calendar page, but only administrators have access to the Add/Remove Events page.

If auditing is a concern, you can easily modify the AddEvent and DeleteEvent procedures to save their information into an audit table. Alternatively, you can make sure IIS is logging the query string and user for each page hit. Then it is simple to parse through the logs to determine who did what and when.

Conclusion

In this article I have shown how to set up a Web-based calendar. My Web-based calendar is very simple, but it could be easily extended into a full-featured site. I have tried to meticulously describe the steps I followed in creating my Web-based calendar so that beginning users can see ASP, SQL, and ADO in action. Finally, I have provided some tips on how to make this site scale as large as possible. If you find any bugs or have ideas on how I might be able to improve this code, please send me e-mail at semyan@hotmail.com.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.