Binding asp.net calendar dates to sql database

centaury · October 22nd, 2008, 02:07 AM

I did many searches on the internet but could not find what I was looking for and so I hope to find my solution here:)

I'm using Visual Web Developer 2008 Express Edition to create an MSSQL database driven site.

I want to use an asp.net calendar from the toolbox, I want to highlight the dates when an article is posted which is stored in an MSSQL database. If you would click on a date it should return an article from that particular date.
My site is coded in ASP.NET VB

Now I found this:
Creating a Calendar-Controlled Diary in ASP.NET with C#
http://www.programminghelp.com/web-d...ry-in-asp-net/

Which is pretty nice, but it is in C# and it is not exactly what I'm looking for.

I hope someone here can help me out.
Thanks in advance.

Ok I have found the first part:
"How to display Selected Dates from a Database in the Calendar Control"

I have a table "PAGES" in my database which contains the fields "timestamp" and "link_active" (True/False).
If the date matches from the field "timestamp" and the value is "True" from the field "link_active" the date in the calendar should be highlighted.

This is the code that needs to be modified:

Code:

Protected dsHolidays 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
        FillHolidayDataset()
    End If
End Sub

Protected Sub FillHolidayDataset()
    Dim firstDate As New DateTime(Calendar1.VisibleDate.Year, _
         Calendar1.VisibleDate.Month, 1)
    Dim lastDate As DateTime = GetFirstDayOfNextMonth()
    dsHolidays = 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
    Else
        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
    FillHolidayDataset()
End Sub

Function GetCurrentMonthData(ByVal firstDate As DateTime, _        ByVal lastDate As DateTime) As DataSet
    Dim dsMonth As New DataSet
    Dim cs As ConnectionStringSettings
    cs = ConfigurationManager.ConnectionStrings("ConnectionString1")
    Dim connString As String = cs.ConnectionString
    Dim dbConnection As New SqlConnection(connString)
    Dim query As String
    query = "SELECT HolidayDate FROM Holidays " & _
        " WHERE HolidayDate >= @firstDate AND HolidayDate < @lastDate"
    Dim dbCommand As New SqlCommand(query, dbConnection)
    dbCommand.Parameters.Add(New SqlParameter("@firstDate", firstDate))
    dbCommand.Parameters.Add(New SqlParameter("@lastDate", lastDate))

    Dim sqlDataAdapter As New SqlDataAdapter(dbCommand)
    Try
        sqlDataAdapter.Fill(dsMonth)
    Catch
    End Try
    Return dsMonth
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
    If Not dsHolidays Is Nothing Then
        For Each dr As DataRow In dsHolidays.Tables(0).Rows
            nextDate = CType(dr("HolidayDate"), DateTime)
            If nextDate = e.Day.Date Then
                e.Cell.BackColor = System.Drawing.Color.Pink
            End If
        Next
    End If
End Sub

Thanks for any help.