Building an Event Calendar Web Application
By Paul Apostolos
Introduction
Recently, I was asked by my company's staff marketing committee to develop an application to track
our marketing efforts. Their requirements were simple (to them, that is):
Graphically display all marketing campaigns/opportunities in a calendar type interface.
Display different types of marketing efforts differently so the committee could see overlapping
mailings and cross-marketing opportunities. For example, if we have a mailing going to Washington
state roofing contractors and we are exhibiting at a convention in Tacoma, we could be sure to
bring the items promoted in that mailing to display at our booth.
Make it easy to add and edit all entries. I think they were afraid that if it was a Web-based
application it would require some special, phantom skills -- at least one member of the committee
still prefers a typewriter to a computer.
After looking over the requirements and glancing at my ever growing list of other tasks, I
immediately thought of the ASP.NET Calendar control. I learned about the Calendar control as I think
most ASP.NET developers have - by seeing the default Calendar demo. You know the one... where the
random .NET expert says, "And adding a calendar control to your Web site is now as simple as
inserting just three lines of code." Then he opens up Notepad, types the following lines:
He saves the file and then opens it up in a browser and, Poof!, a calendar appears.
For this application I decided on using the Calendar control, but quickly realized that is was going
to take a whole lot more than three lines of code. In this article, I will step through my application,
and you can see how to build a similar application using the Calendar control.
Creating the Table and Adding a Record to the Database
Since the committee wanted to be able to view a calendar with a list of upcoming events, I first needed
to create a Web interface for users to add new events to the database. For each event, the committee
wanted to track the date, audience, type of promotion (e.g., direct mail, trade show, print
advertisement, etc.), title, and staff person responsible. With these requirements, I created the
following database table (named tbl_Marketing):
Next, I constructed the "Add a New Event" Web page. As you can see in the source code below, the
"Add a New Event" ASP.NET Web page contains a TextBox Web control for the user to enter a Title,
Audience, and Person Responsible. It contains a Calendar control to allow the user to select the
date of the event, and also contains a DropDownList from which the person may choose
the type of event (mailing, house ad, and so on). Additionally, suitable validation controls are used to
ensure that the user enters the correct information in a proper format.
In addition to the Web controls, the "Add a New Event" page contains a Page_Load event handler
and an event handler for the submit button's server-side Click event (named Do_Insert).
When the values have been entered and the form submitted, the Do_Insert is executed and the
specified event is inserted into the tbl_Marketing table.
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>
<Script runat="server">
Sub Page_Load( Sender As Object, e As EventArgs)
If Not Page.IsPostBack then
'Add the items to the _Type DropDownList Web Control
_Type.Items.Add(new ListItem("Mailing", "1"))
_Type.Items.Add(new ListItem("House ad", "2"))
_Type.Items.Add(new ListItem("Trade show", "3"))
...
_Date.SelectedDate = _Date.TodaysDate
End If
End Sub
Sub Do_Insert(Sender As Object, e As EventArgs)
'Only update the database if the user entered valid inputs
If Not Page.IsValid then Exit Sub
'Add the event to the database
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("MySQLDSN"))
Dim myCommand As New _
SqlCommand("Intranet_sp_Marketing_Item_Insert", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterTitle As New SqlParameter("@Title", SqlDbType.VarChar, 50)
parameterTitle.Value = Title.Text
myCommand.Parameters.Add(parameterTitle)
'I used the simplest version of the calendar control to allow the
'user to pick the date. So, to retrieve the value from the control,
'I needed to get the value from the SelectedDate property
Dim parameterDate As New SqlParameter("@_Date", SqlDbType.DateTime, 8)
parameterDate.Value = _Date.SelectedDate
myCommand.Parameters.Add(parameterDate)
Dim parameterAudience As New _
SqlParameter("@Audience", SqlDbType.VarChar, 50)
parameterAudience.Value = Audience.Text
myCommand.Parameters.Add(parameterAudience)
Dim parameterPResponsible As New _
SqlParameter("@PersonResponsible", SqlDbType.VarChar, 50)
parameterPResponsible.Value = PResponsible.Text
myCommand.Parameters.Add(parameterPResponsible)
Dim parameterType As New SqlParameter("@Type", SqlDbType.Int, 4)
parameterType.Value = _Type.SelectedItem.Value
myCommand.Parameters.Add(parameterType)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Response.Redirect("default.aspx") 'Redirect the user to the calendar
End Sub
</script>
<form runat="server">
<table>
<tr>
<td valign="top">Title:</td>
<td><asp:TextBox width="350" id="Title" runat="server"/>
<asp:RequiredFieldValidator id="rfv_Title" ControlToValidate="Title"
runat="server">* This is a required field</asp:RequiredFieldValidator>
<br><br></td>
</tr>
<tr>
<tr><td valign="top">Date:</td>
<td valign="top"><asp:calendar id="_Date" runat="server" />
<br><br></td></tr>
</tr>
<tr>
<td valign="top">Audience:</td>
<td valign="top"><asp:TextBox id="Audience" runat="server"/>
<asp:RequiredFieldValidator id="rfvAudience" ControlToValidate="Audience"
runat="server">* This is a required field</asp:RequiredFieldValidator>
<br><br></td>
</tr>
<tr>
<td valign="top">Person Responsible:</td>
<td valign="top"><asp:TextBox id="PResponsible" runat="server"/>
<asp:RequiredFieldValidator id="rfvPResponsible" ControlToValidate="PResponsible"
runat="server">* This is a required field</asp:RequiredFieldValidator>
<br><br></td>
</tr>
<tr>
<td valign="top">Type:</td>
<td valign="top"><asp:dropdownlist id="_Type" runat="server" />
<asp:RequiredFieldValidator id="rfv_Type" ControlToValidate="_Type"
runat="server">* This is a required field</asp:RequiredFieldValidator>
<br><br></td>
</tr>
<tr>
<td valign="top"> </td>
<td align="center"><asp:Button id="Submit" text="Submit"
OnClick="Do_Insert" runat="server"/></td>
</tr>
</table>
</form>
A screenshot of the "Add a New Event" Web page can be seen on the right. In examining the code you may
notice that there are a few spots for improvement. Most importantly is the DropDownList that lists the
events. Currently, I have the values of the DropDownList hard-coded in (they are added programmatically
in the Page_Load event). Ideally, the various types should be placed in a lookup table,
and a foreign key should be used to ensure referential integrity. I plan on adding this later, but
currently have implemented this "quicker" approach due to pressing deadlines!
The "workhorse" of the "Add a New Event" Web page is the Do_Insert event handler, which adds
the new event to the database. It starts by creating a database connection using the connection
string stored in the application's Web.config file. For more information about Web.config
files and application level settings see Specifying Configuration Settings in
Web.config.
Next, the Do_Insert event handler creates a SQLCommand object (called
myCommand). The SQLCommand is configured to call the stored procedure
Intranet_sp_Marketing_Get_Items.
This is accomplished by setting the command text to the stored procedure name and then setting the command's
CommandType property to CommandType.StoredProcedure. Next, the various
stored procedure parameters are added, and the stored procedure is executed.
The contents of the Intranet_sp_Marketing_Item_Insert stored procedure are quite simple.
The stored procedure accepts a number of input parameters and then uses them to insert a new record into
the tbl_Marketing table.
Now that we have examined how to add a new event, let's turn our attention to editing an existing event.
We'll see how to accomplish exactly this in Part 2 of this
article.
