[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1266
  • Last Modified:

Creating dynamic rowfilter strings for multiple datatypes

I have a dataview (dv) that is the datasource of a datagrid.
I have a comboxbox with the fieldnames of the dataview (cboxColumnPicker)
I have a text box with the value i want to filter by (txtSearch)

I have tried:

dv.RowFilter = "[" & cboxColumnPicker.SelectedItem & "] LIKE '" & txtSearch.Text & "'"

However, this doesn't work with System.Int32, System.DateTime, etc datatypes. How can I test for all these various types and create an appropriate filter?

I was hoping to utilize the flexibility the LIKE statement, since I want people to be able to enter partial data. For example to type '%2005%' in the text box for a datetime column, and find all datetimes that are in the year 2005, and likewise "01/%" to find all the datetimes with a month of January.

Any help would be greatly appreciated.

Thanks!
0
majnun
Asked:
majnun
  • 7
  • 6
1 Solution
 
Jeff CertainCommented:
1. Using LIKE will greatly slow your queries, since your indexes are no longer valid.
2. Cast all your columns to varchar/text types (depending on whether you're using SQL or OLEDB data classes)
0
 
Jeff CertainCommented:
dv.RowFilter = "CAST([" & cboxColumnPicker.SelectedItem & "] AS varchar(50)) LIKE '" & txtSearch.Text & "'"
0
 
majnunAuthor Commented:
Tried that but got error:

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll
Additional information: The expression contains undefined function call CAST().

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
majnunAuthor Commented:
Using oledb by the way
0
 
Jeff CertainCommented:
Try "Cstr([" & cboxColumnPicker.SelectedItem & "]) LIKE '" & txtSearch.Text & "'"
0
 
majnunAuthor Commented:
(Already did) Same type of error:

An unhandled exception of type 'System.Data.EvaluateException' occurred in system.data.dll

Additional information: The expression contains undefined function call CType().
0
 
Jeff CertainCommented:
Okay... the reference claims this will work:

"Convert(" & cboxColumnPicker.SelectedItem & ",System.String) LIKE '" & txtSearch.Text & "'"
0
 
Jeff CertainCommented:
If you want the refernce, it's
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.htm
0
 
majnunAuthor Commented:
Ok it mostly works (i need to put brackets though since some of my columns have spaces in them):

dv.RowFilter = "Convert([" & cboxColumnPicker.SelectedItem & "], System.String) LIKE '" & txtSearch.Text & "'"

However, DateTimes don't seem to work... i copy a datetime stamp from a cell in the non-filtered data so that I know I have the exact datetime even, but it doesn't find it... is what the datagrid showing me different from what is being stored? Do i need to convert differently in the case of date/time fields?


0
 
Jeff CertainCommented:
Well.... date/time fields really store a floating-point representation of the date, where the integer portion is the number of days since some starting date, and the fractional part is the number of milliseconds since midnight...

So, probably when you do the conversion, you get a number...
0
 
majnunAuthor Commented:
ok it works if i put in '%" before and after the date, or the time...
i can do '%1/15/2004%' or '12:27 PM%' but not '1/15/2004 12:27 PM' and not '1/15/2004 12:27 PM'

I guess its very unlikley that anyone will ever know the exact timestamp, but it would be nice to find a solution for thoroughness sake.

Any thoughts?
0
 
Jeff CertainCommented:
Ah... if it's a date, it might have # on either side... i.e. #1/15/2004 12:27 PM#
0
 
majnunAuthor Commented:
Nope, doesn't help...

odd thing is partial datetimes work fine, its just when i type in a complete timestamp that there is a problem.

Odd.

I'll leave this question open in hopes we can solve this particular problem... again its not critical because its unlikely anyone will search for an exact datetime down to the minute, but it is troubling to my need for completeness.

Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now