Object cannot be cast from DBNull to other types since value in Date column datarow is null

Hi

i am getting error saying : If : Object cannot be cast from DBNull to other types

Can you please help me resolve this… @Palaniyappan , @ClaytonM , @aksh1yadav

Ahtesham

1 Like

For the last condition try with this method
String.IsNullOrEmpty(“yourstring”)
This will include white space as well
@md.ahtesham

1 Like

Hi.

There’s a flaw in your condition. You are checking if it is empty after you converting it to a date, so that’s kind of pointless considering it will error if the value is not a date or empty.

What you need to do is check that it is not a date prior to converting or using it as a date. I recommend just using IsDate() or DateTime.TryParse() to verify the value is in a date format (also, trim the string in case it is only a space)

You can use AndAlso instead of And, and this will allow it to end the condition if the first part is false, therefore not throwing an error - if you use And, it will check both parts and throw an error if the value is not a date.
Something like this:
IsDate(row("Subscription End Date - Sub only").ToString.Trim) AndAlso ( Convert.ToDateTime(row("Subscription End Date - Sub only").ToString.Trim).Month <= Now.Month And Convert.ToDateTime(row("Subscription End Date - Sub only").ToString.Trim).Year <= Now.Year )

Regards

1 Like

Thanks for all the help @ClaytonM,

I tried to convert your query into Select statement to get the filtered as data table so that i can paste these data in excel file and can process each line at a time and update the status at the end.

i am getting error with this query…
retentionTemplateDataTable.AsEnumerable.Where(Function(r) (r(“Subscription End Date - Sub only”).ToString.Trim) AndAlso ( Convert.ToDateTime(r(“Subscription End Date - Sub only”).ToString.Trim).Month <= Now.Month And Convert.ToDateTime(r(“Subscription End Date - Sub only”).ToString.Trim).Year <= Now.Year )).CopyToDataTable

Can you correct me as in what is wrong in the query, so that i can get the filtered result.

Thanks ,
Ahtesham

this part, where you have r().ToString.Trim is a string and should be a condition/boolean comparison.

I suggest you just simply add IsDate() around it, so you check that the string is a date prior to comparing it as a date.

retentionTemplateDataTable.AsEnumerable.Where(Function(r) IsDate(r(“Subscription End Date - Sub only”).ToString.Trim) AndAlso ( Convert.ToDateTime(r(“Subscription End Date - Sub only”).ToString.Trim).Month <= Now.Month And Convert.ToDateTime(r(“Subscription End Date - Sub only”).ToString.Trim).Year <= Now.Year )).CopyToDataTable

Note: if your date is in the form of dd/MM rather than MM/dd, then you will need to use DateTime.TryParse() instead of IsDate()

Regards.