Dataview rowfilter dates

[Astie]

i've got 2 questions in once:

i use VS.NET and having an ms database (if you need this information)

1. i want to filter a row with a dataview.rowfilter. I know how to do this with normal text fields but not with date's. I've got a combobox with the 12 months in it, now when i click for example may, i want the rowfilter to filter al the date's with the 5th month in it like 10-5-2005 and 30-5-2005 etc. how do i do this because = and like statements don't work. i get the next message:

Additional information: Cannot perform '=' operation on System.DateTime and System.Int32.


2. What ar al operators that you can use with a rowfilter, alike = and like?

 

[Astie]

Why?

Why is nobody reacting to my question? to difficult ?

 

[ManicCW]

well your combo probaby has values in integer. set dates as values in combo

 

[Astie]

well your combo probaby has values in integer. set dates as values in combo

oke but how does i filter the dates alike i asked befor

 

[KimmyGoose]

I'm having the same problem. I need to know what expression to use for the dataview.rowfilter. I want to use a function called Month() but it says it doesn't recognize this function. Let me know if any one figures this out.

 

[cjard]

i've got 2 questions in once:

i use VS.NET and having an ms database (if you need this information)

1. i want to filter a row with a dataview.rowfilter. I know how to do this with normal text fields but not with date's. I've got a combobox with the 12 months in it, now when i click for example may, i want the rowfilter to filter al the date's with the 5th month in it like 10-5-2005 and 30-5-2005 etc. how do i do this because = and like statements don't work.

Something like:

SELECT * FROM table WHERE Format(dateColumn, 'mmm') = 'May'

2. What ar al operators that you can use with a rowfilter, alike = and like?

In access? I dont know
In ADO.NET? Google for the .Expression property help in MSDN

 

[cjard]

I'm having the same problem. I need to know what expression to use for the dataview.rowfilter. I want to use a function called Month() but it says it doesn't recognize this function. Let me know if any one figures this out.

We normally range dates by.. er.. using a range:

Dim fromDate as DateTime = DateTime.Now
Dim toDate as DateTime = DateTime.Now.AddMonths(1)

.Filter = string.Format("[datecol] >= {0:MM/dd/yyyy} AND [datecol] < {1:MM/dd/yyyy}", fromDate, toDate)

 

[KimmyGoose]

Yes, thank you for that but that's not my problem. I know how to filter using a range of dates but that's not what I'm trying to do. If you read the very first post in this thread, you will understand my problem.

I have a combobox which has a list of the months. When a month is selected, I want the dataview to be filtered to display only the DOB fields that are in the selected month. I know how to convert the month to an integer. Currently I thought to use the following code:

Dim intMonth as Integer = dataview.item(datagrid.CurrentRowIndex).item("DOB").month
dataview.rowFilter = "Month(DOB) = " & intMonth

But I get an error stating, "The expression contains undefined function call Month()." Is there some other function or way around this?

 

[cjard]

SELECT * FROM table WHERE Format(dateColumn, 'mmm') = 'May'

(Use the database for this)

 

[KimmyGoose]

I get the same error message saying there's no function, Format. I need to use the DataView.RowFilter for this. I have a lot of filtering options, it wouldn't be wise for me to use one other way to filter when I've been using the DataView.RowFilter for everything else. Do you know of an expression I can use to assign to the DataView.RowFilter?

 

[cjard]

I get the same error message saying there's no function, Format

I'm working off the assumption that you, like Astie, are using MS Access as a database. I assume this because he implied he was using it, and you say that if I read his post, then I will understand your problem. Do let us know if you arent actually using Access, because it does kinda change things quite a bit. Additionally, as well as telling us what you arent using, don't forget that it is probably more important for us to know what you ARE using

Access has a Format command. Go here:

http://www.w3schools.com/sql/sql_tryit.asp

And run:

SELECT Format(Now, 'mmm')

--> Jun


Other databases dont have format. Alternatives must be used.

 

[KimmyGoose]

Oh yes yes, I am using ms access as the backend. I know that access has a format function as well as a month function. I am calling the function in the dataview.rowfilter though. Yes, when I use the format or month function with access it works, but here I'm using the data from within the dataview object which is the object that doesn't seem to have the format or month function.

After researching this subject more should I just select an additional column in my initial select statement when loading the data into the dataview? For example, "SELECT *, Month(BirthDate) AS BirthMonth FROM Members". Then when I want to filter for a certain month, such as September, I can set the dataview.rowfilter to something like, "BirthMonth=9".

 

[cjard]

Oh yes yes, I am using ms access as the backend. I know that access has a format function as well as a month function. I am calling the function in the dataview.rowfilter though.

DON'T!

After researching this subject more should I just select an additional column in my initial select statement when loading the data into the dataview? For example, "SELECT *, Month(BirthDate) AS BirthMonth FROM Members". Then when I want to filter for a certain month, such as September, I can set the dataview.rowfilter to something like, "BirthMonth=9".

You can, but this would drag the details for e.g. 500,000 members across a network, just so you can use a comparatively slow Filter algorithm to strip it to approx 8% of its original size, and display it?

It is always far better to just select the records you want!


If you think about it a little more closely, you'll see that there isnt really any difference between your solution, and what I've been telling you for the past X number of posts, except you solution generates 12 times more network traffic than is necessary

Add another SQL to your tableadapter that gets the member info.. The SQL would look pretty much like what I gave before:

SELECT * FROM table WHERE Month(dateColumn) = ?
SELECT * FROM table WHERE Format(dateColumn, 'mmm') = ?

Either of those will do, probably Month() is faster, certainly if you can create an index of Month(col) - i dont know if access can create indexes on functions (better databases like oracle and sqlserver can)

Then you fill like:

tableTableAdapter.FillByMonthNumber(myDataSet.Members, 9)
tableTableAdapter.FillByMonthName(myDataSet.Members, "September")

Depending...

 

[KimmyGoose]

You can, but this would drag the details for e.g. 500,000 members across a network, just so you can use a comparatively slow Filter algorithm to strip it to approx 8% of its original size, and display it?

I don't understand what you mean by this. Using the dataview.rowfilter isn't slower than connecting to the database, using the select statement, every time I want to filter the records.

It is always far better to just select the records you want!

I already have all the records selected from the database that I need. I have many search options available to the user to display certain records at a time so they can be found. This particular month selection is only one of them. I don't want to slow things down by connecting to the database every time the user changes their search options. I will be filtering and unfiltering the same in-memory records over and over.

 

[Arg81]

What Cjard is saying is with ADO.net it's "best" to only grab the data you need.

I.E. use parameterised queries to get the data, instead of loading ALL rows and then using your program to filter them.

It's really dependant on your users. If you have 30 users all accessing your DB and copying over 500,000 rows, then your network is going to take a battering.
However, using parameterised queries, your 30 users are probably only downloading 500 rows - there's a slight difference