To read the article online, visit http://www.4GuysFromRolla.com/articles/041603-1.2.aspx

Building an Event Calendar Web Application, Part 2

By Paul Apostolos


  • Read Part 1

  • 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:

    <asp:Calendar OnDayRender="Calendar1_DayRender" 
        OnSelectionChanged="Calendar1_SelectionChanged"
        OnVisibleMonthChanged="MonthChanged"
        
        DayStyle-Height="100" DayStyle-Width="75" DayStyle-HorizontalAlign="Left"
        DayStyle-verticalalign="Top"
        DayStyle-Font-Name="Arial" DayStyle-Font-Size="12" 
        NextPrevFormat="FullMonth" SelectionMode="Day" 
        TitleStyle-Font-Bold="False" TitleStyle-Font-Name="Verdana" 
        TitleStyle-Font-Size="12" BackColor="white" BorderColor="#000000"
        CellPadding="2" CellSpacing="2" Runat="server" id="Calendar1" 
        SelectedDayStyle-BackColor="#faebd7"
        SelectedDayStyle-ForeColor="#000000"
        OtherMonthDayStyle-ForeColor="#C0C0C0" DayStyle-BorderStyle="Solid" 
        DayStyle-BorderWidth="1" TodayDayStyle-ForeColor="Black" Height="600" 
        DayHeaderStyle-Font-Name="Verdana"
        Width="750">
    </asp:Calendar>
    

    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.

    Good luck and Happy Programming!

  • By Paul Apostolos


    Attachments


  • View a Live Demo of the Calendar Application!
  • Download the complete source code (in ZIP format)

  • Article Information
    Article Title: ASP.NET.Building an Event Calendar Web Application, Part 2
    Article Author: Paul Apostolos
    Published Date: April 16, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/041603-1.2.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers