complex sql in TableNameBindingSource.Filter

[Ultrawhack]

With ref to Paszt's code http://www.vbdotnetforums.com/showthread.php?t=11626

Can someone please help me on adding complex sql to the TableNameBindingSource.Filter ?

I would like user to be able to use the txtQuickSearch's KeyUp as a master search to look for anything in datagridview1 including :
text, memo, date & integer fields whose fields can be [Name], [Info], [Emp ID], [Join date]

The sql would be a complex combination of LIKE... OR.... =... #

I hope I've been able to exlain this clearly but for the life of me I cannot draw up the sql.

Thanks for any help

 

[cjard]

.Filter doesnt take an SQL, it takes something that looks remarkably like a WHERE clause of an SQL.. is that going to be sufficient for your needs?

 

[Ultrawhack]

SURE. Anything that tells it what to do, rather than it telling me what to do.

 

[cjard]

can you define, in terms of the columns you have available, and using (i think) a T-SQL like syntax, the relevant where clause? If so, that is the syntax you would use..

You havent said a whole lot about how your app looks, works etc...

Do you mean that you have a huge datagrid and a single text box, and every character that is typed you want to filter a set of rows such that:

Filter = String.Format("(name LIKE '{0}%') OR (age = {0}) OR ...", text1.Text)

 

[Ultrawhack]

Do you mean that you have a huge datagrid and a single text box, and every character that is typed you want to filter a set of rows such that:

Filter = String.Format("(name LIKE '{0}%') OR (age = {0}) OR ...", text1.Text)

Bingo. That's it. It's a quicksearch where user can enter anything relevant to the fields I mentioned in txtQuickSearch and have the datagridview's rows show just those relevant records. They can then click on the row they want for further processing.

In Access VBA I ran a query & subform bound to txtQuickFind, refreshed by txtQuickFind's onExit event. txtQuickFind's default value would be * and a typical search string would be enclosed, eg: *John Doe*

Here's part of the VBA query's sql:
WHERE (((MyDatabase.[Info]) Like [Forms]![MyForm]![txtQuickFind])) OR
(((MyDatabase.[Name]) Like [Forms]![MyForm]![txtQuickFind])) OR
(((MyDatabase.[Company]) Like [Forms]![MyForm]![txtQuickFind])) OR
(((MyDatabase.[Emp ID]) Like [Forms]![MyForm]![txtQuickFind]))
ORDER BY MyDatabase.[Name]

I hope this makes it clearer. I am trying to mimic this behavior in VStudio2005

 

[cjard]

you have no influence of order in the filter... it's done elsewhere

for very large datasets, i cannot imagine it would be quick, but you can build a filter string like this:

Dim filterStr as String = "{0} = '' "
For Each col as DataColumn in MyDataTable.Columns
  filterStr &= " OR " & col.ColumnName & " LIKE '{0}%'"
Next col

when this finishes you should have a string that looks like this:

{0} = '' OR name LIKE '{0}%' OR company LIKE '{0}' and so on....

so now what we do, when the user types soemthing in filter box:

.Filter = String.Format(filterStr, whatUserTyped)



all the {0} become replaced by what they typed. note, if they type NOTHING then the first condition is true, and all rows are shown. Else, the only rows that are shown are ones where at least one OR condition succeeds

 

[Ultrawhack]

Thanks very much... I'm testing this and I understand what you're saying about speed over a large dataset.

I have followed Paszt's method of using the filtered datagridview

2005 makes it really simple.
Start with an empty form. You've already got the datasource set up right. Have your empty form open in design view and open the Data Sources panel. Open the datset by clicking the plus sign beside it, if it's not already open. The list of available dataTables will be shown. Click the name of the dataTable you want to work with and you be able to access a dropdown. Open that dropdown and make sure DataGridView is selected (more on this later). Now drag the dataTable onto the form designer. The IDE will create a DataSet, BindingSource, TableAdapter, and BindingNavigator whose name will depend on the DataTable you selected. The BindingNavigator is very similar to the navigation buttons you get in Access and you can delete it if you don't have a need for it. Deleting it will have no affect on the other components. The IDE will also create the form's Load event handler with a statement that fills the TableAdapter. At this point you can run the app and the dataGrid will be filled with all the data.
Next add a texbox which will allow the user to type into to filter the datagridview.
Switch to code view and in the dropdown at the upper-left select the textbox, in the dropdown on the upper-right select Keyup. A method signature will be created for the textbox's keyup event. In the method type something like: Me.TableNameBindingSource.Filter = "FieldNametoBeFilter LIKE '" & TextBox1.Text & "%'" where TableNameBindingSource is the BindingSource created and FieldNametoBeFilter is the field you want to filter against.
That's it. It's working and you only had to type one line of code.

With the above method I have MyDataTableBindingSource, MyDataTableDataSet & MyDataTableTableAdaptor none of which have the .columns available.

I get the error "Unable to cast object of type 'MyDatabaseRow' to type 'System.Data.DataColumn' from line
ForEach col As DataColumn In ...

Where can I find the .columns property ?

Should we just search in Datagridview instead ? Any wisdom ?

 

[Paszt]

If you were to use the method cjard shows above, the columns property is found in the DataTable stored in the DataSet. For eample MyDataTableDataSet.DataTableName.Columns. DataTableName will be the name of your dataTable in MyDataTableDataSet.
One problem with the method is the line Dim filterStr as String = "{0} = '' ".
If the user typed d in the textbox you will receive an error because the start of the filter string will look like: "[d] = ''" and unless you have a column named d, that column won't be found.
Another problem is if you have any columns contaning non-string type data, for example Integer or Boolean types because you can't use the LIKE comparer with those data types.

It may be best to create the filter string manually using only the columns you want to include. For integer and date fields you would have to check to see if the data typed in the textbox are of those types before including the field in the filter string and use the appropriate comparer. It would probably be best to include a seperate method to search for dates and integer values.

 

[Ultrawhack]

Thanks for that input Paszt.

So assuming I just wanted to include just textfield [Name] and memofield [Info] what would be the best way to manually filter Datagridview using only these columns ?

Also, would it be preferable to run this filter event from the OnLeave event rather than Keyup event ?

 

[cjard]

If you were to use the method cjard shows above, the columns property is found in the DataTable stored in the DataSet. For eample MyDataTableDataSet.DataTableName.Columns. DataTableName will be the name of your dataTable in MyDataTableDataSet.
One problem with the method is the line Dim filterStr as String = "{0} = '' ".
If the user typed d in the textbox you will receive an error because the start of the filter string will look like: "[d] = ''" and unless you have a column named d, that column won't be found.

Apologies, i have indeed made a slight syntax error. It should read:

Dim filterStr as String = "'{0}' = '' "

Note the extra ' ' around the {0}. When the user types nothing into the box, the resulting Filter string will start with:

'' = ''

which logically evaluates to true, so all rows are shown. THis may or may not be a requirement, but without this section present, an empty textbox will cause all rows to be filtered.

Another problem is if you have any columns contaning non-string type data, for example Integer or Boolean types because you can't use the LIKE comparer with those data types.

again, a good point. The filter string supports a very limited command set which it seems to share with the Expression property of a datacolumn. Hence, the available commands you have to use in filter are
CONVERT, ISNULL, LEN, SUBSTRING, IIF and TRIM in addition to the regualar maths operators.

FOr more info, see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

Note it might also be the case that * is used as a wildcard instead of/aswell as %


Hence to solve this problem you can detect the type of the column when you are building the string and either:
Change the criteria so you arent using LIKE
Or change the composition of the format string using CONVERT or similar

Of the two, i prefer the former. You may additionally choose to skip searching of date columns if they are formatted differently to the default. If the user sees 10 Jul 2006, they may not realise that to search for a date they must use #07/10/2006#
You can provide a date time picker in this case



Here is a guide to performing criteria change so we arent using like:

[LEFT]Dim filterStr as String = "{0} = '' "
For Each col as DataColumn in MyDataTable.Columns
  Select Case col.GetType().ToString()
    Case "System.String"
      filterStr &= " OR " & col.ColumnName & " LIKE '{0}%'"
    Case "System.Int32"
      filterStr &= " OR " & col.COlumnName & " = {0}"
  End Select
Next col[/LEFT]

I'm not in a great position to test this, but maybe you are.. You can place a textbox on your form, and upon textchanged, Try set the filter to the text. Catch any exceptions and set the filter to nothing if an exception arises. use a reasonably small number of rows for this as using thousands may slow your testing.


As paszzssszt says, youre probably better off just writing your filter string out if there is a low number of columns..

filterStr = "'{0}' = '' OR strCol LIKE '{0}%' OR numCol = {0} OR ..."

 

[Paszt]

Also, would it be preferable to run this filter event from the OnLeave event rather than Keyup event ?

That would depend on the amount of data and the speed of the computers running the app. If there are only serveral thousand rows and the computers are relatively new, the keyUp event should work. I have used this solution for a recipe catalog app (cookbook software). If there are hundreds of thousands of rows then you may not want to use the keyUp event.
I personally don't like the idea of using the OnLeave event, perhaps a button would be more user friendly. My ideal solution would be to use a button and make it so the button was 'clicked' when the user pressed the Enter key.

As paszzssszt says

Are you making fun of my name?

 

[cjard]

That would depend on the amount of data and the speed of the computers running the app. If there are only serveral thousand rows and the computers are relatively new, the keyUp event should work. I have used this solution for a recipe catalog app (cookbook software). If there are hundreds of thousands of rows then you may not want to use the keyUp event.

I'd definitely concur with this.. As a user of winamp, i've found (for example), that using the quick-search of the playlist when you have tens of thousands of items in it is very slow.. you type the letter "a" and it filters 10000 to maybe 9900.. (and it takes a while)

One solution I came up with to a similar problem in my code was to make a minimum characters requirement and relate it to the number of items in the list. One such formula might be the round(log10())+1 of the number of items in the list.
So if your list contains 9 items, log10 of 9 is 0+1 = min 1 char to be typed
if your list contains 999 items then rounded log10() of 999 is 2+1 = 3
when your list gets 1000 to 9999 it requires 4 chars be typed before a filter is made.. etc

I found this approach kept things quite quick..

Ive jsut realized another way of doing the calc too.. convert the number to a string and get its length:
999.ToString().Length = 3 so minimum 3 characters must be typed before searching will begin





Oh, there was one last thing io forgot to mention.. if the user types any weird characters in there, like apostrophe, it will/might cause an exception! for more/similar info, read about sql injection hacking

Are you making fun of my name?

lol! I couldnt remember how to spell it, so i put a few extra letters in to make sure! I knew someone here had a sense of humour! :thumb:

 

[Ultrawhack]

I have been able to get this working great. I went with your suggestion and test for length of search string > 2 before firing the filter code and got the filter to reset in case user backspaces and clears text in txtQuickFind.

Thanks very much for all the help. Would not have been able to do this without your help on these forums.

 

[cjard]

Thanks very much for all the help. Would not have been able to do this without your help on these forums.

Cheers for coming back and letting us know! It's perhaps the best payment we could hope to have for our advice (although ladies of dubious intent might be accepted for a small exchange rate fee )

 

[Ultrawhack]

And some gentlemen too