Search code examples
vb.netdatagridviewfilterbindingsource

VB.NET BindingSource filter for Datagridview

In MSSQL I can filter a query on a phone number like this:

where replace(phone,'-','') Like '%480555%'

I am trying to figure out how to do this on a Datasource. A normal query looks like this:

Dim stringFilter As String = String.Empty
String.Format("phone Like '%480555%'")
ViewCustomersBindingSource.Filter = stringFilter

However, this will not find any results because the datasource has the values with hyphens in it. REPLACE is not a valid argument for filtering.

My initial thought was to update the MSSQL View to strip the hyphens. However, for display, I would want to display the hyphens. I can not assume they will all look the same as some phone numbers might be a different country than the US.

Is there another way to filter on a telephone number and ignore the hyphens?

Solution

  • I dont think you can directly set a filter that ignores the hyphens (as in your first query) on your binding source. There is no String operations possible in the filter expression column. From this http://msdn.microsoft.com/fr-fr/library/system.windows.forms.bindingsource.filter.aspx the only string operation allowed is +.

    But you can keep and display the original column which contains the hyphens, and add another column (without the hyphens) that you do not display, but that you use just for filtering.

    With MSSQL, you can for example add a computed column that uses the REPLACE function.

    Here is somes links that could help you to create a computed column:

    Hope this helps.