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

parameters to stored proc

5 Answers 336 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Susan
Top achievements
Rank 1
Susan asked on 13 Jun 2008, 01:56 PM

i have read numerous posts and it appears as if i am doing this correctly, but i cannot get it to work. i am trying to pass 3 parameters to a stored proc used within my report. i use a class library for my report. i can run it in the designer fine, but when trying to execute from my application i get:

"procedure or function 'rpt_unpaidregistrations' expects parameter @OrganizationKey, which was not supplied.

if i put in a breakpoint on report1, i can see my parameters, and, after this code executes, their values. what am i missing?

as another question, in the class library when you are setting up the report, there seem to be 2 places where you can specify parameters. in the report itself AND within the SQLDataAdapter. running within the class library, i don't seem to need to ones at the report level. when are these needed, if at all?

thanks...

Me.ReportViewer1.Report = New rptUnpaidRegistrations

Dim report1 As Telerik.Reporting.Report = Me.ReportViewer1.Report

report1.ReportParameters(0).Value =

CInt(currentUser.OrganizationKey)

report1.ReportParameters(1).Value =

CInt(seasonKey)

report1.ReportParameters(2).Value =

CInt(leagueKey)

Me.ReportViewer1.Report = report1

5 Answers, 1 is accepted

Sort by
0
Susan
Top achievements
Rank 1
answered on 16 Jun 2008, 02:07 PM
Has anyone had a chance to look at this? I am stuck until I can get the parameters working.

thanks again.
susan
0
Milen | Product Manager @DX
Telerik team
answered on 19 Jun 2008, 09:42 AM
Hi Susan,

There is a KB article you will find very helpful describing step by step instructions on this scenario.

Try it and let us know if you need further assistance.

All the best,
Milen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center
0
Susan
Top achievements
Rank 1
answered on 22 Jun 2008, 03:43 AM
I have looked over that article, but still cannot get it to work.

i'm trying to pass a parameter 'organizationkey' to my stored procedure. i have the report setup in its own class library. i added this code to the report:

Public Property OrganizationKey() As Integer

Get

return SqlDataAdapter1.SelectCommand.Parameters(0).Value

End Get

Set(ByVal Value As Integer)

SqlDataAdapter1.SelectCommand.Parameters(0).Value = Value

End Set

End Property

Then in my project, i add a reference to the code library and this code:


Me.ReportViewer1.Report = New rptUnpaidRegistrations

Dim report1 As Telerik.Reporting.Report = Me.ReportViewer1.Report

report1.ReportParameters(0).Value =

CInt(currentUser.OrganizationKey)

when i run it, i still get


Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Note that i seem unable to do this:
report1.organizationkey = cint(currentUser.organizationkey)

I would think i should be able to as this is defined in the report. maybe this has something to do with it, but i can't seem to get it to work.

thanks!
susan

0
Accepted
Milen | Product Manager @DX
Telerik team
answered on 23 Jun 2008, 12:33 PM
Hi Susan,

Looking at the code snippets you provided, I assume that you are trying to follow the steps from the video lesson Design Time Support for Parameterized Queries. You have done almost everything right.

You have taken the right decision to not use the report parameters provided by the Telerik Reports, since you do not want to use the automatically generated UI, but to pass the values to the stored procedure from outside of the report.

You are not able to access the public property you defined in the report definition, because you are using a reference to the base type Report, when you should use reference of type rptUnpaidRegistrations (your report type), where the property is defined.

Something like:

Dim report1 As rptUnpaidRegistrations = new rptUnpaidRegistrations()
report1.OrganizationKey = CInt(currentUser.OrganizationKey)
Me.ReportViewer1.Report = report1

Furthermore, if you have already set the report of the viewer to
rptUnpaidRegistrations report at designtime, instead of the latter code you can use:

Dim report1 As rptUnpaidRegistrations = CType(Me.ReportViewer1.Report, rptUnpaidRegistrations)
report1.OrganizationKey = CInt(currentUser.OrganizationKey)

About the public property that propagates the passed value to the corresponding SP parameter:
It is a good practice to index the SP parameter by name, not by index, because the data adapter wizard adds @RETURN_VALUE parameter to the collection of parameters of the select command, and getting the parameter by index could lead to unexpected result.

Last thing  to clarify: You are trying to set the value you want to pass to the procedure to the first item in the report1.ReportParameters collection. Notice that there are multiple Telerik report parameters, the same as the parameters of the datasource you are using, and that is why you are receiving "Index out of range" exception - because there are no report parameters defined in your report at all. You can find more information about report parameters in this help article.

Let us know if you need further assistance.

Sincerely yours,
Milen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center
0
Susan
Top achievements
Rank 1
answered on 24 Jun 2008, 12:12 AM
I have it working thanks to you! thanks so much.

susan
Tags
General Discussions
Asked by
Susan
Top achievements
Rank 1
Answers by
Susan
Top achievements
Rank 1
Milen | Product Manager @DX
Telerik team
Share this question
or