We help IT Professionals succeed at work.
Get Started

VB.Net & Crystal Reports with subreports table connections

1,093 Views
Last Modified: 2015-06-22
I have been attempting to write a small VB.net (Community version) app to print Crystal report (XI version). The app populates a combo box based on user of available reports from a MySQL db. Almost all of the reports are using ODBC to connect to an AS400 db table(s) to extract data. If the report does not have a sub-report everything works well. If the report does have a sub-report it starts to load the crystal viewer then it pops a log on box asking for username and password. It does not matter what credentials are used it returns log on failed, please try again.

I have tried a lot of different solutions that I have found on the web, but none solve my issue. Here is the current code that is setting up to display the report:

   Friend Function ViewReport(ByVal sReportName As String, Optional ByVal sSelectionFormula As String = "", Optional ByVal param As String = "") As Boolean

        Dim intCounter As Integer
        Dim objReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo
        Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue
        Dim currValue As CrystalDecisions.Shared.ParameterValues
        Dim mySubReportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject
        Dim mySubRepDoc As New CrystalDecisions.CrystalReports.Engine.ReportDocument
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crTables As Tables
        Dim crTable As Table
        Dim crSubTables As Tables
        Dim crSubTable As Table

        Dim strParValPair() As String
        Dim strVal() As String
        Dim index As Integer


        Try

            'Load the report
            objReport.Load(sReportName)
            ConInfo.ConnectionInfo.UserID = gUserName
            ConInfo.ConnectionInfo.Password = gPassword
            ConInfo.ConnectionInfo.ServerName = "assist"
            ConInfo.ConnectionInfo.DatabaseName = "assist"
            crTables = objReport.Database.Tables
            For Each crTable In crTables
                crtableLogoninfo = crTable.LogOnInfo
                crtableLogoninfo.ConnectionInfo = ConInfo.ConnectionInfo
                crTable.ApplyLogOnInfo(crtableLogoninfo)
            Next
            intCounter = objReport.DataDefinition.ParameterFields.Count
            If intCounter = 1 Then
                If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then
                    intCounter = 0
                End If
            End If

            If intCounter > 0 And Trim(param) <> "" Then
                strParValPair = param.Split("&")

                For index = 0 To UBound(strParValPair)
                    If InStr(strParValPair(index), "=") > 0 Then
                        strVal = strParValPair(index).Split("=")
                        paraValue.Value = strVal(1)
                        currValue = _
                            objReport.DataDefinition.ParameterFields(strVal(0)).CurrentValues
                        currValue.Add(paraValue)
                        objReport.DataDefinition.ParameterFields(strVal(0)).ApplyCurrentValues(currValue)
                    End If
                Next
            End If

            For index = 0 To objReport.ReportDefinition.Sections.Count - 1
                For intCounter = 0 To _
                    objReport.ReportDefinition.Sections(index).ReportObjects.Count - 1
                    With objReport.ReportDefinition.Sections(index)
                        If .ReportObjects(intCounter).Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                            mySubReportObject = CType(.ReportObjects(intCounter), CrystalDecisions.CrystalReports.Engine.SubreportObject)
                            mySubRepDoc = mySubReportObject.OpenSubreport(mySubReportObject.SubreportName)
                            crSubTables = mySubRepDoc.Database.Tables
                            For Each crSubTable In crSubTables
                                crtableLogoninfo = crSubTable.LogOnInfo
                                crtableLogoninfo.ConnectionInfo = ConInfo.ConnectionInfo
                                crSubTable.ApplyLogOnInfo(crtableLogoninfo)
                            Next
                        End If
                    End With
                Next
            Next
            If sSelectionFormula.Length > 0 Then
                objReport.RecordSelectionFormula = sSelectionFormula
            End If
            CrystalReportViewer1.ReportSource = Nothing

            CrystalReportViewer1.ReportSource = objReport

            CrystalReportViewer1.Show()
            Return True
        Catch ex As System.Exception
            Dim ErrorInfo As String
            ErrorInfo = "Exception: " & ex.Message & Environment.NewLine & Environment.NewLine
            If ex.InnerException IsNot Nothing Then
                ErrorInfo &= "InnerException: " & ex.InnerException.Message
            End If
            MessageBox.Show(ErrorInfo)
        End Try
        Return True
    End Function
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 4 Answers and 11 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE