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)
You can use the check here and assign the “Nothing” in the null coulmn.
You can use the check here and assign the “Nothing” in GNR number column.
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
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.
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
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
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
I have written as follows.
If(IsNothing(row(“GRN_DATE”)) Or (String.IsNullOrEmpty(row(“GRN_DATE”).tostring.Trim))) Then
row(“GRN_DATE”) = DBNull.Value
Thank you very much for the support, as always . Thanks to others also to pitch in.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.