SQL Query error in Assign Activity

Help Studio
,
1

Hi All,

I have query in assign activity and between that query I need to insert EmpID’s (I have saved in String), how we can do this?

Not sure where exactly I am doing wrong here. Below is what I tried:

1: Reading all ID’s from CSV. storing in csvDt
2: I am storing all ID’s in var = ListIDs of datatype → ListofString using below expression:
csvDt.AsEnumerable().Select(Function(row) Convert.ToString(row(“ID”))).ToList()

Getting o/p as below: ListIDs =

Listofstring
{
“A6556”,
“E4359”,
“Y2424”,
“P5252”
}
3: Idstring is of string datatype where I am storing values: ‘A6556’,‘E4359’,‘Y2424’,‘P5252’
using this expression: “‘” + String.Join(“‘,’”, ListIDs) + “’”

O/p:
‘A6556’,‘E4359’,‘Y2424’,‘P5252’

4: Now,
arr_ids = String.Join(“,”, Idstring.Select(Function(x) “‘” & x.Trim & “’”))

arr_ids is of datatype String → And I am getting error in this assign activity expression (which is arr_ids).

image
image1636×318 28.4 KB

Also, I tried below:

1.Read IDs from CSV into ListIDs:

ListIDs = csvDt.AsEnumerable().Select(Function(row) row(“ID”).ToString).ToList()

2.Converted arr_ids to string for SQL:

arr_ids = String.Join(“,”, ListIDs.Select(Function(x) “‘” & x.Trim & “’”))

Result: ‘A6556’,‘E4359’,‘Y2424’,‘P5252’

3.Used arr_ids inside my query:

“… WHERE EMPLOYEES.ALTER_EMPLID IN (” + arr_ids + “) …”

But still getting error something like “Argument value :compiler error”.

Nutshell: I want to insert list of ID’s (Eg: ‘A6556’,‘E4359’,‘Y2424’,‘P5252’, etc) between the SQL query.

Thank you

BR,
NK

2

Hi @Nitesh

i hope i understand your question right please try the below steps and update us if there is any error:

At first we will read all IDs from the CSV file:

ListIDs = csvDt.AsEnumerable().
        Select(Function(row) row("ID").ToString.Trim).
        ToList()

Then we will make the arr_ids ready to be used in SQL query:

arr_ids = String.Join(",", ListIDs.Select(Function(x) "'" & x & "'"))

At Last the SQL Query to be used:

"SELECT * FROM EMPLOYEES WHERE EMPLOYEES.ALTER_EMPLID IN (" & arr_ids & ")"
3

Hi @mahmoud.zaky ,

I am still getting the same error.

And just wanted to confirm you that above expression of arr_ids I have kept of string datatype.

Thanks.

4

@Nitesh I replicate your scenario with @mahmoud.zaky suggestion in a particular database and it worked.

I think you are using Curly quotes instead of Straight quotes, try to fix and then should work

Straight quotes

String.Join(“,”, ListIDs.Select(Function(x) “'” & x & “'”))

Curly quotes

String.Join(“,”, ListIDs.Select(Function(x) “‘” & x & “’”))

1 Like
Forum Engagement Daily Reports
5

Hi @Camila_Caldas ,

I am using straight quotes. Please find below screenshot.

image

image

BR,
NK

6

Hi @Nitesh
can you share with us sequence file.

7

Hi @mahmoud.zaky ,

Apologies, I cannot as it contains sensitive data.

8

What is the error? Hover over the red exclamation mark in your expression.

9

Hi, @sven.wullum1

Here is the screenshot. There are 112 lines of SQL code but sending you the below one, hope it helps. Thank you.

image
image760×197 56.2 KB

image
image733×130 11.7 KB

10

Hi All,

Thanks guys for your support.

I found the solution.

The error is because of the line break and not with the variable passing.

Hence, I removed the lines and make it in 1 single line all SQL code (that was too painful to bring it in 1 line manually but at the end it worked).

Thank you @arjun.shiroya for giving me idea. Much appreciated.

11

In the future you could hover over the red exclamation mark here to see the error:

image
image795×137 18.3 KB

1 Like
12

You were getting a compiler error because you tried to do Select() on a String, not on the list of IDs. The correct approach is simple:

  1. Read IDs from CSV into a list:

ListIDs = csvDt.AsEnumerable().Select(Function(r) r(“ID”).ToString.Trim).ToList()

  1. Convert the list into a SQL-friendly string:

arr_ids = String.Join(“,”, ListIDs.Select(Function(x) “'” & x & “'”))

This produces:

‘A6556’,‘E4359’,‘Y2424’,‘P5252’

  1. Insert arr_ids directly into your SQL query:

“… WHERE ALTER_EMPLID IN (” & arr_ids & “) …”

The problem happened because you were re-processing an already-formatted string (Idstring), causing UiPath to throw a compiler error. The fix is to format the list only once and use it directly in the SQL query.

13

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