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.
He likes traveling a lot, especially long drives. He occasionally plays guitar, fascinated to watch WWE and likes spending time with his family and friends.
About the work, Pankaj is playing with the MS technologies since last couple of years. He designed and architected quiet a few applications and products which specially includes load balancing, DB clustering and also known to various architectural patterns and application blocks.