locked
Passing a Request.QueryString to a Crystal Report SQL Command RRS feed

  • Question

  • User336760158 posted

    Does anyone know how to pass a Request.QueryString to a Crystal Report SQL Command

    I know this wont work but it demonstrates want I want to do

    SQL Command

    Select * From Mydatabase WHERE LocID = Request.QueryString["id"];

    I need a parameter but I don't know how to get the Reqiest.QueryString into crystal reports

    Saturday, August 2, 2014 10:29 PM

Answers

  • User336760158 posted

    This worked for me

    protected void page_init(object sender, EventArgs e)

        {

           ReportDocument cryRpt = new ReportDocument();

           Label1.Text = Request.QueryString["id"];

           cryRpt.Load(Server.MapPath("CrystalReport1OLD2.rpt"));

           cryRpt.SetDatabaseLogon("User", "PWD");

           ParameterFields parameterFields =new ParameterFields();

            ParameterField parameterField =new ParameterField();

             parameterField.ParameterFieldName ="LocID";

             ParameterDiscreteValue parameterValue =new ParameterDiscreteValue();

             parameterValue.Value = Label1.Text;

             parameterField.CurrentValues.Add(parameterValue);

            parameterFields.Add(parameterField);

           CrystalReportViewer1.ParameterFieldInfo = parameterFields;

           CrystalReportViewer1.ReportSource = cryRpt;

        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 19, 2014 2:54 AM

All replies

  • User-1716253493 posted

    You can set crystal report viewer parameter id in page_load event

    Saturday, August 2, 2014 11:33 PM
  • User336760158 posted

    Thanks for the reply

    Would you have a sample code to do that

    Sunday, August 3, 2014 12:10 AM
  • User1283497924 posted

    Hi try this code

    CrystalReportSource1.ReportDocument.SetParameterValue("@id", Request.QueryString("id"))

    Sunday, August 3, 2014 3:49 AM
  • User336760158 posted

    Thanks for the sample code, but I am getting a Visual Studio error

    non- invocable member 'System.Web.HttpRequest.QueryString' cannot be used like a method

    Any ideas

    //CODE

    using System;

    using System.Collections.Generic;

    using System.Linq;

    using System.Web;

    using System.Web.UI;

    using System.Web.UI.WebControls;


    namespace WebApplication14

    {

      public partial class WebForm1 : System.Web.UI.Page

        {

           protected void Page_Load(object sender, EventArgs e)

            {

                CrystalReportSource1.ReportDocument.SetParameterValue("@id", Request.QueryString("id"));

            }

        }

    }

    Monday, August 4, 2014 5:48 PM
  • User-1716253493 posted
    CrystalReportSource1.ReportDocument.SetParameterValue("@id", Request.QueryString["id"]);

    Monday, August 4, 2014 9:04 PM
  • User336760158 posted

    Thanks that work but how do I get the  @id  to the SQL Command in the database expert   

    WHERE  location.LocationID = @id doesn't work

    Again I appreciate your help

    Monday, August 4, 2014 9:59 PM
  • User-1716253493 posted

    Filter your data in Crystal report, show all data in the Datasource

    You can add parameter in Crystal Report in Parameter Fields, use CrystalReports > Reports > Select Expert to use the parameter

    You can also use controlparameter, querystringparameter etc directly in aspx page

    Monday, August 4, 2014 10:15 PM
  • User336760158 posted

    Thanks again

    Seems to be working but is prompting to enter value

    How do I get rid of that

    Monday, August 4, 2014 11:46 PM
  • User-1716253493 posted

    If the value alreadey passed, i guess it will not prompt you in the viewer

    Try change parameter name from @id to id

    You can also use controlparameter, querystringparameter etc directly in aspx page

    Tuesday, August 5, 2014 12:23 AM
  • User336760158 posted

    Still working a way on this but getting this error

    Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))              Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.            
    Exception Details: System.Runtime.InteropServices.COMException: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
    Source Error:

    Line 10:             TextBox1.Text = "FRED";
    Line 11:             Label1.Text = Request.QueryString["id"];
    Line 12:             CrystalReportSource1.ReportDocument.SetParameterValue("@LocID", Request.QueryString["id"]);
    Line 13:          //   CrystalReportSource1.ReportDocument.SetParameterValue("@LocID", Convert.ToInt32(Request.QueryString["id"]));
    Line 14:             // CrystalReport12.SetParameterValue("@LocID", Request.QueryString["id"])
                      

     
    Stack Trace:

    [COMException (0x8002000b): Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))]
       CrystalDecisions.ReportAppServer.DataDefModel.FieldsClass.get_Item(Int32 Index) +0
       CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(Int32 index) +72
       CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions.get_Item(String fieldName) +96
       CrystalDecisions.CrystalReports.Engine.ReportDocument.SetParameterValue(String name, Object val) +294
       ASP.webform5_aspx.Page_Load(Object sender, EventArgs e) in e:\hostingspaces\powderw\powderworksroad.com\wwwroot\WebForm5.aspx:12
       System.Web.UI.Control.LoadRecursive() +71
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint
    Sunday, August 10, 2014 10:59 PM
  • User-1716253493 posted

    Try remove "@"

    CrystalReportSource1.ReportDocument.SetParameterValue("LocID", Request.QueryString["id"]);

    Sunday, August 10, 2014 11:07 PM
  • User336760158 posted

    I am getting same error

    Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))              Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.            
    Exception Details: System.Runtime.InteropServices.COMException: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
    Source Error:

    Line 11:             Label1.Text = Request.QueryString["id"];
    Line 12:            // CrystalReportSource1.ReportDocument.SetParameterValue(0, Request.QueryString["id"]);
    Line 13:            CrystalReportSource1.ReportDocument.SetParameterValue("LocID", Request.QueryString["id"]);
    Line 14:          //   CrystalReportSource1.ReportDocument.SetParameterValue("@LocID", Convert.ToInt32(Request.QueryString["id"]));
    Line 15:             // CrystalReport12.SetParameterValue("@LocID", Request.QueryString["id"])
    Sunday, August 10, 2014 11:45 PM
  • User-1716253493 posted
        <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
            <Report FileName="....">
                <Parameters>
                    <CR:Parameter ConvertEmptyStringToNull="False" DefaultValue="" Name="LocID" 
                        ReportName="" />
                </Parameters>
            </Report>
        </CR:CrystalReportSource>

    Monday, August 11, 2014 2:34 AM
  • User336760158 posted

    The Code below gives a error message... Failed to open the connection. Details:  [Database Vendor Code: 17 ]

    Page Load

       CrystalReportSource1.ReportDocument.SetParameterValue("LocID", this.HiddenLocID.Value);

    Plus

    <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">      

    <Report FileName="CrystalReport12.rpt">

    <Parameters>     

    <CR:Parameter ConvertEmptyStringToNull="False" DefaultValue="" Name="LocID"

    ReportName="" />

     

    </Parameters>

           

    </Report>

       

    </CR:CrystalReportSource>

    Tuesday, August 12, 2014 12:12 AM
  • User336760158 posted

    This worked for me

    protected void page_init(object sender, EventArgs e)

        {

           ReportDocument cryRpt = new ReportDocument();

           Label1.Text = Request.QueryString["id"];

           cryRpt.Load(Server.MapPath("CrystalReport1OLD2.rpt"));

           cryRpt.SetDatabaseLogon("User", "PWD");

           ParameterFields parameterFields =new ParameterFields();

            ParameterField parameterField =new ParameterField();

             parameterField.ParameterFieldName ="LocID";

             ParameterDiscreteValue parameterValue =new ParameterDiscreteValue();

             parameterValue.Value = Label1.Text;

             parameterField.CurrentValues.Add(parameterValue);

            parameterFields.Add(parameterField);

           CrystalReportViewer1.ParameterFieldInfo = parameterFields;

           CrystalReportViewer1.ReportSource = cryRpt;

        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 19, 2014 2:54 AM