Solved

Multiple parameters passed from C# to stored procedure

Posted on 2014-02-18
11
404 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 100

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
 
LVL 100

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 100

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 100

Accepted Solution

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

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
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 …
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 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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now