You must Sign In to post a response.
  • Category: .NET

    How can i pass Parameter field to stored procedure in design view without coding

    I have created c# parameterized crystal report (winforms) using crystal report's parameter field and record selection formula from field explorer at design time to filter report by help of following link:

    "https://www.mindstick.com/Articles/602/parameter-field-in-crystal-report"

    When executed an input box is generated by crystal report automatically prompting for parameter value. I have not written any code for it.

    Can I use crystal report's parameter field and record selection formula (as used in link stated above) with stored procedure at design time ?
  • #767078
    Hi,
    Yes it is possible.
    First create SP.
    Then create Crystal Report and inside Standard Report Creation Wizard, from Available Data Sources--> select your created SP. So that you can pass parameters to it as you want.
    You can find demo source code over here:
    http://dotnetmentors.com/reporting/crystal-report-with-stored-procedure-parameter-and-visual-studio.aspx

  • #767081
    Yes you can use stored procedure in Crystal report.
    just, while fetching data from database, you need to use 'CommandType.StoredProcedure'. see below snippet

    protected void Page_Load(object sender, EventArgs e)
    {
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
    Customers dsCustomers = GetData();
    crystalReport.SetDataSource(dsCustomers);
    CrystalReportViewer1.ReportSource = crystalReport;
    }

    private Customers GetData()
    {
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand("Customers_GetCustomers");
    using (SqlConnection con = new SqlConnection(conString))
    {
    using (SqlDataAdapter sda = new SqlDataAdapter())
    {
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    sda.SelectCommand = cmd;
    using (Customers dsCustomers = new Customers())
    {
    sda.Fill(dsCustomers, "DataTable1");
    return dsCustomers;
    }
    }
    }
    }

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767098
    Hi,

    You can achieve it by passing parameters in Stored Procedure itself, through application you just pass the parameter value and based on that value in stored procedure you can execute the query and return the result that result you can display in report.

    Ex:

    private void GenerateReport()
    {
    Report rpt = new Report();
    rpt.Show();
    ReportDocument rptDoc = new ReportDocument();
    rptDoc.Load(ReportPath);
    rptDoc.SetParameterValue("@col1", txt1.Text);
    rptDoc.SetParameterValue("@col2", txt2.Text);
    rpt.crystalreportviewer.ViewerCore.ReportSource = rptDoc;
    }


    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767103
    You can pass the parameter from the crystal report. Following needs to be handle in the crystal report coding part.

    //write your crystal Report code
    ParameterFields MyParameterFields= new ParameterFields();
    ParameterField MyparamField = new ParameterField();
    // Set your parameter values
    CrystalReportViewer1.ParameterFieldInfo = paramFields;
    //write your crystal Report code

    In the Standard Report Creation Wizard select the SP as datasource. And following is the sample SP with parameters.

    CREATE PROCEDURE [dbo].[SPNameForyourReport]
    @YourParameter nchar(5)
    AS
    BEGIN
    // Write you DB code
    END

    By Nathan
    Direction is important than speed

  • #767107
    Hi,

    Thanks for your reply but i am not getting result as required

    Please refer to following link for understanding my requirement
    https://www.mindstick.com/Articles/602/parameter-field-in-crystal-report

    After visiting the link, please suggest me how to achieve same result as in above link using stored procedure.


Sign In to post your comments