How should I do this?

I have a "generic" search dialog that I am able to fill a datatable with two columns for searching. These two
columns may be different types from Integer to String to DateTime

There is one textbox for entering a search term. How would I format the DataTable's RowFilter according to the
text entered based on the types of the two columns in the datatable?

Currently, we have it like the code below and it is causing problems when the first column is an Integer column.
The objColl is a collection with an ISearchableWrapper interface that returns the NAME of the particular column in
the SearchItem_DataPropertyName and SearchDesc_DataPropertyName properties. The items in the collection are
what is loaded into the DataTable.

Any ideas on how I should approach this? Have you done something like this before?
Private Sub txtFilter_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtFilter.TextChanged
    If Me.tbl IsNot Nothing Then 
      Me.tbl.DefaultView.RowFilter = Me.objColl.SearchItem_DataPropertyName & " like '" &  Me.txtFilter.Text & "%' OR " & 
                                     Me.objColl.SearchDesc_DataPropertyName & " like '" & Me.txtFilter.Text & "%'"
End Sub

Open in new window

LVL 26
Eddie ShipmanAll-around developerAsked:
Who is Participating?
MoreHeroicConnect With a Mentor Commented:
Well, doing a string comparison like that you might try casting the unknown type into a string prior to performing the like so that your rowexpression would look something like this:

Me.tbl.DefaultView.RowFilter = "Convert(" & Me.objColl.SearchItem_DataPropertyName & ", 'System.String') like '" &  Me.txtFilter.Text & "%' OR " &
                                     "Convert(" & Me.objColl.SearchDesc_DataPropertyName & ", 'System.String') like '" & Me.txtFilter.Text & "%'"
Eddie ShipmanAll-around developerAuthor Commented:
Nah, we've tried casting and it still gives the error because the value in the DataTable is
still an Integer and you can't use the like operator on integers.
That's what I'm saying.  Cast it as part of the expression so that you are in fact using a string.  That's what the Convert(col_name, 'System.String') in the expression would do.  It would still be an integer in the data, you would just cast it for the like comparison.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Eddie ShipmanAll-around developerAuthor Commented:
Oh, I see, but how about datetime values? I can't use LIKE with a partial date
like '06/*' can I? And I certainly can't convert that to a datetime value.

I AM able to determine what the datatype is for each column and do something different for each column, however, knowing what the user is actually searching for is the problem.
He may have an integer column and a datetime column and, in the txtFilter_TextChanged event, the user may enter '06' which would work ok for the Integer but when, he typed the '/', it would break for both datatypes.

Also, in the Convert, wouldn't you convert to the SQL type .vs System.String?

You should be able to if the column you're searching is always cast to a string.  As long as the partial search string is the same syntax as your dates you should be fine.
You may find this link helpful when dealing with expressions:
Eddie ShipmanAll-around developerAuthor Commented:
Works like a charm. I never thought about using Convert.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.