Solved

2 Database Connections in 1 Crystal Report not working.

Posted on 2011-02-14
15
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 101

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 101

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 101

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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