Multiple parameters passed from C# to stored procedure

Posted on 2014-02-18
Last Modified: 2014-03-05
Hey guys!!

I have a Crystal Report in a C# 2008 application.  Currently I have a procedure to pass one variable to the stored procedure that is the data source for the report.(See attached)  This new report requires 3 parameters: CKey, BeginDT, and EndDT.

I'm stumped.  Any suggestions would be WONDERFUL!!

Question by:JDL129
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 15

Expert Comment

by:Minh Võ Công
ID: 39869610
You can query sp with multi Multiple parameters like:

SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection("your connection string");
cmd.CommandText = "dbo.sp_IpFailedFraudCheckUnValidateOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("admin", admin));
cmd.Parameters.Add(new SqlParameter("orderId", orderId));
cmd.Connection = conn;

or SqlCommand scomm = new SqlCommand("UPDATE tblUsers SET FirstName=@FirstName WHERE UserID=@UserID");
scomm.Parameters.Add(new SqlParameter("FirstName", firstName));
scomm.Parameters.Add(new SqlParameter("UserID", userId));
LVL 100

Expert Comment

ID: 39870220
That works for calling the SP from the application but not for passing the parameters to Crystal so it can call the SP.

You do it the same way as you are for 1 except you change the name.  

Here is a method of calling the report and populating it from an application dataset.  This is much easier than trying to pass parameters to the report


Author Comment

ID: 39872288
Hey guys thanks for the post!!

I am not familiar with sqlcmd and eric's code wouldn't run on my machine.  Could you please look at the attached file and see what I need to do differently.  I'm getting a logon error but this is the same logon that I use on all of my other CR repots.

Thanks!!  Sorry for the trouble!!
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 100

Expert Comment

ID: 39872324
Look at the code at the end of this question.


Author Comment

ID: 39872544

That kinda looks like Greek to me.  I am not knowledgeable enough to dig out what will work for me from that code.  Attached is the stored procedure code I am trying to connect to.  Can you adjust the C# code in my previous post to make a connection?


Author Comment

ID: 39878792

I went back over the code from your last post.  I have an error that Session is not defined.  I'm trying your method of parameters but I'm not sure how to make them work.  I have a form that I retrieve the CKey that identifies the individual and a filldate around which I want to select a range from say 01/01/2013 to 12/31/2013.  I couldn't determine how to do that with the parameter wizards. Also I don't want to use the viewer just print to a printer.

Current code:

                ReportDocument report = new ReportDocument();

                ///Load Session variables with values from web-controls
                Session["@CKey"] = cboHOH.Text.Substring(Math.Max(0, cboHOH.Text.Length - 7)).Trim();
                Session["@EndDt"] = txtTDate.Text;
                Session["@BeginDt"] = mskFDate.Text;

                ///Load all parameter fields associated with the report into "fields" object
                ParameterFields fields = new CrystalDecisions.Shared.ParameterFields();
                fields = report.ParameterFields;

                ///Session variables and parameters have same names
                ///reducing repetitious coding.  Setting parameter
                ///values from session variables
                foreach (ParameterField field in fields)
                    report.SetParameterValue(field.Name, Session[field.Name]);
                report.PrintToPrinter(0, true, 0, 0);

Need some help!!!

Thanks Eric,

LVL 100

Expert Comment

ID: 39879304
Are you sure about the names?

Try printing or displaying the field name in your loop.
I haven't written a viewer in a while but when I did I had trouble with this until I did that and I think I found the names include the  { } but I am not sure.


Author Comment

ID: 39884617
Hey guys!!
I need some help with this not just links pointing me to some code that I don't understand!!

Can some one take some of the code I have uploaded and show me how to send multiple parameter from C# to Crystal report?

It would be greatly appreciated!!

LVL 100

Accepted Solution

mlmcc earned 250 total points
ID: 39884643
If the code is the same, then what is the difference between the reports?

Are they using the same data source?

Are they using the same databse connection?

You have a report with one parameter that works.  Try hardcoding values for 2 of the parameters and running this new report.  
If that works try changing the parameter you are passing

If that doesn't work what is the error?

Unfortunately most of the experts in the Crystal zone are report writers or Crystal Enterprise (BO Enterrise) admin types.

I have written viewer but in VB6 and not .Net so the methods I used won't work since you can't use the RDC which made calling the report easy.

I do know if you are passing dates, then you have to pass a date variable not a string that looks like a date.  Crystal parameters won't do type conversion.


Author Closing Comment

ID: 39896124
Thanks Eric!!

Author Comment

ID: 39906354

Actually the problem was that I didn't have 'as' when declaring the variable in the stored procedure.  Just missed it!!


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
C# Windows app updating JSON file error. 9 50
Visual C# Windows Project - Clone Form 4 39
How does this modal work? 3 34
Manage big list of parameter list 8 27
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question