Sorting on dates causes issues about advanceddatagridview HOT 15 CLOSED

Ok so I found a solution to my problem. I'm unable for certain to tell if this is a valid bug fix or if it was just a fluke that the original code didn't work form me.

So what I did was in MenuStrip.cs in the function private string BuildNodesFilterString(IEnumerable<TreeNodeItemSelector> nodes)
i changed the line: sb.Append("(Convert([{0}], 'System.String') LIKE '%" + Convert.ToString((IsFilterDateAndTimeEnabled ? dt : dt.Date), CultureInfo.CurrentCulture) + "%')" + appx);
to: sb.Append("[{0}] = '" + Convert.ToString((IsFilterDateAndTimeEnabled ? dt : dt.Date), CultureInfo.CurrentCulture) + "'" + appx);

My guess is that for some reason it didn't like the string conversion and the "LIKE" method didn't work very well with the data that I had. As I said, this is very difficult to prove that it is a one time thing that only happened to me or if others might have, or can have, the same problem.

In the end it works for me and if you think that this could be a problem for others then be my guest and implement my fix.

P.S. if you know why my code works and not the other, please add an explanation why here. D.S.

from advanceddatagridview.

Hello @Novido , I've checked it right now with the sample project and can not find this issue. If you "Clear Filter And Sort" and then order by the "date" column, to me the date is ordered.

from advanceddatagridview.

Sorry, I meant I wanted to filter the dates. When i choose a date in the tree view all I get is blank. Same goes when I try to make a custom filter. Still, it works if I use "between" function with to and from as the same date.

from advanceddatagridview.

Hello, to me it works. Take a look at this screencast https://streamable.com/m01v5.
Do you have a different behaviour?

from advanceddatagridview.

I have two comments. First, your dates are in a different format than mine. I write my dates like this: 2018-01-11 (todays date).

Secondly, when I peek at the filterstring, even though I only pick one date, I get a string that includes all dates.

Example where I choose "2018-12-31" as my date:
(Convert([{0}], 'System.String') LIKE '%2018-11-30 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-01-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-12-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-02-28 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-03-28 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-03-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-04-30 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-05-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-06-30 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-07-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-08-31 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-09-30 00:00:00%') OR (Convert([{0}], 'System.String') LIKE '%2018-10-31 00:00:00%')

from advanceddatagridview.

I think the problem is not related to the date format (mine is localized in it-IT format) cause your filter date string is build in the same format of your datetime, and this is right. So the problem it's related to the picking choice.
Unluckly I'm not able to repeat your behaviour. But I've some test you can do

1. Try using the sample project, compile it.
2. Try on another PC, is that your PC Windows 10?

from advanceddatagridview.

Unfortunately I have Win7 and can not try on another OS. I do know the issue exist on other PC's though.

I'll try with the sample project and see if it behaves differently.

from advanceddatagridview.

You could also try this sample here: https://file.io/FZSE55 That is the one compiled right now from my machine.

from advanceddatagridview.

The sample project works for me as well.

I guess the bug is related to something else. I'm still curious how I can get a filter string that looks so weird though.

I'll keep looking and see if I can find something else that sticks out.

from advanceddatagridview.

That's good! If you find the reason post it here. So i close here.

from advanceddatagridview.

That is for sure related to the filtering query string on dataset. I think it could be related to the C# compiler version more than the localization. It would be interesing to take a look at the string generate WITH, and WITHOUT your modification. That could point us to an understanding.

from advanceddatagridview.

Here are the filterstrings from both the old and new code:

NEW
([BASE_DT] = '2018-12-31 00:00:00')
([BASE_DT] = '2017-12-31 00:00:00' OR [BASE_DT] = '2018-12-31 00:00:00')

OLD
((Convert([BASE_DT], 'System.String') LIKE '%2018-12-31 00:00:00%'))
((Convert([BASE_DT], 'System.String') LIKE '%2017-12-31 00:00:00%') OR (Convert([BASE_DT], 'System.String') LIKE '%2018-12-31 00:00:00%'))

I've included both a single choice filter and multiple choice filter.

When I was debugging this I compared the filterstring to my other columns. I actually had more columns that contained dates. So I looked at the filterstring on those and it did not look anything like how the "OLD" strings looked. That's why I tried and replicate their output and ended up with the "NEW" output.

from advanceddatagridview.

So, the OLD/mine version with my dll/exe compiled works, right? the OLD/mine version with your dll/exe compiled does not works, right? Compiled by you only works with your patch.
Can you try to build strings like this, and tell me if it works? They would be a mix between your path and my version. I can not do this cause I can not replicate your behaviour, sorry.
case a) (OLD with equals instead of LIKE)
((Convert([BASE_DT], 'System.String') = '2018-12-31 00:00:00'))
case b) (NEW with LIKE instead of equals - note the % simbols)
([BASE_DT] LIKE '%2018-12-31 00:00:00%')
Thank you!

from advanceddatagridview.

Option a - Didn't work. Got the same result as the original code.
Option b - Didn't work. Got an error saying: "Cannot perform 'Like' operation on System.DateTime and System.String.

from advanceddatagridview.

Option b - it's sensible, i should have think about that
Option a - this confirm that that's something in the "Convert" dataset filter function in your compiler that does not work as my compiler.
At least we have find that it's the Convert method. Because no one else seems to have this issue, and because with my compiled version you tell me that it works, I think I'll not change the code. Anyway if someone still has this problem this will be the place to talk about this. Thank you @Novido for your help.

from advanceddatagridview.