Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to avoid error when reading DateTime filed from DataTable that don't have a value

Posted on 2009-07-02
12
433 Views
Last Modified: 2012-05-07
Hi,

I keep running into trouble with my DataTable that has two DateTime columns that sometimes do not have a value in them.

If I try to get the value as DateTime, I get a cast error, when I try to get the value as an object, I get a nullReferenceException when I try to access it later.

Can anyone help please, there must be some good practice way of handling this (and I don't like the "object approach" anyway).

While writing this, I came to think that maybe a try...catch would help since if one or both dates are missing, I don't want to update the ElapsedDays field anyway. But there must be a nicer way of handling this - or?
private void AddNonWorkDays(string startCol, string endCol, DataTable dt)
		{
			// Add the new column...
			//DataColumn dc = new DataColumn("DateExample",System.Type.GetType("System.DateTime"));
			DataColumn dc = new DataColumn();
			dc.ColumnName = "Elapsed Days";
			dc.DataType = typeof(System.Int32);
			
			
			dt.Columns.Add(dc);
 
			// Calculate the values for the new column for each row...
 
			List<DateTime> holidays = new List<DateTime>();
 
			foreach (DataRow row in dt.Rows)
			{
				DateTime startDate = row.Field<DateTime>("Case Open Date");
				// The following line causes an error when there are no data...
				// {"Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type."}
				// DateTime endDate = row.Field<DateTime>("Date/Time Resolved w/Customer");
 
				// Tried to workaround this by using the object type which avoids the error here...
				object endDate = row.Field<object>("Date/Time Resolved w/Customer");
				// ...and then checking if the value is empty...
				if (endDate.ToString() != string.Empty)
				{
					// ...but that causes an error here at (DateTime)endDate...
					// nullReferenceException
					int duration = CountWorkDays(startDate, (DateTime)endDate, holidays);
 
					row.SetField<Int32>("ElapsedDays", duration);
				}
 
			}
 
		}

Open in new window

0
Comment
Question by:Sharp2b
  • 5
  • 5
  • 2
12 Comments
 
LVL 15

Expert Comment

by:jinal
ID: 24765451
Avoid use object.

Use nullabletype.

Please provide detail of countworkdays.
0
 

Author Comment

by:Sharp2b
ID: 24765608
Thanks,
you made a similar comment to my other case yesterday (http://www.experts-exchange.com/Q_24536961.html) Ddo you mean that this entire line should be in?
data.Field<DateTime?>("Case Open Date") != null  nullable type

The compiler didn't like it and I didn't understand it but maybe you can explain and I would very much like to try as it sounds like a better approach.
I got the countworkdays from another request here: http://www.experts-exchange.com/Q_24535798.html
also attached below
private int CountWorkDays( DateTime startDate, DateTime endDate, List<DateTime> excludedDates )
{
    int dayCount = 0;
    int inc = 1;
    bool endDateIsInPast = startDate > endDate;
    DateTime tmpDate = startDate;
    DateTime finiDate = endDate;
 
    if( endDateIsInPast )
    {
        // Swap dates around
        tmpDate = endDate;
        finiDate = startDate;
 
        // Set increment value to -1, so it DayCount decrements rather 
        // than increments
        inc = -1;
    }
 
    while( tmpDate <= finiDate )
    {
        if( !excludedDates.Contains( tmpDate ) )
        {
            dayCount += inc;
        }
 
        // Move onto next day
        tmpDate = tmpDate.AddDays( 1 );
    }
 
    return dayCount;
}

Open in new window

0
 
LVL 15

Expert Comment

by:jinal
ID: 24768950
Hello,

what is value of end date in case of enddate is null ?

I means to say that when DataTable return null value for enddate which value used in CountDays for that. Is that current date is used for that.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Sharp2b
ID: 24770612
Hi and thanks for coming back to me.

Sorry for the delay, I suppose we are in different time zones.

Maybe it's not clear from the code as this is still a first effort but my intention is not to call CountWorkDays() in case of either startDate or endDate don't have a value in the DataTable. In that case, I will also leave the "ElapsedDays" field empty as this should not be included in the further calculations anyway.

Maybe it's possible to check for null values in the DataTable before retrieving the values? If the field is null, I don't want the value anyway so I could just skip this and move on to the next record.

Currently I try to get the value and then check for null but maybe it's better to do it the other way around. If I can figure out how, that is. I have seen somethign like DBNull, maybe that would work, I need to test.
0
 
LVL 4

Expert Comment

by:g_johnson
ID: 24770715
I think you should just check for the null

if (startdate != null)
{
     //do your thing
}
0
 

Author Comment

by:Sharp2b
ID: 24770781
But I can't as when I try to retrieve the startDate and there is no value in the DB, I get an error. I think this is because startDate or endDate can't be null.

I need to test this once again now that I read a little bit more but it looks like it doesn't help declaring these as DateTime? startDate to make it nullable as it fails already on this part:
row.Field<DateTime>("Case Open Date")
I will try again with:
row.Field<DateTime?>("Case Open Date")
but that didn't help in a previous case.
0
 
LVL 4

Expert Comment

by:g_johnson
ID: 24773195
What about

if (row.Field<DateTime>("Date/Time Resolved w/Customer") != null)
{
     //do your thing
}

I'm just guessing here ...
0
 
LVL 15

Expert Comment

by:jinal
ID: 24773327
When you work with NullableType

<DateTime?>dt = null;

if(dt.HasValue) // This will indicate that Nullable Type contain value
{
   CallFunction(dt.Value);  // This contain value for date.
}
else
{
  // Do Nothing
}

0
 
LVL 15

Accepted Solution

by:
jinal earned 500 total points
ID: 24773357
       DataTable dt = new DataTable();
        dt.Columns.Add("Case Owner");
        dt.Columns.Add("Case Open Date",typeof(DateTime));
        dt.Columns.Add("Date/Time Resolved w/Customer",typeof(DateTime));
        DataRow dr = dt.NewRow();
        dr["Case Owner"] = "Test1";
        dr["Case Open Date"] = DateTime.Now.AddDays(-15).Date  ;
        dr["Date/Time Resolved w/Customer"] = DateTime.Now;
        dt.Rows.Add(dr);
       
        dr = dt.NewRow();
        dr["Case Owner"] = "Test2";
        dr["Case Open Date"] = DateTime.Now.Date;
        dr["Date/Time Resolved w/Customer"] = DBNull.Value;
        dt.Rows.Add(dr);

        foreach (DataRow dr1 in dt.Rows)
        {
            DateTime? dtStart = dr1.Field<DateTime?>("Case Open Date");
            DateTime? dtend = dr1.Field<DateTime?>("Date/Time Resolved w/Customer");

            if (dtStart.HasValue && dtend.HasValue)
            {
                CountDay(dtStart.Value, dtend.Value);
            }

        }


 private TimeSpan CountDay(DateTime dtStart, DateTime dtEnd)
    {
        return dtEnd - dtStart;
    }
0
 
LVL 15

Expert Comment

by:jinal
ID: 24773361
Hello ,

In my previous comment  . I just take sample and use your own way. That might help.
0
 

Author Comment

by:Sharp2b
ID: 24782987
Thanks a lot!
Unfortunately some other things came up over the weekend so I didn't get a chance to try this. I will try to check this as soon as possible.

Again, thanks a lot for your efforts.
0
 

Author Closing Comment

by:Sharp2b
ID: 31599279
Sorry I didn't get it at first. I now see that I need to use two "?" operators in the line: DateTime? dtStart = dr1.Field("Case Open Date");
DateTime? is a different type to DateTime and I need to be consistent.

Thanks a lot, I really appreciate all your help!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question