How to pass parameters to crystal report from vb.net code

3
votes

I have created a crystal report (cross tab). I'm not using any dataset, instead I used the wizard in crystal report to call an procedure from my Database schema (Provider given is Microsoft OLEDB provider for oracle, after which I gave my DB credentials(i.e. schema, username, password) and selected the procedure and selected the columns I wanted to display in the report).

There are 5 parameters that I need to pass it from the front end to generate the report. While viewing the crystal report preview, by giving parameters, the report works fine.

Now i want to pass these 5 parameters from the front end(vb.net) to show the report in the CrystalReportViewer. Please suggest the code to write in aspx.vb file. (PS:- I did go through other forums and found out some code, but all of them were giving some or the other error, so am posting one so that i can get the code specific to my requirement).

Thanks in advance..

2
vb.netcrystal-reports
Please refer following threads for reference: > [Tip: How to pass Crystal Report Parameters Programmatically? ](c-sharpcorner.com/UploadFile/mahesh/paramInCR11012006132839PM/…) OR > How to pass discrete parameters to Crystal Reports - Himanshu Jansari
I did try what was written in the first link but in it they are declaring a dataset and setting the datasource of report as dataset but no where i can see they populating the dataset.. also am not using any dataset but calling the procedure while making crystal report(.rpt) using oledb provider.... - Gunner
@Gunner Hi are you using stored procedures parameters for the report or just the crystal report? - Androidz

2 Answers

5
votes

I have gotten the report to work... I wrote the code below:

    Dim RptDocument As New ReportDocument

    RptDocument.Load(Server.MapPath("rpt\Report.rpt"))

    RptDocument.SetParameterValue("param1", Session("param1"))
    RptDocument.SetParameterValue("param2", ddlparam2.SelectedValue)
    RptDocument.SetParameterValue("param3", param3.text)
    RptDocument.SetParameterValue("param4", param4.text)
    RptDocument.SetParameterValue("param5", param5.text)

    'Set login info
    Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

    Dim myTable As Table
    For Each myTable In RptDocument.Database.Tables
        myLogin = myTable.LogOnInfo
        myLogin.ConnectionInfo.ServerName = "server name"
        myLogin.ConnectionInfo.DatabaseName = ""
        myLogin.ConnectionInfo.UserID = "userid"
        myLogin.ConnectionInfo.Password = "pwd"
        myTable.ApplyLogOnInfo(myLogin)
        myTable.Location = myTable.Location

    CrystalReportViewer1.ReportSource = RptDocument

Created a System DNS and had to add Oracle.DataAccess.dll to reference and a class file (with functions same as that in connectooracle.vb class file but with different name), also set up a connection in global.asax to refer to that class connection and using Imports Oracle.DataAccess.Client instead of Imports System.Data.OracleClient (to avoid ambiguity)...

This somehow made it work and there might be some other solution for that..:) 

(For ref:- Adding  myLogin.ConnectionInfo.IntegratedSecurity = True  gave me this error--
Logon failed. Error in File C:\DOCUME~1\Username\LOCALS~1\Temp\Report {1AG3DD86-141D-43YA-B6A2-AEDF3459AE49}.rpt: Unable to connect: incorrect log on parameters.)
2
votes

This works for me and I'm using Visual Studio 2008 for this one since VS2010 doesn't have crystal engine for the reference.

First, make sure you have imported these two:

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Now, on my part I was using the odbc because as what I have noticed crystal report works fine with this and since we are working with odbc. So I did not include the login property on the report in my code. On the report just choose odbc connection.

Private Sub ReportViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim cryRpt As New ReportDocument

        Dim str1 As String


Try
            str1 = Title.str1
            str2  =Title.str2


            cryRpt.Load("c:\Program Files\Report\" & str2 & "")


            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldDefinition As ParameterFieldDefinition
            Dim crParameterValues As New ParameterValues
            Dim crParameterDiscreteValue As New ParameterDiscreteValue


                crParameterDiscreteValue.Value = strStore
                crParameterFieldDefinitions = cryRpt.DataDefinition.ParameterFields
                crParameterFieldDefinition = crParameterFieldDefinitions.Item("Store")
                crParameterValues = crParameterFieldDefinition.CurrentValues

                crParameterValues.Clear()
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldDefinition.ApplyCurrentValues(crParameterValues)


            rptviewer.Cursor = Cursors.AppStarting
            rptviewer.ReportSource = cryRpt
            rptviewer.Refresh()
            rptviewer.Cursor = Cursors.Default
        Catch ex As Exception
            MsgBox(ex.Message)
            Me.Close()
            ReportInterface.Show()
        End Try
    End Sub

I have a class that will get the data inputted by the user. But this will not work using stored procedure parameters.

please mark as accepted if it works for you

Thank You