System.Data.SyntaxErrorException: Syntax error: Missing operand before 'In' operator

Hello,

Can I have solution for below error?
System.Data.SyntaxErrorException: Syntax error: Missing operand before ‘In’ operator.

Code: Dt_keyword3.Select(“In <> Null or In > 0”).CopyToDataTable
FYI. column name is ‘In’


An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Data.SyntaxErrorException: Syntax error: Missing operand before ‘In’ operator.
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression…ctor(DataTable table, String expression, Type type)
at System.Data.DataTable.Select(String filterExpression)
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

Thank you !

Hi @Dorothy_lee

What are you trying to achieve with the below code

Dt_keyword3.Select(“In <> Null or In > 0”).CopyToDataTable

Hello,

I’m trying to exclude Null and 0 value from this datatable !

2021년 8월 28일 (토) 오후 3:10, Varun Kumar via UiPath Community Forum <uipath@discoursemail.com>님이 작성:

Hi @Dorothy_lee

Try This

image

Dt_keyword3.
AsEnumerable.
Where(
	Function (r) r.ItemArray.All(Function(item) Not (item.ToString.Equals(String.Empty) Or item.ToString.Equals("0")))
		).CopyToDataTable

This will remove all the rows from the Data Table which contains one or more empty columns

2 Likes

@Dorothy_lee

It should be like this.

      Dt_keyword3.Select(“In <> 'Null' or In <> '0'”).CopyToDataTable
1 Like

Thank you for your reply !
It worked other column but not working for this ‘In’ column. Really strange…

@kumar.varun2 Thank you for your helps. It works properly with your code !
but could you please help for this error as well?
I’d like to store this Dt_keyword3 to Array variable but it doesn’t work with below error.
Is there any solution of it ?
Code: Dt_keyword3.AsEnumerable().Select(Function(a) a.Field(Of Double)(“In”).ToString).ToArray

@Dorothy_lee

Could you share sample excel file which reading into data table (Dt_keyword3)?

1 Like

Masterfile_Sample - Copy.xlsx (18.1 KB)
This is sample file but originally it’s .xlsm extension !
(Unable to update xlsm file)

@Dorothy_lee

What is you requirement?
Array of the column “In”.
Do you want it to be array of string or array of double?

I want column “in” to be Array of String.

Once it to be Array of String, I’ll utilize it as below.

(From d In Dt2_HSBC_001.AsEnumerable
Let chk1 = St_Arr_keyword.Any(Function (x) d(“설명”).toString.Trim.Contains(x))
Let chk2 = St_Arr_keyword2.Any(Function (y) d(“입금 금액”).toString.Trim.Contains(y))

Let chk3 = St_Arr_keyword3.Any(Function (y) d(“출금 금액”).toString.Trim.Contains(y))

Where {chk1,chk2,chk3}.All(Function (b) b)
Select r=d).CopyToDataTable()

@Dorothy_lee

Try this

Dt_keyword3.
AsEnumerable.
Where(Function (r) Not CDbl("0"+r("In").ToString).Equals(0)).
Select(Function (fr) fr("In").ToString).
ToArray

This will give an array of the “In” Column with all the zeros and the null values removed
If you do not want to remove the null and zero values, then use this

Dt_keyword3.
AsEnumerable.
Select(Function (r) r("In").ToString).
ToArray
1 Like

@kumar.varun2 Really appreciate ! Works !!! :slight_smile:

1 Like

@Dorothy_lee
shifting to the LINQ is fine. Just a feedback why the initial issue occured:

In is a keyword from the search syntax. With the same Columnname it was conflicting. We can explicit mark the column name and the select expression is working:

Dt_keyword3.Select("[In] <> Null or [In] > '0'”).CopyToDataTable

2 Likes

@ppr

If we use the filter exrpression like this

Dt_keyword3.Select("[In] = '0'")

then the output contains no rows

But if we modify the expression like this

Dt_keyword3.Select("[In] = '0' [In] <> Null")

then the output contains all the rows where “In” Column is zero

Why did the first expression not work?

we can analyze it further, but on first action I would suggest to include the conjuction operator And / Or e.g.

Dt_keyword3.Select("[In] = '0' Or [In] <> Null")

https://www.csharp-examples.net/dataview-rowfilter/

@ppr

Without conjunction, it is working on other columns. But on the “In” column it is not.

Thanks @ppr

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