CODEWITHSUNDEEP
c#asp.net
dQlle
dQlle

Reputation: 11

filterexpression returns "wrong" result

I have a filterexpression that enables you to filter the results from a gridview.

I have two dropdownlists (5 and 6 in the filterexpression, the rest is textboxes and works ine) where you can select a specific predefined range to filter your results by.

My filterexpression is as follows: 

FilterExpression="CompanyName LIKE '{0}%' AND CityName like '{1}%' AND Convert(Zipcode, 'System.String') like '{2}%' AND Convert(ActionID, 'System.String') like '{4}%' AND Convert(Employees, 'System.String') like '{5}%' or (Convert(Zipcode, 'System.String') >= '{2}%' AND Convert(Zipcode, 'System.String')  <= '{3}%') or (Convert(Employees, 'System.String') >= '{5}%' AND Convert(Employees, 'System.String')  <= '{6}%')">

My problem is regarding the number of employees. In the available values for the number of employees I have 99, 199, 499, 999 and 1000.

If I choose the first value to be 99 and the second to be 199, I will also get the results for companies that have 999 employees, because it includes 99. However this logically shouldn't happen, as 999 is out of the selected range.

Any idea how to fix this?

edit: Forgot to mention, I have ConvertEmptyStringToNull = false in my controlparamters. So I don't have to fill out each and every textbox to get a result.

Regarding the conversion, it is something I have to do, otherwise it wont show any data in the gridview, unless I specify a filter.

Upvotes: 1

Views: 126

Answers (1)

J&#252;rgen Steinblock
J&#252;rgen Steinblock

Reputation: 31733

If you add some newlines you see what is going on

CompanyName LIKE '{0}%'
AND CityName like '{1}%' 
AND Convert(Zipcode, 'System.String') like '{2}%' 
AND Convert(ActionID, 'System.String') like '{4}%'
AND Convert(Employees, 'System.String') like '{5}%' 

or 
(
    Convert(Zipcode, 'System.String') >= '{2}%' 
    AND Convert(Zipcode, 'System.String')  <= '{3}%'
) 
or 
(
    Convert(Employees, 'System.String') >= '{5}%' 
    AND Convert(Employees, 'System.String')  <= '{6}%'
)

Basically, if your first block (with the four ANDs) matches, a record is shown, even if none of the OR blocks match.

Look close at AND Convert(Employees, 'System.String') like '{5}%' which means 

 AND Employees LIKE '99%'

That is also true for 999. I suggest you remove the Emplyee filter from the first block.

UPDATE

There is another issue, in your OR expression you are using the >= and <= operator with a string. I can't predict how that evaluates but I suggest you convert Employees to int and remove the % at the end.

or 
(
    Convert(Employees, 'System.Int32') >= {5}
    AND Convert(Employees, 'System.Int32')  <= {6}
)

Upvotes: 3

Related Questions