Issue with GridView conditional formatting on DatTime2

Craig Sunderland

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 'JobScansView' 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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vincent Maverick Durano7y ago
I am sure this is to my lack of understanding, but the DateTime2 column in my sql table isn't a string type, and when the webform is outputting the value, it is still a datetime...

Is the code provided trying to turn the datetime2 into a string?

I understand that your column in your database is DateTime2, however once that column gets rendered in GridView, it will turn that column into string. All content in the browser will be treated as plain string. That's why this code:
GridView1.Rows[i].Cells[1].Text
represents a string value. It could be a of any datatype from your database. And in order to get the actual type, you need to do a convertion from string to whatever data type. In your case a DateTime type.

Actually I can see now that it isn't. I 'THINK' the problem might be that not every cell is marked with a date and is therefore still null until it has been updated.

That's why you need to use TryParse() instead of Convert() to ensure that the value that you are trying to convert is a valid date, a null or empty is not a valid date and would throw an exception.

Try to debug your code, set a break point at RowDataBound event and step into your code to figure out what's going there. Check the value that you are trying to convert.
Craig Sunderland7y ago
Thank you Vincent,

I am sure this is to my lack of understanding, but the DateTime2 column in my sql table isn't a string type, and when the webform is outputting the value, it is still a datetime...

Is the code provided trying to turn the datetime2 into a string?

Actually I can see now that it isn't. I 'THINK' the problem might be that not every cell is marked with a date and is therefore still null until it has been updated.

Could this be the issue and if so, are there any suggestions on how to get around it?
Vincent Maverick Durano7y ago
I would always recommend you to use DateTime.TryParse method to ensure the string that you are trying to convert is a valid DateTime. For example:
DateTime myDateTime;

string dateString = GridView1.Rows[i[.Cells[1].Text.Trim();
//If the string has a valid format then convert it to appropriate format
if (DateTime.TryParse(dateString, out myDateTime)) {
     // string is safely converted
    //do something
}
else{
    Response.Write("String is Not a valid DateTime format");
}