Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple parameters passed from C# to stored procedure

Posted on 2014-02-18
11
Medium Priority
?
505 Views
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!!

Thanks,
Jerry
CRParameters.txt
0
Comment
Question by:JDL129
[X]
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
11 Comments
 
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));

http://social.msdn.microsoft.com/Forums/en-US/095ffd74-80ad-481a-a540-b99e6ccd6511/passing-multiple-parameters-in-c-net-40-to-an-sql-2008-stored-procedure?forum=csharpgeneral
0
 
LVL 101

Expert Comment

by:mlmcc
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

http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
0
 

Author Comment

by:JDL129
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!!
Jerry
TransactionCode.txt
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 101

Expert Comment

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

http://www.experts-exchange.com/Database/Reporting/Q_24493076.html

mlmcc
0
 

Author Comment

by:JDL129
ID: 39872544
mlmcc!!!

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?

Thanks!!!!
Jerry
sp-Transaction.txt
0
 

Author Comment

by:JDL129
ID: 39878792
mlmcc!!

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:
try
            {


                ReportDocument report = new ReportDocument();
                report.Load("\\CAKPOSSERVER\\POSiTrackRx\\POSiTrackC\\rptTransactions.rpt");

                ///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,

Jerry
0
 
LVL 101

Expert Comment

by:mlmcc
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.

mlmcc
0
 

Author Comment

by:JDL129
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!!

Thanks,
Jerry
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 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.

mlmcc
0
 

Author Closing Comment

by:JDL129
ID: 39896124
Thanks Eric!!
0
 

Author Comment

by:JDL129
ID: 39906354
mlmcc,

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

Thanks,
Eric
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

670 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