parameters to stored proc

6 posts, 1 answers
  1. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 13 Jun 2008 Link to this post

    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

  2. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 16 Jun 2008 Link to this post

    Has anyone had a chance to look at this? I am stuck until I can get the parameters working.

    thanks again.
    susan
  3. DevCraft banner
  4. Milen
    Admin
    Milen avatar
    163 posts

    Posted 19 Jun 2008 Link to this post

    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
  5. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 21 Jun 2008 Link to this post

    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

  6. Answer
    Milen
    Admin
    Milen avatar
    163 posts

    Posted 23 Jun 2008 Link to this post

    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
  7. Susan
    Susan avatar
    137 posts
    Member since:
    Jul 2007

    Posted 23 Jun 2008 Link to this post

    I have it working thanks to you! thanks so much.

    susan
Back to Top
DevCraft banner