[RESOLVED] .RowFilter search multiple words

[batori]

Hi,

Im using datagridview with

.RowFilter = "loc like '%" + txtSearchT.Text + "%'"

Everything works fine when i filter one word but when i write in the txtSearchT box something like "find this" (multiple words) i get errors.

Is there a solution to that that would be straight forward or a solution would be to split the string in arrays and then to search them with OR / AND operators in the row filter string?


Cheers!

[techgnome]

what kind of errors are you getting because
loc like '%find this%'
is perfectly valid. Perhaps there is something else that is wrong.

-tg

[batori]

well if a record value is : "one-half-four" and i input in search "one four" i get this error :

Value of "-1" is not valid for "Maximum". "Maximum" must be greater than or equal to 0. Parameter name: Maximum

[dunfiddlin]

Er ... where does 'Maximum' come into this? I thought you were searching on values of 'loc'?

[batori]

on form load :

ds = New DataSet()
dv = New DataView()

button to search:

 
                ds.Clear()
                Dim path As String = Application.StartupPath & "\data.xml"
                ds.ReadXml(path)
                dv.Table = ds.Tables(0)
                DataGridView1.DataSource = dv
                dv.RowFilter = "loc like '%" + txtSearchT.Text + "%'"

i get to find one word or one word that follows the other and matches a record...

[techgnome]

well the error has nothing to do with your row filter... but judging by the error message, it sound like you're trying to use the .RecordCount to set on the .Maximum property of a progress bar or something... and the count is coming back as -1...

Even still ... if you search for "one four" it will NOT find "one-half-four" ... it wouldn't even find "one-four" ... if you want it to, then you would need to replace the spaces in the string with the % wildcard as well... so you end up with '%one%four%' ... then it would match "one-half-four".

-tg

[dunfiddlin]

Ok. That still doesn't make any more sense of the error you're getting but the main problem is that you misuse the LIKE clause. Using your example ... One Half Four (although I've no idea what it means!) One%, %Half%, %Four will all get matches but %One Four% won't because it requires recognition that there is an implied wild card represented by the space and the parser just isn't that sophisticated. Neither can you have an explicit wild card in the middle of a LIKE string. When tg said that %find this% would work he was assuming that the complete value was something like "You find this in Kansas".

EDIT: I have so got to learn to type faster!!!

EDIT EDIT: Oh wait, tg got it wrong. Tee hee! Let's see if he notices!

[batori]

EDIT:

let me read your post too

EDIT:

Ok, let's say my record is:

One-Two-Four-Five

So when i search for "one" i will get the result, when i search "one five" i wont get any results.

So, as dunfiddling said i wont be able to do it with adding wildcards?

[techgnome]

did you read post #6 where I said

you would need to replace the spaces in the string with the % wildcard as well... so you end up with '%one%four%' ... then it would match "one-half-four".

you CAN put wildcards in the middle... I do it all the time... I wouldn't be able to search half the things I do w/o being able to.... that being said... I don't usually use the rowFilter so I don't know if it plays by different rules or not.

-tg

[dunfiddlin]

I don't know if it plays by different rules or not

You do now! A wildcard in the middle will get you an invalid string error every time!

[techgnome]

well that's just silly... what good it is then?

it seems the general recommendation is to use the .Select method instead...
https://www.google.com/search?q=how+...able+in+vb.net

-tg

[jmcilhinney]

well that's just silly... what good it is then?

it seems the general recommendation is to use the .Select method instead...
https://www.google.com/search?q=how+...able+in+vb.net

-tg

This:

SomeColumn LIKE %x%y%

is functionally equivalent to this:

SomeColumn LIKE %x% AND SomeColumn LIKE %y%

so it's generally not an issue. It only becomes a problem when there is some overlap between the two literals, e.g. this:

SomeColumn LIKE %wxy%xyz%

is not the same as this

SomeColumn LIKE %wxy% AND SomeColumn LIKE %xyz%

You can still get around that too, although it makes the code more complex again and building the code becomes complex too.

[batori]

Tried to use this : "SomeColumn LIKE %wxy%xyz%" but what i was getting is all the record back as i never searched them.

[techgnome]

This:

SomeColumn LIKE %x%y%

is functionally equivalent to this:

SomeColumn LIKE %x% AND SomeColumn LIKE %y%

I beg to differ... if the search is %one%four% ... and the string is "four one" ... that's not a match....
but if the search is %one% AN %four% then "four one" is a match even though it's "out of order" because it's looking for each search term separately and not together.

-tg

[batori]

This is what i'm doing now:
Record is : One-Two-Four-Five
The code is :

loc LIKE '%one% AND loc LIKE %four%'

Still, i get all the records listed, not the one searched. I'm not sure why ...

[dunfiddlin]

loc LIKE '%one%' AND loc LIKE '%four%'

[jmcilhinney]

I beg to differ... if the search is %one%four% ... and the string is "four one" ... that's not a match....
but if the search is %one% AN %four% then "four one" is a match even though it's "out of order" because it's looking for each search term separately and not together.

-tg

B*gger! Didn't think of that.

[batori]

loc LIKE '%one%' AND loc LIKE '%four%'

Thank you very much, it works.

Im using this, if anyone needs it :

dv.RowFilter = "loc like '%" & Replace(txtSearchT.Text, " ", "%' AND loc LIKE '%") & "%'"