locked
Need help with filter expression to sort data access within date range. RRS feed

  • Question

  • User1414062701 posted

    Im trying to form a gridview that can be sorted by user selection. For now the dropdown list works fine and yielding the result as expected. Now im having problem with the sorting part where the sort will be based on user input of dates (from and to). I cant do this part yet and having problem on the "Filter Expression" part. Im unsure on how to construct the right filter expression for this particular filter.

    I hope you guys can have a look at the code and let me know whr am I doing it wrongly. Here is the complete code for it:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="IncomeReport.aspx.cs" Inherits="IncomeReport" EnableEventValidation="false"%>
    
    <!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">
        <title></title>
        <script type="text/javascript" language="javascript">
            function CallPrint() {
                var grid_ID = 'GridView1';
                var grid_obj = document.getElementById(grid_ID);
                if (grid_obj != null) {
                    var new_window = window.open(print.html);
                    new_window.document.write(grid_obj.outerHTML);
                    new_window.print();
                    new_window.close();
                }
            }
            function winopen() {
                window.open("cal.aspx", "mywindow", "left=20, top=20, width=250, height=250, toolbar=0, resizable=0");
            }
        </script>
        <script src="Scripts/CalendarControl.js" language="javascript" type="text/javascript"></script>
        <link href="Styles/CalendarControl.css" rel="Stylesheet" type="text/css" />
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <table border="1">
        <tr>
        <td>
        Department
        </td>
        <td>
        Cost Centre
        </td>
        <td>
        Month
        </td>
        <td>
        Amount
        </td>
        </tr>
        <tr>
        <td>
        <asp:DropDownList ID="ddlDept" DataSourceID="PopulateDept" AutoPostBack="true" DataValueField="dept_ID" DataTextField="dept_name" runat="server" AppendDataBoundItems="true">
        <asp:ListItem Text="---Select A Department---" Value="default"></asp:ListItem>
        <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
            <asp:SqlDataSource ID="PopulateDept" runat="server" 
                ConnectionString="<%$ ConnectionStrings:MyConn %>" 
                ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" 
                SelectCommand="SELECT DISTINCT [dept_ID], [dept_name] FROM [department]"></asp:SqlDataSource>
        </td>
        <td>
        <asp:DropDownList ID="ddlCC" AutoPostBack="true" DataValueField="cc_ID" DataTextField="costCentre_name" runat="server" AppendDataBoundItems="true">
        <asp:ListItem Text="---Select A Cost Centre---" Value="default"></asp:ListItem>
        <asp:ListItem Text="All" Value="%"></asp:ListItem>
        </asp:DropDownList>
        </td>
        <td>From:
            <input id="from" name="from" onfocus="showCalendarControl(this)" type="text" />
    &nbsp;
            To:
            <input id="to" name="to" onfocus="showCalendarControl(this)" type="text" />
    &nbsp;
            </td>
        <td></td>
        </tr>
        <tr>
        <td colspan="4">
            <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" ShowFooter="true" AllowSorting="true" DataSourceID="dsGridView" PageSize="10" OnRowDataBound="GridView1_RowDataBound">
        <Columns>
        <asp:BoundField DataField="dept_name" HeaderText="Department Name" SortExpression="dept_ID" />
        <asp:BoundField DataField="costCentre_name" HeaderText="Cost Centre" SortExpression="cc_ID" />
        <asp:BoundField DataField="month" HeaderText="Month" FooterText="<b>Total</b>" SortExpression="month" />
        <asp:TemplateField HeaderText="Amount" SortExpression="amount">
        <ItemTemplate>
        <asp:Label ID="lblTotal" runat="server" Text='<%# Eval("amount").ToString() %>'>
        </asp:Label>
        </ItemTemplate>
        <FooterTemplate>
        <asp:Label ID="lblTotal2" runat="server"></asp:Label>
        </FooterTemplate>
        </asp:TemplateField>
        </Columns>
        </asp:GridView>
            <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], m.[month], m.[amount] FROM department d, costCentre c, monthlyIncome m WHERE d.dept_ID=c.dept_ID AND c.cc_ID=m.cc_ID" FilterExpression="Convert(dept_ID, 'System.String')  like '{0}%' AND Convert(cc_ID, 'System.String') like '{1}%' AND ([month] Convert('from.Text', 'System.DateTime') AND Convert('to.Text', 'System.DateTime'))">
                <FilterParameters>
                <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />
                <asp:ControlParameter Name="cc_ID" ControlID="ddlCC" PropertyName="SelectedValue" />
                <asp:ControlParameter Name="month" ControlID="from" PropertyName="Text" />
                <asp:ControlParameter Name="month" ControlID="to" PropertyName="Text" />
                </FilterParameters>
                </asp:SqlDataSource>
        </td>
        </tr>
        </table>
        <input type="button" value="Print" id="btnPrint" runat="server" onclick="javascript:CallPrint()" /> OR
        Report Format:<asp:DropDownList ID="ddlReportFormat" runat="server">
        <asp:ListItem Text="Please Select A Format" Value="default"></asp:ListItem> 
        <asp:ListItem Text="Excel" Value="1"></asp:ListItem>
        <asp:ListItem Text="Word" Value="2"></asp:ListItem>
        <asp:ListItem Text="PDF" Value="3"></asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="repSel" runat="server" Text="Generate Report" OnClick="GenerateReport"/>
             </div>
        </form>
    </body>
    </html>
    

    Monday, April 23, 2012 2:30 AM

Answers

  • User3866881 posted

    Please set DefaultValue for parameters……

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 27, 2012 3:09 AM

All replies

  • User1414062701 posted

    Mate, the DDL is working fine for sorting, the problem is on sorting based on two (from and to) calendar picker. You have any idea on that? Take a look at the FilterExpression.

    Monday, April 23, 2012 5:33 AM
  • User3866881 posted

    It seems that you want to do filtering between the two Selected Dates from the two calendars……So you should use ControlParameter instead for the Filter Expression。You can try to change to this(Suppose your from or to textboxes having values like 2012-01-10 12:12:12——with perticular timespan……)。

    <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name],
     m.[month], m.[amount] FROM department d, costCentre c, monthlyIncome m WHERE d.dept_ID=c.dept_ID AND c.cc_ID=m.cc_ID" FilterExpression="Convert(dept_ID, 'System.String') like '{0}%' AND Convert(cc_ID, 'System.String') like '{1}%' AND ([month]>=Convert(month1,'System.DateTime') AND [month]<=Convert(month2,"System.DateTime")">
                <FilterParameters>
                <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />
                <asp:ControlParameter Name="cc_ID" ControlID="ddlCC" PropertyName="SelectedValue" />
                <asp:ControlParameter Name="month1" ControlID="from" PropertyName="Text" />
                <asp:ControlParameter Name="month2" ControlID="to" PropertyName="Text" />
                </FilterParameters>
                </asp:SqlDataSource>
    Tuesday, April 24, 2012 9:21 PM
  • User1414062701 posted

    It seems that you want to do filtering between the two Selected Dates from the two calendars……So you should use ControlParameter instead for the Filter Expression。You can try to change to this(Suppose your from or to textboxes having values like 2012-01-10 12:12:12——with perticular timespan……)。

    <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name],   m.[month], m.[amount] FROM department d, costCentre c, monthlyIncome m WHERE d.dept_ID=c.dept_ID AND c.cc_ID=m.cc_ID" FilterExpression="Convert(dept_ID, 'System.String') like '{0}%' AND Convert(cc_ID, 'System.String') like '{1}%' AND ([month]>=Convert(month1,'System.DateTime')   AND [month]<=Convert(month2,"System.DateTime")">              <FilterParameters>              <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />              <asp:ControlParameter Name="cc_ID" ControlID="ddlCC" PropertyName="SelectedValue" />              <asp:ControlParameter Name="month1" ControlID="from" PropertyName="Text" />              <asp:ControlParameter Name="month2" ControlID="to" PropertyName="Text" />              </FilterParameters>              </asp:SqlDataSource>

    Mate, I have tried what u suggested. But im getting this error "could not find control 'from' in ControlParameter 'month1'.

    Is it because im using html way of text input instead of <asp:TextBox> ? Any alternative or suggestion?

    Thursday, April 26, 2012 11:44 PM
  • User1414062701 posted

    It seems that you want to do filtering between the two Selected Dates from the two calendars……So you should use ControlParameter instead for the Filter Expression。You can try to change to this(Suppose your from or to textboxes having values like 2012-01-10 12:12:12——with perticular timespan……)。

    <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name],   m.[month], m.[amount] FROM department d, costCentre c, monthlyIncome m WHERE d.dept_ID=c.dept_ID AND c.cc_ID=m.cc_ID" FilterExpression="Convert(dept_ID, 'System.String') like '{0}%' AND Convert(cc_ID, 'System.String') like '{1}%' AND ([month]>=Convert(month1,'System.DateTime')   AND [month]<=Convert(month2,"System.DateTime")">              <FilterParameters>              <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />              <asp:ControlParameter Name="cc_ID" ControlID="ddlCC" PropertyName="SelectedValue" />              <asp:ControlParameter Name="month1" ControlID="from" PropertyName="Text" />              <asp:ControlParameter Name="month2" ControlID="to" PropertyName="Text" />              </FilterParameters>              </asp:SqlDataSource>

    Mate, I have tried what u suggested. But im getting this error "could not find control 'from' in ControlParameter 'month1'.

    Is it because im using html way of text input instead of <asp:TextBox> ? Any alternative or suggestion?

    Thursday, April 26, 2012 11:44 PM
  • User3866881 posted

    Is it because im using html way of text input instead of <asp:TextBox

    Just add "runat="server"" to your plain Html textbox and have a try……

    Thursday, April 26, 2012 11:48 PM
  • User1414062701 posted

    Just add "runat="server"" to your plain Html textbox and have a try……

    I tried it before but wen build, this error will occur:

    DataBinding: 'System.Web.UI.HtmlControls.HtmlInputText' does not contain a property with the name 'Text'.

    Friday, April 27, 2012 2:07 AM
  • User3866881 posted

    Then you have to change to the standard asp:TextBox,this is a class that have the property……

    Or if you insist using HtmlTextBox,Try use "Value"……

    Friday, April 27, 2012 2:09 AM
  • User1414062701 posted

    Ok, now it works. I have change it to asp:TextBox. But do you have any idea why the gridview is loaded when the page is loaded even without user selection?

    I didnt load anythg in page_load().

    Friday, April 27, 2012 2:38 AM
  • User3866881 posted

    But do you have any idea why the gridview is loaded when the page is loaded even without user selection?

    Do you mean when you click something and the page renders,and then your Selected Row disappear to become a normal one……?

    Friday, April 27, 2012 2:43 AM
  • User1414062701 posted

    No mate. By right, when the page is loaded, there will be 2 DropDownList and 2 TextBoxes(From and To) that will be the filter for the gridview formation. So there will be no gridview when the page is loaded until user select something on the provided filters.

    But now the gridview is preloaded once the page is loaded. What's wrong? Is it because of the from and to filter expression?

    If I remove the month filter, the DDL filter works fine. With all 4 filter expressions, the gridview is loaded automatically. Hope im clear here.

    Friday, April 27, 2012 2:50 AM
  • User3866881 posted

    Please set DefaultValue for parameters……

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 27, 2012 3:09 AM