Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Multiple parameters passed from C# to stored procedure

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
JDL129
Asked:
JDL129
  • 6
  • 4
1 Solution
 
Minh Võ CôngCommented:
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
 
mlmccCommented:
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
 
JDL129Author Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mlmccCommented:
Look at the code at the end of this question.

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

mlmcc
0
 
JDL129Author Commented:
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
 
JDL129Author Commented:
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
 
mlmccCommented:
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
 
JDL129Author Commented:
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
 
mlmccCommented:
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
 
JDL129Author Commented:
Thanks Eric!!
0
 
JDL129Author Commented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now