How To Prevent Enter Parameter Value Dialog Popping Up While Using Crystal Report

Introduction

This article may be useful when one is using Crystal report with SQL stored procedure parameters but does not want to pop up "Enter Parameter Value" dialog for each stored procedure parameter for dynamic database and wants to pass them dynamically.

Background

Crystal report with SQL stored procedure always pops up a parameter value dialog on load as well as on refreshing report using the Refresh button click or using RefreshReport() method of the CrystalReportViewer class. However, when one wants to pass a value of parameters dynamically and wants to avoid popup, then there is no way because even parameters are passed on load. On refresh of report, all parameters are unbinded and again, there is a pop up for each parameter. So it seems very ridiculous that on refresh, all parameters are unbinded as well as there is no other way to bind them on refresh.

So, here I've tried my best to solve the problem that I had also faced.

Using the Code

First set the dynamic database connection information:

//Connection info object
private ConnectionInfo objConnectionInfo;
objConnectionInfo = new ConnectionInfo();
            
objConnectionInfo.ServerName    = "ServerName";
objConnectionInfo.DatabaseName  = "DBName";
objConnectionInfo.UserID        = "UserId";
objConnectionInfo.Password      = "Password";

Now, set the database information for each table of the report:

//Set dynamic database connection information
Tables tables = ((ReportClass)crystalReportViewer1.ReportSource).Database.Tables;
foreach (Table tbl in tables)
{
TableLogOnInfo objTableLogOnInfo = new TableLogOnInfo();
objTableLogOnInfo.ConnectionInfo = objConnectionInfo;
tbl.ApplyLogOnInfo(objTableLogOnInfo);
}

Bind the report source to the Crystal report viewer:

//Reset report source
crystalReportViewer1.ReportSource = objMyReport;

Set SQL stored procedure parameters:

ParameterField objParameterField = crystalReportViewer1.ParameterFieldInfo[0];
ParameterDiscreteValue objParameterDiscreteValue;

//Set value for first parameter
objParameterDiscreteValue = new ParameterDiscreteValue();
objParameterDiscreteValue.Description = "First Parameter";
objParameterDiscreteValue.Value = "First Value";

objParameterField.CurrentValues.Add(objParameterDiscreteValue);
//Reset params
crystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

objParameterField = crystalReportViewer1.ParameterFieldInfo[1];

//Set value for second parameter            
objParameterDiscreteValue = new ParameterDiscreteValue();
objParameterDiscreteValue.Description = "Second Parameter";
objParameterDiscreteValue.Value = "Second Value";

objParameterField.CurrentValues.Add(objParameterDiscreteValue);

//Reset params
crystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

//So on.....

Now on refresh report button click event, don't refresh report and refresh report manually. Using the following code, one can prevent popping up parameter value dialog on load as well as on refreshing report:

private void crystalReportViewer1_ReportRefresh
	(object source, CrystalDecisions.Windows.Forms.ViewerEventArgs e)
{
//Don't  handle refresh event on refresh button click
e.Handled = true;

//Manually refresh 
((ReportClass)crystalReportViewer1.ReportSource).Refresh();   
          
//AReset dynamic database connection information
//...
           
//Reset report source
//...
           
//Reset SQL parameters
//...
}

Find the given sample C# code as:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.CrystalReports;
using CrystalDecisions.Shared;

namespace MyCrystalReport
{
    public partial class MyCrystalReport : Form
    {
        public MyCrystalReport()
        {
            InitializeComponent();
        }

        //Crystal report object
        private MyReport objMyReport;
        //Connection info object
        private ConnectionInfo objConnectionInfo;

        private void Form1_Load(object sender, EventArgs e)
        {
            //Initialize report 
            objMyReport = new MyReport();
           
            //Set database settings for connection
            setDatabaseSettings();

            //Loads report first time
            refreshReport();
        }       

        /// <summary>
        /// Sets Database Settings
        /// </summary>
        private void setDatabaseSettings()
        {
            objConnectionInfo = new ConnectionInfo();
            
            objConnectionInfo.ServerName    = "ServerName";
            objConnectionInfo.DatabaseName  = "DBName";
            objConnectionInfo.UserID        = "UserId";
            objConnectionInfo.Password      = "Password";
        }

        private void crystalReportViewer1_ReportRefresh
	(object source, CrystalDecisions.Windows.Forms.ViewerEventArgs e)
        {
            //Don't  handle refresh event on refresh button click
            e.Handled = true;

            //Manually refresh 
            ((ReportClass)crystalReportViewer1.ReportSource).Refresh();   
        }

        /// <summary>
        /// Refreshes report
        /// </summary>
        private void refreshReport()        
        {        
            //Set dynamic database connection information
            Tables tables = 
		((ReportClass)crystalReportViewer1.ReportSource).Database.Tables;
            foreach (Table tbl in tables)
            {
                TableLogOnInfo objTableLogOnInfo = new TableLogOnInfo();
                objTableLogOnInfo.ConnectionInfo = objConnectionInfo;
                tbl.ApplyLogOnInfo(objTableLogOnInfo);
            }

            //Reset report source
            crystalReportViewer1.ReportSource = objMyReport;

            //Reset SQL parameters
            setSQLParams();
        }

        /// <summary>
        /// Sets SQL stored procedure parameters
        /// </summary>
        private void setSQLParams()
        {
            ParameterField objParameterField = 
		crystalReportViewer1.ParameterFieldInfo[0];            
            ParameterDiscreteValue objParameterDiscreteValue;

            //Set value for first parameter
            objParameterDiscreteValue = new ParameterDiscreteValue();
            objParameterDiscreteValue.Description = "First Parameter";
            objParameterDiscreteValue.Value = "First Value";

            objParameterField.CurrentValues.Add(objParameterDiscreteValue);
            //Reset params
            crystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

            objParameterField = crystalReportViewer1.ParameterFieldInfo[1];

            //Set value for second parameter            
            objParameterDiscreteValue = new ParameterDiscreteValue();
            objParameterDiscreteValue.Description = "Second Parameter";
            objParameterDiscreteValue.Value = "Second Value";

            objParameterField.CurrentValues.Add(objParameterDiscreteValue);

            //Reset params
            crystalReportViewer1.ParameterFieldInfo.Add(objParameterField);

            //So on.....            
        }
    }
}

Wish

Hope this will be helpful for everyone who required the above. Have a nice time. Cheers!

History

• 27^th September, 2008: Initial post