Solved

Passing parameter into Crystal Report

Posted on 2004-08-06
14
145,201 Views
Last Modified: 2011-08-18
I am new to Crystal Report.  I am using VB.NET and Crystal Report 8.5

I created a report, when connecting to the database, I only see tables but not store procedures, I have all the access rights but the store procedures not show up in the wizards.  Any way, I just select the table name Fabric and field names.  
I want to display only one record, I look at show sql statement:
 SELECT "Fabric"."FabricID", "Fabric"."CustomerID", "Fabric"."FabricCode", "Fabric"."SMProjectName", "Fabric"."SMPlanID", "Fabric"."SMRoomName", "Fabric"."SupplierName", "Fabric"."PatternAndColor", "Fabric"."QtyReceived", "Fabric"."DocumentID"
 FROM   "enterprise"."dbo"."Fabric" "Fabric"
 WHERE  "Fabric"."FabricID"=222049

The FabricID need to be a parameter field.  So I insert a "Record Selection Formula" and name the field "fabricParam" and it formula was {Fabric.FabricID} = 222049.

My Webform1.aspx.vb code is:

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer

    'Dim crpt As CrystalReport1
    Dim crpt As crFabricLabel

    Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
    Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        'crpt = New CrystalReport1
        crpt = New crFabricLabel
        For Each myTable In crpt.Database.Tables
            myLogin = myTable.LogOnInfo
            myLogin.ConnectionInfo.Password = "pwd"
            myLogin.ConnectionInfo.UserID = "user"
            myTable.ApplyLogOnInfo(myLogin)
        Next

        Dim crParameterFieldDefinitions As ParameterFieldDefinitions
        Dim crParameterFieldDefinition As ParameterFieldDefinition

        Dim crParameterValues As New ParameterValues
        Dim crParameterDiscreteValue As New ParameterDiscreteValue

        'Set discrete value
        crParameterDiscreteValue.Value = CInt("1000002442")

        'Access first parameter field definition
        crParameterFieldDefinitions = crpt.DataDefinition.ParameterFields
        crParameterFieldDefinition = crParameterFieldDefinitions.Item("fabricParam")

        ' Add parameter value
        crParameterValues = crParameterFieldDefinition.CurrentValues

        crParameterValues.Add(crParameterDiscreteValue)

        ' Apply the current value to the parameter definition
        crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)

        CrystalReportViewer1.ReportSource = crpt

    End Sub

End Class

When run the web form I got this error:

Exception Details: CrystalDecisions.CrystalReports.Engine.InvalidArgumentException: Invalid field name.

Source Error:


Line 51:         'Access first parameter field definition
Line 52:         crParameterFieldDefinitions = crpt.DataDefinition.ParameterFields
Line 53:         crParameterFieldDefinition = crParameterFieldDefinitions.Item("fabricParam")
Line 54:
Line 55:         ' Add parameter value
 
So can anybody help me how to put in a parameter field into the Crystal report form so that {Fabric.FabricID} = some variable instead of a pre-defined number.
The error message tell me that I have not define fabricParam anywhere in the form but It is there.
I will get to how to get store procedure, but that will be next.

Many thanks

0
Comment
Question by:tlfeet
  • 6
  • 4
  • 2
  • +2
14 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11740981
It sounds like you've forgotten to create the parameter in your report.  In the crystal designer you need to create a parameter named fabricParam of type number.  Then you want to set your select expert to be

   {Fabric.FabricID} = {?fabricParam}

Now you can pass the parameter from your application to Crystal and it should work as you want.

By the way, check your database options for the stored procedures.  By default you see only tables but you can check the option to see stored procedures and views also if desired.

HTH

frodoman
0
 
LVL 13

Expert Comment

by:EwaldL
ID: 11741963
follow frodoman's steps and it should work.

this code may also help to keep your syntax neat. parameter passing was made possible with a single line of code, ie
rpt.SetParameterValue("{?fabricParam}", 222049)

0
 

Author Comment

by:tlfeet
ID: 11756098
Thanks for the tips.

I bought the Crystal book today, and on the session adding a parameter.  It said to add a parameter to a report select insert, field object.

However, when I right click on the crFabricLabel form designer, the drop down list insert field was gray out.  Also, I go back to add/remove database to bring up the database wizards, I don't see any places for store procedure, it only give me tables and views, and don't see any check box.

So I think that I only have a basic Crystal Report engine. I installed Visual studio.net on my PC and using my local IIS 5.1, do we need addition installation for Crystal Report or down load from Microsoft any addition components?  

I create this Crystal Report in ASP.NET by right click the project name in solution explorer and click add/new item. Then I choose Crystal Report.

By the way, we have MSDN version.  Do we have separate Crystal DVD provided w/ MSDN DVD(s)? I haven't seen the whole MSDN diskettes yet. I worked for a very small company and a silly owner who want to take control of everything but very silly w/ hi-tech.  So every time, if I mumbling “I am stupid”, then I correct myself right away, there is one even more stupid than you.
0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 11756216
You indicated in your first posting that you're using CR8.5.  It sounds like you're actually using the version that comes with Visual Studio which is a stripped down version of CR9 - is that correct?

Within VS to insert a parameter you need to look at the "Field Explorer" window, rt-click on "Parameter Fields", then select "Insert".

HTH

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11756273
Also, assuming you are using the VS .net version to get stored procedures do this:

rt-click in the designer window then select: Designer -> Default Settings -> Database   then check the stored procedures option.

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11756274
You indicated in your first posting that you're using CR8.5.  It sounds like you're actually using the version that comes with Visual Studio which is a stripped down version of CR9 - is that correct?

Within VS to insert a parameter you need to look at the "Field Explorer" window, rt-click on "Parameter Fields", then select "Insert".

HTH

frodoman
0
 
LVL 13

Expert Comment

by:EwaldL
ID: 11756307
the field explorer can be hard to find in case it has been closed. this explains how to get it on screen
http://support.businessobjects.com/library/kbase/articles/c2010362.asp

crystal should be installed automatically with .net, by default it is selected to be installed. some lower versions of .net don't include crystal at all. not sure whiches ones have it though. if you click on "new item" you should see "crystal report", this would confirm if it has been installed correctly
0
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.

 
LVL 42

Expert Comment

by:frodoman
ID: 11756408
Crystal is included w/ all versions of Visual Studio (at least all production versions - I can't say for the beta version if that's still floating around).  If you buy a standalone (non-studio) tool such as VB.Net, C#.Net, etc. then you don't get Crystal with it.
0
 

Author Comment

by:tlfeet
ID: 11757064
U're right, I 've a stripped down version of Crystal Report.  Not sure what version it is, my VS is 2003.  Don't know how to tell what version of Crystal I had.

Thanks for every thing.

But I do have a question.  Do you suggest this book
Professional Crystal Reports for Visual Studio.NET, 2nd Edition
By David McAmis
Publisher Wrox - A Branded Imprint of Wiley

This morning I came to San Diego Tech book store to get this, however they only have 3 books display on the book sheves.  Don't want to wait for 7 - 15 shipping days, I picked up the Using Crystal Enterprise 8.5.  I have to return this book.  So I wonder it is worthed to exchange it with the above book.  This owner is very creedy, I paid out of my pocket.  I don't bother to ask him for reimbursement.

Thanks,
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11757203
>>> Do you suggest this book   Professional Crystal Reports for Visual Studio.NET, 2nd Edition

I haven't used that book so I can't say whether it's good or not.  What I can tell you is

a) I've personally been fairly happy w/ other Wrox books that I've bought.  Some were better than others but only one was really bad so the odds are in your favor.  Check Amazon.com for reviews though and see what other people have said about it...

b) The Crystal Enterprise 8.5 book is going to be useless to you.  Keep in mind that Crystal ENTERPRISE is not the same as Crystal REPORTS.  They're cousins and they work together but they aren't the same thing.  Also the Visual Studio version is much different than Crystal Reports 8.5.

Don't worry about the version - it's just the ".Net" version of Crystal which is an OEM version of Crystal Reports 9.  It's not too stripped down - you can still do 90% of what the standalone version can do and it's still a great tool.

It's your call on the book.  Personally I've never read a Crystal book - I picked it up by playing with it and of course w/ help from people on this site.  If you have some time to work with it and can use this site as a resource then I'd save the money - if you don't have the time for trial and error then I'd definately look for a book like the one you mention.  The .Net version is just different enough that you could get frustrated trying to learn with a "regular" Crystal book.

Good luck!

frodoman
0
 

Author Comment

by:tlfeet
ID: 11757306
Thanks
0
 

Expert Comment

by:rhedrick
ID: 12413751
I used that 2nd edition book and thought it wasn't great.  It's ok if you're just starting but you can't do much with it
0
 

Author Comment

by:tlfeet
ID: 12423257
Thanks, I'd returned it long a go.  Any way, I did write separate windows application to run batch report because we can't not send to printer directly w/o user clicking on print under web application.

I didn't use Crystal report.
0
 

Expert Comment

by:erikleeolson
ID: 12556636
I'm having a similar problem, I can pass the value (incidentally, Ewaldl, your suggestion works, but the short syntax is

rpt.SetParameterValue("fabricParam", 222049)

without the curly braces or question mark)  and my sql command uses the parameters that I pass, specifically, my sql command is thus:

select * from [Runtime Library].dbo.CIFReportCommissionDuednis ('{?WebUserID}','{?WebPassword}')

however I am getting an empty recordset, as though I was passing nulls.  I placed the actual parameter values on the report and they show up as having been set properly (in my case the parameters are both "test1" which in query analyzer returns a full recordset).

Hmmmm I can't figure this one out!  Should I not be using a simply "command" data source?  


0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

13 Experts available now in Live!

Get 1:1 Help Now