ASP.NET

Filter expressions and NULLS RSS

7 replies

Last post Jan 06, 2013 03:24 PM by craigbtx

‹ Previous Thread|Next Thread ›

- Reply
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
- Edited by craigbtx on Jan 04, 2013 06:50 PM
- Reply
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)

Welcome To My Blog
- Reply
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.
- Edited by craigbtx on Jan 04, 2013 08:15 PM
- Reply
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.

Welcome To My Blog
- Reply
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>
- Edited by craigbtx on Jan 04, 2013 09:05 PM
- Edited by craigbtx on Jan 04, 2013 09:06 PM
- Edited by craigbtx on Jan 04, 2013 09:10 PM
- Edited by craigbtx on Jan 04, 2013 09:16 PM
- Edited by craigbtx on Jan 04, 2013 10:10 PM
- Edited by craigbtx on Jan 04, 2013 10:10 PM
- Reply
Decker Dong ...

All-Star

118619 Points

18779 Posts
Re: Filter expressions and NULLS

Jan 06, 2013 02:33 AM|LINK
craigbtx

but when you add LastName it won't show null firstnames

FilterExpression="(FirstName Like '
```
<FilterParameters>
 <asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="" />
 <asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="" />
 </FilterParameters>
```
' AND LastName Like '%{1}%')"

<FilterParameters>
<asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
<asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="%" />
</FilterParameters>
Hi,

Please change DefaultValue from "%" to ""：
```
<FilterParameters>
 <asp:ControlParameter ControlID="FirstNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="" />
 <asp:ControlParameter ControlID="LastNameTextBox" Name="newparameter" PropertyName="Text" DefaultValue="" />
 </FilterParameters>
```
- Reply
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.
- Edited by craigbtx on Jan 06, 2013 01:58 PM
- Edited by craigbtx on Jan 06, 2013 01:58 PM
- Reply
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>
- Edited by craigbtx on Jan 06, 2013 03:24 PM
- Edited by craigbtx on Jan 06, 2013 03:31 PM
- Edited by craigbtx on Jan 06, 2013 07:42 PM
- Edited by decker dong - msft on Jan 07, 2013 12:26 AM
- Edited by decker dong - msft on Jan 07, 2013 12:26 AM
- Edited by decker dong - msft on Jan 10, 2013 02:13 AM
- Marked as answer by decker dong - msft on Jan 10, 2013 02:13 AM