Highlighting Selected dates from database in Calendar control

Question

I am currently using VB. I want to do a Calendar Control which have dates highlighted/selected. All these dates are retrieved from a database.

First thing I need to know is how to put all the dates into an array Second thing I need to know is how to highlight all the dates in the array.

I have done some research on the internet and they said something about selectedDates and selectedDates collection and dayrender. But frankly speaking, I can't really find any VB codes regarding this. Dates format will be in dd/MM/yyyy

    Imports System.Data.SqlClient

    Partial Class _Default
Inherits System.Web.UI.Page

Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("CleanOneConnectionString").ConnectionString
    Dim connection As SqlConnection = New SqlConnection(connectionString)
    connection.Open()
    Dim sql As String = "Select schedule From OrderDetails Where schedule is not null"
    Dim command As SqlCommand = New SqlCommand(sql, connection)
    Dim reader As SqlDataReader = command.ExecuteReader()
    If (reader.Read()) Then
        If (reader.HasRows) Then
            While reader.Read()
                myCalendar.SelectedDates.Add(CType(reader.GetDateTime(0), Date))
            End While
        End If
    End If
    reader.Close()
    connection.Close()
    myCalendar.SelectedDayStyle.BackColor = System.Drawing.Color.Red
End Sub
    End Class

My Calendar

    <asp:Calendar ID="myCalendar" runat="server" ShowGridLines="True">
</asp:Calendar>

Updated with what I have done, but still does not show Thanks for the help

Answer 1

For the second part of your question, you can see on this post how to highlight specified days, albeit using C# syntax.

I'm going to assume a L2S format is being used to fetch the dates for now (comment with actual implementation if you need better detail).

I would recommend the dates you want to select be held in a variable on the form (instead of scoped to the function) to prevent database queries running everytime a day is rendered. With that in mind, here is some sample code (free-hand so please excuse and comment on basic/troubling syntax issues):

Private DatesToHighlight As IEnumerable(Of Date)

' implementation details provided so commented this bit out, see EDIT below
'Protected Sub PopulateDatesToHighlight()
'    DatesToHighlight = db.SomeTable.Select(Function(n) n.MyDateField)
'End Sub

Protected Sub DayRenderer(ByVal object As Sender, ByVal e As DayRenderEventArgs)
   If DatesToHighlight.Contains(e.Day.Date) Then
       e.Cell.BackColor = System.Drawing.Color.Red
   End If
End Sub

As specified in the question I linked, you'll need to change the markup for the calendar control to provide the ondayrender parameter like so ondayrender="DayRenderer"

Regarding changing your dates to an array, it depends what format they are in at the start. If in anything that inherits from IEnumerable then you can use ToArray(). If they are just variables you can initialise the array with the dates

Dim myDates() As Date = {dateVar1, dateVar2}

Hope that helps?

EDIT: (In response to OP's addition of code)

To get from your data reader to an array (though I'm not convinced you need an array) I would do the following:

' using the variable I declared earlier
DatesToHighlight = New IEnumerable(Of Date)
If reader.HasRows Then
    Dim parsedDate As Date
    While reader.Read()
        If Date.TryParse(reader(0), parsedDate) Then
            DatesToHighlight.Add(parsedDate)
        End If
    End While
End If

Dim myArrayOfDates() As Date = DatesToHighlight.ToArray()

Answer 2

Assume you have a DataTable named myDates, and a Calendar control named myCalendar:

For i As Int = 0 To myDates.Rows.Count - 1
    myCalendar.SelectedDates.Add(CType(myDates.Row(i)(0), Date)
Next

You can declare the highlighting in your markup:

<asp:Calendar ID="myCalendar" runat="server">
    <SelectedDayStyle BackColor="red" />
</asp:Calendar>

Or programatically:

myCalendar.SelectedDayStyle.BackColor = System.Drawing.Color.Red

UPDATE For SqlDataReader (VB.NET this time)

If reader.HasRows Then
    While reader.Read()
        myCalendar.SelectedDates.Add(CType(reader(0), Date)
    End While
End If

UPDATE based on OP's code

Are you getting any errors when the code runs? SqlDataReader.GetDateTime will throw an InvalidCastException if the column being read isn't a DateTime column.

I'm wondering if it's a format issue? Can you verify the data type of the column in the database, as well as the format the date is being stored in?

I've modified your code a bit with a couple of suggestons.

Imports System.Data.SqlClient

Partial Class _Default Inherits System.Web.UI.Page  

    Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("CleanOneConnectionString").ConnectionString

        ' Using blocks will automatically dispose of the object, and are 
        ' pretty standard for database connections
        Using connection As New SqlConnection(connectionString)
            connection.Open()     
            Dim sql As String = "Select schedule From OrderDetails Where schedule is not null"
            Dim command As SqlCommand = New SqlCommand(sql, connection)     
            Dim reader As SqlDataReader = command.ExecuteReader() 

            ' This is not needed - in fact, this line will "throw away"
            ' the first row in the row collection
            'If (reader.Read()) Then

            If (reader.HasRows) Then             
                While reader.Read()
                    myCalendar.SelectedDates.Add(CType(reader.GetDateTime(0), Date))              End While         
            End If

            reader.Close()     

            ' Not needed because of the Using block
            'connection.Close()     
        End Using

        myCalendar.SelectedDayStyle.BackColor = System.Drawing.Color.Red 
    End Sub     
End Class