Click here to Skip to main content
12,897,360 members (54,686 online)
Click here to Skip to main content
Add your own
alternative version


54 bookmarked
Posted 19 Apr 2005

Loading Crystal Report reports which use Stored Proc in C#

, 19 Apr 2005
Rate this:
Please Sign up or sign in to vote.
An article on how to load Crystal Report reports using C#.


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.


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

  1. How to set parameters of stored 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:
    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";
        //Add the paramField to paramFields
        //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";
        //Add the paramField to paramFields
        crystalReportViewer1.ParameterFieldInfo = paramFields;
        //set the database loggon information. 
        //**Note that the third parameter is the DSN name 
        //  and not the Database or System name
        reportDocument.SetDatabaseLogon("pchitriv", "Windows2000", 
                                   "TestDB_DSN", "testDB", false);
        //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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Pankaj A. Chitriv
Web Developer
India India
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.

You may also be interested in...


Comments and Discussions

QuestionDymanic Parameters Pin
MikeO@Indy11-Sep-07 16:40
memberMikeO@Indy11-Sep-07 16:40 
GeneralCrystal Repor Pin
Pramod ji20-Jun-07 19:34
memberPramod ji20-Jun-07 19:34 
GeneralRe: Crystal Repor Pin
Pradeep K V2-Jul-07 23:08
memberPradeep K V2-Jul-07 23:08 
GeneralPassing sql query parameter to Crystal Report Pin
mehmetned6-Apr-07 4:18
membermehmetned6-Apr-07 4:18 
AnswerRe: Passing sql query parameter to Crystal Report Pin
Shahan Ayyub17-Sep-11 23:43
memberShahan Ayyub17-Sep-11 23:43 
GeneralOracle [modified] Pin
Viswanatha Rao12-Oct-06 11:52
memberViswanatha Rao12-Oct-06 11:52 
GeneralWorks like a charm Pin
slice2711-Apr-06 13:22
memberslice2711-Apr-06 13:22 
Questionunsuccess,why? Pin
iceboat200516-Dec-05 2:39
membericeboat200516-Dec-05 2:39 
VS 2003 + SQL2000
public virtual new void SetDatabaseLogon ( System.String user , System.String password , System.String server , System.String database )
public virtual new void SetDatabaseLogon ( System.String user , System.String password )
not the fifthly parameter??????????D'Oh! | :doh:

Generalpopup window Pin
dinoniko5-Dec-05 11:50
memberdinoniko5-Dec-05 11:50 
GeneralCrystal Report not working with Stored Procedures Pin
Gireesh Viswanathan18-Oct-05 2:52
memberGireesh Viswanathan18-Oct-05 2:52 
GeneralRe: Crystal Report not working with Stored Procedures Pin
barryw425-Aug-06 3:54
memberbarryw425-Aug-06 3:54 
GeneralRe: Crystal Report not working with Stored Procedures Pin
ic3b3rg37-Sep-07 2:21
memberic3b3rg37-Sep-07 2:21 
QuestionImpossible to connect to my database with CR10 & C# Pin
Anonymous10-Oct-05 6:15
sussAnonymous10-Oct-05 6:15 
GeneralRelated... Good Article Pin
Jeff Emery26-Sep-05 15:20
memberJeff Emery26-Sep-05 15:20 
GeneralCrystal Report Problem Pin
Muhammad Waqas Butt22-Sep-05 23:10
memberMuhammad Waqas Butt22-Sep-05 23:10 
QuestionMake Crystal Prompt For Parameter Values Pin
Anonymous6-Sep-05 9:37
sussAnonymous6-Sep-05 9:37 
QuestionHow to Successfully Pass Values thru Crystal to Stored Procs Pin
wgbarnum5-Sep-05 5:14
memberwgbarnum5-Sep-05 5:14 
AnswerRe: How to Successfully Pass Values thru Crystal to Stored Procs Pin
wgbarnum5-Sep-05 12:40
memberwgbarnum5-Sep-05 12:40 
GeneralCrystal Report Driver Error Pin
Anup Singh J31-Aug-05 5:10
memberAnup Singh J31-Aug-05 5:10 
K.selvambikai8-Aug-05 19:50
memberK.selvambikai8-Aug-05 19:50 
GeneralReportDocument's load method taking too much time. Pin
erganesh17-Jul-05 20:25
membererganesh17-Jul-05 20:25 
GeneralCrystal Report for Web Forms Pin
tudela14-Jul-05 4:32
membertudela14-Jul-05 4:32 
GeneralCrystal that comes with Visual Studio Pin
thejonz30-Jun-05 4:47
memberthejonz30-Jun-05 4:47 
GeneralRe: Crystal that comes with Visual Studio Pin
Pankaj A. Chitriv30-Jun-05 17:09
memberPankaj A. Chitriv30-Jun-05 17:09 
GeneralRe: Crystal that comes with Visual Studio Pin
Aubyone23-Nov-05 18:50
memberAubyone23-Nov-05 18:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170424.1 | Last Updated 20 Apr 2005
Article Copyright 2005 by Pankaj A. Chitriv
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid