Solved

2 Database Connections in 1 Crystal Report not working.

Posted on 2011-02-14
15
410 Views
Last Modified: 2012-05-11
I have a 1 page crystal report that has 2 seperate database connections, that won't load automatically.  It worked previously, but now there is more security and both servers require a username and password, to access the tables.  The servers are not linked nor can they be linked other then linking a field from each table in Crystal Reports.  I attached a screen shot of the message, which if I click Log On without changing anything it opens the report.  See code below.


        Dim ConnInfo1 As New ConnectionInfo
        Dim ConnInfo2 As New ConnectionInfo
        With ConnInfo1
            .ServerName = "Adv"
            .DatabaseName = "adt"
            .UserID = "ad"
            .Password = "ad"
        End With
        With ConnInfo2
            .ServerName = "NP"
            .DatabaseName = "NP"
            .UserID = "ad"
            .Password = "ad"
        End With
        Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
        If "21411064975176" <> "" Then
            Me.CrystalReportViewer1.ReportSource = Server.MapPath("CrystalReport1.rpt")
            Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
            Dim p_EmpID As New ParameterField
            p_EmpID.Name = "@tempID"
            Dim p_Seq As New ParameterField
            p_Seq.Name = "@Seq"
            Dim p_EmpID_Value As New ParameterDiscreteValue
            Dim p_Seq_Value As New ParameterDiscreteValue
            p_Seq_Value.Value = "1"
            p_EmpID_Value.Value = "21411064975176"
            p_EmpID.CurrentValues.Add(p_EmpID_Value)
            p_Seq.CurrentValues.Add(p_Seq_Value)
            ParamFields.Add(p_EmpID)
            ParamFields.Add(p_Seq)
        Else
            Me.CrystalReportViewer1.ReportSource = Server.MapPath("CrystalReport1.rpt")
        End If
        Me.CrystalReportViewer1.LogOnInfo.Item(0).ConnectionInfo = ConnInfo1
        Me.CrystalReportViewer1.LogOnInfo.Item(1).ConnectionInfo = ConnInfo2
        Me.CrystalReportViewer1.RefreshReport()

db-Error.doc
0
Comment
Question by:sborchers
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34893255
Crystal is not designed to handle multiple data sources like you are using.  It can do it but it is not by design and the APIs used to call reports do not support more than 1 connection.

I don't know of a way to do it that would get around requiring you to log in as you are.

It can be done if one of the sources is for a subreport and that is the stock answer from Crystal is to use a subreport for one of the data sources.

mlmcc
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 34893496
you may also want to try to push a dataset from your application (that can be filled from as many datasources you want).

check http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34895515
Give this a try. Change your code and loop through the Tables collection in the report

        Dim lInfo As New CrystalDecisions.Shared.TableLogOnInfo
        For Each t As CrystalDecisions.CrystalReports.Engine.Table In MyReport.Database.Tables
            If t.Name = "DB1TableName" Then
               lInfo = t.LogOnInfo
               lInfo.ConnectionInfo = ConnInfo1
               t.ApplyLogOnInfo(lInfo)
            Else If t.Name = "DB2TableName Then
               lInfo = t.LogOnInfo
               lInfo.ConnectionInfo = ConnInfo2
               t.ApplyLogOnInfo(lInfo)
            End If
        Next
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sborchers
ID: 34897854
the 'MyReport.Datase.Tables' code doesn't work in my app.  The entire code for my page is listed above (all belonging in the Page_Load), could you incorporate that code with this idea?  Thanks!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34897905
Ah yours is ASP.NET. MyReport is an instance of reportclass. See if you can instantiate your report class using

Dim MyReport As New ReportName

syntax.
0
 

Author Comment

by:sborchers
ID: 34898373
How would it be done with a subreport?
0
 

Author Comment

by:sborchers
ID: 34898706
I changed one of the databases to a subreport (Adv).  So the NP datasource didn't change, just added the Adv subreport to it.  Why don't I need to still pass the NP connectioninfo to connect to it?  

For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
            cnInfo.ConnectionInfo = ConnInfo1
Next
Only loops 1 time, which I tried both connection info's and only the Adv information causes the report to open up.  I guess I just don't understand why we don't need to pass the NP connection information.  I've never used sub-reports until now.  Thanks.

Dim ConnInfo1 As New ConnectionInfo
        With ConnInfo1
            .ServerName = "Adv"
            .DatabaseName = ""
            .UserID = "ad"
            .Password = "ad"
        End With
        Me.CrystalReportViewer1.ParameterFieldInfo.Clear()
        If "21411064975176" <> "" Then
            Me.CrystalReportViewer1.ReportSource = Server.MapPath("CrystalReport1.rpt")
            Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
            Dim p_EmpID As New ParameterField
            p_EmpID.Name = "@tempID"
            Dim p_Seq As New ParameterField
            p_Seq.Name = "@Seq"
            Dim p_EmpID_Value As New ParameterDiscreteValue
            Dim p_Seq_Value As New ParameterDiscreteValue
            p_Seq_Value.Value = "1"
            p_EmpID_Value.Value = "21416482628547"
            p_EmpID.CurrentValues.Add(p_EmpID_Value)
            p_Seq.CurrentValues.Add(p_Seq_Value)
            ParamFields.Add(p_EmpID)
            ParamFields.Add(p_Seq)
        Else
            Me.CrystalReportViewer1.ReportSource = Server.MapPath("CrystalReport1.rpt")
        End If
        For Each cnInfo As TableLogOnInfo In Me.CrystalReportViewer1.LogOnInfo
            cnInfo.ConnectionInfo = ConnInfo1
        Next
        Me.CrystalReportViewer1.RefreshReport()

Open in new window

0
 

Author Comment

by:sborchers
ID: 34899817
Ok, it worked in development...published it to the server and it wants me to do the AD logon.  I manually enter the password and it still won't pass.  Works perfectly in VS, but not deployed....using the subreport.  Thoughts?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34903622
Why are you using this test?

If "21411064975176" <> "" Then


mlmcc
0
 

Author Comment

by:sborchers
ID: 34906925
Because I didn't want to show the real code.  But it performs the same function.  How do I connect to a Crystal Report (Main) and a Subreport?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34907447
What do you mean?

Code to change the datasource for the sub?

mlmcc
0
 

Accepted Solution

by:
sborchers earned 0 total points
ID: 34908545
I found code to make it work.  It now sends the connectioninfo for both reports(Main and Sub).  I've attached the code that I used to make it work.  I used the following website to help guide me through it.  http://www.businessobjects.com/global/pdf/dev_zone/VS2005_Walkthroughs.pdf
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Web

Partial Class _Default
    Inherits System.Web.UI.Page
    Private LetterRpt As ReportDocument

    Private Sub ConfigureCrystalReports()
        LetterRpt = New ReportDocument
        Dim reportPath As String = Server.MapPath("Letter.rpt")
        LetterRpt.Load(reportPath)

        Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo
        myConnectionInfo.DatabaseName = "NP"
        myConnectionInfo.ServerName = "NP"
        myConnectionInfo.UserID = "ad"
        myConnectionInfo.Password = "ad"
        Dim myConnectionInfoSub As ConnectionInfo = New ConnectionInfo
        myConnectionInfoSub.DatabaseName = ""
        myConnectionInfoSub.ServerName = "Adv"
        myConnectionInfoSub.UserID = "ad"
        myConnectionInfoSub.Password = "ad"

        SetDBLogonFOrReport(myConnectionInfo, LetterRpt)
        SetDBLogonForSubreports(myConnectionInfoSub, LetterRpt)

        CrystalReportViewer1.ReportSource = LetterRpt

        If Request.QueryString("1") <> "" Then
            Dim ParamFields As ParameterFields = Me.CrystalReportViewer1.ParameterFieldInfo
            Dim p_EmpID As New ParameterField
            p_EmpID.Name = "@tempID"
            Dim p_Seq As New ParameterField
            p_Seq.Name = "@Seq"
            Dim p_EmpID_Value As New ParameterDiscreteValue
            Dim p_Seq_Value As New ParameterDiscreteValue
            p_Seq_Value.Value = "1"
            p_EmpID_Value.Value = Request.QueryString("1")
            p_EmpID.CurrentValues.Add(p_EmpID_Value)
            p_Seq.CurrentValues.Add(p_Seq_Value)
            ParamFields.Add(p_EmpID)
            ParamFields.Add(p_Seq)
        Else
            Me.CrystalReportViewer1.ReportSource = Server.MapPath("CrystalReport1.rpt")
        End If

        Me.CrystalReportViewer1.RefreshReport()
    End Sub

    Private Sub SetDBLogonFOrReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)
        Dim myTables As Tables = myReportDocument.Database.Tables
        For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
            Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo = myConnectionInfo
            myTable.ApplyLogOnInfo(myTableLogonInfo)
        Next
    End Sub

    Private Sub SetDBLogonForSubreports(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)
        Dim mySections As Sections = myReportDocument.ReportDefinition.Sections
        For Each mySection As Section In mySections
            Dim myReportObjects As ReportObjects = mySection.ReportObjects
            For Each myReportObject As ReportObject In myReportObjects
                If myReportObject.Kind = ReportObjectKind.SubreportObject Then
                    Dim mySubreportObject As SubreportObject = CType(myReportObject, SubreportObject)
                    Dim subReportDocument As ReportDocument = mySubreportObject.OpenSubreport(mySubreportObject.SubreportName)
                    SetDBLogonFOrReport(myConnectionInfo, subReportDocument)
                End If
            Next
        Next
    End Sub

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        ConfigureCrystalReports()
    End Sub

End Class

Open in new window

0
 

Author Comment

by:sborchers
ID: 35128539
Going to accept my solution.
0
 

Author Closing Comment

by:sborchers
ID: 35170821
I found the example of what I needed.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question