Loading Crystal Report reports which use Stored Proc in C#

Introduction

This article focuses on loading Crystal Report reports in C#. The Crystal Report report created here uses a SQL Server stored procedure which takes two parameters and it uses DSN with SQL Server authentication.

Background

When I started working on Crystal Reports, mainly I was wondering about these problems:

1. How to set parameters of store procedure from C# code using Crystal's APIs.
2. How to avoid popup window which comes when we use DSN with SQL Server authentication.
3. How to avoid these errors:
   • Missing prompting unit
   • The parameter is incorrect

This article gives a solution to all of the above issues and also gives a few notes to avoid unpredictable results.

Using the code

The attached code here loads the "SalseReport.rpt" file. The steps to run the attached application are:

1. Create database say "testDB" in SQL Server and execute the script "SalseData_Table_SP.sql" in the SQL Server Query Analyser which will create a stored procedure "Sel_SalesData" and a table "SalesData" for you.
2. Import the sample data to the table "salseData" from file "SalesData_Data.txt" (data is comma separated).
3. Create a DSN named "TestDB_DSN" with SQL Server authentication. Give valid user name and password.
4. Open "frmSalseData.cs" file and update the below line with your logon information, in the function "btnPreview_Click".

   //The parameters are in the order 
   //- UserName, Password, DSN Name, DatabaseName, Case Sensitive
   reportDocument.SetDatabaseLogon("pchitriv", "Windows2000",
                            "TestDB_DSN", "testDB", false);

5. In case you have created a DSN with some other name than "TestDB_DSN", then open the "SalseReport.rpt" file from the Reports directory and set the DataSource location to point to the correct DSN and "Sel_SalseData" stored procedure again.
6. The code to load the report looks like this:
   Collapse

   private void btnPreview_Click(object sender, System.EventArgs e)
   {
     //Instantiate variables
     ReportDocument reportDocument = new ReportDocument();
     ParameterField paramField = new ParameterField();
     ParameterFields paramFields = new ParameterFields();
     ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
   
     //Set instances for input parameter 1 -  @vDepartment
     paramField.Name = "@vDepartment";
     //Below variable can be set to any data 
     //present in SalseData table, Department column
     paramDiscreteValue.Value = "South";
     paramField.CurrentValues.Add(paramDiscreteValue);
     //Add the paramField to paramFields
     paramFields.Add(paramField);
   
     //Set instances for input parameter 2 -  @iSalseYear
     //*Remember to reconstruct the paramDiscreteValue and paramField objects
     paramField = new ParameterField();
     paramField.Name = "@iSalesYear";
     paramDiscreteValue = new ParameterDiscreteValue();
     paramDiscreteValue.Value = "2004";
     paramField.CurrentValues.Add(paramDiscreteValue);
   
     //Add the paramField to paramFields
     paramFields.Add(paramField);
   
     crystalReportViewer1.ParameterFieldInfo = paramFields;
   
     reportDocument.Load(@"..\..\..\Reports\SalseReport.rpt");
   
     //set the database loggon information.  
     reportDocument.SetDatabaseLogon("userid", "password","servername", "DBName");
   
     //Load the report by setting the report source
     crystalReportViewer1.ReportSource = reportDocument;
   }

Points of Interest

1. Error - Missing prompting unit:

   I got this error when I was not creating a new “ParameterField" object for each of the input parameters.

2. Error - The parameter is incorrect:

   I got this error when I used paramField.Name = "vDepartment"; instead of paramField.Name = "@vDepartment";. This is because in the report, the parameter field name in the Field Explorer is @vDepartment. So remember to use the exact name in the C# code as that is used in the report (for parameter/input fields).

3. Note:

   Remember to uncheck the option “Save data with Report” under Crystal Reports File menu. Checking this option gives unpredictable results when you call a report in a C# form.