** RESOLVED ** [2005] BO XI R2 db connection issue

[Pasvorto]

I am having a problem trying to figure out how to have a report connect to two databases. I have one db that contains tooling data, but I also need to connect to another db that has product description data. I thought I might be able to do this:

Case "Z:\Tooling\Xref.rpt"
                ReportPath = Application.StartupPath & "\Xref.rpt"
                CRViewer1.ReportSource = ReportPath
                Dim myTableLogOnInfos As TableLogOnInfos = CRViewer1.LogOnInfo
 
                For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
                    If report.name = "Unprinted" Then
                        myTableLogOnInfo.ConnectionInfo = myConnecionInfo2
                    Else
                        myTableLogOnInfo.ConnectionInfo = myConnecionInfo
                    End If
                Next

That does nto seem to be working. I can't seem to find out how to determine the table name that it is trying to cvonnect to. Also, it is telling me that myConnectionInfo & myConnectionInfo2 are not defined. Yet at the top of the subroutine I have:

Dim myConnectionInfo As ConnectionInfo
        Dim myConnectionInfo2 As ConnectionInfo
        Dim ReportPath As String
        Dim mySelectionFormula As String = ""
        myConnectionInfo = New ConnectionInfo
        With myConnectionInfo
            .ServerName = "Primero"
            .DatabaseName = "Tooling"
            .UserID = "sa"
            .Password = myPasswordTechniSQL
        End With
        With myConnectionInfo2
            .ServerName = "Primero"
            .DatabaseName = "Techni"
            .UserID = "sa"
            .Password = myPasswordTechniSQL
        End With

[warrenayen]

Are you assigning back to the viewer the logon info?

[Pasvorto]

I guess I don't understand what you mean.

[warrenayen]

Well I see you're creating logon info, but I don't see you assigning that logon object back to the viewer so it can use it. I just see you creating it.

I'm not seeing:

CRViewer1.LogOnInfo = myTableLogOnInfo

[Pasvorto]

Well, it's getting wierder by the minute...

I run the program (in the IDE) and run the report. It runs fine. I run it again (going back to the reports menu) and I get the "Load Report Failed" error and have to log on again. If I stop the program and restart it, I get the same error. Now, the weird part... If I stop the program and wait a couple minutes and restart it, the report runs just fine. Is this a 'garbage collection' thing?

[warrenayen]

I had the same error, before. I remember that.

I have no issues now, and I never figured out what caused it. All I know is my code works perfectly when running reports, and now I'm running them in two different applications here. I can send you a copy.

[Pasvorto]

I would appreciate any help. This is driving me nuts. I got the code to work for 2 different database (see below). But, this "randomness" is getting to me. It seems like every new version of Crystal reports entails relearning everything (with precious little documentation). I even installed the latest MHF & CHF. That didn't change anything.

Friend Class CRViewer
    Inherits System.Windows.Forms.Form
    'Private rsMachine As ADODB.Recordset
    'Private Const PARAMETER_FIELD_NAME As String = "Machine"
 
    Private Sub ConfigureCrystalReports()
 
        CRViewer1.ReportSource = Nothing
        CRViewer1.SelectionFormula = ""
 
        Dim myConnectionInfo As ConnectionInfo
        Dim myConnectionInfo2 As ConnectionInfo
        Dim ReportPath As String
        'Dim myArrayList As ArrayList = New ArrayList()
        'Dim myParameterFields As ParameterFields
        Dim mySelectionFormula As String = ""
        myConnectionInfo = New ConnectionInfo
        With myConnectionInfo
            .ServerName = "Primero"
            .DatabaseName = "Tooling"
            .UserID = "sa"
            .Password = myPasswordTechniSQL
        End With
        myConnectionInfo2 = New ConnectionInfo
        With myConnectionInfo2
            .ServerName = "Primero"
            .DatabaseName = "Techni"
            .UserID = "sa"
            .Password = myPasswordTechniSQL
        End With
 
 
        Select Case glbReportname
            Case "Z:\Tooling\PartialToolList.rpt"
                'myArrayList.Add(glbreportParam)
                'myParameterFields = CRViewer1.ParameterFieldInfo
                'SetCurrentValuesForParameterField(myParameterFields, myArrayList)
                mySelectionFormula = "{MachineTool.Machine} = " & Val(glbreportParam)
                ReportPath = Application.StartupPath & "\PartialToolList.rpt"
                CRViewer1.SelectionFormula = mySelectionFormula
                CRViewer1.ReportSource = ReportPath
                SetDBLogonForReport(myConnectionInfo, myConnectionInfo2)
            Case "Z:\Tooling\Tooling.rpt"
                ReportPath = Application.StartupPath & "\Tooling.rpt"
                CRViewer1.ReportSource = ReportPath
                SetDBLogonForReport(myConnectionInfo, myConnectionInfo2)
            Case "Z:\Tooling\FullToolList.rpt"
                ReportPath = Application.StartupPath & "\FullToolList.rpt"
                CRViewer1.ReportSource = ReportPath
                SetDBLogonForReport(myConnectionInfo, myConnectionInfo2)
            Case "Z:\Tooling\Xref.rpt"
                ReportPath = Application.StartupPath & "\Xref.rpt"
                CRViewer1.ReportSource = ReportPath
                SetDBLogonForReport(myConnectionInfo, myConnectionInfo2)
        End Select
 
        'CRViewer1.Show()
    End Sub
    
    Private Sub SetDBLogonForReport(ByVal myConnecionInfo As ConnectionInfo, ByVal myConnecionInfo2 As ConnectionInfo)
        Dim myTableLogOnInfos As TableLogOnInfos = CRViewer1.LogOnInfo
 
        For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
            If myTableLogOnInfo.TableName = "Unprinted" Then
                myTableLogOnInfo.ConnectionInfo = myConnecionInfo2
            Else
                myTableLogOnInfo.ConnectionInfo = myConnecionInfo
            End If
        Next
 
    End Sub

[Pasvorto]

OK. I went stepping through the code. The errors are occurring in thsi code:

[vbocde]
Private Sub SetDBLogonForReport(ByVal myConnecionInfo As ConnectionInfo, ByVal myConnecionInfo2 As ConnectionInfo)
Dim myTableLogOnInfos As TableLogOnInfos = CRViewer1.LogOnInfo

For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
If UCase(myTableLogOnInfo.TableName) = "UNPRINTED" Then
myTableLogOnInfo.ConnectionInfo = myConnecionInfo2
Else
myTableLogOnInfo.ConnectionInfo = myConnecionInfo
End If
Next

End Sub
[/Highlight]

It hits this line:
Dim myTableLogOnInfos As TableLogOnInfos = CRViewer1.LogOnInfo

and I get the "Load report failed" error, and it has me supply the password (it seems to maintain the usercode). I do this for both databases, the report displys, I get the Load error again. i close the report and it steps to the next line (the FOR loop). Every tablename in the loop is blank.

So, maybe if I can resolve the "DIM" issue, the other stuff will fall into place.

[warrenayen]

Private Sub ConfigureCrystalReports()
 
        Dim myConnectionInfo As ConnectionInfo
        Dim ReportPath As String
 
 
 
        myConnectionInfo = New ConnectionInfo
 
        With myConnectionInfo
            .ServerName = "Gemini"
            .DatabaseName = "New_Inv_Dev"
            .UserID = "sa"
            .Password = "bobcre10"
        End With
 
        ReportPath = reportFilename
 
        CRViewer1.ReportSource = ReportPath
 
        SetDBLogonForReport(myConnectionInfo)
 
 
        CRViewer1.Show()
    End Sub
 
    Private Sub CRViewer_Load(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles MyBase.Load
        Try
 
            ConfigureCrystalReports()
 
        Catch ex As Exception
 
            Kinsale.DisplayMessage("Crystal Reports was unable to load the report: " & ex.Message, "", KinsaleClass.retExclamation, False, False)
 
        End Try
 
 
    End Sub
 
    Private Sub SetDBLogonForReport(ByVal myConnecionInfo As ConnectionInfo)
        Dim myTableLogOnInfos As TableLogOnInfos = CRViewer1.LogOnInfo
 
        For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
            myTableLogOnInfo.ConnectionInfo = myConnecionInfo
        Next
 
    End Sub

That's my code. Works perfectly. It only uses one database, though.