Passing Parameters to Stored Procedures

by Syed Aziz ur Rahman 20. July 2004 23:33

Reports that use stored procedures as their data source are no different than reports that use any other data source. When you open the report, it automatically calls the stored procedure, retrieves the data, and populates the report with this data. The difference between using a stored procedure and using a table is that stored procedures accept parameters as input.

When a report is designed to get its data from a stored procedure, Crystal Reports examines the stored procedure to see if it requires parameters. If so, the designer automatically creates a report parameter that corresponds to each parameter in the stored procedure. There is a one-to-one mapping of report parameters to the parameters in a stored procedure. When the report runs, the report engine takes the value of each of these parameters and automatically passes them to the stored procedure.

As you know, the user is always prompted to enter the parameters before the report can execute. Of course, you probably don't want to prompt the user for this information because your application has already done so via the user interface. To prevent this from happening, manually populate the parameter(s) via code with the information the user has already provided.

Caution! Crystal Reports can't connect to stored procedures that have output parameters defined. When attempting to do so, the report returns the error "Failed to open a rowset" and doesn?t display any data.

Listing shows how to use the viewer control to populate the paramter fields that will be passed to the stored procedure.

Private Sub SpWithViewer(ByVal UserId As String, ByVal Password As StringByVal SpParameter As String)    'Logon to the server
    Dim crReport As New CustomerSP()
    Dim crTable As CrystalDecisions.CrystalReports.Engine.Table
    Dim crLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()
    CrystalReportViewer1.LogOnInfo = New CrystalDecisions.Shared.TableLogOnInfos()
    crLogonInfo.TableName = "spCustomers;1"

    With crLogonInfo.ConnectionInfo
        .ServerName = "(local)"
        .DatabaseName = "Northwind"
        .UserID = UserId
        .Password = Password

    'Create the parameter
    Dim ParameterFields As CrystalDecisions.Shared.ParameterFields
    Dim ParameterField As CrystalDecisions.Shared.ParameterField
    Dim ParameterRangeValue As CrystalDecisions.Shared.ParameterRangeValue
    Dim spValue As CrystalDecisions.Shared.ParameterDiscreteValue

    ParameterFields = New CrystalDecisions.Shared.ParameterFields()
    ParameterField = New CrystalDecisions.Shared.ParameterField()
    ParameterField.ParameterFieldName = "@CustPattern"
    spValue = New CrystalDecisions.Shared.ParameterDiscreteValue()
    spValue.Value = SpParameter
    CrystalReportViewer1.ParameterFieldInfo = ParameterFields

    'Show the report
    CrystalReportViewer1.ReportSource = crReport

End Sub

The first half logs onto the data source with the appropriate server name, database name, and login credentials. Notice that the TableName property is the name of the stored procedure with ?;1? shown at the end of it. This is how Crystal Reports identifies the stored procedures and you have to remember to include it as part of the table name. The second half of the code creates a new parameter field, adds is to the parameter fields collection and assigns this collection to the report viewer.You can also pass parameter using the ReportDocument object

Tip: If you want to pass a NULL value to a stored procedure parameter, set the parameter's Value property to Nothing in VB.Net and null in C#.


Although this section assumes you already familiar with stored procedures, there is one statement that doesn't get much attention but is very important.


If you are working with simple stored procedures, then the majority of them consist of a SELECT statement followed by a list of tables, fields and a join method. Crystal Reports handles this type of stored procedure fine. Once you start getting into writing more complex stored procedures you will find that you often need to execute multiple SQL statements within one stored procedure. This can happen when you are using temporary tables, and updating data prior to executing the final SELECT statement.


In the above code, the SET NOCOUNT ON statement is commented out. Running this code will generate two output messages for each statement. They will be in the format of ?xx records affected?. This message is passed prior to the records being returned from the stored procedure. This conflicts with what the report is expecting. Thus, it doesn't use the data from the SELECT statement as its resultset. By uncommenting the first line of code, you tell the database server not to report how many records are affected. This eliminates Crystal Reports from incorrectly using these messages as part of the database. Ideally, this statement would be the first statement in every stored procedure.




ASP.Net | DotNet

Comments (1) -

7/22/2004 3:56:00 AM #

HI Aziz
        Thanks , above one is working well , How can i user multiple parameter inthis code
Thanking you

8/20/2013 8:58:10 AM #

Select Sites offer a pitch and ferry reservation service for both ­independent campers  lslinstruments.org - www.lslinstruments.org/.../  and caravanners as well as 84 cheques drawn to the tune of $360, 493 and 40 cash withdrawls totaling $266, 605.

termite control Arizona
termite control Arizona
8/25/2013 2:22:40 PM #

Wow that was odd. I just wrote an very long comment but after I clicked submit my comment didn't appear. Grrrr... well I'm not writing all that over again. Regardless, just wanted to say superb blog!

Here is my web-site -  termite control Arizona - http://intra.phimart.com/?document_srl=353145

Meanwhile, insurers have similar deals in place with repair firms, which adds £155 to the cost of motor insurance. Simon Douglas, director of AA Insurance, said that despite the sharp rise in premiums.

My page motor trade insurance ( smssaff.sagada.org/.../the-significance-of-finding-a-good-motor - smssaff.sagada.org/.../the-significance-of-finding-a-good-motor )

sold Out after crisis review
sold Out after crisis review
9/3/2013 3:46:02 AM #

Hi there, its good piece of writing concerning media print, we all know media is a great source of information.

Here is my webpage ...  sold Out after crisis review - http://www.youtube.com/watch?v=DW96Sth_FN8

motor trade insurance
motor trade insurance
9/4/2013 4:38:51 PM #

Mr Cooper asked if there was any grace period during which the cost of  motor trade insurance - http://www.bioingenios.ira.cinvestav.mx:81/tallerBS2011/index.php/Updated_Tips_On_Vital_Criteria_In_Anchor-Text_--_Convenient_Tips  is" pricing young people out of driving", according to the AA Index started in 1994. I think it's time for an inquiry that could take up to two years, the discount rate increases to 25% at the time, then claim the cost back when you return. Companies that will provide insurance for young drivers could rise considerably. Last month, the government said it had" reasonable grounds for suspecting that there are features of the market that prevent, restrict or distort competition.

Find More Info
Find More Info
9/17/2013 12:18:56 AM #

Have you ever considered writing an e-book or guest authoring on other websites? I have a blog based on the same subjects you discuss and would really like to have you share some stories/information. I know my viewers would value your work. If you are even remotely interested, feel free to shoot me an e mail.

Also visit my blog:  Find More Info - beckham-magazine.com/.../profile.php

Wonderful work! This is the kind of info that are meant to be shared across the net. Shame on Google for no longer positioning this publish higher! Come on over and consult with my site . Thank you =)

Feel free to surf to my blog  best text message marketing companies - http://tiny.ly/1294

eugene limousines
eugene limousines
10/1/2013 1:20:44 AM #

Saved as a favorite, I like your blog!

Look into my homepage:  eugene limousines - http://www.TheEugenePartybus.com

brutus tile tile cutter how to use
brutus tile tile cutter how to use
10/15/2013 7:16:00 PM #

Thank you a bunch for sharing this with all of us you really understand what you are talking about! Bookmarked. Kindly also visit my site =). We will have a hyperlink exchange contract among us

Check out my page  brutus tile tile cutter how to use - www.bartlettairheat.com/.../bartlett-heating-and-air-compares-the-three-carrier-series-air-conditioners

Mud runs 2014
Mud runs 2014
10/30/2013 5:29:42 AM #

When someone writes an article he/she retains the idea of a user in his/her brain that how a user can be aware of it. So that's why this article is perfect. Thanks!

my page;  Mud runs 2014 - dan.math.kyushu-u.ac.jp/.../...hio_Techniques.html

Fitness Clubs Toledo Ohio
Fitness Clubs Toledo Ohio
10/30/2013 7:55:07 AM #

Nice blog here! Also your website loads up very fast! What host are you using? Can I get your affiliate link to your host? I wish my site loaded up as fast as yours lol

my page ::  Fitness Clubs Toledo Ohio - nstreet.blackburnarch.com/.../Club.html

gyms in toledo ohio
gyms in toledo ohio
10/30/2013 8:38:09 AM #

Fine way of describing, and good article to take data regarding my presentation topic, which i am going to convey in college.

Visit my site;  gyms in toledo ohio - comedu.andong.ac.kr/xe/?document_srl=1854189

gyms in perrysburg ohio
gyms in perrysburg ohio
10/30/2013 8:48:59 AM #

Your style is very unique in comparison to other folks I've read stuff from. Many thanks for posting when you've got the opportunity, Guess I'll just book mark this page.

Have a look at my web-site ...  gyms in perrysburg ohio - www.lazarus.or.kr/.../

gyms in perrysburg ohio
gyms in perrysburg ohio
10/30/2013 5:49:08 PM #

Very soon this web site will be famous amid all blogging viewers, due to it's pleasant content

my web-site ::  gyms in perrysburg ohio - nopro.boulderkoreanchurch.org/.../

gyms in perrysburg ohio
gyms in perrysburg ohio
10/30/2013 11:25:22 PM #

Hi there mates, nice article and good arguments commented here, I am genuinely enjoying by these.

Also visit my web blog ::  gyms in perrysburg ohio - http://www.www.vincistar.us/b02_sub1_1/786309

gyms in toledo ohio
gyms in toledo ohio
10/31/2013 5:41:20 PM #

Informative article, just what I was looking for.

my web blog -  gyms in toledo ohio - www.agri.kmitl.ac.th/.../%E0%B8%9C%E0%B8%B9%E0%B9%89%E0%B9%83%E0%B8%8A%E0%B9%89:LakeshaMoller

mud run ohio
mud run ohio
11/1/2013 6:53:22 AM #

Hello, yes this paragraph is genuinely pleasant and I have learned lot of things from it concerning blogging. thanks.

Also visit my homepage -  mud run ohio - http://edufriend.co.kr/se/?document_srl=275118

carpet cleaners nj
carpet cleaners nj
11/2/2013 9:56:50 PM #

To begin with, business carpet cleaning devices have bigger temperature and pressure amounts for more thorough cleansing. This belt can wear out over time and eventually either break or get so loose that it doesn't actually turn the brush roller. If that is the case, it would be a good idea if you go to establishments which offer carpet cleaning in Atlanta.

My webpage -  carpet cleaners nj - http://www.youtube.com/watch?v=2ZOrENaTSIs

Gyms In Perrysburg Ohio
Gyms In Perrysburg Ohio
11/6/2013 10:02:33 AM #

What's up to every one, the contents existing at this website are genuinely remarkable for people experience, well, keep up the good work fellows.

Stop by my web site  Gyms In Perrysburg Ohio - mcculloch.ciber.ulpgc.es/wiki/index.php/Usuario:BrandenGomez

mud run ohio
mud run ohio
11/6/2013 7:59:23 PM #

Excellent blog! Do you have any tips for aspiring writers? I'm hoping to start my own site soon but I'm a little lost on everything. Would you advise starting with a free platform like Wordpress or go for a paid option? There are so many choices out there that I'm totally confused .. Any ideas? Cheers!

Here is my webpage:  mud run ohio - wiki.constellio.com/.../Nourishment_Tips_For_Ohio_Mud_Runners

mud run ohio
mud run ohio
11/6/2013 8:38:12 PM #

Hi there to every single one, it's really a pleasant for me to pay a quick visit this web site, it contains priceless Information.

My page ...  mud run ohio - wiki.kspu.karelia.ru/.../Nutrition_Thought_For_Ohio_Mud_Runners

ohio mud run
ohio mud run
11/7/2013 1:00:44 AM #

Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You clearly know what youre talking about, why waste your intelligence on just posting videos to your site when you could be giving us something enlightening to read?

my webpage -  ohio mud run - http://chess.geosciences.ensmp.fr/author/NidaV99

merchant services uk
merchant services uk
11/9/2013 9:41:21 AM #

What's Going down i am new to this, I stumbled upon this I have found It positively useful and it has aided me out loads. I am hoping to contribute & assist different customers like its helped me. Great job.

Here is my blog -  merchant services uk - http://www.lowestratemerchantservices.com

webcam chatroulette free girls
webcam chatroulette free girls
11/11/2013 5:18:02 PM #

I couldn't refrain from commenting. Perfectly written!

my homepage ::  webcam chatroulette free girls - http://cubiclerelief.com/view_topic.php?tid=105

I'm not sure exactly why but this website is loading very slow for me. Is anyone else having this issue or is it a issue on my end? I'll check back later on and see if the problem still exists.

My blog post - raspberry ketones ( http://www.youtube.com/watch?v=uU4_bQ9zBAU - http://www.youtube.com/watch?v=uU4_bQ9zBAU )

Hi, its good paragraph on the topic of media print, we all be aware of media is a great source of information.

Also visit my blog;  credit card processing machine terminals with wifi - http://www.lowestratemerchantservices.com

Powered by BlogEngine.NET
Original Design by Laptop Geek, Adapted by onesoft