In Part 1 we examined how to create an ASP.NET Web page to add
a new event to the database. In this part we'll look at editing an existing event. We'll also examine
displaying the Calendar Web control.
Editing an Existing Event
Once I had created a way to add records to the database, I needed a way to edit those records. To
accomplish this I used the same form as the "Add a New Event" Web page with just one major
modification -- the form's fields were pre-populated with the values from the database.
To do this, I used a subroutine called Bind(), which populates the form fields with the
information from the database. Now, because this page is posting back to itself, and I don't want
to call Bind() if the page has been posted back, I placed the call to Bind()
in an If statement that checks for postback.
I also put the code that populates the DropDownList within the same If statement, just
as in the "Add a New Event" Web page.
In the subroutine Bind() a database connection is created and a command object and set
up that calls the stored procedure Intranet_sp_Marketing_Get_Single_Item, which returns the
attributes for a specific event. The results from the stored procedure are then assigned to the appropriate
Web controls.
The complete code (minus the Web controls) for the "Edit a Record" Web page is as follows:
<%@ 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
_Type.Items.Add(new ListItem("Mailing", "1"))
_Type.Items.Add(new ListItem("House ad", "2"))
...
Bind()
End if
End Sub
Sub Bind()
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("MySQLDSN"))
Dim myCommand As New _
SqlCommand("Intranet_sp_Marketing_Get_Single_Item", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterID As New SqlParameter("@ID", SqlDbType.Int, 4)
parameterID.Value = Request.Querystring("ID")
myCommand.Parameters.Add(parameterID)
Dim myDataReader As SqlDataReader
myConnection.Open()
myDataReader = myCommand.ExecuteReader()
myDatareader.Read()
'Assign the values of the stored procedure results to the Web controls
'In order to ensure the _Date being edited is displayed in the current calendar
'I set the calendar's VisibleDate property to the item's Date value
_Date.VisibleDate = myDataReader.Item("_Date")
'And the SelectedDate property to the item's Date as well
_Date.SelectedDate = myDataReader.Item("_Date")
_ID.Text = myDataReader.Item("ID")
'// This next one is a bit of a hack.
'//Remember that the values in my dropdown list begin with 1.
'// Well, the SelectedIndex property is based on a zero-based collection.
'// So, to get the correct value, I simply subtract 1.
_Type.SelectedIndex = myDataReader.Item("Type") - 1
Title.Text = myDataReader.Item("Title")
Audience.Text = myDataReader.Item("Audience")
PResponsible.Text = myDataReader.Item("PersonResponsible")
myDataReader.Close()
myConnection.Close()
End Sub
Sub Do_Update(Sender As Object, e As EventArgs)
'Make sure the user has entered valid values...
If Not Page.IsValid then Exit Sub
Dim myConnection As New _
SqlConnection(ConfigurationSettings.AppSettings("MySQLDSN"))
Dim myCommand As New _
SqlCommand("Intranet_sp_Marketing_Item_Edit", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterTitle As New SqlParameter("@Title", SqlDbType.VarChar, 50)
parameterTitle.Value = Title.Text
myCommand.Parameters.Add(parameterTitle)
Dim parameterDate As New SqlParameter("@_Date", SqlDbType.DateTime, 8)
parameterDate.Value = _Date.SelectedDate
myCommand.Parameters.Add(parameterDate)
Dim parameterType As New SqlParameter("@Type", SqlDbType.Int, 4)
parameterType.Value = _Type.SelectedItem.Value
myCommand.Parameters.Add(parameterType)
Dim parameterID As New SqlParameter("@ID", SqlDbType.Int, 4)
parameterID.Value = Cint(_ID.Text)
myCommand.Parameters.Add(parameterID)
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)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Response.Redirect("default.aspx") 'Return the user to the calendar page
End Sub
</script>
... Web controls removed for brevity ...
... They are the same as from the "Add a New Event" Web page ...
Adding the Calendar
Just as I said earlier, it is easy to create a fully functioning ASP.NET calendar with
just three lines of code, but making it look good and handle events, is a little more involved.
To get started I created the Calendar control using the following Web control declaration:
The Calendar Web control has many properties that allow for a high level of customization. The
above instance only demonstrates the use of some of them. For more information about the Calendar
control's properties and methods check out the technical
documentation.
While most of the content in the Calendar Web control's declaration is to "pretty-up" the appearance
of the calendar, there are three events wired up to event handlers (specified in the bold text):
DayRender, VisibleMonthChanged and SelectionChanged.
The DayRender event is raised for each day that is rendered by the calendar control.
This makes it a perfect place for adding visual information about a marketing effort for that particular
day. But, because it is called every time (roughly thirty five times per calendar rendered), it would
be a bad place to place a database connection. Instead, I'll access the database once in a separate
subroutine (Get_DBItems()) and load the results into a collection that can be accessed in
the DayRender event.
That is, rather than connecting to a database ~35 times on each page view - which would be a performance nightmare -
the code instead makes one database connection and loads all of the events for the given month into a collection.
Then, in the DayRender event handler, this collection can be quickly accessed to determine
what, if any, events have occurred for the particular day being rendered.
To accommodate this, I declared and defined a structure to hold the data that will be displayed for
each day:
Public Structure MyDates
Dim _Title as String
Dim _Date as DateTime
Dim _Type as String
End Structure
I also declared a public collection object that can be accessed by different subroutines:
Public MyCollection As Collection
And, finally, a public variable tempDate used to access the database and pull all
records within a specified date range and the Page_Load subroutine.
Public tempDate As DateTime
In the Page_Load event handler I set tempDate to the currently selected Calendar
date. Then, if the page is not being visited via a postback, I call the Get_DBItems() subroutine
to load the events for the month into a collection.
Public Sub Page_Load()
tempDate = Calendar1.TodaysDate
If Not (Page.IsPostBack) Then
Get_DBItems() '// The Sub that loads the collection
End if
End Sub
The GetDBItems() subroutine calls the Intranet_sp_Marketing_Get_Items stored
procedure, which retrieves all items within the range of the supplied input parameters:
CREATE PROCEDURE Intranet_sp_Marketing_Get_Items
(
@_Date datetime,
@_Date_Plus_1 datetime
)
AS
SELECT *
FROM tbl_Intranet_Marketing
WHERE _Date between @_Date AND @_Date_Plus_1
ORDER BY _Date
GO
Just as with the Bind() subroutine in the "Edit an Event" page, the GetDBItems()
subroutine makes a database connection, calls the stored procedure, and then iterates through its results.
For each record returned by the stored procedure, a MyDates structure is created and its values
populated. This record is then added to a collection. This is accomplished with the following code:
... at this point the stored procedure has been called
and a DataReaer, myDataReader, has been populated with the
stored procedure's results ...
myCollection = New Collection()
Dim temp As myDates
While(myDataReader.Read())
'// Created each time through the loop to get a new item
'// to load and add to the collection
temp = New MyDates()
temp._Title = myDataReader.Item("Title")
temp._Date = CDate(myDataReader.Item("_Date"))
temp._Type = myDataReader.Item("Type")
MyCollection.Add(temp)
End While
Recall that the DayRender event automatically fires each and every time a day is
formatted and placed in to the calendar. In fact, it simply creates a table cell with some style
attributes and an html anchor tag for the number that represents that cell's date. So, if I wanted
some information about items occurring on a specific date to be displayed on the calendar, this would
be the perfect place to inject some code. First, let me lay out a couple of business rules for this
particular application. Each day might have one or more items and each item must be displayed with
a different text color representing the item's type.
To display a day's events I simply iterate through the collection. For each item in the collection I
test to see if the item's _Date property is equal to the date being rendered. If it is,
I display the _Title property with the correct color (based on the _Type property).
To improve the efficiency, I ordered the stored procedure's results by the date in increasing
order (note the ORDER BY clause in the stored procedure). So, if I reach an item in the
collection for the current date, and then at some point reach a different while enumerating through
the collection, then obviously there are no more dates in the remainder of the collection that apply
to the date being rendered, so I can exit the loop. (An even more efficient way would be to use a
Hashtable hashing on the date, as opposed to a generic collection. Such an approach would limit the
unnecessary looping through the collection.)
The code to display the event(s) for a particular day looks as follows:
For Each Item In MyCollection
'//The collection is loaded in date order. So, if the date is
'//different than the DayHold variable and I've already found
'//the right day in the collection, I can exit the for loop
If DayHold <> Item._Date Then
If DayTextHasChanged = True
Exit for
End if
MultipleItemDay = False
DayHold = Item._Date
Else
MultipleItemDay = True
End if
'//If I've found a date matching the current date...
'//I need to create the appropriate text & color
If e.Day.Date = Item._Date.ToString("d") Then
Select Case Item._Type
Case 1:TextColor="Blue"
Case 2:TextColor="Red"
Case 3:TextColor="Orange"
Case 4:TextColor="Green"
Case 5:TextColor="Brown"
Case 6:TextColor="Gray"
Case 7:TextColor="#408080"
Case Else : TextColor="Black"
End Select
If MultipleItemDay = False Then
temp = New StringBuilder() '//Create a new stringbuilder object
Else
temp.Append("<br>") '//add a seperator to the stringbuilder
End If
temp.Append("<span style=""font-family:Arial; font-weight:bold;font-size:12px; color:")
temp.Append(TextColor)
temp.Append("""><br>")
temp.Append(Item._Title.ToString())
temp.Append("</span>")
DayTextHasChanged = True '//Set the flag
End If
Loop
That's it for the loop, but there is still one problem. I set the loop to exit if the current
DayHold variable is not equal to the Item._Date property and the
DayTextHasChanged is true. That means, once I've found the correct date in the
collection, added its contents to the StringBuilder and checked to be sure there wasn't multiple
items for that date, I can exit the loop.
I still have to actually add the contents of the StringBuilder to the Calendar cell for the date.
To determine if I need to add any text to the Calendar's cell (after all, there may be no events
for a given day) I checked the DayTextHasChanged variable - if it is equal to True, I
add the contents of the StringBuilder to the currently rendering date's table cell using the following
code:
If DayTextHasChanged = True then
e.Cell.Controls.Add(New LiteralControl(temp.ToString()))
End If
Now I needed a way to display the details for the items. Remember, I have five fields: Title,
Date, Type, Audience, Person Responsible. And, only two of them are displayed in the Calendar control.
I needed a way so that a user could see the details about a particular event for a given day.
To accomplish this I used the Calendar control's SelectionChanged event. The
SelectionChanged event fires whenever the user clicks on the hyperlink of the day in the
Calendar. Whenever a user does this, I wanted to show the details of the events for that day.
To accomplish this, I created yet another stored procedure, Intranet_sp_Marketing_Get_Items_For_Single_Date,
which retrieved the events for a particular date. The code for this stored procedure is fairly straightforward,
and is shown below:
CREATE PROCEDURE Intranet_sp_Marketing_Get_Items_For_Single_Date
(
@_Date datetime
)
AS
SELECT * FROM
tbl_Intranet_Marketing
WHERE _Date = @_Date
GO
The SelectionChanged event handler simply calls this stored procedure and displays
the events returned by the stored procedure. (For brevity, I am not showing the code for this
event handler - it's pretty straightforward and can be found in the code download at the end of this
article.)
The only piece left in the puzzle is to handle the VisibleMonthChanged event for the calendar.
The VisibleMonthChanged event fires whenever the user navigates to a different month.
If a user moves to a new month using the previous/next month links in the calendar's title
bar, I need to reload the collection that contains the events for the month. To accomplish this,
the VisibleMonthChanged event handler simply calls the Get_DBItems() subroutine
after setting tempDate to the new date of the Calendar control.
Private Sub MonthChanged(source As Object, e As MonthChangedEventArgs)
'//Set the tempDatevariable to the value in the
'//MonthChangedEventArgs NewDate property
tempDate = e.NewDate
'//Reload the collection
Get_DBItems()
End Sub
That's it -- an extended ASP.NET calendar control that displays items within the day rendered table
cells from a database. And, just as in my objectives, I've built a simple, easy to use calendar
that displays marketing efforts graphically and allows for editing.
You can view a live demo of the calendar system at
http://www.allpaul.com/programming/4guysfromrolla/calendar/default.aspx.
