Multivalue parameter with stored procedure

[Bryan Strader]

I have a report that needs to use a multivalue parameter with a stored procedure, yet I am getting an error when I select multiple values because it comes back as an array when the report doesn't seem to be expecting it.

Does anybody have an example where they are using a stored proc with a multi value parameter?

Thanks in advance.

[Steve]

Hello Bryan,

Take a look at the following forum thread for more information. As we've been on the market for a while, it is highly likely that somebody else has encountered your problem, so please search our site before posting an inquiry. Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

Best wishes,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Bryan Strader]

Hi Steve, thanks for the reply. In the forum you directed me too, I see that you are converting the parameters that comeback as an arraylist to a string, where is the best place to call the function that converts them...? I tried converting them in the need datasource event, but it looks like they still are comparing as an arraylist and not as strings....? Thanks.

[Bryan Strader]

This is the error I am getting:

An error has occured while processing Report '':
Cannot perform '=' operation on System.String and System.Collections.ArrayList.


Even though I am using the same FormatArray() method that was posted in the example:

        private void MyReport_NeedDataSource(object sender, EventArgs e)
        {

            this.sqlDataAdapter1.SelectCommand.Parameters["@0"].Value = this.ReportParameters[0].Value;
            this.sqlDataAdapter1.SelectCommand.Parameters["@1"].Value = this.ReportParameters[1].Value;
            this.sqlDataAdapter1.SelectCommand.Parameters["@2"].Value = FormatArray((ArrayList)this.ReportParameters[2].Value);
            this.sqlDataAdapter1.SelectCommand.Parameters["@3"].Value = FormatArray((ArrayList)this.ReportParameters[3].Value);
           
            //Instantiate report object, and box the sender as the report.
            Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;
       
            //Set the datasource to the SQL Adapter.
            report.DataSource = this.sqlDataAdapter1;
        }

[Steve]

Hi Bryan,

This is expected as if you look at the FormatArray function, you would notice that it returns the array members separated by comma. Obviously this is not a single value, which is expected and you should modify your select statement as well i.e. the WHERE part should be handled with an IN operator e.g.

before:
WHERE CustomerID = @CustomerID

should be:

WHERE CustomerID IN (@CustomerID)

The easiest way would be to generate the sqlDataAdapter CommandText in code like shown in our ProductLineSales report for binding the charts.

Kind regards,
Steve
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.