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