This is a migrated thread and some comments may be shown as answers.

Multivalue parameter with stored procedure

4 Answers 269 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Bryan Strader
Top achievements
Rank 2
Bryan Strader asked on 11 Sep 2009, 03:52 PM
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.

4 Answers, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 11 Sep 2009, 04:00 PM
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.
0
Bryan Strader
Top achievements
Rank 2
answered on 11 Sep 2009, 04:39 PM
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.
0
Bryan Strader
Top achievements
Rank 2
answered on 11 Sep 2009, 09:44 PM
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;
        }

0
Steve
Telerik team
answered on 16 Sep 2009, 12:00 PM
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.
Tags
General Discussions
Asked by
Bryan Strader
Top achievements
Rank 2
Answers by
Steve
Telerik team
Bryan Strader
Top achievements
Rank 2
Share this question
or