Link to home
Start Free TrialLog in
Avatar of Southern_Gentleman
Southern_GentlemanFlag for United States of America

asked on

Getting 'String was not recognized as a valid Datetime'

I'm having a simple problem where a date autofilter is being used in my Gridview. I'm getting this error when I try to use the other header filters in the other columns.

My ,aspx looks like this:
<dx:ASPxGridView ID="ASPxGridView1" runat="server" Theme="DevEx" AutoGenerateColumns="False" DataSourceID="sqlPrintingHistory" KeyFieldName="OrderID" Width="100%" 
OnAutoFilterCellEditorCreate="ASPxGridView1_AutoFilterCellEditorCreate" OnAutoFilterCellEditorInitialize="ASPxGridView1_AutoFilterCellEditorInitialize" 
OnProcessColumnAutoFilter="ASPxGridView1_ProcessColumnAutoFilter">     
<Columns>
<dx:GridViewDataHyperLinkColumn FieldName="OrderID" Visible="true" VisibleIndex="0" Width="65px">
</dx:GridViewDataHyperLinkColumn>                                               
<dx:GridViewDataTextColumn FieldName="Printer" VisibleIndex="1" Width="100px" >
</dx:GridViewDataTextColumn>
<dx:GridViewDataDateColumn FieldName="PrintDate"  VisibleIndex="2" Width="200px">
</dx:GridViewDataDateColumn>
<dx:GridViewDataTextColumn FieldName="OrderStatus" VisibleIndex="3" Width="90px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn FieldName="BillingName" VisibleIndex="4" Width="145px" CellStyle-Wrap="False">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn FieldName="ShipCity"  VisibleIndex="5" Width="200px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="ST" FieldName="ShipState"  VisibleIndex="6" Width="20px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="Amount" FieldName="PaymentAmount" VisibleIndex="7" Width="60px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn Caption="Received" FieldName="Total_Payment_Received" VisibleIndex="8" Width="60px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataDateColumn FieldName="InHands" ReadOnly="True" VisibleIndex="9" width="80px">
</dx:GridViewDataDateColumn>
<dx:GridViewDataTextColumn Caption="Manager" FieldName="Custom_Field_AccountManager" VisibleIndex="10" width="80px">
</dx:GridViewDataTextColumn>
<dx:GridViewDataTextColumn FieldName="QTY"  VisibleIndex="11" Width="35px">
</dx:GridViewDataTextColumn>
</Columns>
</dx:ASPxGridView>         

Open in new window


Here is my code behind:
Protected Sub ASPxGridView1_AutoFilterCellEditorCreate(sender As Object, e As ASPxGridViewEditorCreateEventArgs) Handles ASPxGridView1.AutoFilterCellEditorCreate
        If e.Column.FieldName = "PrintDate" Then
            e.EditorProperties = New ComboBoxProperties()
        End If
    End Sub
    Protected Sub ASPxGridView1_AutoFilterCellEditorInitialize(sender As Object, e As ASPxGridViewEditorEventArgs) Handles ASPxGridView1.AutoFilterCellEditorInitialize
        If e.Column.FieldName = "PrintDate" Then
            Dim combobox As ASPxComboBox = TryCast(e.Editor, ASPxComboBox)
            combobox.ValueType = GetType(Int32)
            combobox.Items.Add("Today", 0)
            combobox.Items.Add("Yesterday", 1)
            combobox.Items.Add("This Week", 2)
            combobox.Items.Add("Next Week", 3)
            combobox.Items.Add("Last Week", 4)
            combobox.Items.Add("Two Weeks Ago", 5)
            combobox.Items.Add("This Month", 6)
            combobox.Items.Add("Last Month", 7)
            combobox.Items.Add("This Year", 8)
            combobox.Items.Add("Last Year", 9)
        End If
    End Sub

    Protected Sub ASPxGridView1_ProcessColumnAutoFilter(sender As Object, e As ASPxGridViewAutoFilterEventArgs) Handles ASPxGridView1.ProcessColumnAutoFilter
        If e.Kind <> GridViewAutoFilterEventKind.CreateCriteria Then
            Dim value As DateTime = System.Convert.ToDateTime(e.Value, CultureInfo.InvariantCulture)
            Dim start As New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day), [end] As DateTime = start

            If value >= start AndAlso value <= [end].AddDays(1.0).AddMilliseconds(-1.0) Then
                e.Value = "Today"

            ElseIf value >= start.AddDays(-1.0) AndAlso value <= [end].AddMilliseconds(-1.0) Then
                e.Value = "Yesterday"

            ElseIf value >= start.Subtract(New TimeSpan(0, 0, 0, 0)) AndAlso value <= [end].AddDays(7.0).AddMilliseconds(-1) Then
                e.Value = "This Week"

            ElseIf value >= start.AddDays(7.0) AndAlso value <= [end].AddDays(14.0).AddMilliseconds(-1) Then
                e.Value = "Next Week"

            ElseIf value >= start.Subtract(New TimeSpan(7, 0, 0, 0)) AndAlso value <= [end].Subtract(New TimeSpan(0, 0, 0, 0)) Then
                e.Value = "Last week"

            ElseIf value >= start.Subtract(New TimeSpan(14, 0, 0, 0)) AndAlso value <= [end].Subtract(New TimeSpan(7, 0, 0, 0)) Then
                e.Value = "Two Weeks Ago"

            ElseIf value >= New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1) AndAlso value <= New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(1).AddMilliseconds(-1) Then
                e.Value = "This month"

            ElseIf value >= New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1) AndAlso value <= New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMilliseconds(-1.0) Then
                e.Value = "Last month"

            ElseIf value >= New DateTime(DateTime.Now.Year, 1, 1) AndAlso value <= New DateTime(DateTime.Now.Year, 1, 1).AddYears(1).AddMilliseconds(-1.0) Then
                e.Value = "This year"

            ElseIf value >= New DateTime(DateTime.Now.Year - 1, 1, 1) AndAlso value <= New DateTime(DateTime.Now.Year, 1, 1).AddMilliseconds(-1) Then
                e.Value = "Last year"

            End If

            Return
        End If

        If e.Column.FieldName = "PrintDate" Then
            Dim start As New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day), [end] As DateTime = start
            Dim value As Int32 = Convert.ToInt32(e.Value)

            
            If value = 0 Then
                [end] = [end].AddDays(1.0).AddMilliseconds(-1)
            ElseIf value = 1 Then
                start = start.AddDays(-1.0)
                [end] = [end].AddMilliseconds(-1.0)
            ElseIf value = 2 Then
                start = start.Subtract(New TimeSpan(0, 0, 0, 0))
                [end] = [end].AddDays(7.0).AddMilliseconds(-1)
            ElseIf value = 3 Then
                start = start.AddDays(7.0)
                [end] = [end].AddDays(14.0).AddMilliseconds(-1)
            ElseIf value = 4 Then
                start = start.Subtract(New TimeSpan(7, 0, 0, 0))
                [end] = [end].Subtract(New TimeSpan(0, 0, 0, 0, 1))
            ElseIf value = 5 Then
                start = start.Subtract(New TimeSpan(14, 0, 0, 0))
                [end] = [end].Subtract(New TimeSpan(7, 0, 0, 0, 1))
            ElseIf value = 6 Then
                start = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
                [end] = start.AddMonths(1).AddMilliseconds(-1.0)
            ElseIf value = 7 Then
                start = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-1)
                [end] = New DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMilliseconds(-1.0)
            ElseIf value = 8 Then
                start = New DateTime(DateTime.Now.Year, 1, 1)
                [end] = start.AddYears(1).AddMilliseconds(-1.0)
            ElseIf value = 9 Then
                start = New DateTime(DateTime.Now.Year - 1, 1, 1)
                [end] = New DateTime(DateTime.Now.Year, 1, 1).AddMilliseconds(-1)
            End If

            e.Criteria = New GroupOperator(GroupOperatorType.And, New BinaryOperator(e.Column.FieldName, start, BinaryOperatorType.GreaterOrEqual), New BinaryOperator(e.Column.FieldName, [end], BinaryOperatorType.Less))
        End If
End Sub

Open in new window


Here is my Select Statement:
<asp:SqlDataSource ID="sqlPrintingHistory" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" 
SelectCommand="SELECT DISTINCT ps.Printer, TRY_CONVERT(datetime, ps.PrintDate, 101) AS PrintDate, o.OrderID, CASE WHEN o.OrderStatus LIKE '%New%' THEN 'New' WHEN o.OrderStatus LIKE 'Ready to Ship' THEN 'At Printer' WHEN o.OrderStatus LIKE 'Processing' THEN 'New' ELSE o.OrderStatus END AS OrderStatus, CASE WHEN o.BillingCompanyName = ' ' THEN o.BillingFirstName + ' ' + COALESCE (o.BillingLastName , '') ELSE o.BillingCompanyName END AS BillingName, o.ShipCity, o.ShipState, o.PaymentAmount, o.Total_Payment_Received, TRY_CONVERT(date, o.Custom_Field_InHandsDate, 101) AS InHands, o.Custom_Field_AccountManager, SUM(od.Quantity) AS QTY FROM Orders AS o INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID INNER JOIN PrinterSchedule AS ps ON o.OrderID = ps.OrderID WHERE (NOT (od.ProductCode LIKE N'%Add%')) AND (NOT (od.ProductCode LIKE N'%Embroid%')) AND (NOT (od.ProductCode LIKE N'PRT')) AND (NOT (od.ProductCode LIKE N'%DSC%')) AND (NOT (od.ProductCode LIKE N'Printing')) AND (NOT (od.ProductCode LIKE N'art')) AND (NOT (od.ProductCode LIKE N'Vector')) AND (NOT (od.ProductCode LIKE N'%ship%')) AND (NOT (od.ProductCode LIKE N'Print Fee')) AND (NOT (od.ProductCode LIKE N'Design')) AND (NOT (o.OrderStatus LIKE N'Cancelled')) AND (od.OrderDetailID &lt;&gt; 45336) GROUP BY ps.Printer, ps.PrintDate, o.OrderID, CASE WHEN o.BillingCompanyName = ' ' THEN o.BillingFirstName + ' ' + COALESCE (o.BillingLastName , '') ELSE o.BillingCompanyName END, TRY_CONVERT(date, o.Custom_Field_InHandsDate, 101), o.Custom_Field_AccountManager, o.OrderStatus, o.ShipCity, o.ShipState, o.PaymentAmount, o.Total_Payment_Received, TRY_CONVERT(date, ps.PrintDate, 101), CASE WHEN o.OrderStatus LIKE '%New%' THEN 'New' WHEN o.OrderStatus LIKE 'Ready to Ship' THEN 'At Printer' WHEN o.OrderStatus LIKE 'Processing' THEN 'New' ELSE o.OrderStatus END, TRY_CONVERT(datetime, ps.PrintDate, 101) HAVING (NOT (o.OrderStatus LIKE N'Cancelled')) ORDER BY PrintDate, ps.Printer" FilterExpression="Printer='{0}'">
<FilterParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="SelectPrinter" PropertyName="SelectedValue" ConvertEmptyStringToNull="true" />                                
</FilterParameters>
</asp:SqlDataSource>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kamal Khaleefa
Kamal Khaleefa
Flag of Kuwait image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial