[RESOLVED] How to connect MSSQL and CR 8.5 programmatically?

[jeruelx5d]

Guys!

I'm using MSSQL2000, VB6 and Crystal Report 8.5

My application is working fine. I'm able to produce report from MSSQL down to CR 8.5. I'm using "Crystal Report Contro"l to access .rpt within my VB6 application.

The problem started when we are using several MSSQL server. since all my .rpt is configured only to one server, i would have to encounter some report errors e.g. "cannot connect specified server".

Please help me to connect Crystal Report to SQL Server programmatically using VB6. I think i could help me a lot to resolve this issue.

Thanks a lot.

[brucevde]

Note: The Crystal Report Control is outdated and is only included for backward compatibility. Think about switching to the Crystal Report Viewer Control.

To change the datasource at runtime use the Connect or LogOnInfo properties or the LogonServer method.

[Ginster]

As above use the Report Viewer Control.
After setting the server details at runtime I use the code below (.NET but you should get the idea) to connect.

Dim crtableLogoninfos As New TableLogOnInfos
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crConnectioninfo As New ConnectionInfo
        Dim crTables As Tables
        Dim crTable As Table
        Dim Environ As String
        Dim crReportDocument As New ReportDocument
        Dim subReportDocument As New ReportDocument
        Dim crSection As Section
        Dim crSections As Sections
        Dim crReportobject As ReportObject
        Dim crReportobjects As ReportObjects
        Dim crSubReportobject As SubreportObject
        Dim crDatabase As Database
       
With crConnectioninfo
            .ServerName = strServerOrDSNName
            .DatabaseName = strDBNameOrPath
            .UserID = strUserID
            .Password = strPassword
        End With
 
        Try
            'Loop through each table in the report and set the connection info
            crTables = crReportDocument.Database.Tables
            For Each crTable In crTables
                crtableLogoninfo = crTable.LogOnInfo
                crtableLogoninfo.ConnectionInfo = crConnectioninfo
                crTable.ApplyLogOnInfo(crtableLogoninfo)
            Next
        Catch ex As Exception
            MsgBox("Unable to Log On To report Tables", MsgBoxStyle.Critical)
            MsgBox("Loading of " & strReportName & " Cancelled.", MsgBoxStyle.Information)
 
            'MsgBox(ex.ToString)
 
            GoTo StopHere
        End Try
 
        Try
            ' Loop through each section and find report objects
            crSections = crReportDocument.ReportDefinition.Sections
            For Each crSection In crSections
                crReportobjects = crSection.ReportObjects
                For Each crReportobject In crReportobjects
                    If crReportobject.Kind = ReportObjectKind.SubreportObject Then
                        ' If a subreport is found cast as subreportobject
                        crSubReportobject = CType(crReportobject, SubreportObject)
                        ' Open the sub report
                        subReportDocument = crSubReportobject.OpenSubreport(crSubReportobject.SubreportName)
                        crDatabase = subReportDocument.Database
                        crTables = crDatabase.Tables
                        ' Loop through each table in the sub report and set the connection info
                        For Each crTable In crTables
                            crtableLogoninfo = crTable.LogOnInfo
                            crtableLogoninfo.ConnectionInfo = crConnectioninfo
                            crTable.ApplyLogOnInfo(crtableLogoninfo)
                        Next
                    End If
                Next
            Next
        Catch ex As Exception
            MsgBox("Unable to Log On To Sub Report Tables")
            ' MsgBox(ex.ToString)
 
        End Try

[jeruelx5d]

Thanks a lot guys!


I'm now working with CR Viewer. Thanks a lot for your advice and sample code. It really helps me a lot!


More power!


God Bless!


jeruelx5d

[Ginster]

Have found that there is an issue connecting in this manner. Each time a user opened a new report a new process was opened on the server which remained until the user closed the application. This could mean that a user would have upwards of 20 processes running at anyone time as the app is used intensively.

The solution I found was to put the following

crReportDocument.Dispose()

in the close event of the form which had the viewer embedded.

[jeanette_db]

aja! =)