Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiple parameters passed from C# to stored procedure

Posted on 2014-02-18
11
Medium Priority
?
531 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
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

972 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