Datatable.select not recognizing '**'

datatable
selector

#1

Hi. I’m trying to make a select on a datatable. The selector is like this: “[Column1] = ‘**’”

Seems preety simple but I’m getting an error.

Assign : Cannot perform ‘=’ operation on System.Double and System.String.

And i’ve already tried to switch the operand to LIKE but it doesn’t work anyway. Does anybody know how to solve that? Thank you all!


#2

To me, it’s easier to manipulate the table with AsEnumerable and .Where
So alternatively you can do this:
dt.AsEnumerable.Where(Function(row) row(1).ToString = “**”).ToArray

And you can use all the functions and operators like AND, OR, .Contains, .StartsWith, et cetera.

Regards.


#4

I don’t understand it very well to be honest. But i’ve tried that piece of code and the return is not what I’ve expected. It returns the right line(the one that I want) and another one (the next one in the original dt). Any idea why? Sorry but I never manipulated the datatable with AsEnumerable and .Where so I may be a little “rookie” on that.


#5

Sure I can explain it better, hopefully.
If you do a google search for LINQ or lambda you will find expressions like this in either C# or vb.net (UiPath uses vb.net).

To explain it simply, it goes through each item in the List whether it’s datarows in a datatable or items in an array, which both work.
.Where(Function(row) … ) tells it to go through each item using the variable name of your choice (in this case I used “row” as my own choice)

and only keep the items that meet your specified criteria like row(1).ToString = “**”

Then you end with either .ToArray or .CopyToDataTable to set the list back to your desired type.

It should only find the rows that meet your criteria condition, so if it found something wrong then check your data or condition.

If there are multiple rows that meet your condition and you only want the first one, then add (0) on the end and it will only take 1 datarow.

Regards.


#6

Hi. I can understand it better now and I’m very very thankful to you. I have only one doubt now --> the number inside the row item means what? Like, if you have 10 rows that match the criteria then you must use “(10)” ? It doesn’t seem very good to me bc I may not know the number of returns in the selector. But anyway, you had been such a great help till now in every question that I make. Thank you Clayton!


#7

Hi,
The number inside the parenthesis, like row(1) or row(0) means the columnIndex of that row, 0 being the first column. You can also use the column Header like row(“Column1”). Then you just need .ToString on the end, and I also recommend .Trim incase there are trailing spaces.

Regards.


#8

Oh nice! I’ve understand it one hundred % now. Thank you. When I have this selector:
**

dtOutCopy.AsEnumerable.Where(Function(row) row(“Column1”).ToString = “**” and row(“Column2”).ToString = “”).CopyToDataTable

**

I have an error message wich says “The source contains no datarows”. I am sure that is because I’m trying to search for empty values on the Column2. The question is: I really want to search for empty values. Do you have idea how to solve this???


#9

Hi,
Yeah, your condition is correct to find “**” in first column and empty value in second column. But, I would also add .Trim to make sure you remove extra spaces.
dtOutCopy.AsEnumerable.Where(Function(row) row(“Column1”).ToString.Trim = “**” and row(“Column2”).ToString.Trim = “”).CopyToDataTable

If it doesn’t find any datarows then you will need some extra code, like:
If dt.Rows.Count=0
or if it is an array by using .ToArray (instead of .CopyToDataTable)
If arrDt.Count=0

Then you can skip the part where you try to use an empty datatable.

Hope that helps!

Regards.