Avatar of RSchnabel
RSchnabel
Flag for United States of America asked on

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

Avatar of undefined
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.
SOLUTION
Mike McCracken

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
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

mlmcc
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
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.

     ConInfo.ConnectionInfo.ServerName = crtableLogoninfo.ConnectionInfo.ServerName
     ConInfo.ConnectionInfo.DatabaseName = crtableLogoninfo.ConnectionInfo.DatabaseName

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RSchnabel

ASKER
Since all comments were help full, I split the points.