Calendar Control with Database Access and Lightbox popup

Published on
10,617 Points
1 Endorsement
Last Modified:
Community Pick
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this was a daunting task since controlling the rendered dates with a link to the event seemed out of reach, well, once I sat down and started working, it was actually a simple task. There are a few examples of using a database to create event dates, but I never found one that created a pop-up of the events.  Lets quickly walk through it.

First, lets create a database of the necessary data.  I used Access in this example but can be easily migrated to your preferred database.  Create a table with the following columns and  data-types, (sample data included);

ID      eventDate      eventName      EventData      eventLink
2      10/21/2010      New Event      Testing a new event      http://www.mgfic.com

ID: AutoNumber
eventDate: Date/Number
eventName: Text
eventData: Memo
eventLink: Text

Once the database has been created and saved in the appropriate directory, (I'll use _database in this example), lets create a page with the Calendar Control embedded and the required CSS and JavaScript calls.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link rel="stylesheet" href="../css/mediaboxAdvBlack21.css" type="text/css" media="screen" /> 
<script src="../js/mootools.js" type="text/javascript"></script> 
<script src="../js/mediaboxAdv-1.3.4.js" type="text/javascript"></script>
<script type="text/javascript" src="../js/wz_tooltip.js"></script>
<script type="text/javascript" src="../js/tip_balloon.js"></script>
    <form id="form1" runat="server">
        <asp:Calendar ID="Calendar1" runat="server" BackColor="White" 
            BorderColor="Black" BorderWidth="1px" Font-Names="Segoe UI" Font-Size="9pt" 
            ForeColor="Black" Height="180px" NextPrevFormat="FullMonth" Width="260px" 
            SelectionMode="None" BorderStyle="Solid" 
            Caption="Calendar of Events" CaptionAlign="Top" ToolTip=" ">
            <SelectedDayStyle BackColor="White" ForeColor="White" />
            <SelectorStyle Font-Bold="False" />
            <TodayDayStyle BackColor="White" Font-Bold="True" 
                Font-Names="Segoe UI" Font-Size="14px" />
            <OtherMonthDayStyle ForeColor="#999999" />
            <NextPrevStyle Font-Bold="True" Font-Size="8pt" ForeColor="#F9F9D2" 
                VerticalAlign="Bottom" />
            <DayHeaderStyle Font-Bold="True" Font-Size="8pt" BackColor="#F9F9D2" 
                BorderStyle="Solid" BorderWidth="0px" />
            <TitleStyle BackColor="#931D1D" BorderColor="Black" BorderWidth="1px" 
                Font-Bold="True" Font-Size="13pt" ForeColor="White" 
                BorderStyle="Ridge" />

Open in new window

There are a number of styles set in the above .aspx page that are not necessary in your control, I used them to show the possible customization available.  You will also notice that the file "Default.aspx.vb" is called as the CodeFile, this is where all the work takes place to connect to the database and creating the necessary HREF tags using the DayRender event.  Some of this code comes from Microsoft.  Lets take a look at this vb;

Imports System.Data
Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected dsEvents As DataSet

    Protected Sub Page_Load(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Calendar1.VisibleDate = DateTime.Today
        End If
    End Sub

    Protected Sub FillDataset()
        Dim firstDate As New DateTime(Calendar1.VisibleDate.Year, Calendar1.VisibleDate.Month, 1)
        Dim lastDate As DateTime = GetFirstDayOfNextMonth()
        dsEvents = GetCurrentMonthData(firstDate, lastDate)
    End Sub

    Protected Function GetFirstDayOfNextMonth() As DateTime
        Dim monthNumber, yearNumber As Integer
        If Calendar1.VisibleDate.Month = 12 Then
            monthNumber = 1
            yearNumber = Calendar1.VisibleDate.Year + 1
            monthNumber = Calendar1.VisibleDate.Month + 1
            yearNumber = Calendar1.VisibleDate.Year
        End If
        Dim lastDate As New DateTime(yearNumber, monthNumber, 1)
        Return lastDate
    End Function

    Protected Sub Calendar1_VisibleMonthChanged(ByVal sender As Object, _
            ByVal e As System.Web.UI.WebControls.MonthChangedEventArgs) _
            Handles Calendar1.VisibleMonthChanged
    End Sub

    Function GetCurrentMonthData(ByVal firstDate As DateTime, ByVal lastDate As DateTime) As DataSet

        Dim sConnectionString, sSQL As String
        'SQL Connection String
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/_database/CalendarDb.mdb") & ";User Id=admin;Password=;"
        Dim objConnection As New OleDbConnection(sConnectionString)
        'SQL parameters
        sSQL = "SELECT * FROM tblEvents WHERE eventDate >= #" & firstDate & "# And eventDate < #" & lastDate & "#"
        Dim Table As New DataSet
        Dim Adpt As New OleDbDataAdapter(sSQL, objConnection)
        End Try
        ' return the recordset as an array
        Return Table
    End Function

    Protected Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DayRenderEventArgs) Handles Calendar1.DayRender
        Dim nextDate As DateTime
        ' Checks to see if there are any dates that need to be returned
        If Not dsEvents Is Nothing Then
            ' if there is, then draw them
            For Each dr As DataRow In dsEvents.Tables(0).Rows ' this array will be full of your db data
                nextDate = CType(dr("eventDate"), DateTime)
                If nextDate = e.Day.Date Then
                    'e.Cell.BackColor = System.Drawing.Color.Pink  ' changes the background color
                    e.Day.IsSelectable = True  ' makes the date selectable
                    e.Cell.Font.Bold = True
                    Dim iLink As New HyperLink() ' Create a hyperlink of the date
                    iLink.Text = e.Day.Date.Day '& "<br>" & dr(2) ' sets the date as a link
                    iLink.ForeColor = Drawing.ColorTranslator.FromHtml("#931D1D") ' give it a cool color
                    iLink.Font.Underline = True ' underline it
                    ' Here is where we create a nice ToolTip when the mouseOver event fires
                    iLink.Attributes.Add("onmouseover", "Tip('" & dr(2) & "', BALLOON, true, ABOVE, true, OFFSETX, -20)")
                    iLink.ToolTip = dr(2)
                    ' Lets create the link to fire the Lightbox
                    iLink.NavigateUrl = Server.UrlPathEncode("javascript:this.onclick(Mediabox.open('#mb_inline" & dr(0) & "', '" & dr(2) & "', '360 180'));") ' the URL
                    e.Cell.Controls.Add(iLink) 'Add the link
                    buildDivTags(dr(0), dr(2), dr(3), dr(4))
                End If
        End If
    End Sub

' We need to create <Div> tags to control the output of the Lightbox contents
    Private Sub buildDivTags(ByRef id As String, ByRef title As String, ByRef data As String, ByRef link As String)
        Response.Write(vbCrLf & "<div id='mb_inline" & id & "' style='display: none;'>" & vbCrLf)
        Response.Write("<span style='color: #fff; text-align: center;'><A href='" & link & "' target='_blank' id='mbTitleLink'>" & title & "</a><br/><br/>" & vbCrLf)
        Response.Write(data & "<br/><br/>" & vbCrLf)
        Response.Write("<a href='' style='color: #dcdcdc;' onclick='Mediabox.close();return false;'>Close</a></span></div>" & vbCrLf)
    End Sub
End Class

Open in new window

It's important to note that this page uses MooTools and MediaBoxAdvanced as the Lightbox and the now difficult to find ToolTip by Walter Zorn, All the required .js files can be found here.  

If you want to see a working example of this code, you can CLICK HERE.

The entire code can be downloaded, including a sample database, the .aspx, the .vb, the .css and the .js files by clicking HERE.

If you have any problems with the code or any suggestions, please let me know.  I tested this code across FireFox and IE 7 and 8 with no issues.  Enjoy!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month