I have two filters here, department's DropDownList and Month calendar.
For now, the DDL is working find and filtering the GridView as expected. Now the problem is, I cannot sort the GridView using 2 range of dates (From and To). The code below is working fine but what makes it to not sort? Is it possible without a 'submit'
button to make it sort (press it to make it sort)?
I hope some1 can guide me thru this. Anythg pls let me noe as Im more than happy to assist u guys on solving this.
Here's the code:
<asp:SqlDataSource ID="" 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 [month] >= '{1}' AND [month] <= '{2}' ">
1）Just make your GridView's AllowSort = True，everything will be automatically done by the GridView's inner state or algorithm。
2）Also you can add order by statement at the back of SelectCommand statement。
【Sample】
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 [month] >= '{1}' AND [month] <= '{2}' order by SomeColumnName"
PS：If you want to dynamically sorting，just handle the SqlDataSource_Selecting event to dynamically set the SelectCommand。
NIthya Natha...
Member
6 Points
59 Posts
Sorting data in between 2 dates range.
May 07, 2012 09:20 AM|LINK
I have two filters here, department's DropDownList and Month calendar.
For now, the DDL is working find and filtering the GridView as expected. Now the problem is, I cannot sort the GridView using 2 range of dates (From and To). The code below is working fine but what makes it to not sort? Is it possible without a 'submit' button to make it sort (press it to make it sort)?
I hope some1 can guide me thru this. Anythg pls let me noe as Im more than happy to assist u guys on solving this.
Here's the code:
<asp:SqlDataSource ID="" 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 [month] >= '{1}' AND [month] <= '{2}' ">
<FilterParameters>
<asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" />
<asp:ControlParameter Name="month1" ControlID="from" PropertyName="Text" Type="DateTime" DefaultValue="1/1/1900" />
<asp:ControlParameter Name="month2" ControlID="to" PropertyName="Text" Type="DateTime" DefaultValue="1/1/3000" />
</FilterParameters>
<asp:SqlDataSource>
The page will look like this when executed:
Department: Month
DropDownList From: TextBox 1 To: TextBox2
So when user input the filter either the DDL or the calendar, the GridView will be formed accordingly.
Hope Im clear here.
Thanks in advance.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Sorting data in between 2 dates range.
May 09, 2012 01:54 AM|LINK
Two ideas for you NIthya：）
1）Just make your GridView's AllowSort = True，everything will be automatically done by the GridView's inner state or algorithm。
2）Also you can add order by statement at the back of SelectCommand statement。
【Sample】
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 [month] >= '{1}' AND [month] <= '{2}' order by SomeColumnName"
PS：If you want to dynamically sorting，just handle the SqlDataSource_Selecting event to dynamically set the SelectCommand。