I am not able to insert records into the database where date column is null

Hi,

Good Day.

I have a use case where I am inserting records to Oracle database, I am getting error wherever the GRN_DATE is null.

The error what I get is

Insert Records - Gr Blank Ageing: Failed to convert parameter value from a String to a DateTime.

2 records were inserted into the table at the third record it gave the above error, since the GRN_DATE is null

.

I am not sure what needs to be done in this scenario. I am attaching the source file also. Appreciate a feedback on the same.
Book1.xlsx (9.8 KB)

@manjesh_kumar

You can use the check here and assign the “Nothing” in the null coulmn.

@manjesh_kumar

You can use the check here and assign the “Nothing” in GNR number column.

Dear @Vaibhav_Rajpoot_17

I did not get your point, would be great if you can elaborate.

In principle you have only two options:
1/ modify the table in Oracle DB to allow NULL values or
2/ insert valid (i.e. not NULL) values

Cheers

@manjesh_kumar
Try to pass some Values or the Nothing to the cell whose value is null.

As Suggested by @J0ska

Hello @J0ska ,

I have allowed nullable values in the database.

image

image

you passing datetime as a string or datetime?

we would recommend to check in case of the date column is empty instead of String.Empty or Nothing use DBNull.Value

Hello Peter,

This is what I had written

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row) row(“GRN_DATE”)= if(String.IsNullOrEmpty(row(“GRN_DATE”).tostring),“”,DateTime.ParseExact(row(“GRN_DATE”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)))

we would recommend to check the empty cells by

isNothing(row(“GRN_DATE”) OrElse String.IsNullOrEmpty(row(“GRN_DATE”).toString.Trim)
and would replace with DBNull.Value

maybe you can quick test with a constructed dummy datarow

Kindly note:
DateTime.ParseExact(row(“GRN_DATE”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture) is parsing the string with the configured format
ToString(…) is outputting the same format which is expected for the parsing.
When the parse format has to be the same as the reformated format then input is equal to output, so there is no change and it is unneeded

A powerfull technique is to work with TryParseExact and all non parseable date string can be set to DBNull.Value

Dear Peter,

I have written as follows.

in_dt_GrBlankAgeingDT.AsEnumerable.ToList.ForEach(Sub(row)
If(IsNothing(row(“GRN_DATE”)) Or (String.IsNullOrEmpty(row(“GRN_DATE”).tostring.Trim))) Then
row(“GRN_DATE”) = DBNull.Value
End If
End Sub
)

Thank you very much for the support, as always . Thanks to others also to pitch in.

Regards,
Manjesh

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.