Passing sql stored proc parameters to C R with C# or VB

[The1dono]

I know this question has been asked before, but I've been messing around for days and I've had no luck.

I'm using SQL server, .net and CR 8.5

I have a stored procedure that has 1 parameter (for simplicity), and I want to dynamically change this at run time.

I've got 1 form with a CR viewer, and I've added User.rpt to my project. I have found the following code on the web somewhere, but I'm still lost

If anyone can give me some code or advice - I'll be extremely gratefull

static public void AddCrystalParameter(CrystalDecisions.CrystalReports.Engine.ReportDocument crReport, string ParameterName, string ParameterValue)
{
ParameterFieldDefinitions crParameterFieldDefinitions = crReport.DataDefinition.ParameterFields;

ParameterValues crParameterValues = new ParameterValues();
ParameterFieldDefinition param = crParameterFieldDefinitions[ParameterName];
crParameterValues = param.CurrentValues;

ParameterDiscreteValue param_Val = new ParameterDiscreteValue();
param_Val.Value = ParameterValue;
crParameterValues.Add(param_Val);

param.ApplyCurrentValues(crParameterValues);
param.ApplyDefaultValues(crParameterValues);
}


private void cmdOk_Click(object sender, System.EventArgs e)
{
User ReportDoc;
ReportDoc = new User();

AddCrystalParameter(ReportDoc,"@EventID",txtParam.Text );
}

 

[The1dono]

B I N G O
After messing around for a whole week, I've got it. If anyone else has the same problem, well here is the solution.

In my walk through, I'm going to use the Northwind database that comes with SQL server.
I'll use the CustOrdersOrders stored procedure because it has one parameter (I haven't experimented with more than 1 parameter yet), Buy anyway - here we go!

First Step:
Add a crystal reports viewer to your form.
Then add a button and a text box (leave their default names for now)

Second Step:
If you have already created your report, then don't worry and goto next step
If you don't have an existing report you can create one in .Net IDE by adding a new item.
Goto the solution explorer and right click on your project (bold) and add new item. Creating a report is fairly easy so just mess around until you're done.

Third Step:
copy and paste this code over all your form1's code.
you will have to change my db connection settings to yours (line 147 » 150)
Hopefully all goes well and you cr viewer will fetch some arb data from the Northwind database
I've also included some commented out tags to help you distinguish between generated code and my code. I would also like to thank the author of the AddCrystalParameter method - RATSEY-WOODROFFE, Jens. It helped me alot and I hope all this helps someone else too.
Cheers,
Dono

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
//<added by me>
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
//</added by me>
namespace NorthwindDemo
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
// <Added by me>
string report_file = &quot;&quot;;
ReportDocument ReportDoc = new ReportDocument();
// </Added by me>

private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.TextBox textBox1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;

public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();

//
// TODO: Add any constructor code after InitializeComponent call
//
}

/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.crystalReportViewer1 = new CrystalDecisions.Windows.Forms.CrystalReportViewer();
this.button1 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.SuspendLayout();
//
// crystalReportViewer1
//
this.crystalReportViewer1.ActiveViewIndex = -1;
this.crystalReportViewer1.Location = new System.Drawing.Point(8, 8);
this.crystalReportViewer1.Name = &quot;crystalReportViewer1&quot;;
this.crystalReportViewer1.ReportSource = null;
this.crystalReportViewer1.Size = new System.Drawing.Size(672, 232);
this.crystalReportViewer1.TabIndex = 0;
this.crystalReportViewer1.Load += new System.EventHandler(this.crystalReportViewer1_Load);
//
// button1
//
this.button1.Location = new System.Drawing.Point(528, 256);
this.button1.Name = &quot;button1&quot;;
this.button1.Size = new System.Drawing.Size(64, 24);
this.button1.TabIndex = 1;
this.button1.Text = &quot;Ok&quot;;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(600, 256);
this.textBox1.Name = &quot;textBox1&quot;;
this.textBox1.Size = new System.Drawing.Size(80, 20);
this.textBox1.TabIndex = 2;
this.textBox1.Text = &quot;&quot;;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(688, 285);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.textBox1,
this.button1,
this.crystalReportViewer1});
this.Name = &quot;Form1&quot;;
this.Text = &quot;Form1&quot;;
this.ResumeLayout(false);

}
#endregion

/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}

private void button1_Click(object sender, System.EventArgs e)
{
//<Added by me>
AddCrystalParameter(ReportDoc,&quot;@CustomerID&quot;,textBox1.Text);
makeReport(report_file);
crystalReportViewer1.Refresh();
//</Added by me>
}

//<Added by me>
static public void AddCrystalParameter(CrystalDecisions.CrystalReports.Engine.ReportDocument crReport, string ParameterName, string ParameterValue)
{
ParameterFieldDefinitions crParameterFieldDefinitions = crReport.DataDefinition.ParameterFields;

ParameterValues crParameterValues = new ParameterValues();
ParameterFieldDefinition param = crParameterFieldDefinitions[ParameterName];
crParameterValues = param.CurrentValues;

ParameterDiscreteValue param_Val = new ParameterDiscreteValue();
param_Val.Value = ParameterValue;
crParameterValues.Add(param_Val);

param.ApplyCurrentValues(crParameterValues);
}

private void makeReport(string ReportFile)
{
//declare constant variables used for database
const string LOG_SERVER = &quot;&quot;;
const string LOG_USER = &quot;Team&quot;;
const string LOG_PASS = &quot;web&quot;;
const string LOG_DB = &quot;Northwind&quot;;

ReportDoc.Load(ReportFile);


//establish connection with the database
CrystalDecisions.Shared.TableLogOnInfo Login;

Login = ReportDoc.Database.Tables[0].LogOnInfo;

Login.ConnectionInfo.ServerName = LOG_SERVER;
Login.ConnectionInfo.UserID = LOG_USER;
Login.ConnectionInfo.Password = LOG_PASS;
Login.ConnectionInfo.DatabaseName = LOG_DB;

ReportDoc.Database.Tables[0].ApplyLogOnInfo(Login);

crystalReportViewer1.ReportSource = ReportDoc;

}
//</Added by me>
private void crystalReportViewer1_Load(object sender, System.EventArgs e)
{
//<Added by me>
OpenFileDialog file = new OpenFileDialog();
file.Filter = &quot;Report Files (*.rpt)|*.rpt&quot;;
if(file.ShowDialog() == DialogResult.OK)
{
report_file = (string)file.FileName;
}

makeReport(report_file);
//</Added by me>
}
}
}

More than 1 parameter is not much different just call the
AddCrystalParameter method again
for example:

AddCrystalParameter(ReportDoc,&quot;@CustomerID&quot;,textBox1.Text);
AddCrystalParameter(ReportDoc,&quot;@OrdersID&quot;,textBox2.Text);
AddCrystalParameter(ReportDoc,&quot;@InvoiceID&quot;,textBox3.Text);