Using a list of integers as a user provided parameter

[kgb2013]

Hello.

I am trying to use a list of integers as a user provided parameter.
I have a query like this:

Code: Select all

SELECT *
FROM TableName
WHERE Id IN (@LST)

I have defined a list of integers variable and I see a dropdown with checkboxes on the user variable definition area.
However, I don't know what type my query parameter should be to accept this variable.
I have tried many of them but nothing has worked yet.

I have found this : http://forum.stimulsoft.com/viewtopic.php?t=1421
..but I need to get the list from the user, not the code.

Thanks.

[kgb2013]

I have thought of using a "Text" parameter and set its value to:

Code: Select all

string.Join(",", LST.Select(x => x.ToString()))

..but I guess this will be wrapped around double quotes in the actual SQL query...

(LST is the name of my list variable and the name of my parameter too)

[kgb2013]

I have tried using it like that:

Code: Select all

SELECT *
FROM TableName
WHERE Id IN ({LST})

..and removing the parameter, while leaving only the variable, but I get this error:

The multi-part identifier "Stimulsoft.Report.IntList" could not be bound.
Statement(s) could not be prepared.

[kgb2013]

OMG! Yes!
I get this error because the SQL query is this:

Code: Select all

SELECT *
FROM TableName
WHERE Id IN (Stimulsoft.Report.IntList)

!!!

How do I actually put the values in there?
Sth like that:

Code: Select all

SELECT *
FROM TableName
WHERE Id IN (1,2,3,4)

[kgb2013]

Now I have tried this:

I added this reference in the MRT file:

Code: Select all

System.Linq.Dll

And turned the SQL query into that:

Code: Select all

SELECT *
FROM TableName
WHERE Id IN ({string.Join(",", LST.Select(x=>x.ToString()))})

But I get an error:

Could not load file or assembly 'System.Linq.Dll' or one of its dependencies. The system cannot find the file specified.

Should I copy the DLL somewhere for it to find it?

I use the Designer.exe to save the referenced assemblies in the MRT.
I see that the referenced assembly is saved correctly when I load the report (see image below), but it doesn't find the DLL.

[Alex K.]

Hello,

It is not possible to use the Linq construction in SQL query.
Can you please send us your report for analysis.

Thank you.

[kgb2013]

OK. Here is the file (although I am not sure what you could find from it).
Basically, what I want is to put in the SQL query the list of intergers stored in the user-provided variable "DocType".
It would be nice if you could help me with this.

Thanks.

[Alex K.]

Hello,

Please try to use the following query:

Code: Select all

SELECT * FROM MyTable WHERE Id IN ({DocType.ToQueryString()})

Also please set the RequestParameters property of report to true, the ConnectOnStart property for Ekkrem datasource to false and in BeforePrint event use the following code:
Ekkrem.Connect();

Thank you.

[kgb2013]

Thanks a lot!
That seems to work.

[Andrew]

Hello,

Great! Have a nice weekend!