How to filter a dataview with two filters

Question

0.00/5 (No votes)

See more:

I have a dataview containing the results of a golf competition. I wish to split the results into three divisions based on "Handicap"
I have two Global Variables namely:

GlobalVariables.DivNo

and

GlobalVariables.DivNo2

which are defined from a csv file as 16 and 20
For the first Division my code, which works satisfactorily, is

Dim LowHandi As String = GlobalVariables.DivNo + 1
        MResultsDV.RowFilter = "Handicap <" & LowHandi

the third Division code, which also works satisfactorily, is

MResultsDV.RowFilter = "Handicap >" & GlobalVariables.DivNo2

However my code which needs to filter out the results for handicaps between 17 and 20 does not work.

What I have tried:

My first try was

Dim HighHandi As String = GlobalVariables.DivNo2 + 1
        MResultsDV.RowFilter = "Handicap >" & GlobalVariables.DivNo
        MResultsDV.RowFilter = "Handicap <" & HighHandi

However, the last filter over wrote the first.
So, I tried

Dim HighHandi As String = GlobalVariables.DivNo2 + 1
        MResultsDV.RowFilter = "Handicap >" & GlobalVariables.DivNo And "Handicap <" & HighHandi

but got the following error;
System.InvalidCastException: 'Conversion from string "Handicap >16" to type 'Long' is not valid.'
I then tried

Dim HighHandi As String = GlobalVariables.DivNo2 + 1
        MResultsDV.RowFilter = "Handicap >" & GlobalVariables.DivNo AndAlso "Handicap <" & HighHandi

but got this error; System.InvalidCastException: 'Conversion from string "Handicap >16" to type 'Boolean' is not valid.'

I also tried

MResultsDV.RowFilter = '"Handicap >" & GlobalVariables.DivNo' And '"Handicap <" & HighHandi'

which ran but did not work

Posted 26-Feb-19 12:01pm

Dave the Golfer

Updated 26-Feb-19 20:41pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2019-02-26T16:48:00

Solution 1

Here should be the syntax to return in between two numbers

VB

MResultsDV.RowFilter = "Handicap >=" & GlobalVariables.DivNo & " AND Handicap <=" & HighHandi

Posted 26-Feb-19 16:48pm

Bryian Tan

Comments

Maciej Los 27-Feb-19 2:41am

5ed!

Bryian Tan 3-Mar-19 23:33pm

Thanks :)

Dave the Golfer 27-Feb-19 13:40pm

Thanks the change works

Maciej Los · Accepted Answer · 2019-02-26T20:42:00

A DataView.RowFilter Property (System.Data) | Microsoft Docs[^] property works in the same manner as DataColumn.Expression Property (System.Data) | Microsoft Docs[^].
So, you can build complex expression, for example:

VB

DataView11.RowFilter = "ColumnName1 = 555 AND (ColumnName2 Like 'A*' OR ColumnName3 Like '*A')"

Operators:
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

String operators:
To concatenate a string, use the + character. The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive.

Please, follow the links i've provided.