Filter Data Table int and Str conditions in one line


#1

Hi,

When I try to filter with 2 conditions (1 string, 1 int), then I get an error saying
“disallow implicit conversions from String to Long”

If I use 2 assigns, they work. But I am just curious if there a way to put them in one Assign?

I try to put .ToString after the int but won’t work. Thanks

nDT = dt.Select("[Open]>0" AND “[Reason code] =”+"’"+ReasonCode+"’" ).CopyToDataTable


#2

I’m not sure; you’re syntax might be wrong. Try placing the AND inside the quotes as part of the string.

Alternatively, you can reformulate your conditions in a lambda expression with this syntax (which I find easier to understand):

dt.AsEnumerable.Where(Function(row) If(IsNumeric(row("Open").ToString.Trim), CInt(row("Open").ToString.Trim)>0, False) And row("Reason code").ToString.Trim=ReasonCode ).CopyToDataTable

#3

@lavint

Try below code

nDT = dt.Select("[Open]>0 AND [Reason code] =’"+ReasonCode+"’" ).CopyToDataTable


#4

Nope~ it said: Cannot find column “Z67” which is the string that I put in the variable ReasonCode. Thanks Though :slight_smile:


#5

Hi @ClaytonM,

It works Perfectly! Thank you very much!
I am trying to understand AsEnumerable - Search a bit: it said it’s LINQ to Object and it’s on the client side.
I tried to remove AsEumerable in the code that you provided and it said ‘Where is not a member of System.Data.DataTable’.
So… I assume .AsEnumerable change the type of dt into object. And based on this dt object, it processes the conditions. Is this a correct understanding? If not, could you kindly give a brief explanation why we need to use Enumerable or IEnumerable? Thanks!

Regards,
Lavina


#7

The way I understand it is that LINQ methods can only work on “countable” objects such as Arrays, Lists, and Enumerables (which is a synonym for countable). So, that’s why Data Tables need to be converted to a countable format first, and when you do it understands the countable objects as the Data Rows within the table.

Regards.


#8

Got it! Thanks a lot :smile:


#9

@lavint Maybe due to space after/before Z67. In that case copy header name from excel and paste in the query. it will will work.