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
RSchnabelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
0
RSchnabelAuthor Commented:
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.
0
mlmccCommented:
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

mlmcc
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

vastoCommented:
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:

http://www.r-tag.com/Pages/CommunityEdition.aspx
http://www.groffautomation.com/
http://www.logicitysuite.com/
 
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.
0
habibbCommented:
Try to write credentials code line after specifying parameter code lines.
0
RSchnabelAuthor Commented:
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.
0
RSchnabelAuthor Commented:
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.
0
mlmccCommented:
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
0
vastoCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RSchnabelAuthor Commented:
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.
0
RSchnabelAuthor Commented:
Since all comments were help full, I split the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.