Solved

2 Database Connections in 1 Crystal Report not working.

Posted on 2011-02-14
15
408 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
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sborchers
Comment Utility
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
Comment Utility
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
Comment Utility
How would it be done with a subreport?
0
 

Author Comment

by:sborchers
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:sborchers
Comment Utility
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
Comment Utility
Why are you using this test?

If "21411064975176" <> "" Then


mlmcc
0
 

Author Comment

by:sborchers
Comment Utility
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
Comment Utility
What do you mean?

Code to change the datasource for the sub?

mlmcc
0
 

Accepted Solution

by:
sborchers earned 0 total points
Comment Utility
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
Comment Utility
Going to accept my solution.
0
 

Author Closing Comment

by:sborchers
Comment Utility
I found the example of what I needed.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now