Data Connectivity
 Crystal Reports Forum : Crystal Reports .NET 2003 : Data Connectivity
Message Icon Topic: Passing a Crystal report parameter to stored proc. Post Reply Post New Topic
Page  of 2 Next >>
Author Message
sajesh
Newbie
Newbie


Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
Quote sajesh Replybullet Topic: Passing a Crystal report parameter to stored proc.
    Posted: 25 Jan 2008 at 1:22pm
Hi,
I am really new to crystal report. I am trying to do a report which accepts one parameter and use that parameter in a stored procedure in CR. I am not able to find exact syntax for executing SP. Please help me ? It will be great if  you can provide some example .orn suggest any other way to get records depending on the parameter passed through webform to CR.
 
Thanks in advance 
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 25 Jan 2008 at 1:47pm
You can select a stored procedure just as you would any other database table. However, most people don't see it listed b/c that option is turned off on the computer. Go to File > Options and click the Database tab. Then check the box for Stored Procedures. Now it should show up in your list.

If your stored procedure has an input paramter, Crystal Reports will automatically create a corresponding parameter for you in the report and give it the same name as what is in the stored procedure.

Lastly, since you are using CR.NET 2003, you need to prepopulate the parameter with a value from the webform before calling the report. This is a bit tricky b/c different things can go wrong. This is MUCH easier with .NET 2005 though.

Are you coding in C# or VB.NET?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
sajesh
Newbie
Newbie


Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
Quote sajesh Replybullet Posted: 28 Jan 2008 at 6:21am
Thanks a lot.....
I am using c#.  I am trying to work on your sugestion. I will be back with result.
Thanks a lot.... for the help
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 28 Jan 2008 at 9:34am
Ok. Also, I have a complete chapter that talks about all this and gives complete code samples in my book Crystal Reports .Net Programming

Edited by BrianBischof - 28 Jan 2008 at 9:34am
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
sajesh
Newbie
Newbie


Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
Quote sajesh Replybullet Posted: 28 Jan 2008 at 1:57pm
Thanks .... I got it. Actually the SPs are not showing in VS. So i used the CR10 environment.some how I made it.THanks a lot.
 
Appreciated
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 28 Jan 2008 at 3:01pm
Cool. If you don't see the stored procedures, you can turn them on by going to Crystal Reports, Design > Default Settings > Database tab. Check the box for stored procedures.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
sajesh
Newbie
Newbie


Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
Quote sajesh Replybullet Posted: 29 Jan 2008 at 10:20am
HI, I have one more trouble in formula. After executing SP I get 4 diffrent tables.How can I check if  one coloumn values is null then next tables diffrent coloum value so on.
 
thanks
IP IP Logged
Hyvong
Newbie
Newbie


Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
Quote Hyvong Replybullet Posted: 19 Feb 2008 at 12:52pm
Hi there,
I'm using VS 2005 with SQL Server 2005.
Have any one ever run into this issue, i passed the parameters into the reportDocument, and i know that the report is receiving it, but somehow the report doesn't pass these parameter values into its stored procedure.  Is there some sort of setting or anything we have to do to pass these values to the report's stored procedure's required parameters?
Are there a particular order i have to do when comes to passing the database connecting or passing parameter?
Note: All of my parameter names are unique.
I would appreciate if someone can help me with this issue.
 
This is the error i received:
Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Procedure or Function 'ReportStandardPartSummary' expects parameter '@p_ProfileKey', which was not supplied. ....
 
 
This is how i passed my parameter value:

' Declare the parameter related objects.

Dim crParameterDiscreteValue As ParameterDiscreteValue

Dim crParameterFieldDefinitions As ParameterFieldDefinitions

Dim crParameterFieldLocation As ParameterFieldDefinition

Dim crParameterValues As ParameterValues

' Get the report's parameters collection.

crParameterFieldDefinitions = reportDocument.DataDefinition.ParameterFields

' - Set the first parameter

' - Get the parameter, tell it to use the current values vs default value.

' - Tell it the parameter contains 1 discrete value vs multiple values.

' - Set the parameter's value.

' - Add it and apply it.

' - Repeat these statements for each parameter. (my report param)

crParameterFieldLocation = crParameterFieldDefinitions.Item("CompanyCode")

crParameterValues = crParameterFieldLocation.CurrentValues

crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

crParameterDiscreteValue.Value = "ABC"

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

'Stored proc param

crParameterFieldLocation = crParameterFieldDefinitions.Item("@p_ProfileKey")

crParameterValues = crParameterFieldLocation.CurrentValues

crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue

crParameterDiscreteValue.Value = ProfileKey.ToString

crParameterValues.Add(crParameterDiscreteValue)

crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

Love your love ones like there is no tomorrow.
IP IP Logged
Hyvong
Newbie
Newbie


Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
Quote Hyvong Replybullet Posted: 19 Feb 2008 at 1:33pm
I even tried to pass my parameters this way, and still receive the error.

reportFileName = "StandardPartSummaryReport.rpt" 'Set the Report File Name based on the ReportEnum

reportDocument.Load(reportFilePath + reportFileName) 'Load the Report file

Dim tableLogonInfo As New TableLogOnInfo

tableLogonInfo.ConnectionInfo = myConnectionInfo

'Set logon info for the main report

For Each reporTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables

reporTable.ApplyLogOnInfo(tableLogonInfo)

Next

'Set logon info for any sub report in the main report

For Each subReportDocument As ReportDocument In reportDocument.Subreports

For Each subReportTable As CrystalDecisions.CrystalReports.Engine.Table In subReportDocument.Database.Tables

Try

subReportTable.ApplyLogOnInfo(tableLogonInfo)

Catch ex As Exception

End Try

Next

Next

(instead of the code from prev post, this is what i have)
 
reportDocument.SetParameterValue("CompanyCode", "USF")

reportDocument.SetParameterValue("@p_ProfileKey", ProfileKey.ToString)

myCrystalReportViewer.ReportSource = reportDocument ' Set the Crytal Report Viewer control's source to the report document.

myCrystalReportViewer.DataBind()

Love your love ones like there is no tomorrow.
IP IP Logged
Hyvong
Newbie
Newbie


Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
Quote Hyvong Replybullet Posted: 20 Feb 2008 at 5:27am
After some further testing, i think the parameter begins with @ are the one that causes issues.  (I think).  These @ parameters are my stored proc's parameter, however, they have a different datatype (hmm...)
Any help is greatly appreciated.
Any one??Stern%20Smile
 
Just wondering, is it because my stored proc's parameter has a type of GUID...
I noticed that in the report file, it treats these @ parameters as string datatype.


Edited by Hyvong - 20 Feb 2008 at 2:05pm
Love your love ones like there is no tomorrow.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.