Search code examples
vb.netcrystal-reports

Passing Parameters in Crystal report with linked parameters

I changed the title from: Error In File Temp: Operation illegal on linked parameter

To: Passing Parameters in Crystal report

I Think I know how to pass parameters from main report to subreport, I did something like this:

enter image description here

I can now check through crystal reports preview how my reports look like, and everything is OK.

However, when I need to call the report to VB.Net, I use this code.

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports System.Data


Public Class rptXbar

    Private Sub rptXbar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadXbar(PartNoRep, DimItmRep)

    End Sub

    Sub LoadXbar(ByVal PartNo As String, ByVal DimItem As String)
        Dim sqlcom As New SqlCommand
        Dim dt As New DataTable
        Dim dta As New SqlDataAdapter
        Dim crConnectionInfo As New ConnectionInfo
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim repX As New XbarRchart
        Try
            repX.Load(System.AppDomain.CurrentDomain.BaseDirectory() & "XbarRchart.rpt")
            With sqlcom
                Me.Cursor = Cursors.WaitCursor
                conn.Open()
                .Connection = conn
                .CommandTimeout = 100
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.XBarRChart"
                .Parameters.AddWithValue("@PartNo", PartNo)
                .Parameters.AddWithValue("@DimItem", DimItem)
                .ExecuteNonQuery()
            End With
            dt.Clear()
            dta = New SqlDataAdapter(sqlcom)
            dta.Fill(dt)
            repX.SetDatabaseLogon(userid, password, dtsrc, initcat)
            repX.SetParameterValue("@PartNo", PartNo)
            repX.SetParameterValue("@DimItem", DimItem)

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRHeader.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRHeader.rpt")

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRDetails.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRDetails.rpt")

            repX.SetDataSource(dt)
            Me.CrystalReportViewer1.ReportSource = repX
            Me.CrystalReportViewer1.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Me.Cursor = Cursors.Default
        End Try
        conn.Close()
    End Sub
End Class

A error would pop up, it would say:

enter image description here

If I remove the links, the parameters would ask for input, If I remove this code

        repX.SetParameterValue("@PartNo", PartNo, "MIPIRHeader.rpt")
        repX.SetParameterValue("@DimItem", DimItem, "MIPIRHeader.rpt")

        repX.SetParameterValue("@PartNo", PartNo, "MIPIRDetails.rpt")
        repX.SetParameterValue("@DimItem", DimItem, "MIPIRDetails.rpt")

It would also ask for parameters, is there any suggestions on how I could fix this problem?

Solution

  • I finally found the answer!

    Sub LoadXbar(ByVal PartNo As String, ByVal DimItem As String)
        Dim sqlcom As New SqlCommand
        Dim dt As New DataTable
        Dim dta As New SqlDataAdapter
        Dim crConnectionInfo As New ConnectionInfo
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim repX As New XbarRchart
        Try
            repX.Load(System.AppDomain.CurrentDomain.BaseDirectory() & "XbarRchart.rpt")
            With sqlcom
                Me.Cursor = Cursors.WaitCursor
                conn.Open()
                .Connection = conn
                .CommandTimeout = 100
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.XBarRChart"
                .Parameters.AddWithValue("@PartNo", PartNo)
                .Parameters.AddWithValue("@DimItem", DimItem)
                .ExecuteNonQuery()
            End With
            dt.Clear()
            dta = New SqlDataAdapter(sqlcom)
            dta.Fill(dt)
            repX.SetDatabaseLogon(userid, password, dtsrc, initcat)
            repX.SetParameterValue("@PartNo", PartNo)
            repX.SetParameterValue("@DimItem", DimItem)

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRHeader.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRHeader.rpt")

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRDetails.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRDetails.rpt")

            repX.SetDataSource(dt)
            Me.CrystalReportViewer1.ReportSource = repX
            Me.CrystalReportViewer1.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Me.Cursor = Cursors.Default
        End Try
        conn.Close()
    End Sub

    This is the code where I populate my main report data and the subreports, if you would notice repX.SetDataSource(dt) is below the codes where I populate the subreports, I changed the code into the following:

    Sub LoadXbar(ByVal PartNo As String, ByVal DimItem As String)
        Dim sqlcom As New SqlCommand
        Dim dt As New DataTable
        Dim dta As New SqlDataAdapter
        Dim crConnectionInfo As New ConnectionInfo
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim repX As New XbarRchart
        Try
            repX.Load(System.AppDomain.CurrentDomain.BaseDirectory() & "XbarRchart.rpt")
            With sqlcom
                Me.Cursor = Cursors.WaitCursor
                conn.Open()
                .Connection = conn
                .CommandTimeout = 100
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.XBarRChart"
                .Parameters.AddWithValue("@PartNo", PartNo)
                .Parameters.AddWithValue("@DimItem", DimItem)
                .ExecuteNonQuery()
            End With
            dt.Clear()
            dta = New SqlDataAdapter(sqlcom)
            dta.Fill(dt)
            repX.SetDatabaseLogon(userid, password, dtsrc, initcat)
            'New Location
            repX.SetDataSource(dt)
            repX.SetParameterValue("@PartNo", PartNo)
            repX.SetParameterValue("@DimItem", DimItem)

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRHeader.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRHeader.rpt")

            repX.SetParameterValue("@PartNo", PartNo, "MIPIRDetails.rpt")
            repX.SetParameterValue("@DimItem", DimItem, "MIPIRDetails.rpt")


            Me.CrystalReportViewer1.ReportSource = repX
            Me.CrystalReportViewer1.Refresh()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Me.Cursor = Cursors.Default
        End Try
        conn.Close()
    End Sub

    And my code worked! So please do be careful also when placing your commands in VB.net, you might have placed it somewhere wrong.