• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4652
  • Last Modified:

How can I control gridview display based on calendar?

I have this app I've been tasked with building.  I've used a lot of EE help on it and will now need some more.

http://www.co.frederick.va.us/planning/CALENDARS/Default.aspx is the URL.

The gridview on the left needs to only show events for the month selected on the calendar.  And if the user clicks to the next month, the gridview should update to show only that month's events.  

I was thinking maybe I would need another dataset for the gridview that is based on !e.IsOtherMonth, but I'm really clueless as to what to do now.  I'm attaching all of the code from my codefile.

I also tried something with making a second SQL string and new data adapters and so on, but it didn't work:

string strSQLGridView = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar WHERE [meeting_datetime] != '" + e.Day.IsOtherMonth.ToString() + "'";
using System;
using System.Data;
using System.Web;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page
{
    public void DayRender(Object source, DayRenderEventArgs e)
    {
 
        System.Data.SqlClient.SqlConnection calendarConnection = default(System.Data.SqlClient.SqlConnection);
        System.Data.SqlClient.SqlDataAdapter calendarAdapter = default(System.Data.SqlClient.SqlDataAdapter);
        DataTable calendarDataTable = new DataTable();
        // DataRow calendarDataRow = default(DataRow);
        string strSQL = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar";
 
        string calendarString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["planning_calendarConnectionString"].ConnectionString;
        calendarConnection = new System.Data.SqlClient.SqlConnection(calendarString);
        calendarConnection.Open();
        calendarAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, calendarConnection);
        calendarAdapter.Fill(calendarDataTable);
 
        GridView1.DataSource = calendarDataTable;
        GridView1.DataBind();
 
        e.Cell.BorderStyle = BorderStyle.Solid;
        e.Cell.BorderWidth = 1;
 
        if (e.Day.IsOtherMonth)
        {
            e.Cell.Controls.Clear();
        }
 
        if (e.Day.IsToday)
        {
            e.Cell.BackColor = System.Drawing.Color.Yellow;
        }
 
        foreach (DataRow calendarDataRow in calendarDataTable.Rows)
        {
            if (e.Day.Date.Date == Convert.ToDateTime(calendarDataRow["meeting_datetime"]).Date && !e.Day.IsOtherMonth)
            {
                e.Cell.BackColor = System.Drawing.Color.Green;
            }
        }
    }
}

Open in new window

0
mrcoulson
Asked:
mrcoulson
  • 24
  • 15
1 Solution
 
CtrlAltDlCommented:
Create calendar events for SelectionChanged and VisibleMonthChanged and create a month filter for your datasource.
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        DataSource1.FilterExpression = "Month = " + Calendar1.SelectMonthText;
 
    }
    protected void Calendar1_VisibleMonthChanged(object sender, MonthChangedEventArgs e)
    {
        DataSource1.FilterExpression = "DepartmentID = " + DepartmentID;
    }

Open in new window

0
 
CtrlAltDlCommented:
Oops,
DataSource1.FilterExpression = "DepartmentID = " + DepartmentID;
should be:
DataSource1.FilterExpression = "Month = " + Calendar1.SelectMonthText;

or actually your filter would probably look like this:
DataSource1.FilterExpression = "DATENAME(MONTH,myDateField) = " + Calendar1.SelectMonthText;

0
 
mrcoulsonAuthor Commented:
Okay.  I'll try it.  Any advice as to how to implement that?  I'm going to just paste willy-nilly.

Jeremy
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mrcoulsonAuthor Commented:
Okay, here's an elementary problem.  How do I get my calendarDataTable to be global?

"The name 'calendarDataTable' does not exist in the current context"
0
 
mrcoulsonAuthor Commented:
Or if it helps the discussion at all, here's another attempt I made.  Basically, I just don't know what to do with that SQL string where I have "somethingThatMeansTheMonthOnTheCalendar".
using System;
using System.Data;
using System.Web;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page
{
    public void DayRender(Object source, DayRenderEventArgs e)
    {
   
        // Connection
        System.Data.SqlClient.SqlConnection calendarConnection = default(System.Data.SqlClient.SqlConnection);
        
        // Adapters
        System.Data.SqlClient.SqlDataAdapter calendarAdapter = default(System.Data.SqlClient.SqlDataAdapter);
        System.Data.SqlClient.SqlDataAdapter gridViewAdapter = default(System.Data.SqlClient.SqlDataAdapter);
        
        // DataTables
        DataTable calendarDataTable = new DataTable();
        DataTable gridViewDataTable = new DataTable();
 
        // SQL strings
        string strSQL = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar";
        string strSQLGridView = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar WHERE [meeting_datetime] != '" + somethingThatMeansTheMonthOnTheCalendar + "'";
 
        // Connect
        string calendarString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["planning_calendarConnectionString"].ConnectionString;
        calendarConnection = new System.Data.SqlClient.SqlConnection(calendarString);
        calendarConnection.Open();
        
        // Fill DataTables
        calendarAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, calendarConnection);
        calendarAdapter.Fill(calendarDataTable);
        gridViewAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQLGridView, calendarConnection);
        gridViewAdapter.Fill(gridViewDataTable);
        
        // Bind to GridView
        GridView1.DataSource = gridViewDataTable;
        GridView1.DataBind();
 
        // Style calendar
        e.Cell.BorderStyle = BorderStyle.Solid;
        e.Cell.BorderWidth = 1;
 
        // Clear dates from other months
        if (e.Day.IsOtherMonth)
        {
            e.Cell.Controls.Clear();
        }
 
        // Make today yellow
        if (e.Day.IsToday)
        {
            e.Cell.BackColor = System.Drawing.Color.Yellow;
        }
 
        // Color days with events green
        foreach (DataRow calendarDataRow in calendarDataTable.Rows)
        {
            if (e.Day.Date.Date == Convert.ToDateTime(calendarDataRow["meeting_datetime"]).Date && !e.Day.IsOtherMonth)
            {
                e.Cell.BackColor = System.Drawing.Color.Green;
            }
        }
    }
}

Open in new window

0
 
CtrlAltDlCommented:
Assuming your using Visual Studio, in Design View view the Properties of your calendar, then click on Events (looks like a lightning bolt), then double click your two events, which will create your code-behind events.  Then all you have to do is copy the filter expressions and rename the controls and the month field name in your datasource.
0
 
CtrlAltDlCommented:
I can tell you right now you don't want to do it in the DayRender event.  That event takes place each time a day is rendered.  You don't want to be binding data to your GridView 31 times after each page refresh.
0
 
mrcoulsonAuthor Commented:
Okay.  That's good advice.  I'll try it your way then and come back with hopefully cheerful news.
0
 
mrcoulsonAuthor Commented:
"rename the controls and the month field name in your datasource."

But there is no month field in my datasource.  There's a simple datetime field with a month in it along with everything else.

I plugged the events in and I'm being told again that calendarDataTable does not exist in the current context.  Do I need a new data source for the gridview?

Sorry if I'm being an idiot.  This is how I'm learning.
0
 
CtrlAltDlCommented:
Ok, want you want to do is drag and drop a SqlDataSource control and use this query in it:
SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar

Since there is no month field we convert the meeting_datetime to a month name with "DateName", so your filter would like this:
DataSource1.FilterExpression = "DATENAME(MONTH,meeting_datetime) = " + Calendar1.SelectMonthText;
0
 
mrcoulsonAuthor Commented:
Oh, it needs a totally new source!  Okay.  Being right back!
0
 
mrcoulsonAuthor Commented:
Okay, I put the data source on there and changed the data source of the gridview.  I received this error:

The expression contains undefined function call DATENAME().

on the line:

GridView1.DataBind();

Complete new code is attached.
using System;
using System.Data;
using System.Web;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page
{
    
    public void DayRender(Object source, DayRenderEventArgs e)
    {
 
        System.Data.SqlClient.SqlConnection calendarConnection = default(System.Data.SqlClient.SqlConnection);
        System.Data.SqlClient.SqlDataAdapter calendarAdapter = default(System.Data.SqlClient.SqlDataAdapter);
        DataTable calendarDataTable = new DataTable();
        string strSQL = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar";
 
        string calendarString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["planning_calendarConnectionString"].ConnectionString;
        calendarConnection = new System.Data.SqlClient.SqlConnection(calendarString);
        calendarConnection.Open();
        calendarAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, calendarConnection);
        calendarAdapter.Fill(calendarDataTable);
 
        GridView1.DataSource = SqlDataSource2;
        GridView1.DataBind();
 
        e.Cell.BorderStyle = BorderStyle.Solid;
        e.Cell.BorderWidth = 1;
 
        if (e.Day.IsOtherMonth)
        {
            e.Cell.Controls.Clear();
        }
 
        if (e.Day.IsToday)
        {
            e.Cell.BackColor = System.Drawing.Color.Yellow;
        }
 
        foreach (DataRow calendarDataRow in calendarDataTable.Rows)
        {
            if (e.Day.Date.Date == Convert.ToDateTime(calendarDataRow["meeting_datetime"]).Date && !e.Day.IsOtherMonth)
            {
                e.Cell.BackColor = System.Drawing.Color.Green;
            }
        }
    }
 
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        SqlDataSource2.FilterExpression = "DATENAME(MONTH,meeting_datetime) = " + Calendar1.SelectMonthText;
    }
 
    protected void Calendar1_VisibleMonthChanged(object sender, MonthChangedEventArgs e)
    {
        SqlDataSource2.FilterExpression = "DATENAME(MONTH,meeting_datetime) = " + Calendar1.SelectMonthText;
    }
}

Open in new window

0
 
CtrlAltDlCommented:
Remove this from the DayRender event:
        GridView1.DataSource = SqlDataSource2;
        GridView1.DataBind();

and just add the DataSource to your gridview:
<asp:GridView ID="GridView1" runat="server" DataSource="SqlDataSource2">

And change your filters to:
SqlDataSource2.FilterExpression = "MONTH(meeting_datetime) = " + Calendar1.SelectedDate.Month.ToString();
0
 
mrcoulsonAuthor Commented:
Argh!  

"The 'DataSource' property cannot be set declaratively."
0
 
CtrlAltDlCommented:
Doh, it should be DataSourceID!
0
 
mrcoulsonAuthor Commented:
The expression contains undefined function call MONTH().
0
 
CtrlAltDlCommented:
Sorry apparently you can't use T-SQL expressions in a filter, so we'll have to filter with a SelectParameter.

Add this to your SqlDatasource:
                <SelectParameters>
                    <asp:Parameter Name="MeetingMonth" Type="Int16" />
                </SelectParameters>

and change your SqlDataSource query to this:
SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar WHERE MONTH([meeting_datetime]) = @MeetingMonth

and then remove the filters and add this:
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = Calendar1.SelectedDate.Month.ToString();
        GridView1.DataBind();
    }
 
    protected void Calendar1_VisibleMonthChanged(object sender, MonthChangedEventArgs e)
    {
        SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = Calendar1.SelectedDate.Month.ToString();
        GridView1.DataBind();
    }

Open in new window

0
 
mrcoulsonAuthor Commented:
Okay, dude.  I have to head up a meeting right now (people are watching me type this).  As soon as I'm out, I'll try this.  I really appreciate your help!  

Jeremy
0
 
mrcoulsonAuthor Commented:
Okay.  Now I don't get any errors; unfortunately, I also don't get a gridview.

But it's progress, right?

http://www.co.frederick.va.us/planning/CALENDARS/Default_3.aspx
0
 
mrcoulsonAuthor Commented:
If I change it to

!= = @MeetingMonth

I get the normal ol' gridview.

Jeremy
0
 
CtrlAltDlCommented:
Yea, I think that is good progress.  Now I think you just need a default value.
    protected void Page_Load(object sender, EventArgs e)
    {
            if (!IsPostBack)
            {
                SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = DateTime.Now.Month.ToString();
                GridView1.DataBind();
            }
    }

Open in new window

0
 
mrcoulsonAuthor Commented:
Progress, indeed!  Now it works on page load, but not when clicking through the calendar months.

http://www.co.frederick.va.us/planning/CALENDARS/Default_3.aspx
0
 
mrcoulsonAuthor Commented:
So, if

SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = DateTime.Now.Month.ToString();

works on page load, then the problem must be in

Calendar1.SelectedDate.Month.ToString();
0
 
CtrlAltDlCommented:
Is the Calendar in it's own UpdatePanel?  The page doesn't seem to refresh, so I assume it is in it's own updatepanel, but the gridview should be in the same updatepanel as the calendar.
0
 
mrcoulsonAuthor Commented:
UpdatePanel?  No.  There are no UpdatePanels.  I'm pretty new at this.  I see it there under AJAX Extensions.  Should I try that?
0
 
CtrlAltDlCommented:
no, if you don't have one I wouldn't put it in until you have it working.  One less thing to worry about for now.  But I would recommend it when it is all working.
0
 
CtrlAltDlCommented:
Try putting a breakpoint in the Calendar1_VisibleMonthChanged event and see if it hits that point when debuggin.
0
 
mrcoulsonAuthor Commented:
Okay.  

So...no UpdatePanel.  What now?  Does that affect the "if (!IsPostBack)"?
0
 
mrcoulsonAuthor Commented:
Ah, this isn't set up like a solution.  It's just two loose files.  Breakpoints and debugging won't work, right?
0
 
CtrlAltDlCommented:
Nope no debugging :-(

but you could change the GridView.Caption when you change the month that way you'll know if that event ever takes place.
0
 
mrcoulsonAuthor Commented:
Caption not displaying.  Maybe that event never takes place then.
0
 
mrcoulsonAuthor Commented:
In case you haven't seen it all yet, here's the entire ASPX code followed by the .cs code.
<%@ Page Language="C#" Debug="true" AutoEventWireup="true"  CodeFile="Default_3.aspx.cs" Inherits="_Default" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
    public void page_Load(object sender, System.EventArgs e)
    {
        // Get the current URL for testing reloads.
        string strHTTP_HOST = Request.ServerVariables["HTTP_HOST"];
        string strURL = Request.ServerVariables["URL"];
        string strCompleteURL = "http://" + strHTTP_HOST + strURL;
        HyperLink lnkURL = new HyperLink();
        lnkURL.NavigateUrl = strCompleteURL;
        lnkURL.Text = "Reload for testing.";
        form1.Controls.Add(lnkURL);
 
        if (!IsPostBack)
        {
            SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = DateTime.Now.Month.ToString();
            GridView1.DataBind();
            GridView1.Caption = "Hey, honkies!";
        }
 
        
    }
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <table border="0">
            <tr>
                <td valign="top">
                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:planning_calendarConnectionString %>" SelectCommand="SELECT * FROM [tblCalendar]"></asp:SqlDataSource>
                    <asp:Calendar ID="Calendar1" runat="server" OnDayRender="DayRender" DayStyle-VerticalAlign="Top" onselectionchanged="Calendar1_SelectionChanged" onvisiblemonthchanged="Calendar1_VisibleMonthChanged" />
                </td>
                <td style="width:20px;"></td>
                <td valign="top">
                    <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource2" AutoGenerateColumns="false" CellPadding="5">
                        <Columns>
                            <asp:BoundField DataField="meeting_datetime" HeaderText="Date" SortExpression="meeting_datetime" />
                            <asp:BoundField DataField="description" HeaderText="Description" SortExpression="description" />
                            <asp:BoundField DataField="location" HeaderText="Location" SortExpression="location" />
                            <asp:TemplateField HeaderText="Status">
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# ((bool)Eval("status")) ? "Scheduled" : "Canceled" %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                        </Columns>
                        <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                        <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                        <RowStyle BackColor="#E3EAEB" />
                        <EditRowStyle BackColor="#7C6F57" />
                        <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                        <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                        <AlternatingRowStyle BackColor="White" />
                    </asp:GridView>
                    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:planning_calendarConnectionString %>" SelectCommand="SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar WHERE MONTH([meeting_datetime]) = @MeetingMonth">
                        <SelectParameters>
                            <asp:Parameter Name="MeetingMonth" Type="Int16" />
                        </SelectParameters>
                    </asp:SqlDataSource>
                </td>
            </tr>         
        </table>   
    </div>
    </form>
</body>
</html>
 
 
// Here's the .cs code.
 
using System;
using System.Data;
using System.Web;
using System.Web.UI.WebControls;
 
public partial class _Default : System.Web.UI.Page
{
    
    public void DayRender(Object source, DayRenderEventArgs e)
    {
 
        System.Data.SqlClient.SqlConnection calendarConnection = default(System.Data.SqlClient.SqlConnection);
        System.Data.SqlClient.SqlDataAdapter calendarAdapter = default(System.Data.SqlClient.SqlDataAdapter);
        DataTable calendarDataTable = new DataTable();
        string strSQL = "SELECT [meeting_datetime], [description], [location], [status] FROM tblCalendar";
 
        string calendarString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["planning_calendarConnectionString"].ConnectionString;
        calendarConnection = new System.Data.SqlClient.SqlConnection(calendarString);
        calendarConnection.Open();
        calendarAdapter = new System.Data.SqlClient.SqlDataAdapter(strSQL, calendarConnection);
        calendarAdapter.Fill(calendarDataTable);
 
        e.Cell.BorderStyle = BorderStyle.Solid;
        e.Cell.BorderWidth = 1;
 
        if (e.Day.IsOtherMonth)
        {
            e.Cell.Controls.Clear();
        }
 
        if (e.Day.IsToday)
        {
            e.Cell.BackColor = System.Drawing.Color.Yellow;
        }
 
        foreach (DataRow calendarDataRow in calendarDataTable.Rows)
        {
            if (e.Day.Date.Date == Convert.ToDateTime(calendarDataRow["meeting_datetime"]).Date && !e.Day.IsOtherMonth)
            {
                e.Cell.BackColor = System.Drawing.Color.Green;
            }
        }
    }
 
    protected void Calendar1_SelectionChanged(object sender, EventArgs e)
    {
        SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = Calendar1.SelectedDate.Month.ToString();
        GridView1.DataBind();
        GridView1.Caption = "Hey, honkies!";
    }
 
    protected void Calendar1_VisibleMonthChanged(object sender, MonthChangedEventArgs e)
    {
        SqlDataSource2.SelectParameters["MeetingMonth"].DefaultValue = Calendar1.SelectedDate.Month.ToString();
        GridView1.DataBind();
        GridView1.Caption = "Hey, honkies!";
    }
}

Open in new window

0
 
mrcoulsonAuthor Commented:
Well, it gets to those events.  If I change

Calendar1.SelectedDate.Month.ToString();

to

DateTime.Now.Month.ToString();

I get a gridview for every month.  Of course, it's only this months' gridview, but at least we know those events are sorta working.  It's just the Calendar1.SelectedDate.Month.ToString();

Hmmmmmmmm....................

Jeremy
0
 
mrcoulsonAuthor Commented:
If I set the caption to Calendar1.SelectedDate.Month.ToString();, I get "1".

Jeremy
0
 
mrcoulsonAuthor Commented:
Ah.  But if I replace it with

Calendar1.SelectedDate.Date.Month.ToString();

and click a date, it shows me the month number.  Hmmm....
0
 
CtrlAltDlCommented:
Try using Calendar1.VisibleDate.Month.ToString() instead of SelectedDate.
0
 
mrcoulsonAuthor Commented:
THAT IS IT!!!!

http://www.co.frederick.va.us/planning/CALENDARS/Default_3.aspx


CHECK IT!  SWEET!

Man, I've been working on that all day.  You freaking rule.  I owe you a drink.  I owe you several drinks.  Man, what a beautiful thing to see working.

Thank you so much.  It's unfair I can only award you 500 points for this.  
0
 
CtrlAltDlCommented:
Yea, that is pretty kewl.  I think I'm going to have to use that for a Job Scheduling app that I have.
0
 
mrcoulsonAuthor Commented:
Rad.  Then my question served us both.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 24
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now