Link to home
Start Free TrialLog in
Avatar of Eddie Shipman
Eddie ShipmanFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of MoreHeroic
MoreHeroic

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Eddie Shipman

ASKER

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.
Avatar of MoreHeroic
MoreHeroic

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.
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:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
Works like a charm. I never thought about using Convert.