Filter a table using DefaultView.RowFilter

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.
LVL 3
maidinhtaiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
PockyMasterCommented:
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") )
0
maidinhtaiAuthor 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'
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

PockyMasterCommented:
Hmm. that sucks, is there a way you can apply your filter in the select statement on your datatable?
0
maidinhtaiAuthor 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.
0
Brian CroweDatabase AdministratorCommented:
What are you trying to "show" the rows in? a datagrid?
0
maidinhtaiAuthor Commented:
Yes, I want to show the rows in a DataGridView. Only rows have NgaySinh >= mindate and NgaySinh <= maxdate are showed.
0
Brian CroweDatabase AdministratorCommented:
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") + "'"
0
maidinhtaiAuthor Commented:
BriCrowe, I receive this error message again:The expression contains unsupported operator 'Between'
0
Brian CroweDatabase AdministratorCommented:
change to...

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

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

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maidinhtaiAuthor Commented:
-   Unfortunately, I receive another error message:
         Cannot perform '>=' operation on System.DateTime and System.String.
-   Additional information: NgaySinh is a datetime field.
0
maidinhtaiAuthor Commented:
Thank Sancler and everyone, this code really works:
  TableName.DefaultView.RowFilter = "NgaySing >= #" & mindate & "# AND NgaySing <= #" & maxdate & "#"
0
PockyMasterCommented:
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?
0
maidinhtaiAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.