• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

2 Database Connections in 1 Crystal Report not working.

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
sborchers
Asked:
sborchers
  • 8
  • 3
  • 2
  • +1
1 Solution
 
mlmccCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
CodeCruiserCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
sborchersAuthor Commented:
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
 
CodeCruiserCommented:
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
 
sborchersAuthor Commented:
How would it be done with a subreport?
0
 
sborchersAuthor Commented:
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
 
sborchersAuthor Commented:
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
 
mlmccCommented:
Why are you using this test?

If "21411064975176" <> "" Then


mlmcc
0
 
sborchersAuthor Commented:
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
 
mlmccCommented:
What do you mean?

Code to change the datasource for the sub?

mlmcc
0
 
sborchersAuthor Commented:
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
 
sborchersAuthor Commented:
Going to accept my solution.
0
 
sborchersAuthor Commented:
I found the example of what I needed.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 8
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now