When the user doesn't type anything in the textbox and clicks Search it works fine.
If they type in anything for the firstname and clicks search, I programatically change the filterexpression to return the right results, to not include NULLs. FilterExpression="(FirstName Like '%{0}%')" and this all works fine.
But is there a way to do this WITHOUT programatically changing the filterexpression? Maybe different SQL for the SQLDatasource or a more complex filterexpression or both?
I was hoping NOT to programatically change the filterexpression.
I get a syntax error in FilterExpression="(FirstName Like '%{0}%' OR {0} IS NULL)"
Syntax error: Missing operand before 'Mod' operator
the syntax it accepts is FilterExpression="(FirstName Like '%{0}%' OR FirstName IS NULL)"
=============================
Wierdly it works as I wnat it to with
FilterExpression="(FirstName Like '%{0}%')
<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" />
WITH NO DEFAULT VALUE
</FilterParameters>
but when you add LastName it won't show null firstnames
FilterExpression="(FirstName Like '%{0}%' AND LastName Like '%{1}%')"
FilterExpression="(FirstNameNulls Like '%{0}%') AND LastName Like '%{1}%'"
SelectCommand="SELECT CustomerID, LoginName, FirstName, ISNULL(FirstName, '') AS FirstNameNulls, LastName, Address1, Address2, City, State, Zip, Phone, EmailAddress FROM Customers ORDER BY FirstName">
craigbtx
Participant
1702 Points
896 Posts
Filter expressions and NULLS
Jan 04, 2013 06:49 PM|LINK
I have a TextBox and a search button
First Name ____________
SearchButton
The SQLDatasource for a results gridview has a filter expression as defined below
FilterExpression="(FirstName Like '%{0}%' OR FirstName IS NULL)"
<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" DefaultValue="%" Name="newparameter"
</FilterParameters>
When the user doesn't type anything in the textbox and clicks Search it works fine.
If they type in anything for the firstname and clicks search, I programatically change the filterexpression to return the right results, to not include NULLs. FilterExpression="(FirstName Like '%{0}%')" and this all works fine.
But is there a way to do this WITHOUT programatically changing the filterexpression? Maybe different SQL for the SQLDatasource or a more complex filterexpression or both?
I was hoping NOT to programatically change the filterexpression.
Thanks
Yousef_Jadal...
Star
8812 Points
1502 Posts
Re: Filter expressions and NULLS
Jan 04, 2013 07:25 PM|LINK
Try to use this filterexpression = (FirstName Like '%{0}%' OR {0} IS NULL)
craigbtx
Participant
1702 Points
896 Posts
Re: Filter expressions and NULLS
Jan 04, 2013 08:15 PM|LINK
But when someone types in a first name, it will also return first names that are NULL.
It shouldn't find nulls if a firstname is entered.
Yousef_Jadal...
Star
8812 Points
1502 Posts
Re: Filter expressions and NULLS
Jan 04, 2013 08:22 PM|LINK
My query will not do that. just check it.
craigbtx
Participant
1702 Points
896 Posts
Re: Filter expressions and NULLS
Jan 04, 2013 08:56 PM|LINK
I get a syntax error in FilterExpression="(FirstName Like '%{0}%' OR {0} IS NULL)"
Syntax error: Missing operand before 'Mod' operator
the syntax it accepts is FilterExpression="(FirstName Like '%{0}%' OR FirstName IS NULL)"
=============================
Wierdly it works as I wnat it to with
FilterExpression="(FirstName Like '%{0}%')
<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" />
WITH NO DEFAULT VALUE
</FilterParameters>
but when you add LastName it won't show null firstnames
FilterExpression="(FirstName Like '%{0}%' AND LastName Like '%{1}%')"
<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
<asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
</FilterParameters>
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Filter expressions and NULLS
Jan 06, 2013 02:33 AM|LINK
Hi,
Please change DefaultValue from "%" to "":
craigbtx
Participant
1702 Points
896 Posts
Re: Filter expressions and NULLS
Jan 06, 2013 01:58 PM|LINK
Sorry decker that solution doesn't work. But thanks anyway.
craigbtx
Participant
1702 Points
896 Posts
Re: Filter expressions and NULLS
Jan 06, 2013 03:24 PM|LINK
I got it!
The trick is in the SQL - Use ISNULL(FirstName, '') AS FirstNameNulls
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPDotNet35ExamplesConnectionString %>"
FilterExpression="(FirstNameNulls Like '%{0}%') AND LastName Like '%{1}%'"
SelectCommand="SELECT CustomerID, LoginName, FirstName, ISNULL(FirstName, '') AS FirstNameNulls, LastName, Address1, Address2, City, State, Zip, Phone, EmailAddress FROM Customers ORDER BY FirstName">
<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
<asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
</FilterParameters>
</asp:SqlDataSource>