GridView with unbound columns [Answered]RSS

• • Reply
  techmandem

  Member

  3 Points

  8 Posts

  GridView with unbound columns

  Sep 03, 2019 10:57 AM|techmandem|LINK

  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;
                  }
              }
          }

• • Reply
  KathyW

  Contributor

  5081 Points

  2011 Posts

  Re: GridView with unbound columns

  Sep 03, 2019 12:59 PM|KathyW|LINK

  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".

• • Reply
  techmandem

  Member

  3 Points

  8 Posts

  Re: GridView with unbound columns

  Sep 03, 2019 01:38 PM|techmandem|LINK

  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!

• • Reply
  samwu

  Contributor

  2130 Points

  861 Posts

  Re: GridView with unbound columns

  Sep 04, 2019 09:38 AM|samwu|LINK

  Hi techmandem,

  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

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  techmandem

  Member

  3 Points

  8 Posts

  Re: GridView with unbound columns

  Sep 04, 2019 10:22 AM|techmandem|LINK

  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?

• • Reply
  KathyW

  Contributor

  5081 Points

  2011 Posts

  Re: GridView with unbound columns

  Sep 04, 2019 03:02 PM|KathyW|LINK

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