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

Parameterized Query Problem using where in

5 Answers 323 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Mike Booth
Top achievements
Rank 1
Mike Booth asked on 02 Sep 2009, 08:57 PM
Okay I've watched this video about parameterized queries.

I created my sqldataadapter with the following select:
SELECT     ApplicationDisplay, RoleDisplay, FirstName, LastName, EffectiveStartDate, EffectiveEndDate, EnvironmentDisplay, ApplicationId
FROM         AppUsersByRoleView
where     applicationid in (@appId)

I want to use the built-in report parameters in telerik reporting.  I created a report parameter named "Application" with a type of Integer.  (I've also tried a type of string).  It is a MultiValue parameter with the available values valuemembers being integers.  I also setup the filter to be =Fields.ApplicationId In =Parameters.Application.

Within my codebehind for NeedDataSource I have the following code.

'Transfer the ReportParameter value to the parameter of the select command    
Me.SqlDataAdapter1.SelectCommand.Parameters("@appID").Value = Me.ReportParameters("Application").Value

'Take the Telerik.Reporting.Processing.Report instance and set the adapter as   
'it's DataSource    
Dim report As Telerik.Reporting.Processing.Report = CType(sender, Telerik.Reporting.Processing.Report)
report.DataSource = Me.SqlDataAdapter1

When I run the report, NeedDataSource runs fine, but then I get an error within my reportviewer.

An error has occured while processing Report '':
Failed to convert parameter value from a ArrayList to a Int32.

Any help would be appreciated.
Thanks.

5 Answers, 1 is accepted

Sort by
0
Svetoslav
Telerik team
answered on 04 Sep 2009, 05:51 PM
Hello Mike Booth,

Just like you set the data source, you have to use the processing report to get the evaluated parameters. Use the Parameters property like this:

Dim report As Telerik.Reporting.Processing.Report = CType(sender, Telerik.Reporting.Processing.Report) 
 
Me.SqlDataAdapter1.SelectCommand.Parameters("@appID").Value = report.Parameters("Application"
 
report.DataSource = Me.SqlDataAdapter1 


Best wishes,
Svetoslav
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
Mike Booth
Top achievements
Rank 1
answered on 11 Sep 2009, 05:03 PM
After making the code changes I am still getting the same error.

The error is:  An error has occured while processing Report '':
Failed to convert parameter value from a ArrayList to a Int32.

My code behind now is.

 Private Sub MyReport_NeedDataSource(ByVal sender As System.Object, ByVal e As System.EventArgs) _
            Handles MyBase.NeedDataSource
        'Take the Telerik.Reporting.Processing.Report instance
        Dim report As Telerik.Reporting.Processing.Report = CType(sender, Telerik.Reporting.Processing.Report)

        'Transfer the ReportParameter value to the parameter of the select command    
        Me.SqlDataAdapter1.SelectCommand.Parameters("@appID").Value = report.Parameters("Application")

        report.DataSource = Me.SqlDataAdapter1
    End Sub

My report parameter has a datatype of Integer.

My filter is still =Fields.ApplicationId In =Parameters.Application

My sqldataadapter is still the same.

Thanks.
0
Steve
Telerik team
answered on 14 Sep 2009, 08:41 AM
Hi Mike,

Obviously your report parameter has MultiValue = true, which means that it would return an ArrayList. You can see how we handle such scenario in the ProductLineSales demo report - check the code in the VS examples installed with Telerik Reporting.

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
Mike Booth
Top achievements
Rank 1
answered on 14 Sep 2009, 08:54 PM
The way of doing it in the ProductLineSalesDemo report works.

The problem is you are building your sqldataadpter in your code behind on the fly and just adding a string into the commandtext.  Your example code is also not using the filters property of the report.

I would like to have my sqldataadapter built up front with the parameter embedded in it.  Then I would like to grab the users selected values out of the parameters section and apply them using the filters property of the report.

Viewing your parameterized queries video it looks like it should be possible using a parameter in your sqldataadapter.  It does work except in the case of using an "In" operator.  Can you give me a solution that works with the "In" operator.

If you watch the video and reread my posts, my problem should become apparent.

Thanks.
0
Steve
Telerik team
answered on 16 Sep 2009, 08:06 AM
Hello Mike,

The example is not using the report filters as the charts are bound through NeedDataSource and the data is filtered on before hand with that exact approach, for better performance.
On the contrary when you're using our report filters, all the data from your datasource would be initially pulled and then filtered on report level, which brings its performance toll. Anyway I've created a sample project for your convenience - please review it and let us know if further help is needed.

Greetings,
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
Mike Booth
Top achievements
Rank 1
Answers by
Svetoslav
Telerik team
Mike Booth
Top achievements
Rank 1
Steve
Telerik team
Share this question
or