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

Help
, ,
1

Hi,

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?

2

@mbalaji1985

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

1 Like
3

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

1 Like
4

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

Cheers @mbalaji1985

1 Like
5

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.

6

Adding CopyToDatatable at the end didn’t work either.

7

@mbalaji1985
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?

3 Likes
8

@mbalaji1985
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

2 Likes
9

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
10

@mbalaji1985
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

11

@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))

12

please check Updated post
use only the x.SetField method

13

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)))

14

@mbalaji1985
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

15

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

16

try below code for Date Conversion

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

1 Like
17

@mbalaji1985
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
18

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) 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!

1 Like
19

Thanks @Vara, your code worked for me too.

20

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