Nulls and Datetime

paul walker

4y1.1kC#

Afternoon...

I have a small app that loads data from an excel worksheet to a sql server database table via a data table.

I have a column in the spreadsheet that contains a date.

I set the column in the data table thus...

dt.Columns.Add("EffectiveDateOfTransaction_CR0057", typeof(DateTime));

and on processing each row of the spreadsheet into the data table thus...

MyNewRow["EffectiveDateOfTransaction_CR0057"] = (DateTime)(worksheet.Cells[i, 25] as Excel.Range).Value;

I get an exception as DateTime cannot be null.

I have checked the web, but I am pretty new to this, there seems to be something DateTime?, but I cant seem to make it work.

Any help appreciated.

9 Replies

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

Amit Mohanty4y ago
Accepted answer
Hey try below code.
if (string.IsNullOrEmpty((worksheet.Cells[i, 25] as Excel.Range).Value))
    MyNewRow["EffectiveDateOfTransaction_CR0057"] = DBNull.Value;
else
    MyNewRow["EffectiveDateOfTransaction_CR0057"] = (DateTime)(worksheet.Cells[i, 25] as Excel.Range).Value;
+2
paul walker4y ago
Many thanks Amit, sorted now.
+2
Amit Mohanty4y ago
Hey, .ToString() it will cause null reference exception when the value is null. So use Convert.ToString()
if (string.IsNullOrEmpty(Convert.ToString((worksheet.Cells[i, 25] as Excel.Range).Value)))
    myNewRow["EffectiveDateOfTransaction_CR0057"] = DBNull.Value;
else
    myNewRow["EffectiveDateOfTransaction_CR0057"] = (DateTime)(worksheet.Cells[i, 25] as Excel.Range).Value;
+1
paul walker4y ago
HI Amit,
The 'ToString' worked fine until I hit an empty DateTime in my input spreadsheet, I then got....

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException
HResult=0x80131500
Message=Cannot perform runtime binding on a null reference
Source=
StackTrace:
How hard can it be to put a null datetime into a SQL Server datatable??
+1
Amit Mohanty4y ago
Try this if you are getting 'string.IsNullOrEmpty(string)' has some invalid arguments.
if (string.IsNullOrEmpty((worksheet.Cells[i, 25] as Excel.Range).Value.ToString()))
+1
paul walker4y ago
Unfortunately when I tried the code above I got this...

Microsoft.CSharp.RuntimeBinder.RuntimeBinderException
HResult=0x80131500
Message=The best overloaded method match for 'string.IsNullOrEmpty(string)' has some invalid arguments
Source=
StackTrace:
It seems odd as there is a DateTime?, which allows nulls, but I cant seem to get it to work...
Any ideas??
+1
paul walker4y ago
Cheers guys.
Quality answers as usual...
+1
Satya Karki4y ago
Hi, I think your DateTime column in the SQL table is not allowing to insert null. If you need to insert null values in the DateTime column then allow the DateTime column property to insert Null.

Otherwise, you need to provide DateTime values set to default DateTime( for e.g DateTime.Now) while inserting or in your Excel sheet DateTime value should not be empty/Null.
+1
Nitin Sontakke4y ago
Hello,

Personally, I have rarely used data table.

From what read online after reading your question, to add a nullable column, there is a separate property called AllowDBNull, which needs to be set to true BEFORE adding column to data table.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/adding-columns-to-a-datatable

You may also wish to read this post:

https://stackoverflow.com/questions/1638746/table-is-nullable-datetime-but-dataset-throws-an-exception

Please also note that this is now out-dated and probably no longer supported.

You probably better of creating a custom class and populating List of that.

Hope it helps!
+1