?
Solved

2 Database Connections in 1 Crystal Report not working.

Posted on 2011-02-14
15
Medium Priority
?
418 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
14 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 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