Visual Basic.NET Filtering DataGridView control using BindingSource.Filter and the LIKE operator


  • I want to allow the User to filter by selecting the DataGridView column name, the filter operator, and the filter value.

    (User will do this using the following controls: cboColumnFilter.Text, cboOperator.Text, and txtFilterValue.Text).


    The following error occurs when I explicitly hardcode the LIKE operator. The user is trying to find those records whose the Description columns contain the word "Four" in their values. So, the user selects the Description item from cboColumnFilter, selects the LIKE item from cboFilterOperator, and enters "Four" into txtFilterValue.


    This is the program code that processes the filter operation.

    myBindingSource.Filter = String.Format("{0} LIKE '{1}'", cboColumnFilter1.Text, txtFilterValue1.Text)


    But, I receive the error entitled, "Syntax Error Was Unhandled," with the message, "Missing operand after the 'Four' operator."


    How can I fix this? I don't receive any such error when I substitute the LIKE with the following operators: =, <>, <, or >.

    When I use these operators in the other columns there is no problem.


    Better yet, how can I write the filter without hard-coding the operator? For example:

    myBindingSource.Filter = String.Format("{0} cboFilterOperator.Text '{1}'", cboColumnFilter1.Text, txtFilterValue1.Text). 



    Note that cboFilterOperator lists the following items: =, <>, LIKE, <, and >.



    I even tried this code:


    myBindingSource.Filter = String.Format("{0}, {1}, '{2}'", cboColumnFilter1.Text, cboFilterOperator.Text, txtFilterValue1.Text)


    But I got a error message, "Syntax error in the expression."


    What's a guy to do? Can you help me please



    Tuesday, January 08, 2008 6:16 PM

All replies


    I am using a datagridview control in my project. The user can specify up to three separate filter criteria. The program will then display these results into the datagridview in one run. My approach is to filter using the databindingsource.filter() method. The program performs the filters in an iterative fashion.

    Once the first filter (based upon criteria_1) is completed, the program puts the filtered results in a separate temporary table:


    table2 = myTableAdapter.GetData

    myBindingSource.DataSource = table2


    Next, the program automatically processes the second filter based upon criteria_2:

    'Second criteria: ComboBox.Text lists column names; TextBox.Text contains search value

    myBindingSource.Filter = String.Format(CStr(cboColumnFilter2.Text) & " = " & CStr(txtFilterValue2.Text))


    When I run the program, I receive the following exception:


    EvaluateException was unhandled.

    Cannot perform '=' operation on System.String and System.Int32.

    This error occurs when the search column is the LocationCode column.


    Note: When I plug in the actual values, I have no problem (see below):


    myBindingSource.Filter = String.Format("Description Like '" & "Four") & "*' And Location = 7173"


    It is just when I attempt to use the control names and properties within the filter expression that the problem occurs with the syntax:


    Questions: It this the best way to process a filter based upon a series of criteria? Am I correct in the assignment of results of the first filter into table2 above? How can I fix the EvaluateException error?

    Monday, January 07, 2008 9:23 PM