• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145425
  • Last Modified:

Passing parameter into Crystal Report

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
tlfeet
Asked:
tlfeet
  • 6
  • 4
  • 2
  • +2
1 Solution
 
frodomanCommented:
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
 
EwaldLCommented:
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
 
tlfeetAuthor Commented:
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
frodomanCommented:
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
 
frodomanCommented:
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
 
frodomanCommented:
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
 
EwaldLCommented:
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
 
frodomanCommented:
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
 
tlfeetAuthor Commented:
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
 
frodomanCommented:
>>> 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
 
tlfeetAuthor Commented:
Thanks
0
 
rhedrickCommented:
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
 
tlfeetAuthor Commented:
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
 
erikleeolsonCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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