locked
Pass a value to a Store procedure in Crystal Report RRS feed

  • Question

  • User546194788 posted

    I created a CR which data source is a store procedure.

    Within the store procedure there is one parameter. 

    How to pass a value to the parameter and display CR on the screen?

    Friday, July 16, 2010 3:43 PM

All replies

  • User1224194097 posted

    If you have a report with parameters, you can pass the value by finding the parameter by name and applying the value to the parameter

    try this:

     Public Function GetReport(ByVal stringFromDate As String, _
                             ByVal stringToDate As String, ByVal stringOfficeID As String) _
                    As CrystalDecisions.CrystalReports.Engine.ReportClass
            Try
                ' Create an instance of the report and set it's datasource.
                Dim sType As String = "ReportsNameSpace.Report"
                Dim reportType As Type = Type.[GetType](sType, True, True)
                If reportType IsNot Nothing Then
                    ' Create an instance of the report and set it's datasource.
                    Dim rpt As CrystalDecisions.CrystalReports.Engine.ReportClass = DirectCast(Activator.CreateInstance(reportType),  _
                              CrystalDecisions.CrystalReports.Engine.ReportClass)
                    Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                    crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields
                    Dim en As SCRCollectionEnumerator = crParameterFieldDefinitions.GetEnumerator()
                    While (en.MoveNext())
                        Dim param As ParameterFieldDefinition
                        param = en.Current
                        Dim crValue As New ParameterValues()
                        ' you have to check for parameter name 
                        ' and pass values
                        If (param.Name = "@FromDate") Then
                            crValue.AddValue(stringFromDate)
                            param.ApplyCurrentValues(crValue)
                        ElseIf (param.Name = "@ToDate") Then
                            crValue.AddValue(stringToDate)
                            param.ApplyCurrentValues(crValue)
                        End If
                    End While
                    Dim logOnInfo As New TableLogOnInfo()
                    ' I am assuming that you have crystal server database 
                    ' connection strings in your web.config
                    logOnInfo.ConnectionInfo.ServerName = ConfigurationManager.AppSettings("CrystalServer").ToString()
                    logOnInfo.ConnectionInfo.UserID = ConfigurationManager.AppSettings("CrystalUser").ToString()
                    logOnInfo.ConnectionInfo.Password = ConfigurationManager.AppSettings("CrystalPassword").ToString()
                    Dim login As TableLogOnInfo
                    For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rpt.Database.Tables
                        login = tbl.LogOnInfo
                        login.ConnectionInfo.ServerName = ConfigurationManager.AppSettings("CrystalServer").ToString()
                        login.ConnectionInfo.UserID = ConfigurationManager.AppSettings("CrystalUser").ToString()
                        login.ConnectionInfo.Password = ConfigurationManager.AppSettings("CrystalPassword").ToString()
                        tbl.ApplyLogOnInfo(login)
                    Next
                    Return rpt
                Else
                    Return Nothing
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Function

    try this and let me know.

    Friday, July 16, 2010 7:17 PM