Filtering with nulls 2

[WelshyWizard]

Hi all,

I'm trying to run a filter upon some information shown to the user on a windows form using the following code:

DataView1.RowFilter = "Title Like '*" & TextBox3.Text & "*' OR Solution Like '*" & TextBox4.Text & "*'"

Each textbox has a default value of *. This is so that if one of the textboxes is not filled in, the filter will still show all records that match the textbox that is fillled in (leaving it blank would not do this). However, this plan backfires because all records with a <NULL> value are filtered out if there is a * in the textbox.

How can I adjust my code to include NULLS if a textbox is not filled in?

Cheers



Today is the tomorrow you worried about yesterday - and all is well.....

 

[Mastakilla]

Hi,

this is not the most elegant solution, but it should work like a charm:

dim str as string

if textbox3.text.trim.length <> 0 AndAlso textbox4.text.trim.legnth <> 0 then
  str = "Title Like '*" & TextBox3.Text & "*' OR Solution Like '*" & TextBox4.Text & "*'"
elseif textbox3.text.length <> 0 then
  str = "Title Like '*" & TextBox3.Text & "*'"
elseif textbox4.text.length <> 0 then
  str = "Solution Like '*" & TextBox4.Text & "*'"
end if

DataView1.RowFilter = str

 

[MacLeod72]

Good morning-

This should create the filter you need:

DataView1.rowFilter = String.Format("{0}{1}{2}", _
    IIf("".Equals(TextBox3.Text), "", String.Format("Title Like '*{0}*'", TextBox3.Text)), _
    IIf("".Equals(TextBox3.Text) Or "".Equals(TextBox4.Text), "", " Or "), _
    IIf("".Equals(TextBox4.Text), "", String.Format("Solution Like '*{0}*'", TextBox4.Text)))

Hope this helps!

--Rob

 

[WelshyWizard]

Thanks guys,

Any ideas why my code does not work the way it is?

Today is the tomorrow you worried about yesterday - and all is well.....

 

[MacLeod72]

The problem is that when you filter using "like" or "IN" or equalities, NULLs aren't included. NULL is a special case in databases; in fact, NULL = NULL does not evaluate to true. When working with NULLs, you have to either specifically include them using the "IS NULL" phrase, or exclude the condition from the filter completely. Both Mastakilla and I chose the latter.

Hope this helps!

--Rob

 

[WelshyWizard]

I see,

So how would I go about including them using ISNULL?

Today is the tomorrow you worried about yesterday - and all is well.....

 

[Mastakilla]

dataview.rowfilter = "YourField Is Null"

You can check the help file (visual studio documentation) about datacolumn expression syntax:

if you're using vs2005, open the documentation and type this url:

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/P_System_Data_DataColumn_Expression.htm