Page 1 of 1
Using a list of integers as a user provided parameter
Posted: Fri Nov 22, 2013 2:28 pm
by 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.
Re: Using a list of integers as a user provided parameter
Posted: Fri Nov 22, 2013 2:42 pm
by 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)
Re: Using a list of integers as a user provided parameter
Posted: Mon Nov 25, 2013 8:01 am
by 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.
Re: Using a list of integers as a user provided parameter
Posted: Mon Nov 25, 2013 8:22 am
by 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)
Re: Using a list of integers as a user provided parameter
Posted: Mon Nov 25, 2013 8:31 am
by kgb2013
Now I have tried this:
I added this reference in the MRT file:
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.
Re: Using a list of integers as a user provided parameter
Posted: Mon Nov 25, 2013 12:54 pm
by 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.
Re: Using a list of integers as a user provided parameter
Posted: Wed Nov 27, 2013 10:56 am
by 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.
Re: Using a list of integers as a user provided parameter
Posted: Thu Nov 28, 2013 1:37 pm
by 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.
Re: Using a list of integers as a user provided parameter
Posted: Fri Dec 13, 2013 3:11 pm
by kgb2013
Thanks a lot!
That seems to work.
Re: Using a list of integers as a user provided parameter
Posted: Sun Dec 15, 2013 4:54 am
by Andrew
Hello,
Great! Have a nice weekend!