locked
GridView with unbound columns RRS feed

  • Question

  • User-1109492799 posted

    Hi, I have a gridview which refers to a database table, with two datetime columns. I have the following code running to conditionally format the backcolour if the difference between those two dates is more than 4 hours, but I would like to be able to create an extra column where the value will be '0' if the difference is less than 4 hours, or '1' if the difference is more than 4 hours, and '2' one of the dates is blank. is there a way to do this?

    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 ScannedTime = Convert.ToDateTime(ScanEmpty);
                        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 (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;
                    }
                
    
                    if (JobPriority == "B")
                    {
                        GridView1.Rows[i].BackColor = Color.Yellow;
                    }
                    else if (JobPriority == "A")
                    {
                        GridView1.Rows[i].BackColor = Color.LightGreen;
                    }
                }
            }

    Tuesday, September 3, 2019 10:57 AM

Answers

  • User409696431 posted

    How are you binding your GridView?  You could add another column in your database Select that is generated from that calculation.

    An example:

    SELECT [scantime],[otherfields],
    (CASE
    WHEN scantime IS NULL THEN 2
    WHEN DATEDIFF(hour, scantime, GETDATE()) < 4 THEN 0
    WHEN DATEDIFF(hour, scantime, GETDATE()) >= 4 THEN 1
    ELSE 2 END)
    AS diffhours FROM [jobs]

    Then add a new  readonly boundfield to your GridView for the new column "diffhours".

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

All replies

  • User409696431 posted

    How are you binding your GridView?  You could add another column in your database Select that is generated from that calculation.

    An example:

    SELECT [scantime],[otherfields],
    (CASE
    WHEN scantime IS NULL THEN 2
    WHEN DATEDIFF(hour, scantime, GETDATE()) < 4 THEN 0
    WHEN DATEDIFF(hour, scantime, GETDATE()) >= 4 THEN 1
    ELSE 2 END)
    AS diffhours FROM [jobs]

    Then add a new  readonly boundfield to your GridView for the new column "diffhours".

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

    Oddly enough, after I posted, I realised from previously working with Microsoft Access, that you can calculate a separate column and display that, so wondered if SQL would do the same.

    Currently the Gridview pulls data from a View but i'm positive I can duplicate it to test.

    Thanks again Kathy, much appreciated!

    Tuesday, September 3, 2019 1:38 PM
  • User288213138 posted

    Hi techmandem,

    that you can calculate a separate column and display that, so wondered if SQL would do the same.

    i tested in SQL, it works fine.

    The result:

    Best regards,

    Sam

    Wednesday, September 4, 2019 9:38 AM
  • User-1109492799 posted

    Hi Sam / Kathy, thank you again for the assist.

    I did note during the course of last night that the rule seems to fall over a little when we approach the 4 hour timer, it seems to cause the output to change if the hour difference is right but doesn't seem to take the actual time into account.

    i.e. time of 16.42, plus 4 hours should be 20.42  and then trip over the 4 hour marker, however..

    16.42, and checking again at 20.01, the hours difference is correct but obviously we're still 41 minutes before 4 hours has actually passed.

    Is there a minutes version of the DIFF?

    Wednesday, September 4, 2019 10:22 AM
  • User409696431 posted

    If you want to compare to the minute, change to DATEDIFF(minute, ...), and change 4 to 240  (4 hours is 240 minutes).

    Wednesday, September 4, 2019 3:02 PM