Shepwedd
asked on
How do I filter a linq query using textbox controls?
I have written a C# web site using visual studio 2008. I am using a listview control bound to a linqdatasource. My listview is populated upon the OnSelecting event of my linqdatasource which contains a linq select query. I want to use two textboxes (both containing date values) on my frontend but sitting outside of my listview to further filter my backend linq query so that only data between certain dates is returned and shown in my listview. Is this possible? I guess I will have to use a button click event to run my filter on the linqdatasource but the textboxes and button will be sitting outside of the listview so will I still be able to filter the listview? What would be my best approach?
Thanks.
Thanks.
Here's a simple example that had a linq data source to a users table. I use 2 textboxes to set the range of birth dates that i care about.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:CompareValidator ID="CompareValidator1" runat="server" ControlToValidate="TextBox1" Display="Dynamic" ErrorMessage="Must Be Date" Operator="DataTypeCheck" Type="Date"></asp:CompareValidator>
<br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:CompareValidator ID="CompareValidator2" runat="server" ControlToValidate="TextBox2" Display="Dynamic" ErrorMessage="Must Be Date" Operator="DataTypeCheck" Type="Date"></asp:CompareValidator>
<br />
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
<br />
<asp:ListView ID="lv1" runat="server" DataSourceID="LinqDataSource1" runat="server" ItemPlaceholderID="ph1">
<LayoutTemplate>
<asp:PlaceHolder ID="ph1" runat="server"></asp:PlaceHolder>
</LayoutTemplate>
<ItemTemplate>
<asp:Label ID="lbl1" runat="server" Text='<%# Eval("UserName") %>'></asp:Label>
</ItemTemplate>
</asp:ListView>
<asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="Web_Form.DataClasses1DataContext" TableName="Users" Where="BirthDate >= @BirthDate && BirthDate <= @BirthDate1">
<WhereParameters>
<asp:Parameter Name="BirthDate" Type="DateTime" />
<asp:Parameter Name="BirthDate1" Type="DateTime" />
</WhereParameters>
</asp:LinqDataSource>
</form>
</body>
</html>
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.LinqDataSource1.WhereParameters[0].DefaultValue = "1/1/1800";
this.LinqDataSource1.WhereParameters[1].DefaultValue = "1/1/9000";
}
}
protected void Button1_Click(object sender, EventArgs e)
{
this.LinqDataSource1.WhereParameters[0].DefaultValue = string.IsNullOrEmpty(this.TextBox1.Text) ? "1/1/1800" : this.TextBox1.Text;
this.LinqDataSource1.WhereParameters[1].DefaultValue = string.IsNullOrEmpty(this.TextBox2.Text) ? "1/1/9000" : this.TextBox2.Text;
this.lv1.DataBind();
}
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wht1986,
Your example is fine when you're only initially selecting data from the one table but my data is coming from multiple tables, will this approach still be possible? Attached in the OnSelecting event (containing my linq select query) of the linqdatasource that is bound to my listview...
Your example is fine when you're only initially selecting data from the one table but my data is coming from multiple tables, will this approach still be possible? Attached in the OnSelecting event (containing my linq select query) of the linqdatasource that is bound to my listview...
protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
TrustSystemDataContext db = new TrustSystemDataContext();
var query = from tt in db.Trusts
join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
join cdtcd in db.TrustCrucialDates on tt.TrustID equals cdtcd.TrustID
join cdel in db.EventLookups on cdtcd.EventCode equals cdel.EventCode
join ptp in db.TrustPayments on tt.TrustID equals ptp.TrustID
join pdl in db.DayLookups on ptp.DayCode equals pdl.DayCode
join pml in db.MonthLookups on ptp.MonthCode equals pml.MonthCode
join pyl in db.YearLookups on ptp.YearCode equals pyl.YearCode
join ppfl in db.PaymentFrequencyLookups on ptp.FrequencyCode equals ppfl.FrequencyCode
orderby tt.ClientName ascending
select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustTypeCode, tt.TrustNotes, cdtcd.CrucialNotes, ptp.PaymentNotes, felt.FeeExecDescription, plt.PartnerDescription, tlt.TypeDescription, cdtcd.DueDate, cdel.EventDescription, pdl.DayDescription, pml.MonthDescription, pyl.YearDescription, ppfl.FrequencyDescription, ptp.Amount };
e.Result = query;
}
Yes both ways posted should work
Here's an example of the code behind manual setting, ran with no problems
Here's an example of the code behind manual setting, ran with no problems
DataClasses1DataContext dc = new DataClasses1DataContext();
var results = from d in dc.Users
join c in dc.CUSTOMERs on d.UserID equals c.kundenr
select new { d.UserName, d.BirthDate, c.postnr };
if (this.TextBox1.Text.Trim() != "")
results = results.Where(x => x.BirthDate >= DateTime.Parse(this.TextBox1.Text));
if (this.TextBox2.Text.Trim() != "")
results = results.Where(x => x.BirthDate <= DateTime.Parse(this.TextBox2.Text));
this.lv1.DataSource = results;
this.lv1.DataBind();
if you notice im not using the selecting event in either of my examples
1) way uses a static 'where' declaration with parameters in the linq data source. I set the default values for the parameters in code to achieve what i want.
2) second way doesnt use a page level linqdatasource at all, i manually construct the linq expression and assign it to the listview datasource when i want to.
1) way uses a static 'where' declaration with parameters in the linq data source. I set the default values for the parameters in code to achieve what i want.
2) second way doesnt use a page level linqdatasource at all, i manually construct the linq expression and assign it to the listview datasource when i want to.
ASKER
Great!
But I seem to be getting the error: "Specified cast is not valid" on the below line (lvReports is the ID of my listview) :
this.lvReports.DataBind();
??
But I seem to be getting the error: "Specified cast is not valid" on the below line (lvReports is the ID of my listview) :
this.lvReports.DataBind();
??
are you manually setting
this.lvReports.DataSource = (whatever your linq query source is here);
this.lvReports.DataBind();
or are you still using the on page LinqDataSource?
this.lvReports.DataSource = (whatever your linq query source is here);
this.lvReports.DataBind();
or are you still using the on page LinqDataSource?
ASKER
I am manually setting.
Attached is my listview and textboxes (outside of listview) along with my SetLinq method...
Attached is my listview and textboxes (outside of listview) along with my SetLinq method...
<div class="filterReports">
From:
<asp:TextBox ID="tbSearchDateFrom" runat="server" CssClass="calendarTxtBox"></asp:TextBox>
<asp:RequiredFieldValidator ID="DateFromValidator" runat="server" ControlToValidate="tbSearchDateFrom" ErrorMessage="*" SetFocusOnError="true" Display="Dynamic" ValidationGroup="SearchInsert"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="DateFromRegularValidator" runat="server" ErrorMessage="<br><small>Invalid date.</small>" ValidationExpression="^(?=\d)(?:(?:31(?!.(?:0?[2469]|11))|(?:30|29)(?!.0?2)|29(?=.0?2.(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00)))(?:\x20|$))|(?:2[0-8]|1\d|0?[1-9]))([-./])(?:1[012]|0?[1-9])\1(?:1[6-9]|[2-9]\d)?\d\d(?:(?=\x20\d)\x20|$))?(((0?[1-9]|1[012])(:[0-5]\d){0,2}(\x20[AP]M))|([01]\d|2[0-3])(:[0-5]\d){1,2})?$" Display="Dynamic" ControlToValidate="tbSearchDateFrom" ValidationGroup="SearchInsert"></asp:RegularExpressionValidator>
<ajaxToolKit:CalendarExtender ID="DateFromCalendar" runat="server" TargetControlID="tbSearchDateFrom" Format="dd/MM/yyyy" CssClass="MyCalendar" PopupButtonID="DateFromCalendarImage">
</ajaxToolKit:CalendarExtender>
To:
<asp:TextBox ID="tbSearchDateTo" runat="server" CssClass="calendarTxtBox"></asp:TextBox>
<asp:RequiredFieldValidator ID="DateToValidator" runat="server" ControlToValidate="tbSearchDateTo" ErrorMessage="*" SetFocusOnError="true" Display="Dynamic" ValidationGroup="SearchInsert"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="DateToRegularValidator" runat="server" ErrorMessage="<br><small>Invalid date.</small>" ValidationExpression="^(?=\d)(?:(?:31(?!.(?:0?[2469]|11))|(?:30|29)(?!.0?2)|29(?=.0?2.(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00)))(?:\x20|$))|(?:2[0-8]|1\d|0?[1-9]))([-./])(?:1[012]|0?[1-9])\1(?:1[6-9]|[2-9]\d)?\d\d(?:(?=\x20\d)\x20|$))?(((0?[1-9]|1[012])(:[0-5]\d){0,2}(\x20[AP]M))|([01]\d|2[0-3])(:[0-5]\d){1,2})?$" Display="Dynamic" ControlToValidate="tbSearchDateTo" ValidationGroup="SearchInsert"></asp:RegularExpressionValidator>
<asp:CompareValidator ID="DateToCompareValidator" runat="server" ControlToCompare="tbSearchDateFrom" ControlToValidate="tbSearchDateTo" ErrorMessage="<br><small>To date must be after From date.</small>" Operator="GreaterThan" ValidationGroup="SearchInsert" Type="Date"></asp:CompareValidator>
<ajaxToolKit:CalendarExtender ID="DateToCalendar" runat="server" TargetControlID="tbSearchDateTo" Format="dd/MM/yyyy" CssClass="MyCalendar" PopupButtonID="DateToCalendarImage">
</ajaxToolKit:CalendarExtender>
<asp:Button ID="ReportButton" runat="server" Text="Report" onclick="ReportButton_Click" />
</div>
<asp:ListView ID="lvReports" runat="server">
<LayoutTemplate>
<table id="Reports" runat="server" class="gridReports" cellspacing="0" border="0">
<tr>
<th><asp:LinkButton ID="btnSortClientName" runat="server" Text="Client Name | Matter | Type | Partner | FE" CommandName="Sort" CommandArgument="ClientName" Width="245px" /></th>
<th><asp:LinkButton ID="btnSortCrucialDueDate" runat="server" Text="Crucial Due Date | Event" CommandName="Sort" CommandArgument="DueDate" Width="140px" /></th>
<th><asp:LinkButton ID="btnSortPayment" runat="server" Text="Payment Details" CommandName="Sort" CommandArgument="FeeExecCode" Width="95px" /></th>
<th><asp:LinkButton ID="btnSortDetails" runat="server" Text="Notes: Trust | Crucial | Payment" CommandName="Sort" CommandArgument="Details" Width="185px" /></th>
</tr>
<tr id="itemPlaceholder" runat="server" />
<tr class="pagerReports">
<td colspan="9">
<div class="container">
<%--***************************************************************************************************************--%>
<%--*******ListView Pager with Page Slide Control - PageSize controls how many rows on each page of ListView*******--%>
<%--***************************************************************************************************************--%>
<asp:DataPager ID="pagerReports" runat="server" PageSize="10">
<Fields>
<asp:TemplatePagerField OnPagerCommand="PagerCommand">
<PagerTemplate>
<div class="commandReports">
<asp:ImageButton ID="btnFirst" runat="server" CommandName="First" ImageUrl="~/App_Themes/images/ReportsUI/first.gif" AlternateText="First Page" ToolTip="First Page" />
<asp:ImageButton ID="btnPrevious" runat="server" CommandName="Previous" ImageUrl="~/App_Themes/images/ReportsUI/prev.gif" AlternateText="Previous Page" ToolTip="Previous Page" />
</div>
<div class="commandReports">
<asp:TextBox
ID="txtSlider" runat="server"
Text=
'<%#
Container.TotalRowCount > 0
? Math.Ceiling(((double)(Container.StartRowIndex + Container.MaximumRows) / Container.MaximumRows))
: 0
%>'
AutoPostBack="true" OnTextChanged="CurrentPageChanged"
style="visibility:hidden" />
<ajaxToolkit:SliderExtender
ID="slider" BehaviorID="slider" runat="server"
TargetControlID="txtSlider"
Orientation="Horizontal"
Minimum="1"
Maximum='<%# Math.Ceiling((double)Container.TotalRowCount / Container.MaximumRows) %>'
TooltipText='<%# "Page {0} of " + Math.Ceiling ((double)Container.TotalRowCount / Container.MaximumRows).ToString() + " (" + Container.TotalRowCount + " items)" %>'/>
</div>
<div class="commandReports">
<asp:ImageButton ID="btnNext" runat="server" CommandName="Next" ImageUrl="~/App_Themes/images/ReportsUI/next.gif" AlternateText="Next Page" ToolTip="Next Page" />
<asp:ImageButton ID="btnLast" runat="server" CommandName="Last" ImageUrl="~/App_Themes/images/ReportsUI/last.gif" AlternateText="Last Page" ToolTip="Last Page" />
</div>
<div class="info">
Page
<b>
<%# Container.TotalRowCount > 0 ? Math.Ceiling(((double)(Container.StartRowIndex + Container.MaximumRows) / Container.MaximumRows)) : 0%>
</b>
of
<b>
<%# Math.Ceiling((double)Container.TotalRowCount / Container.MaximumRows)%>
</b>
(<%# Container.TotalRowCount%> items)
</div>
</PagerTemplate>
</asp:TemplatePagerField>
</Fields>
</asp:DataPager>
<%--***************************************************************************************************************--%>
<%--***************************************************END*********************************************************--%>
<%--***************************************************************************************************************--%>
</div>
</td>
</tr>
</table>
</LayoutTemplate>
<EmptyDataTemplate>
<table id="Table1" runat="server" style="">
<tr>
<td>No data was returned.</td>
</tr>
</table>
</EmptyDataTemplate>
<ItemTemplate>
<%--The line below allows for the itemtemplate (odd numbers) to also accommodate for the alternateitemtemplate (even numbers) as these two template are, in my case, a duplicate of each other. The style classes to use for each are specified after the "?".--%>
<tr class='<%# (Container.DataItemIndex % 2 == 0)?"row":"altrow" %>' id="row" runat="server" style="height:72px;">
<td>
<b><%# Eval("ClientName") %></b>
<br />
<b><font color="green"><%# Eval("MatterNo")%></font></b>
<br />
<%--<b><font color="blue"><%# Eval("TrustTypeDescription")%> <%# Eval("TypeDescription")%></font></b>
<br />--%>
<b><font color="brown"><%# Eval("PartnerDescription")%></font></b>
<br />
<b><font color="orange"><%# Eval("FeeExecDescription")%></font></b>
</td>
<td>
<%# Eval("DueDate")%>
<br />
<%# Eval("EventDescription")%>
</td>
<td>
<%# Eval("DayDescription")%> <%# Eval("MonthDescription")%> <%# Eval("YearDescription")%>
<br />
<%# Eval("FrequencyDescription")%>
<br />
£<%# Eval("Amount")%>
</td>
<td>
<ajaxToolkit:HoverMenuExtender
ID="HoverMenuExtenderTrusts"
runat="server"
TargetControlID="ImageButtonTrusts"
PopupControlID="PanelPopUpTrusts"
PopupPosition="Right"
OffsetX="5"
PopDelay="25"
HoverCssClass="popupHover">
</ajaxToolkit:HoverMenuExtender>
<asp:Panel
ID="PanelPopUpTrusts"
runat="server"
Width="165px"
CssClass="popupMenu">
<asp:Label ID="LabelDetailsTrusts" runat="server" Text='<%# Eval("TrustNotes") ?? "No Details Written" %>'></asp:Label>
</asp:Panel>
<asp:ImageButton ID="ImageButtonTrusts" runat="server" ImageUrl="~/App_Themes/images/Thickbox/info.png" />
<br />
<ajaxToolkit:HoverMenuExtender
ID="HoverMenuExtenderCrucialDates"
runat="server"
TargetControlID="ImageButtonCrucialDates"
PopupControlID="PanelPopUpCrucialDates"
PopupPosition="Right"
OffsetX="5"
PopDelay="25"
HoverCssClass="popupHover">
</ajaxToolkit:HoverMenuExtender>
<asp:Panel
ID="PanelPopUpCrucialDates"
runat="server"
Width="165px"
CssClass="popupMenu">
<asp:Label ID="LabelNotesCrucialDates" runat="server" Text='<%# Eval("CrucialNotes") ?? "No Notes Written" %>'></asp:Label>
</asp:Panel>
<asp:ImageButton ID="ImageButtonCrucialDates" runat="server" ImageUrl="~/App_Themes/images/Thickbox/info.png" />
<br />
<ajaxToolkit:HoverMenuExtender
ID="HoverMenuExtenderPayments"
runat="server"
TargetControlID="ImageButtonPayments"
PopupControlID="PanelPopUpPayments"
PopupPosition="Right"
OffsetX="5"
PopDelay="25"
HoverCssClass="popupHover">
</ajaxToolkit:HoverMenuExtender>
<asp:Panel
ID="PanelPopUpPayments"
runat="server"
Width="165px"
CssClass="popupMenu">
<asp:Label ID="LabelNotesPayments" runat="server" Text='<%# Eval("PaymentNotes") ?? "No Notes Written" %>'></asp:Label>
</asp:Panel>
<asp:ImageButton ID="ImageButtonPayments" runat="server" ImageUrl="~/App_Themes/images/Thickbox/info.png" />
</td>
</tr>
</ItemTemplate>
</asp:ListView>
**********************************************************
private void SetLinq()
{
TrustSystemMARTINSPALDINGDataContext db = new TrustSystemMARTINSPALDINGDataContext();
var query = from tt in db.Trusts
join tlt in db.TypeLookups on tt.TypeCode equals tlt.TypeCode
join felt in db.FeeExecLookups on tt.FeeExecCode equals felt.FeeExecCode
join plt in db.PartnerLookups on tt.PartnerCode equals plt.PartnerCode
join cdtcd in db.TrustCrucialDates on tt.TrustID equals cdtcd.TrustID
join cdel in db.EventLookups on cdtcd.EventCode equals cdel.EventCode
join ptp in db.TrustPayments on tt.TrustID equals ptp.TrustID
join pdl in db.DayLookups on ptp.DayCode equals pdl.DayCode
join pml in db.MonthLookups on ptp.MonthCode equals pml.MonthCode
join pyl in db.YearLookups on ptp.YearCode equals pyl.YearCode
join ppfl in db.PaymentFrequencyLookups on ptp.FrequencyCode equals ppfl.FrequencyCode
orderby tt.ClientName ascending
select new { tt.TrustID, tt.ClientName, tt.MatterNo, tt.PartnerCode, tt.FeeExecCode, tt.TypeCode, tt.TrustNotes, cdtcd.CrucialNotes, ptp.PaymentNotes, felt.FeeExecDescription, plt.PartnerDescription, tlt.TypeDescription, cdtcd.DueDate, cdel.EventDescription, pdl.DayDescription, pml.MonthDescription, pyl.YearDescription, ppfl.FrequencyDescription, ptp.Amount };
if (this.tbSearchDateFrom.Text.Trim() != "")
query = query.Where(x => x.DueDate >= DateTime.Parse(this.tbSearchDateFrom.Text));
if (this.tbSearchDateTo.Text.Trim() != "")
query = query.Where(x => x.DueDate <= DateTime.Parse(this.tbSearchDateTo.Text));
this.lvReports.DataSource = query;
this.lvReports.DataBind();
}ASKER
Do I perhaps have to Convert my textbox values to DateTime?
From the code behind everything looks good. Somewhere in the ListView template a control is being bound that is not happy. I usually see this when people try to use a bit field thinking it will return a 0 or 1 but instead it is really a boolean. If possible I would strip everything out of the template except maybe a single label (save your existing template in notepad or something) and then run. If the data binding works with that, then we know the linq expression is good and the problem is in the template. Then start adding back pieces of your old template a piece at a time to see where the template breaks.
Shep you are already converting them to DateTime with the lines
if (this.tbSearchDateFrom.Tex
query = query.Where(x => x.DueDate >= DateTime.Parse(this.tbSear
if (this.tbSearchDateTo.Text.
query = query.Where(x => x.DueDate <= DateTime.Parse(this.tbSear
if (this.tbSearchDateFrom.Tex
query = query.Where(x => x.DueDate >= DateTime.Parse(this.tbSear
if (this.tbSearchDateTo.Text.
query = query.Where(x => x.DueDate <= DateTime.Parse(this.tbSear
ASKER
Thought so, I was just clinching at straws. I will strip everything out and let you know. Thanks.
ASKER
You were right, one of my other controls was creating the error. I now have the correct filtered data binding to my list view. I'm now trying to create another filter and SetLinq where I have three dropdownlists on my frontend, one containing a month string value, one a day string value and one a year int value. How would I write the linq to filter my listview on these selections? My attempt is attached...
if (this.PaymentMonthDDL.SelectedValue.Trim() != "")
query = query.Where(x => x.MonthDescription == this.PaymentMonthDDL.SelectedValue);
if (this.PaymentDayDDL.SelectedValue.Trim() != "")
query = query.Where(x => x.DayDescription == this.PaymentDayDDL.SelectedValue);
if (this.PaymentYearDDL.SelectedValue.Trim() != "")
query = query.Where(x => x.YearDescription.ToString() == this.PaymentYearDDL.SelectedValue);
this.lvReports.DataSource = query;
this.lvReports.DataBind();
im assuming MonthDescription is an integer, and that you have an option inthe list for one of the items being an empty string.
use
if (this.PaymentDayDDL.Select
query = query.Where(x => x.DayDescription == int.Parse(this.PaymentDayD
use
if (this.PaymentDayDDL.Select
query = query.Where(x => x.DayDescription == int.Parse(this.PaymentDayD
ASKER
DayDescription and MonthDescription are both strings, YearDescription is an int. However, I am using lookup tables for these so I have DayCode and MonthCode which are both int values, should I be using these istead of the strings? i.e. Use DayCode instead of DayDescription above? It's just when I'm dealing with strings I can't seem to use "==".
ASKER
I just tried using the int values but I again get no data returned?
ASKER
What does the below part of your code do?...
x => x.MonthCode
x => x.MonthCode
things like .Where(x => x.MonthCode == ?) are called lambda expressions
heres a link http://geekswithblogs.net/dotnetnomad/archive/2008/01/29/119037.aspx
and yes generally i like to bind my drop downs to a source with text and a value, e.g. the primary key as the value of the items
when using strings there are some odd ways it does the compare, if you want to try the strings still, i would use something more like
if (this.PaymentDayDDL.Select
query = query.Where(x => x.DayDescription.Equals(th
heres a link http://geekswithblogs.net/dotnetnomad/archive/2008/01/29/119037.aspx
and yes generally i like to bind my drop downs to a source with text and a value, e.g. the primary key as the value of the items
when using strings there are some odd ways it does the compare, if you want to try the strings still, i would use something more like
if (this.PaymentDayDDL.Select
query = query.Where(x => x.DayDescription.Equals(th
ASKER
Great
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx