Before I explain what I want I am going to post the source code ahead of time:
HTML SOURCE CODE
<%@ Page Title="" Language="vb" AutoEventWireup="false" MasterPageFile="~/Site.Master" CodeBehind="CalendarEvents.aspx.vb" Inherits="BFF_Foam_Receiving.CalendarEvents" %>
<%@ Register Assembly="DayPilot" Namespace="DayPilot.Web.Ui" TagPrefix="DayPilot" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>
<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" runat="server">
<asp:Calendar ID="cldCalendar" runat="server" BackColor="White"
BorderColor="#333333" BorderStyle="Solid" BorderWidth="1px"
Font-Names="Verdana" Font-Size="14pt" ForeColor="Black" Height="305px"
NextPrevFormat="FullMonth" ondayrender="cldCalendar_DayRender" Width="555px">
<SelectedDayStyle BackColor="#333399" ForeColor="White" />
<TodayDayStyle BackColor="#CCCCCC" />
<OtherMonthDayStyle ForeColor="#999999" />
<NextPrevStyle Font-Bold="True" Font-Size="14pt" ForeColor="#333333"
VerticalAlign="Bottom" />
<DayHeaderStyle Font-Bold="True" Font-Size="14pt" />
<TitleStyle BackColor="White" BorderColor="Black" BorderWidth="2px"
Font-Bold="True" Font-Size="16pt" ForeColor="#333399" />
</asp:Calendar>
</asp:Content>
This is the ASP.NET CODE
Imports System.Data.OleDb
Public Class CalendarEvents
Inherits System.Web.UI.Page
Private Shadows events As New Hashtable()
Private i As Integer
Protected Sub cldCalendar_DayRender(sender As Object, e As DayRenderEventArgs)
Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("TestLocal").ConnectionString.ToString)
Dim strSQL As String = "SELECT tblCompany.CompanyName, Min(tblWorkOrderItem.Description) As Description, tblWorkOrder.DateRequired FROM tblCompany " &
"INNER JOIN (tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.[WorkOrderID] = tblWorkOrder.[WorkOrderID]) ON " &
"tblCompany.CompanyID = tblWorkOrder.BuyerID Where tblCompany.Calendar=-1 And tblWorkOrder.DateRequired is not null " &
"GROUP BY tblCompany.CompanyName, tblWorkOrder.DateRequired;"
Dim cmd As New OleDbCommand(strSQL, con)
cmd.CommandType = CommandType.Text
con.Open()
Dim drCalendar As OleDbDataReader = cmd.ExecuteReader()
If drCalendar.HasRows Then
Do While drCalendar.Read
Dim b As New Label
Dim strCalendar As Date = drCalendar("DateRequired")
b.Font.Size = 8
b.Font.Bold = True
b.ForeColor = System.Drawing.ColorTranslator.FromHtml("#336699")
cldCalendar.SelectedDate = strCalendar '.ToString("dd-MM-yyy").ToString
b.Text = events(strCalendar.ToString("dd-MM-yyy")).ToString + Chr(10) + Chr(13) + Chr(10) + Chr(13) + drCalendar("Description").ToString
e.Cell.Controls.Add(b)
Loop
End If
con.Close()
End Using
End Sub
Private Sub CalendarEvents_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Using con As New OleDbConnection(ConfigurationManager.ConnectionStrings("TestLocal").ConnectionString.ToString)
Dim strSQL As String = "SELECT tblCompany.CompanyName, Min(tblWorkOrderItem.Description) As Description, tblWorkOrder.DateRequired FROM tblCompany " &
"INNER JOIN (tblWorkOrderItem INNER JOIN tblWorkOrder ON tblWorkOrderItem.[WorkOrderID] = tblWorkOrder.[WorkOrderID]) ON " &
"tblCompany.CompanyID = tblWorkOrder.BuyerID Where tblCompany.Calendar=-1 And tblWorkOrder.DateRequired is not null " &
"GROUP BY tblCompany.CompanyName, tblWorkOrder.DateRequired;"
Dim cmd As New OleDbCommand(strSQL, con)
cmd.CommandType = CommandType.Text
con.Open()
Dim drCalendar As OleDbDataReader = cmd.ExecuteReader()
If drCalendar.HasRows Then
Do While drCalendar.Read
Dim strCalendar As Date = drCalendar("DateRequired")
Try
events.Add(strCalendar.ToString("dd-MM-yyy"), drCalendar("CompanyName"))
Catch ex As Exception
End Try
Loop
End If
con.Close()
End Using
End If
End Sub
End Class
Here is what I want. I want it when it reads from the SQL from access it looks at the daterequired from SQL and find 04/04/2014 from the database then in the calendar it puts in the calendar in the date in that calendar 04/04/2014 company name and description and repeat cycle until all the SQL query results are exhausted. However what I am facing is that it is putting all the 141 records into one month and putting as much of them as possible into one date in the calendar as you can see down below. Any help would be greatly appreciate it.