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:
- How to set parameters of stored procedure from C# code using Crystal's APIs.
- How to avoid popup window which comes when we use DSN with SQL Server authentication.
- 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:
- 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.
- Import the sample data to the table "salseData" from file "SalesData_Data.txt" (data is comma separated).
- Create a DSN named "TestDB_DSN" with SQL Server authentication. Give valid user name and password.
- Open "frmSalseData.cs" file and update the below line with your logon information, in the function "
btnPreview_Click
".
reportDocument.SetDatabaseLogon("pchitriv", "Windows2000",
"TestDB_DSN", "testDB", false);
- 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.
- The code to load the report looks like this:
private void btnPreview_Click(object sender, System.EventArgs e)
{
ReportDocument reportDocument = new ReportDocument();
ParameterField paramField = new ParameterField();
ParameterFields paramFields = new ParameterFields();
ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
paramField.Name = "@vDepartment";
paramDiscreteValue.Value = "South";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
paramField = new ParameterField();
paramField.Name = "@iSalesYear";
paramDiscreteValue = new ParameterDiscreteValue();
paramDiscreteValue.Value = "2004";
paramField.CurrentValues.Add(paramDiscreteValue);
paramFields.Add(paramField);
crystalReportViewer1.ParameterFieldInfo = paramFields;
reportDocument.Load(@"..\..\..\Reports\SalseReport.rpt");
reportDocument.SetDatabaseLogon("pchitriv", "Windows2000",
"TestDB_DSN", "testDB", false);
crystalReportViewer1.ReportSource = reportDocument;
}
Points of Interest
- Error - Missing prompting unit:
I got this error when I was not creating a new �ParameterField
" object for each of the input parameters.
- 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).
- 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.