locked
Passing Parameters to Stored Procedure in Report

    Question

  • I have created a non-embbeded crystal report in VS2005 that calls

    a stored procedure. The procedure needs an input parameter. As usual, the

    report's parameter has the same name as that of the stored procedure i.e.

    @ParaOne. In my code I pass a value to parameter @ParaOne but the

    program complains that the stored procedure's parameter has not been assigned

    a value. Please, assist me on how I can get the procedure fired up. Part of the code

    is shown below:

    customersByCityReport = New ReportDocument()

    Dim reportPath As String = Application.StartupPath & "\" & "MOTORS LIMITED.rpt"

    customersByCityReport.Load(reportPath)

    Dim currentParameterValues As ParameterValues = New ParameterValues()

    Dim myParameterDiscreteValue As ParameterDiscreteValue = New ParameterDiscreteValue()

    myParameterDiscreteValue.Value = "Holland"

    currentParameterValues.Add(myParameterDiscreteValue)

    Dim myParameterFieldDefinitions As ParameterFieldDefinitions = customersByCityReport.DataDefinition.ParameterFields

    Dim myParameterFieldDefinition As ParameterFieldDefinition =

    myParameterFieldDefinitions("@ParaOne")

    myParameterFieldDefinition.ApplyCurrentValues(currentParameterValues)

    myCrystalReportViewer.ReportSource = customersByCityReport

    SetDBLogonForReport(myConnectionInfo, customersByCityReport)

    Friday, March 02, 2007 1:41 PM

Answers

  • You could reduce your code by making a call to
    customersByCityReport .SetParameterValue("@ParaOne", "Holland");

    Then you can get rid of all the code having to do with ParameterFieldDefinition.  I've used both methods and for now I'm using the simple command.  I'm not sure what the difference is but I suspect they use different objects. 

    I hope this helps. 
    Friday, March 02, 2007 2:25 PM
  • After a little more searching I found a post that has the answer... thought other's would want to know. 

    Apparently, if you have a stored procedure with parameters that you're trying to set in code and you're doing the "SetDBLogonForReport" thing where you're changing the connection information for the report... this posting fixed the problem beautifully...

     

    http://www.codecomments.com/Visual_Basic_Crystal_Reports/message690985.html

    Tuesday, May 01, 2007 8:01 PM

All replies

  • You could reduce your code by making a call to
    customersByCityReport .SetParameterValue("@ParaOne", "Holland");

    Then you can get rid of all the code having to do with ParameterFieldDefinition.  I've used both methods and for now I'm using the simple command.  I'm not sure what the difference is but I suspect they use different objects. 

    I hope this helps. 
    Friday, March 02, 2007 2:25 PM
  • Thank you as your suggestion has assisted me in shortening the code. However, what I really need is for the parameter that is passed to the report, to act as an input parameter to a stored procedure that is referenced by the report. The way the report works is that it calls an SQL stored procedure to get its data, and it has to pass a parameter to this procedure.
    Monday, March 05, 2007 6:02 AM
  • That code, that single statement, should work for the situation you've described.  Most of my reports also use Stored Procedures (SPROCs) as their data source and the SPROC takes a parameter, which the report passes along to the database. 

    If the code is not working for you then there must be something else interfering with it.

    I hope that helps.
    Monday, March 05, 2007 1:48 PM
  • I am comparitively a newbie for CR

    but what i intend to do is something like, passing a parameter to the Crystal Report, which basically retrieves the data from a set of tables and views,

    and amongst those tables, theres a column on the basis of which the grouped.

    the data of this column is the source of values for the Stored Proc.

     

    I have tried all the ways i could think of, with no luck at all ofcourse...

    I guess someone might have tried this.

    Would appreciate it if you could share this info. on weather its possible. (Always good to ask before loosing all hopes)

    and if yes, a little guidance in the right direction.

     

    Friday, March 09, 2007 5:46 AM
  • I'm not sure how you can use a column in a table as a parameter for a report or a stored procedure.  You might want to start a new thread and provide some more details.  There may be a way to do it, but I can't think of one at the moment.
    Sunday, March 11, 2007 4:16 AM
  • Thanks Crystal...

    Will Do.

    Monday, March 12, 2007 10:54 PM
  • ok i have two reports. The first works fine. On the first report, their is a hyperlink action to jump to report: report#2. The action also has a parameter (called ACP). It pass a two character varchar

    The second report has a report parameter called ACP. This parameter is needed to send to a store procedure. The store procedure needs a varchar(2).

    I have tried

    EXEC sp_workschedule2("@ACP") - syntax error

    EXEC sp_workschedule2 ACP -  gives me a blank table

    EXEC sp_workschedule2 @ACP - Must declare the scalar variable "@ACP"

    Please help..

    THanks

    Thursday, March 15, 2007 3:36 PM
  • I'm experiencing the exact same problem... and the solution didn't work for me either.  Did you ever figure it out?
    Tuesday, May 01, 2007 7:29 PM
  • Where are you calling the stored procedure from?  If you use the stored procedure as your data source, you shouldn't need the EXEC.  When the report is opened, it will automatically execute the stored procedure.  You pass the parameter to the report because the stored procedure will need it. 

     

    So, I'm not sure what your setup is.  Sorry, I may not have read carefully enough. 

    Tuesday, May 01, 2007 7:43 PM
  • After a little more searching I found a post that has the answer... thought other's would want to know. 

    Apparently, if you have a stored procedure with parameters that you're trying to set in code and you're doing the "SetDBLogonForReport" thing where you're changing the connection information for the report... this posting fixed the problem beautifully...

     

    http://www.codecomments.com/Visual_Basic_Crystal_Reports/message690985.html

    Tuesday, May 01, 2007 8:01 PM
  • When using MS Sql Server Reporting Services us the folloiing format:

     

    EXEC dbo.sp_workschedule2 @para1

     

    you have to be sure to name the parameters with the same names as the ones in the store procedure. In the example above para1 is the name of the parameter. And "@" tells sql that it is a parameter.

    Wednesday, May 02, 2007 12:59 PM
  • HI, good day.

     

    I need your help, how do I design the report from an stored procedure?

     

    When I add the crystal report item to the proyect the expert prompts for the data that the report has to show. When I add an sp, I cannot enable the next nor the finish button.

     

    How do I put the fields of the sp in the report?

    How do I call it.

     

     

    Thursday, May 03, 2007 5:53 PM
  • Thanks alot Turner. Passing parameters to a stored procedure worked.
    Monday, May 14, 2007 10:14 AM
  • hi can anyone help

     

    i'am currently doing reporting service, with SQL server 2005

    i facing a problem, which is, my report cannot run the store procedure i create,

    i can 100% garenty my store procedure is correct, cos i can execute it in SQL server,

    but when come to reporting service it prompt me a error message saying something like "string binary data truncated"

    i not really sure what is that error

     

    any one can help

     

     

    Tuesday, May 22, 2007 9:50 AM
  • It works with above link

     

    But i have unique problem

    my database name is ST_Rel_4.050_Test

    & It fails as in b/w dot comes

     

    Can anybody tell me what can be done for this

     

    Thanks in advance

     

    Waiting.....................................

    Friday, July 20, 2007 1:03 PM
  •  

    "string binary data truncated" typically means that you're trying to force a string of length N into a variable with length<N.

     

    For example:

     

    declare @string_var nvarchar(5)

    set @string_var = 'abcdef'

     

    Error: String Binary Data Truncated

    Friday, January 25, 2008 6:37 PM
  • Manish, have you tried wrapping the name in [] brackets?

     

    Such as:  [dbo].[tablename]

     

    Friday, January 25, 2008 6:40 PM