Datatable column update using Foreach in lambda expression is throwing an error


I am using the below expression and trying this is in Assign activity -
Datatable.Select(“Shift=‘MSP’”).ToList().ForEach(Sub(row) row(“Tag_Shift”)=“MSP-MOB”)

but I am getting a compile error “Expression does not produce a value”.
Not sure why. Can anyone help on this?


Use Invoke Code activity instead of Assign activity and then try once.

1 Like

Thanks @lakshman for the quick response. But I am still receiving the same error

1 Like

Kindly try with this expression
datatable = datatable.AsEnumerable().Where(Function(a) a.Field(of string)(“Tag_Shift”).ToString.Equals(“MSP-MOB”)).CopyToDatatable()

Cheers @mbalaji1985

Thanks @Palaniyappan for your response. But I am trying to set the value for the entire column and not trying to compare. I am looking to update an entire column of a datatable with a single update without using ForEach activity.

Adding CopyToDatatable at the end didn’t work either.

You have written a perfect title for this topic. i will have a Look on this issue. give me some little time and I will revert to you with Feedback.

For solution verifying Demo Data in Form of description or Excel is Always helpfully. Do you have any for us?


PFA demo xaml based on some sample data:
mbalaji1985.xaml (7.5 KB)

Let us know your feedback. Please mark the solving post as solution. So others can benefit from it


Hi @ppr thanks for the demo xaml. The compile time error is gone now. I have also realized that we should only use Invoke Code activity instead of Assign activity(Thanks to @lakshman too).

I am now facing a different problem. Which I am trying to resolve. Would be great if yourself or anyone could pitch in for that too.

Background -
I am trying to use this logic suggested to convert a string column have DateTime values in Datatable to custom Date format. But I am getting an error in the Invoke Code activity

Code -
dtDateFormatColumn.AsEnumerable().ToList().ForEach(Sub(x) x.SetField(“Start Date”, CDate(x(“Start Date”).ToString()).ToString(“M/d/yyyy”)))

I have tried the below code as well -
DataTable.AsEnumerable.ToList.ForEach(Sub(x) x(“Start Date”)= CDate(x(“Start Date”).ToString).ToString(“M/d/yyyy”))

Error Message -
An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ----> System.InvalidCastException: Conversion from string “” to type ‘Date’ is not valid.
at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value)

The error seems to point to the Date conversion, but when I tried for a sample data, it works like a charm -
CDATE(“1/1/2019 12:00:00 AM”).ToString(“M/d/yyyy”)

Any suggestion would be much appreciated!

1 Like

a first rough guess, Try to Use Date.ParseExact Method instead of CDAte
Example: Date.ParseExact(YourDateString,“YourInputDateFormatString”, CultureInfo.InvariantCulture).ToString(“M/d/yyyy”)

CultureInfo.InvariantCulture can be replaced with specific Culture e.g. For France new CultureInfo(“fr-FR”)
ensure to have imported System.Globalization into the namespaces

@ppr, Date.ParseExact didn’t help. I am getting the same error. I am not sure what is the root cause.

I tried -
DataTable.AsEnumerable().ToList().ForEach(Sub(x) x(“Start time”)= Date.ParseExact(x(“Start time”).ToString(),“M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture))

please check Updated post
use only the x.SetField method

Yup. I am trying both the options everytime -
DataTable.AsEnumerable().ToList().ForEach(Sub(x) x.SetField(“Start time”, Date.ParseExact(x(“Start time”).ToString(),“M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture)))

if it is rot running, please send me sample data string and i will try it on my end for you
Take care about this: x.SetField (Of String) (“Start time”, D

1/1/2019 12:00:00 AM,
2/1/2019 12:00:00 AM
1/1/2020 12:00:00 AM
2/1/2020 12:00:00 AM

Need to have them in a column of a datatable and then we should try and change their format

try below code for Date Conversion

(Date.Parse(x(“Start time”).ToString(),System.Globalization.CultureInfo.InvariantCulture)).ToString(“M/d/yyyy”)

Have a look on edited XAML here:
mbalaji1985_II.xaml (7.7 KB)
please crosscheck the formats e.g. day and month are in the right order

Date conversion were finally done with:
r.SetField(Of String)(“Start time”,Date.ParseExact(r(“Start time”).ToString(),“M/d/yyyy hh:mm:ss tt”, CultureInfo.InvariantCulture).ToString(“M/d/yyyy”)

1 Like

Hi @ppr,

Thank you so much for your help. And sorry about the delayed post. Your xaml was working fine when I ran it individually but it was not working when i formed the datatable from excel source.

Not sure why, but only the below code seems to working for me -
DataTable.AsEnumerable().ToList().ForEach(Sub ® r.SetField(Of String)(“Start time”, Date.Parse(r.Item(“Start time”).ToString(), CultureInfo.InvariantCulture).ToString(“M/d/yyyy”)))

I don’t think I would have been able to fix this without your help. So, thanks a ton for all your help!

Thanks @Vara, your code worked for me too.

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