Hi

Need some help - attempting to add multiple filters but not getting back expected results. Scenario is I want to filter by intEventID (say 35) and bring back all records that have CanSelectedForPositionInd (a numeric indicator) that is equal to 0 (zero) OR equal to NULL.

Code I am using is:

    Dim colCandidates As New NonSelectedCandidatesViewCollection
    Dim dtCandidates As New DataTable
    Dim sorter As New SortExpression

    '** sort by surname
    sorter.Add(New SortClause(NonSelectedCandidatesViewFields.CanLastNameText, SortOperator.Ascending))

    '** filter by intEventID
    Dim objSelectFilter As PredicateExpression = New PredicateExpression
    objSelectFilter.Add(New FieldCompareValuePredicate(NonSelectedCandidatesViewFields.EveIdentifier, _
        ComparisonOperator.Equal, intEventID))

    '** filter by Select For Position Indicator = 0 or IS NULL
    objSelectFilter.AddWithAnd(New FieldCompareValuePredicate(NonSelectedCandidatesViewFields.CanSelectedForPositionInd, _
        ComparisonOperator.Equal, 0)).AddWithOr(New FieldCompareNullPredicate(NonSelectedCandidatesViewFields.CanSelectedForPositionInd))
    '** old-code        objSelectFilter.AddWithOr(New FieldCompareNullPredicate(NonSelectedCandidatesViewFields.CanSelectedForPositionInd))

    dtCandidates = colCandidates.GetMultiAsDataTable(objSelectFilter, 0, sorter)

    '** return datatable

    Return dtCandidates

Grateful if anyone can help with this.

Thanks in advance

here are some examples in c# 2.0

IPredicateExpression subfilter = new PredicateExpression();
subfilter.Add(NonSelectedCandidatesViewFields.CanSelectedForPositionInd == 0);
subfilter.AddWithOr(NonSelectedCandidatesViewFields.CanSelectedForPositionInd == DBNull.Value);

IPredicateExpression filter = new PredicateExpression();
filter.Add(NonSelectedCandidatesViewFields.EveIdentifier == intEventID);
filter.Add(subFilter);

dtCandidates = colCandidates.GetMultiAsDataTable(filter, 0, sorter);

or the short hand version

IPredicateExpression filter = new PredicateExpression();
filter.Add(NonSelectedCandidatesViewFields.EveIdentifier == intEventID);
filter.Add(NonSelectedCandidatesViewFields.EveIdentifier == intEventID | NonSelectedCandidatesViewFields.CanSelectedForPositionInd == DBNull.Value);

dtCandidates = colCandidates.GetMultiAsDataTable(filter, 0, sorter);

Thanks Jason

I was hoping for an ASP.NET v1.1 solution (for VS2003) - hard enough trying to understand this code never mind C# v2.0.

Any other assistance would be gratefully appreciated.

I can get the filters working OK inside an SQL View, but I would rather the code did the work not the View.

Many thanks

Danny

this should work

Dim colCandidates As New NonSelectedCandidatesViewCollection
Dim dtCandidates As New DataTable
Dim sorter As New SortExpression

'** sort by surname
sorter.Add(New SortClause(NonSelectedCandidatesViewFields.CanLastNameText, SortOperator.Ascending))

'** filter by Select For Position Indicator = 0 or IS NULL
Dim subFilter As PredicateExpression = New PredicateExpression();
subFilter .Add(New FieldCompareValuePredicate(NonSelectedCandidatesViewFields.CanSelectedForPositionInd, ComparisonOperator.Equal, 0))
subFilter.AddWithOr(New FieldCompareNullPredicate(NonSelectedCandidatesViewFields.CanSelectedForPositionInd))

'** filter by intEventID
Dim objSelectFilter As PredicateExpression = New PredicateExpression
objSelectFilter.Add(New FieldCompareValuePredicate(NonSelectedCandidatesViewFields.EveIdentifier, ComparisonOperator.Equal, intEventID))
objSelectFilter.Add(subFilter);

dtCandidates = colCandidates.GetMultiAsDataTable(objSelectFilter, 0, sorter)

'** return datatable
Return dtCandidates

Jason - you're a genius - fancy a job in rainy Glasgow ??

Many thanks - 1st time we've used this forum - and it won't be the last