VB.Net & Crystal Reports with subreports table connections
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
Visual Basic.NETCrystal Reports.NET Programming
Last Comment
RSchnabel
8/22/2022 - Mon
Mike McCracken
Subreports always cause issues.
You need code that
finds the subreport
opens it as a report
applies the logon criteria
It is a mirror of your code but there is a loop around it like
For each reportObject in reportobjectset
if objecttype = subreport then
setup logon criteria
end if
end loop
I am not a .Net programmer so I don't have an example.
I'll see if I can find an example from SAP/Crystal.
mlmcc
RSchnabel
ASKER
Thank you for your response, but I think that the loop around it is there. The top level for index loop cycles through all objects in the report and the for intCounter loop cycles through the sections, then if the object is a subreport then loop for each table in the subReport. But if you have some example that works I am more that happy to give it a shot. I have looked at numerous ways to do this and none have panned out for me.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Unlimited question asking, solutions, articles and more.
RSchnabel
ASKER
Vasto - thanks for the links, and if I have to go down that path I will, but I would prefer to work through the issues to resolve them.
habbibb - I will give this a shot and let you know.
Your help has saved me hundreds of hours of internet surfing.
fblack61
RSchnabel
ASKER
I have figured out what the real issue is. While it is true that any of the reports that have a sub-report failed, that led me down the wrong path (and I led you down the wrong path). I am not the author of the reports, so I was not looking at the reports that close. All of the reports with sub reports were written by someone who decided to use both MS access and DB2, so two different data sources. The thing that is interesting is that all of the data comes ultimately from db2, because access is just used to create views layering views. The report creator thought this was easier to implement. So what this meant is that I was passing the wrong log on info for the access database.
I do not have it working yet, but now that I understand what the real issue is, I am hopeful that I will be able to insert code to detect different data sources and pass the correct log on info.
Mike McCracken
Good luck. It may work since you are using subreports for the 2nd data source. Crystal never has really embraced multiple data sources though it does work so long as you don't need to change them through code.
Your method should work so long as each report/subreport only has 1 data source
Unlimited question asking, solutions, articles and more.
RSchnabel
ASKER
I was able to solve my specific issue. There were a couple of things I did, one of which I was not wild about. First when filling out the TableLogOnInfo object I retrieved the server and database information from the report.
and then I also changed the UserID to Admin if it was an access database, but to determine if it was an access I parsed the Server name and checked if the last 4 characters were .mdb. This is the part I really don't like I would have preferred a data source type code or something, but I was not able to find it.
You need code that
finds the subreport
opens it as a report
applies the logon criteria
It is a mirror of your code but there is a loop around it like
For each reportObject in reportobjectset
if objecttype = subreport then
setup logon criteria
end if
end loop
I am not a .Net programmer so I don't have an example.
I'll see if I can find an example from SAP/Crystal.
mlmcc