RowFilter with *

[Oka Morikawa]

I'm having little troubles with RowFilter. I need to use RowFilter from
TextBox which can contain size of the product or null value to show all
values.

I have three TextBox like: W, H, L
If W has value = 105 but H and L are empty DataView should only show
products with W=105 and H=* and L=* but this seems to be quite difficult
with Expressions in ADO.NET. Sure I could do this with multiple IF clauses
but there should be some easier way to do this.

Any ideas?


Thanks,
Oka Morikawa

 

[William Ryan eMVP]

Hi Oka:

It depends on the AND logic. Like Ryan* or = 'Ryan' with both evaluate to
true for my last name so you may just be able to use like textBoxH.Text* and
Like textBoxL.Text*. However, if you are brancing into OR's depending on if
they are filled or not, it's going to be a lot easier to branck with an else
structure. Another thing, depending on the circumstances, you may not need
to specify the other conditions b/c by default they won't be filtered. So
if they entered 105 in W and you needed all matches for 105 as well as
everything for H and everything for L you could just use H='105' If W = 105
and H = 12 and anything from L you could juse W='105' and H='12'

I'm not sure exactly which this is and the nuance of and and or is a big
deal here, but either way it should be fairly straightforward.

Let me know if I didn't answer it and if not, give me a more specific
exampel and I'm sure I can get you through it.

HTH,

Bill

www.devbuzz.com
www.knowdotnet.com

 

[Oka Morikawa]

Thanks for fast reply,
I can't use eg. "SizeW LIKE tbSizeW.Text*" since SizeW is Int16 and I get
EvaluateException. So to able to use Like * I need to change the
SizeW.Column DataType to String which then gives me another problems on my
DAL.

The idea here is that I have product table with ProductID (PK), SizeW, SizeH
and SizeL columns. In presentation layer I need to filter the products using
TextBox(tbSizeW, tbSizeH and tbSizeL) and get that result on DropDownBox.

Users now can filter the content of DropDownBox by using those
TextBox(tbSizeW, tbSizeH and tbSizeL) with different combinations. Eg. like
(W=105, H=*, L=*) or (W=*, H=100, L=2000) but how do I accomplish this
easily with RowFilter on DataView which I bind to DropDownBox?


Thanks,
Oka Morikawa

 

[Val Mazur]

Hi,

What you could try to do here (I did not try, but you could) is to use
CONVERT function inside of your filter to convert values into strings and
use LIKE clause

W=105 and CONVERT(H,'System.String') LIKE '*' and CONVERT(L,'System.String')
LIKE '*'

 

[William Ryan eMVP]

Oka:

If this is numeric why do you need like at all? Why not check if there's an
* and if so, just leave it out of the expression, and otherwise just use the
explict value?

 

[Oka Morikawa]

Thanks! Now I got this working as I wanted:

Hi,

What you could try to do here (I did not try, but you could) is to use
CONVERT function inside of your filter to convert values into strings and
use LIKE clause

W=105 and CONVERT(H,'System.String') LIKE '*' and
CONVERT(L,'System.String') LIKE '*'

 

[Oka Morikawa]

string filterW = "";
string filterH = "";
string filterL = "";

int indexW = this.lbFilterW.SelectedIndex;
int indexH = this.lbFilterH.SelectedIndex;
int indexL = this.lbFilterL.SelectedIndex;

// * | FILTER W
if (indexW == 0)
filterW = "Convert(SizeW, 'System.String') LIKE '*'";
// < 105
else if (indexW == 1)
filterW = "SizeW < 105";
// 105 +
else if (indexW == 2)
filterW = "SizeW >= 105 AND SizeW < 120";
// 120 +
else if (indexW == 3)
filterW = "SizeW >= 120 AND SizeW < 140";
// 140 +
else if (indexW == 4)
filterW = "SizeW > 140";

// * | FILTER H
if (indexH == 0)
filterH = "Convert(SizeH, 'System.String') LIKE '*'";
// < 100
else if (indexH == 1)
filterH = "SizeH < 100";
// 100 +
else if (indexH == 2)
filterH = "SizeH >= 100 AND SizeH < 200";
// 200 +
else if (indexH == 3)
filterH = "SizeH >= 200 AND SizeH < 300";
// 300 +
else if (indexH == 4)
filterH = "SizeH >= 300 AND SizeH < 400";
// 400 +
else if (indexH == 5)
filterH = "SizeH >= 400";

// * | FILTER L
if (indexL == 0)
filterL = "Convert(SizeL, 'System.String') LIKE '*'";
// < 2000
else if (indexL == 1)
filterL = "SizeL < 2000";
// 2000 +
else if (indexL == 2)
filterL = "SizeL >= 2000 AND SizeL < 3000";
// 3000 +
else if (indexL == 3)
filterL = "SizeL >= 3000 AND SizeL < 4000";
// 4000 +
else if (indexL == 4)
filterL = "SizeL >= 4000 AND SizeL < 5000";
// 5000 +
else if (indexL == 5)
filterL = "SizeL >= 5000 AND SizeL < 6000";
// 6000 +
else if (indexL == 6)
filterL = "SizeL > 6000";

// DataView sort & filter
dv.Sort = "SizeW, SizeH, SizeL, Ply";
dv.RowFilter =
filterW + " AND " +
filterH + " AND " +
filterL;

this.cbTest.DataSource = dv;
this.cbTest.DisplayMember = "Code";
this.cbTest.ValueMember = "ProductID";