RowFilter with *

  • Thread starter Thread starter Oka Morikawa
  • Start date Start date
O

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
 
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
 
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
 
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:

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?
 
Thanks! Now I got this working as I wanted:

Val Mazur said:
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 '*'
Click to expand...
 
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";
 
Back
Top