Datatable select

Help Activities
, ,
1

Hi Team,

I am storing some values in a dt which contains multiple row values say
Regis No, Roll No
111 abc123
222 xyz456
333 yyy123

i am getting an input say 333 and yyy123.
i want to check whether this value is matched with the values in the dt
i need a basic select statement in assign to perform this.

Thanks

2

Hi

Hope this expression will help you resolve this

Use a assign activity like this with a Boolean variable in right side

bool_exists = dt.AsEnumerable().Any(Function(a) a.Field(of String)(“Regis No”).ToString.Contains(“333”) AND a.Field(of String)(“Roll No”).ToString.Contains(“yyy123”))

Cheers @Robotics

3

Getting error - Unable to case object of type System.Double to type system.string

4

Hi @Robotics ,
try below query
dt.Select("[Regis No]='333' and [RollNo]='yyy123'").length>0

Regards,
Arivu

5

in addition to

dt.AsEnumerable().Any(Function(x) x("Regis No").ToString.Contains("333") AND x("Roll No").ToString.Contains("yyy123"))

making it very strict

dt.AsEnumerable().Any(Function(x) x("Regis No").ToString.Trim.Equals("333") AND x("Roll No").ToString.Trim.Equals("yyy123"))

the Field(of xx…) method is bound to the datacolumn datatype and can do a cast. thats why you got the exception double string casting

6

Correct me if i am wrong.
Storing your result code in a boolean variable
Expected is , if the below value is found in dt then it should return true
333 yyy123
Any corrections to be done ?

7

The above query will return true or false. if value present will return true.

Regards,
Arivu

8

This [Regis No] and [Roll No] should be the exact column name in excel?
Returns True for all rows it should return true only for 3rd row

or i need replace Regis No as row(0) and Roll No as row(1) ?

9

@Robotics

Yes you need to provide the column names exactly as per your input file and also it should be case sensitive. Else you can provide column index instead of column name.

Eg. Regis No column is first column then pass index as 0.

Try below expression in IF activity.

dt.AsEnumerable().Where(Function(row) row(0).ToString.Equals(“333”) AND row(1).ToString.Equals(“yyy123”)).Length > 0

If matching rows found then above expression will result True else False.

10

No irrespective of row(0) or the excel column its returning true for all rows

11

@Robotics

Above expression will check entire DataTable and then will result True if atleast one matching row found else False.

If you want to check one by one row then follow below steps.

            ForEach row in dt
               If row(0).ToString.Equals(“333”) AND row(1).ToString.Equals(“yyy123”)
               Then
                    Matching row found
                Else
                    Matching row not found
12

Working as expected, thanks all

1 Like
13

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