[Solved] DateTime Sorting Problem

[Akzhol The Kyrgyz]

Hi,

Sorting of DateTime collumn is not working for me. I'm getting following exception:

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM"

In the following (bold) line:

private void WriteRows(IGridRenderer<T> renderer)
        {
            renderer.BodyStart();

            bool empty = true;

            if (DataProcessor.ProcessedDataSource != null)
            {
                IEnumerable<T> dataSource = DataProcessor.ProcessedDataSource.Cast<T>();

                int rowIndex = 0;

                dataSource.Each(row =>
                {
                    WriteRow(renderer, row, rowIndex);
                    rowIndex += 1;
                    empty = false;
                });
            }
            
            if (empty)
            {
                renderer.EmptyRow();
            }

            renderer.BodyEnd();
        }


DataProcessor.ProcessedDataSource is already containing the exception when execution comes to this function.
(All the DateTime data is valid, i.e. 2009-2010 years)
Any suggestions where to look? About possible causes? 

Update:
 My DB collation is Turkish_CI_AS and I suspect that it could be a problem. Because I tried Northwind DB, and Telerik works perfectly with it. But I can't change collation of my DB, since it'll affect sorting of other types such as strings.
How can I change/edit/configure Telerik, so that it can work with Turkish_CI_AS collation.

Update2:
I tried to use Ajax binding. Ajax binding work good for northwind database, but again I'm getting "500-Internal Server Error" for the list of items from my DB, the collation of which is Turkish_CI_AS.

Are  Telerik extensions tested for different DB collations? Is it bug?


Any information is greatly appreciated.
Thanks.

[Atanas Korchev]

Hello Akzhol The Kyrgyz,

I tested a similar setup using SQL Server Express 2005 and Turkish collation. I also used Linq to Sql. However I couldn't reproduce this problem. I think I may be missing something. Could you specify the following info:

1. What database server are you using?
2. What is the method of data access (Linq2Sql, EF, etc)?

You could open a support ticket and attach your database so we can test locally. I am also attaching my test project.

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Akzhol The Kyrgyz]

Hi, Atanas Korchev

After spending huge amount of time, I realized that the cause was just a little thing :)
I think Telerik is not able to sort DateTime columns that are NOT nullable.
If I make column nullable, then it works ok. As soon as I change it to NON nullable, it begins giving above error.

So I think it's a bug. Is it possible to fix this?
My DataBase is very big and I can't change it's definition (to NON Nullable).

Thanks very much for your help.
Regards

[Atanas Korchev]

Hi Akzhol The Kyrgyz,

I still cannot reproduce the problem locally. I updated the sample database to have non-nullable dates and still it worked as expected. Could you please try running it at your end (I have attached the updated project)?

If I am unable to reproduce the problem locally you can try using custom databinding as a workaround.

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Akzhol The Kyrgyz]

You can regenerate the error by following steps:
1- Open Test.dbml (of the project that you've sent me)
2- Left Click, Date propery of the table
3 - Set Nullable to False

Yeah, custombinding is another solution. But since my application is big, and I use Grid possibly in  around 30-40 places, this solution will not be the best.

Thanks.

[Atanas Korchev]

Hi Akzhol The Kyrgyz,

Thank you for helping me reproduce the problem. Indeed you have discovered a problem that our expression engine cannot sort by DateTime column. Until we find a way to fix that problem you can patch the source code. Open the Telerik.Web.Mvc\Infrastructure\Implementation\Expressions\MemberAccess\PropertyAccessExpressionBuilder.cs file and modify the CreateMemberAccessExpression method like this (change the last argumentof MakeMemberAccess to false):

public override Expression CreateMemberAccessExpression()
        {
            //if no property specified then return the item itself
            if (string.IsNullOrEmpty(this.MemberName))
            {
                return this.ParameterExpression;
            }

            return ExpressionFactory.MakeMemberAccess(this.ParameterExpression, this.MemberName, false);
        }

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Akzhol The Kyrgyz]

Hi, Atanas Korchev

Thank you for your quick and continous support. It seems it's working now.

Also I want to inform you that DateTime column can't be filtered along with other columns.

Example:
column1:  column2  column3
date           decimal         string 

filter column3 - OK, then filter column1   => FormatException("String was not recognized as a valid DateTime.") in ParseDateTimeExpression function in FilterParser.cs

[Atanas Korchev]

Hello Akzhol The Kyrgyz,

Again I am unable to reproduce this problem. Could you point me to any directions how to reproduce that?

Regards,
Atanas Korchev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.