We help IT Professionals succeed at work.

Filter a table using DefaultView.RowFilter

maidinhtai
maidinhtai asked
on
Medium Priority
2,113 Views
Last Modified: 2012-06-27
I have a table that has a datetime field named NgaySinh. I need to filter this table (using TableName.DefaultView.RowFilter) to show only rows that satisfy these criteria:
NgaySinh < mindate and NgaySinh > maxdate (mindate and maxdate both are datetime and variable)
How could I do that. Thank you.
Comment
Watch Question

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataViewClassRowFilterTopic.asp

and then you might type NgaySihn between mindate and maxdate

where you fill the minDate and Maxdate from a string.format e.g.

So e.g. RowFilter = string.format ("NgaySihn BETWEEN '{0}' AND '{1}'", Format(mindate , "dd-MMM-yyyy HH:mm:ss") ,  Format(maxdate, "dd-MMM-yyyy HH:mm:ss") )

Author

Commented:
Hi PockyMaster. When I add this code:
string.format ("NgaySinh BETWEEN '{0}' AND '{1}'", Format(me.dtpFromNgaySinh.Value , "dd-MMM-yyyy HH:mm:ss") ,  Format(me.dtpToNgaySinh.Value, "dd-MMM-yyyy HH:mm:ss") )
I receive an error message:The expression contains unsupported operator 'Between'
Hmm. that sucks, is there a way you can apply your filter in the select statement on your datatable?

Author

Commented:
Sorry, I forget. Here is the complete code:
da = New OleDb.OleDbDataAdapter("select * from Nhanvien", cn)
da.Fill(dtNhanVien)
dtNhanVien.DefaultView.RowFilter = String.Format("NgaySinh BETWEEN '{0}' AND '{1}'", Format(Me.dtpFromNgaySinh.Value, "dd-MMM-yyyy HH:mm:ss"), Format(Me.dtpToNgaySinh.Value, "dd-MMM-yyyy HH:mm:ss"))
And I receive this error message:The expression contains unsupported operator 'Between'
I use SQL SERVER 2005. dtNhanVien is a datatable.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
What are you trying to "show" the rows in? a datagrid?

Author

Commented:
Yes, I want to show the rows in a DataGridView. Only rows have NgaySinh >= mindate and NgaySinh <= maxdate are showed.
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
dim cm as currencymanager

cm = ctype(bindingcontext(dgv.datasource, dgv.datamember), currencymanager)
ctype(cm.list, dataview).rowfilter = "NgaySing BETWEEN '" + mindate.tostring("yyyyMMdd") + "' AND '" + maxdate.tostrin("yyyyMMdd") + "'"

Author

Commented:
BriCrowe, I receive this error message again:The expression contains unsupported operator 'Between'
Brian CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
change to...

ctype(cm.list, dataview).rowfilter = "NgaySing >= '" + mindate.tostring("yyyyMMdd") + _
   "' AND NgaySing <= '" + maxdate.tostrin("yyyyMMdd") + "'"
Commented:
Or, if you want to use the dates without converting to strings

   TableName.DefaultView.RowFilter = "NgaySing >= #" & mindate & "# AND NgaySing <= #" & maxdate & "#"

Roger

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
-   Unfortunately, I receive another error message:
         Cannot perform '>=' operation on System.DateTime and System.String.
-   Additional information: NgaySinh is a datetime field.

Author

Commented:
Thank Sancler and everyone, this code really works:
  TableName.DefaultView.RowFilter = "NgaySing >= #" & mindate & "# AND NgaySing <= #" & maxdate & "#"
Hmm... it seems to me you're fetching the data from your database and the applying a filter. Why not filter in your SQL query?

Author

Commented:
I don't want to apply the filter to the the table in an SQL query because I want to filter it only for a while, and then I will disable the filter. So if I filter in my SQL query, my program will run very slowly.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.