How can I write RowFilter in this situation

[Tony Johansson]

This method Search below works almost perfect but there is a problem.
This GetCaseList() is returning a DataSet with one DataTable.
In this DataTable we have several column but two are interesting here and
these are
status and enhet
If I look at each row in the DataTable I can see that in some rows status is
blank and in other rows enhet is blank.
Assume that I from the calling program choose "show all" for either status
or enhet.
In this example we can say I choose "show all" for enhet and we can also
assume that we have a couple of rows where we have blank value in enhet

This construction will not include those rows where we had blank values for
enhet
dataView.RowFilter = string.Format("Status LIKE '%{0}%' and DName LIKE
'%{1}%'", status, enhet);

If both status and enhet have values then this construction give me the
correct result
dataView.RowFilter = string.Format("Status LIKE '%{0}%' and DName LIKE
'%{1}%'", status, enhet);

So my question is if there is any fancy way to fix this without write
several statement with RowFilter anf if clauses


public static DataView Search(string status, string enhet)
{
BackEnd backEnd = new BackEnd();
DataView dataView = new DataView(backEnd.GetCaseList().Tables[0]);

if (status == "Show all")
status = "*";

if (enhet == "Show all")
enhet = "*";

if (status == "*" && enhet == "*")
return dataView.

dataView.RowFilter = string.Format("Status LIKE '%{0}%' and DName LIKE
'%{1}%'", status, enhet);

return dataView;
}

//Tony

 

[Anders Eriksson]

On 2013-12-04 03:21, Tony Johansson wrote:

This construction will not include those rows where we had blank values
for enhet
dataView.RowFilter = string.Format("Status LIKE '%{0}%' and DName LIKE
'%{1}%'", status, enhet);

So my question is if there is any fancy way to fix this without write
several statement with RowFilter anf if clauses

You could add an OR statement, e.g.

dataView.RowFilter = string.Format("(Status LIKE '%{0}%' OR
RTRIM(Status) is null) and (DName LIKE '%{1}%' OR RTRIM(DName) is
null)", status, enhet);


// Anders
PS! You really should look up "parameterized query". It's not a good
idea to use string.format.

 

[Jeff Johnson]

You could add an OR statement, e.g.

dataView.RowFilter = string.Format("(Status LIKE '%{0}%' OR RTRIM(Status)
is null) and (DName LIKE '%{1}%' OR RTRIM(DName) is null)", status,
enhet);


// Anders
PS! You really should look up "parameterized query". It's not a good idea
to use string.format.

A RowFilter is different from a query. No one should be building query
strings by hand (unless you're doing more than modifying the WHERE clause,
that is), but setting a filter on a data set is another matter entirely.

Of course, the OP should be asking himself if he REALLY needs to filter the
data set AFTER retrieving it or if he should have retrieve a filtered result
set (via a query) from the database in the first place.