Passing String Date to Stored Procedure from C# Web Page

Question

User-1112770754 posted

    Help! Pleeeeeeese! Okay, I have been struggling with passing parameters to Crystal Report.
 
 Here is the situation... 
 My report is using a simple stored procedure that needs two variable as input parameters ("@FROMDT" and "@TODT")
 The report runs without problems with I enter the parameters MANUALLY - It's run without problems in Crystal Reports OR if I run it via Button1_Click on my Web page (and when prompted... @FROMDT = 01/01/2009 and @TODT = 10/01/2009)   Note the dates are NOT within quotes.

    The problem is with I attempt to PASS parameters dynamically as per the code below.
 I can tell you that the "first" parameter a string (Report Title) is okay... it get passed without any problems and shows up on the report.
 The TWO parameters for the stored procedure is blowing up...
 
 When I run the code as below... it will prompt for ReportTitle and @FROMDT (NOT @TODT).  It seems as @TODT is being passed on ... but in the wrong format.
 Because, in this case even when I enter the correct ReportTitle and @FROMDT I will get an error.
 
 Failed to retrieve data from the database. Details: [Database Vendor Code: 241 ] Failed to retrieve data from the database. Error in File DirectRecruit {7DAE5E65-22D7-483C-9FB7-0E5E2D1AD979}.rpt: Failed to retrieve data from the database. Details: [Database Vendor Code: 241 ]
 
 I am not sure what else to try.   Could it be the format eg... "10/01/2009" (with quotes causing the problem???)
 Please note: the stored procedure is expecting a nvarchar. So passing in a STRING should work.
 
 Anyways, I am desperate for any help.
 Thanks in advance.

 ALTER PROCEDURE [dbo].[RS_SP_Report_DirectRecruits]
 (
  @FROMDT   nvarchar(20), 
  @TODT    nvarchar(20) 
 )
 etc...

 protected void Button1_Click(object sender, EventArgs e)
    {
        //Passing the parameter at run time
        ParameterFields PmFields = new ParameterFields();
        ParameterField PmField = new ParameterField();

        ParameterDiscreteValue myDiscreteValue1 = new ParameterDiscreteValue();
        ParameterDiscreteValue myDiscreteValue2 = new ParameterDiscreteValue();
        ParameterDiscreteValue myDiscreteValue3 = new ParameterDiscreteValue();

        PmField.ParameterFieldName = "ReportTitle";
        myDiscreteValue1.Value = "crParameterField does not exist in the current";
        PmField.CurrentValues.Add(myDiscreteValue1);

        PmField.ParameterFieldName = "@FROMDT";
        myDiscreteValue2.Value = "01/01/2009";
        PmField.CurrentValues.Add(myDiscreteValue2);

        PmField.ParameterFieldName = "@TODT";
        myDiscreteValue3.Value = "10/01/2009";
        PmField.CurrentValues.Add(myDiscreteValue3);

        PmFields.Add(PmField);
        CrystalReportViewer1.ParameterFieldInfo = PmFields;

        CrystalReportSource1.Report.FileName = Server.MapPath("~/DirectRecruit.rpt");
 
  //specify all the database details
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.ServerName = "TESTNFS";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.UserID = "myUserId";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.Password = "myPwd";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.DatabaseName = "FSB";

        CrystalReportViewer1.ReportSourceID = "CrystalReportSource1";
    }

Answer 1

User-1827453801 posted

No idea, but why not pass the date as a date?

Might be a localisation issue, again passing as a typed date would solve that.

Answer 2

User-1112770754 posted

   
 worldspawn, thanks for your suggestion. 
 
 I tried your suggestion to see if I can make any progress with this...
 1.  I recompled the stored to accept DATETIME parameters (@FROMDT and @TODT).
 2.  Updated my report file to 'pickup' the 'new parameter type'.
 3.  Updated my Code behind (see below).
 4.  Run the report.
 
 The result... getting somewhere but not where I want to go.
 Executing the below code WILL prompt for ALL parameters (3). 
 After I enter the parameters... click OK and the report is generated (all is good).
 
 Question/problem: How can I get rid of the "Prompting" for input parameters. 
 I am passing the parameters see below. Am doing something wrong????
 
 If I can get past the prompting... I will still need to figure out how to pass the "date strings" and re-complie the sproc
    to it's orignal format.   The reason for this is after I get this report to work... there are more reports (approx 25) to develop
    that uses other stored procs that has similar input parameter type (nVarchar (20)).   Don't want to recompile 'that many' stored procs if it can be avoided.
 
 However, I will cross that bridge when/if I get there.
 
 again, any help/suggestion will be appreciated.   Thanks.
 
 
 
 protected void Button1_Click(object sender, EventArgs e)
    {
        //Passing the parameter at run time
        ParameterFields PmFields = new ParameterFields();
        ParameterField PmField = new ParameterField();

        ParameterDiscreteValue myDiscreteValue1 = new ParameterDiscreteValue();
        ParameterDiscreteValue myDiscreteValue2 = new ParameterDiscreteValue();
        ParameterDiscreteValue myDiscreteValue3 = new ParameterDiscreteValue();

        PmField.ParameterFieldName = "ReportTitle";
        myDiscreteValue1.Value = "crParameterField does not exist in the current";
        PmField.CurrentValues.Add(myDiscreteValue1);

        DateTime FromDt = new DateTime();
        FromDt = Convert.ToDateTime("01/01/2009");

        DateTime ToDt = new DateTime();
        ToDt = Convert.ToDateTime("10/01/2009");

        PmField.ParameterFieldName = "@FROMDT";
        myDiscreteValue2.Value = FromDt;
        PmField.CurrentValues.Add(myDiscreteValue2);

        PmField.ParameterFieldName = "@TODT";
        myDiscreteValue3.Value = ToDt;
        PmField.CurrentValues.Add(myDiscreteValue3);

        PmFields.Add(PmField);
        CrystalReportViewer1.ParameterFieldInfo = PmFields;
 
  CrystalReportSource1.Report.FileName = Server.MapPath("~/DirectRecruit.rpt");
       
  
        //specify all the database details
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.ServerName = "TESTNFS";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.UserID = "ss";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.Password = "sss!";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.DatabaseName = "NsssFS";

        CrystalReportViewer1.ReportSourceID = "CrystalReportSource1";
    }

Answer 3

User-1827453801 posted

I am not clear on the source of the prompting. Is that a function of crystal reports? I have never actually used crystal reports so I probably can't help you with that... wouldn't the parameters need to be applied to the reportsource instead of the viewer (wild guessing going on there)?

Check this out: http://social.msdn.microsoft.com/Forums/en-US/vscrystalreports/thread/20559857-1d8b-4def-9854-1b7aaaad8678

If that doesn't help check out the other results here: http://www.google.com.au/search?hl=en&safe=off&client=firefox-a&rls=org.mozilla%3Aen-GB%3Aofficial&hs=98n&q=crystal+reports+parameter+prompting&btnG=Search&meta=

Regarding the passing date as a string - have you tried profiling (sql profiler is usually installed as part of the client tools) the database query that crystal reports is issuing to see if it sheds some light on what the problem is?

What dateformat is your server using (d/m or m/d) and also check what format the sql server uses (it doesn't use the operating system setting).

Answer 4

User-1112770754 posted

Worldspawn... thanks again for trying to help.

Anyways, after countless hours (it's 2:45am) in NYC :(.  I finally figure this out.  Will post below a working version... and hope that It will
help someone that's new to crystal reports and wanting to use the CrystalReportViewer from C# and passing parameters to report/stored proc.

Let me tell you what was causing the problem (that was not too obvious to me).
Basically the fix was a "ParameterField" object has to set/saved for EACH parameter.

anyways... here is the code that works.

    protected void Button1_Click(object sender, EventArgs e)
    {
        ParameterDiscreteValue objDiscreteValue;
        ParameterField objParameterField;

        //Set the ReportSourceID before Report.FileName... otherwise "the report filename was empty" message will show up on your web page.
        CrystalReportViewer1.ReportSourceID = "CrystalReportSource1";

        // set the report.FileName BEFORE setting database Login Details.
        CrystalReportSource1.Report.FileName = Server.MapPath("~/DirectRecruits.rpt");

        //specify all the database Login details
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.ServerName = "TESTNFS";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.UserID = "saxxx";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.Password = "sggs!";
        CrystalReportViewer1.LogOnInfo[0].ConnectionInfo.DatabaseName = "NFSSSS";

        //Set value for first parameter
        objDiscreteValue = new ParameterDiscreteValue();
        objDiscreteValue.Value = "01/01/2009";
        objParameterField = CrystalReportViewer1.ParameterFieldInfo["@FROMDT"];
        objParameterField.CurrentValues.Add(objDiscreteValue);
        CrystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

        objParameterField = CrystalReportViewer1.ParameterFieldInfo["@TODT"];
        objDiscreteValue = new ParameterDiscreteValue();
        objDiscreteValue.Value = "10/30/2009";
        objParameterField.CurrentValues.Add(objDiscreteValue);
        CrystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

        objParameterField = CrystalReportViewer1.ParameterFieldInfo["ReportTitle"];
        objDiscreteValue = new ParameterDiscreteValue();
        objDiscreteValue.Value = "Direct Recruits";
        objParameterField.CurrentValues.Add(objDiscreteValue);
        CrystalReportViewer1.ParameterFieldInfo.Add(objParameterField);
    }

 

 

Answer 5

User-1196485451 posted

Go through the following link.

http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-date-parameter.htm

Liam.