VB.Net & Crystal Reports with subreports table connections

RSchnabel used Ask the Experts™
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


            'Load the report
            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
            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 = _
                    End If
            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
                        End If
                    End With
            If sSelectionFormula.Length > 0 Then
                objReport.RecordSelectionFormula = sSelectionFormula
            End If
            CrystalReportViewer1.ReportSource = Nothing

            CrystalReportViewer1.ReportSource = objReport

            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
        End Try
        Return True
    End Function
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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.



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.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
I didn't see in my initial glance at the code.  I see it now.

That is one reason why my recommended method of passing data to a report the push method where the application builds a dataset then passes it to the report.

That moves the database connections to the application where you can handle them.

Works well if you only have a few reports.  Not necessarily so good if you have hundreds.

The other way I have avoided this problem was to setup my dev environment as a mirror of the production so even my dev db server was named the same.  We used integrated security so the windows login also logged them into the db.  COde that worked in dev worked just fine in prod since it didn't see a db change.

I'll see if I can find sample code for this

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Top Expert 2011
Sorry that I am not answering your question, but you have long way to go. Are you developing this for fun or to help somebody ? If the goal is to just to run few reports there are some commercial applications with free versions, which will cover you scenario. Here are links to 3 free apps:

They are not just viewers but schedulers too. I guess groffautomation and logicitysuite have some restrictions in the free version , but R-Tag is just a gift with practically all you need to run and schedule your reports for free. Just check them before to spend more time on your project.
Top Expert 2007
Try to write credentials code line after specifying parameter code lines.


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.


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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

Top Expert 2011
This is just an example of a specific scenario. You may also have problems with reports with commands, reports which are using ODBC or OLEDB connections or both , reports , which require to change the server ( not to provide just database name and password) etc. There are many scenarios and each one has specifics. That is why I said you have long way in front of you and it will be wise to try to find an existing working solution


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.


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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial