?
Solved

Filter a table using DefaultView.RowFilter

Posted on 2006-03-21
15
Medium Priority
?
2,021 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.
0
Comment
Question by:maidinhtai
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16250857
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
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16250880
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
 
LVL 3

Author Comment

by:maidinhtai
ID: 16250962
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
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.

 
LVL 14

Expert Comment

by:PockyMaster
ID: 16251014
Hmm. that sucks, is there a way you can apply your filter in the select statement on your datatable?
0
 
LVL 3

Author Comment

by:maidinhtai
ID: 16251080
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16251998
What are you trying to "show" the rows in? a datagrid?
0
 
LVL 3

Author Comment

by:maidinhtai
ID: 16252109
Yes, I want to show the rows in a DataGridView. Only rows have NgaySinh >= mindate and NgaySinh <= maxdate are showed.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16252177
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
 
LVL 3

Author Comment

by:maidinhtai
ID: 16252253
BriCrowe, I receive this error message again:The expression contains unsupported operator 'Between'
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16252537
change to...

ctype(cm.list, dataview).rowfilter = "NgaySing >= '" + mindate.tostring("yyyyMMdd") + _
   "' AND NgaySing <= '" + maxdate.tostrin("yyyyMMdd") + "'"
0
 
LVL 34

Accepted Solution

by:
Sancler earned 2000 total points
ID: 16252852
Or, if you want to use the dates without converting to strings

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

Roger
0
 
LVL 3

Author Comment

by:maidinhtai
ID: 16252854
-   Unfortunately, I receive another error message:
         Cannot perform '>=' operation on System.DateTime and System.String.
-   Additional information: NgaySinh is a datetime field.
0
 
LVL 3

Author Comment

by:maidinhtai
ID: 16252886
Thank Sancler and everyone, this code really works:
  TableName.DefaultView.RowFilter = "NgaySing >= #" & mindate & "# AND NgaySing <= #" & maxdate & "#"
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16261215
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
 
LVL 3

Author Comment

by:maidinhtai
ID: 16286801
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question