locked
ASP.NET aspx page with Gridview Conditional Format RRS feed

  • Question

  • User-1109492799 posted

    Hi, I am wanting to do a conditional format based on a time difference but I am so far not being successful with it.

    The database holding the value is 'SQL Server 2016'

    The database is called 'DataLive'

    The tables are called 'CurrentJobs' and 'CodeScans' which are joined together via a unique value which works perfect.

    DataLive.CurrentJobs.JobDue is of type DateTime and this cannot be altered.

    DataLive.CodeScans.ScanDate is of type DateTime2(7), Any date that goes into this column shows as '2019-08-31 13:15:41.0000000' when I perform a SELECT from the table.

    A view called has been set up for the combined tables, which is accessed via a GridView1 on index.aspx.

    'ScanDate' column shown on the webpage shows the data as '31-08-2019 13:15:41', so there appears to be some kind of translation on the date there.

    Now, I have so far been able to conditionally format the rows as needed but this last one has me pickled.

    I want to flag the row up in red, if the difference in time between ScanDate and DateTime.Now is greater than 4 hours.

    The second thought on this is to show some kind of image of a Tick or X in a new cell in this GridView1 depending on the difference.

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    string JobPriority = Convert.ToString(GridView1.Rows[i].Cells[0].Text);
                    DateTime JobDue = Convert.ToDateTime(GridView1.Rows[i].Cells[1].Text);
                    
              // <---- This bit shows error 
              //    DateTime ScannedTime = Convert.ToDateTime(GridView1.Rows[i].Cells[6].Text);
    
                    if (JobPriority == "B")
                    {
                       GridView1.Rows[i].BackColor = Color.Yellow;
                    }
                    else if (JobPriority == "A")
                    {
                        GridView1.Rows[i].BackColor = Color.LightGreen;
                    }
    
                    else if (JobDue < DateTime.Now)
                    {
                        GridView1.Rows[i].BackColor = Color.LightBlue;
                    }
    
                    else if (JobDue == DateTime.Now && JobDue <= DateTime.Today.AddDays(1) )
                    {
                        GridView1.Rows[i].BackColor = Color.Orange;
                    }
    
                // <---- This bit below doens't work ---->
                //    else if (ScannedTime < DateTime.Now.AddHours(-4)) //
                //        GridView1.Rows[i].BackColor = Color.Red;
                //        GridView1.Rows[i].ForeColor = Color.Black;
                //    }
                // <---- end of bit that doesn't work ---->
                }
            }

    When I run the page with the commented out code it works fine, however if I uncomment, I get the following error:

    String was not recognized as a valid DateTime but only on the ScanDate column that is DateTime2(7).

    Any ideas where I can look for an answer?

    Note, on reading the post, I can see I need to do a little work on the condition on the between DateTime.Now and DateTime.Today.AddDays(1) because it's not right.

    Any help here would also be appreciated.

    Saturday, August 31, 2019 12:46 PM

Answers

  • User409696431 posted

    Run the commented code using the debugger with a breakpoint at the first DateTime line, then step through the code line by line.  What are the values for ScannedTime and TimeCalc as you hit each row?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 2, 2019 10:25 PM
  • User288213138 posted

    Hi techmandem,

    It's not a question of whether the value is null,  It's a question of whether  GridView1.Rows[i].Cells[6].Text can be converted to DataTime.

    So please check the type of the GridView1.Rows[i].Cells[6].Text.

    You can use debugger or  Response.Write(GridView1.Rows[i].Cells[6].Text) to see its value.

    If your problem hasn't been solved, please post your aspx code.

     

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 7:32 AM
  • User-1109492799 posted

    While I was checking into the debug and step through as suggested, I saw an error message which I haven't seen before in another panel, this error led me to change the build configuration from Release, to Debug which then showed the proper variables as I stepped through.

    Strangely, the page then started working as I was expecting?!

    I changed the Build Configuration back to Release and published the page, which seems to be working now as expected. This threw me completely out and i've spent the better part of a day tying to get there.

    Thank you very much for aiming me in the right direction.

    Now then,

    is it possible to add an extra column which would act as a calculation, for instance, if the two dates are more than 4 hours apart, store '1' as a value, and then be able to sort the gridview by that value?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 8:58 AM

All replies

  • User288213138 posted

    Hi techmandem,

    According to your description, I did a demo, but I didn't encounter any errors.

    Please check if the value of gridview1.rows [I].cells [6].text is of type DateTime2(7).

    The code:

    <div>
                <asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1" Width="213px">
                    <Columns>
                        <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                        <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
                        <asp:BoundField DataField="ScanDate" HeaderText="ScanDate" SortExpression="ScanDate" />
                    </Columns>
                </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString2 %>" SelectCommand="SELECT [Id], [Name], [ScanDate] FROM [CodeScans]"></asp:SqlDataSource>
                <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
            </div>
    
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
             
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    DateTime ScannedTime = Convert.ToDateTime(GridView1.Rows[i].Cells[2].Text);
                    if(ScannedTime < DateTime.Now.AddHours(-4))
                    {
                        Label1.Text = ScannedTime.ToString();
                    }
                    else
                    {
                        Label1.Text = "123";
                    }
                    
                }
            }
    

    Best regards,

    Sam

    Monday, September 2, 2019 10:06 AM
  • User-1109492799 posted

    After doing some hunting, I wondered if the fact that not all cells in my ScanDate column are populated. I know that the blank ones are returned as null on the database view which shows the combined tables. If this is the case, will it cause an issue and if so, how do I get around it?

    Edit: after a little more hunting and realising that yes the Nulls can cause issues, I was led to the following:

    public partial class index : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Response.AppendHeader("Refresh", "20");
        }
    
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                string JobPriority = Convert.ToString(GridView1.Rows[i].Cells[0].Text);
                DateTime JobDue = Convert.ToDateTime(GridView1.Rows[i].Cells[1].Text);
                string ScanEmpty = Context.Server.HtmlDecode(GridView1.Rows[i].Cells[6].Text).Trim();
    
                if (string.IsNullOrEmpty(ScanEmpty))
                {
                    GridView1.Rows[i].Cells[6].BackColor = Color.Red;
                    GridView1.Rows[i].Cells[7].BackColor = Color.Red;
                }
              //else
              //{
              //    DateTime ScannedTime = Convert.ToDateTime(GridView1.Rows[i].Cells[6].Text);
              //    DateTime TimeCalc = DateTime.Now.AddHours(-4);
              //    if (ScannedTime < TimeCalc)
              //    {
              //        GridView1.Rows[i].Cells[6].BackColor = Color.MediumPurple;
              //        GridView1.Rows[i].Cells[7].BackColor = Color.MediumPurple;
              //    }
              //}
            }
        }
    }

    Now that I've gotten the nulls sorted out, I am wanting to fix the commented out code. What I am trying to calculate is if the 'ScannedTime' is older than 4 hours, so I used DateTime.Now.AddHours(-4) and see if 'ScannedTime' is less than that.

    This however does not want to work for me. It either colours the cells all together or none of them all together regardless of if the ScannedTime is older than 4 hours or not...

    Monday, September 2, 2019 11:30 AM
  • User409696431 posted

    Run the commented code using the debugger with a breakpoint at the first DateTime line, then step through the code line by line.  What are the values for ScannedTime and TimeCalc as you hit each row?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 2, 2019 10:25 PM
  • User-1109492799 posted

    Hi KathyW, thank you for the pointer. Sadly I didn't get much out of it, if the field is not null it shows me a date, but when I step through, I can't see any values at all when I hover over the two variables...

    Actually it took a moment longer than I thought but after a few seconds there was a date and time value in there as per Samwu's post... ScannedTime is ok in that sense, but i'm not getting much from: DateTime TimeCalc = DateTime.Now.AddHours(-4); which just seems to give me '-4' when stepping through the code...

    :-(

    Tuesday, September 3, 2019 7:25 AM
  • User288213138 posted

    Hi techmandem,

    It's not a question of whether the value is null,  It's a question of whether  GridView1.Rows[i].Cells[6].Text can be converted to DataTime.

    So please check the type of the GridView1.Rows[i].Cells[6].Text.

    You can use debugger or  Response.Write(GridView1.Rows[i].Cells[6].Text) to see its value.

    If your problem hasn't been solved, please post your aspx code.

     

    Best regards,

    Sam

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 7:32 AM
  • User-1109492799 posted

    Hi Samwu, thank you for the input. I'm afraid I don't know where in my code to place the 'Response.Write(GridView1.Rows[i].Cells[6].Text)' as it just seems to flood the page with lots of datetime information? (Sorry i'm not experienced with this...)

    Cells[6] is indeed of type DateTime2(7) however I have read on another forum that the GridView1 converts everything into a string by default? Is this correct?

    Code as follows:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="index.aspx.cs" Inherits="JobScansAPI.index" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Job Tracking</title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
                <h1 style="font-family: Calibri; color: #00824B;">Job Tracking</h1>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [JobTracker] ORDER BY [ScanDate] DESC, [Priority] DESC, [PromisedDeliveryDate]"></asp:SqlDataSource>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="5" DataKeyNames="CustomerAccountNumber" DataSourceID="SqlDataSource1" Font-Names="Calibri" HorizontalAlign="Left" OnRowDataBound="GridView1_RowDataBound" GridLines="Horizontal">
                    <Columns>
                        <asp:BoundField DataField="Priority" HeaderText="Priority" SortExpression="Priority" />
                        <asp:BoundField DataField="PromisedDeliveryDate" DataFormatString="{0:d}" HeaderText="Del Date" SortExpression="PromisedDeliveryDate" />
                        <asp:BoundField DataField="DocumentNo" HeaderText="Job No" SortExpression="DocumentNo" />
                        <asp:BoundField DataField="CustomerAccountNumber" HeaderText="Acc No" ReadOnly="True" SortExpression="CustomerAccountNumber" />
                        <asp:BoundField DataField="CustomerDocumentNo" HeaderText="PO No" SortExpression="CustomerDocumentNo" />
                        <asp:BoundField DataField="AnalysisCode3" HeaderText="Notes" SortExpression="AnalysisCode3" />
                        <asp:BoundField DataField="ScanDate" HeaderText="Last Scanned At" SortExpression="ScanDate" />
                        <asp:BoundField DataField="Dept" HeaderText="Dept" SortExpression="Dept" />
                    </Columns>
                    <HeaderStyle BackColor="#00824b" Font-Bold="True" ForeColor="White" />
                </asp:GridView>
                <br />
            </div>
        </form>
    </body>
    </html>
    using System;
    using System.Collections.Generic;
    using System.Drawing;
    using System.Globalization;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace JobScansAPI
    {
        public partial class index : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                Response.AppendHeader("Refresh", "20");
            }
    
            protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
            {
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    string JobPriority = Convert.ToString(GridView1.Rows[i].Cells[0].Text);
                    DateTime JobDue = Convert.ToDateTime(GridView1.Rows[i].Cells[1].Text);
                    string ScanEmpty = Context.Server.HtmlDecode(GridView1.Rows[i].Cells[6].Text).Trim();
    
                    if (string.IsNullOrEmpty(ScanEmpty))
                    {
                        GridView1.Rows[i].Cells[6].BackColor = Color.Red;
                        GridView1.Rows[i].Cells[7].BackColor = Color.Red;
                    }
                    else
                    {
                        DateTime ScannedTime = Convert.ToDateTime(GridView1.Rows[i].Cells[6].Text);
                        DateTime TimeCalc = DateTime.Now.AddHours(-4);
                        if (ScannedTime < TimeCalc)
                        {
                            GridView1.Rows[i].Cells[6].BackColor = Color.MediumPurple;
                            GridView1.Rows[i].Cells[7].BackColor = Color.MediumPurple;
                        }
                    }
    
                    if (JobPriority == "B")
                    {
                        GridView1.Rows[i].BackColor = Color.Yellow;
                    }
                    else if (JobPriority == "A")
                    {
                        GridView1.Rows[i].BackColor = Color.LightGreen;
                    }
    
                    if (JobDue < DateTime.Today)
                    {
                        GridView1.Rows[i].BackColor = Color.LightBlue;
                    }
    
                    else if (JobDue == DateTime.Today)
                    {
                        GridView1.Rows[i].BackColor = Color.DarkOrange;
                    }
    
                    else if (JobDue == DateTime.Today.AddDays(1))
                    {
                        GridView1.Rows[i].BackColor = Color.Orange;
                    }
                }
            }
        }
    }

    Hope the code helps...

    Tuesday, September 3, 2019 7:43 AM
  • User-1109492799 posted

    While I was checking into the debug and step through as suggested, I saw an error message which I haven't seen before in another panel, this error led me to change the build configuration from Release, to Debug which then showed the proper variables as I stepped through.

    Strangely, the page then started working as I was expecting?!

    I changed the Build Configuration back to Release and published the page, which seems to be working now as expected. This threw me completely out and i've spent the better part of a day tying to get there.

    Thank you very much for aiming me in the right direction.

    Now then,

    is it possible to add an extra column which would act as a calculation, for instance, if the two dates are more than 4 hours apart, store '1' as a value, and then be able to sort the gridview by that value?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 3, 2019 8:58 AM
  • User288213138 posted

    Hi techmandem,

    Congratulations on solving the problem.

    is it possible to add an extra column which would act as a calculation, for instance, if the two dates are more than 4 hours apart, store '1' as a value, and then be able to sort the gridview by that value?

    This is a new problem, please post the problem in another thread.

    Best regards,

    Sam

    Tuesday, September 3, 2019 9:15 AM