Change Set Datasource Location Programatically

[Former Member]

Hi All,

When testing reports on my testing server, I have an issue where I need to change the Datasource Location of the reports whenever I deploy them to the production server to point to a new SQL server. Is there a way in the ASP.Net VB code to make this change without having to go to opening the report in the designer and selecting Database > Set Datasource Location to make the change? I am using CR 2008

Thank you in advance,

[former_member183750 · Accepted solution]

Moved to the .NET - SAP Crystal Reports forum.

Yes there are CR APIs for .NET that will let yo change any database connection. The place to start will be with the developer help files:

http://help.sap.com/businessobject/product_guides/boexir31/en/crsdk_net_dg_12_en.chm

http://help.sap.com/businessobject/product_guides/boexir31/en/crsdk_net_apiRef_12_en.chm

Also, have a look at [Crystal Reports for Visual Studio 2005 Walkthroughs|https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/2081b4d9-6864-2b10-f49d-918baefc7a23] and finally, app samples:

https://wiki.sdn.sap.com/wiki/display/BOBJ/CrystalReportsfor.NETSDK+Samples

Vbnet_win_dbengine will be a good sample to start with.

Oh, and make sure you're on SP3:

https://smpdl.sap-ag.de/~sapidp/012002523100007123572010E/cr2008_sp3.exe

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

Got Enhancement ideas? Try the [SAP Idea Place|https://ideas.sap.com/community/products_and_solutions/crystalreports]

[Former Member]

Just to inform other forum users of the answer:

I used the following code:

Dim logoninfo As TableLogOnInfo

report.load ...

For Each tbcurrent As CrystalDecisions.CrystalReports.Engine.Table In report.Database.Tables

logoninfo = tbcurrent.LogOnInfo()

logoninfo.ConnectionInfo.DatabaseName = "[database]"

logoninfo.ConnectionInfo.ServerName = "[server]"

logoninfo.ConnectionInfo.UserID = "[user]"

logoninfo.ConnectionInfo.Password = "[password]"

tbcurrent.ApplyLogOnInfo(logoninfo)

Next

report.setdatabaselocation...

CrystalReportViewer1.ReportSource = report

[former_member183750]

Thank you Antoine. Nice to see that one of the simpler solutions worked for you. I could have provided it, but it is difficult to give possible code for us as it can get quite messy - depending on all kinds of things...

Happy coding,

Ludek

[Former Member]

I have not found a lot of information on how to programatically adjust the Provider information.

  If logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider").Value <> "SQLOLEDB" Then
                    logOnInfo.ConnectionInfo.LogonProperties.Set("Provider", "SQLOLEDB")
                End If

I have come up with the following code bit, however on a report with sub reports it will give a link error. any suggestions would be of great help.

 Public Shared Sub CrystalReportLogOn(ReportDoc As ReportDocument, serverName As String, databaseName As String, userName As String, password As String)
        Dim logOnInfo As TableLogOnInfo
        Dim subRd As ReportDocument
        Dim sects As Sections
        Dim ros As ReportObjects
        Dim sro As SubreportObject
        Dim ErrorLog As New clsErrorLog

        If ReportDoc Is Nothing Then
            Throw New ArgumentNullException("reportParameters")
        End If

        Try
            For Each t As CrystalDecisions.CrystalReports.Engine.Table In ReportDoc.Database.Tables
                logOnInfo = t.LogOnInfo

                '
                ' Make sure the provider is set to SQLOLEDB
                '
                'Dim MyProvider As Object = logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider")
                'If IsNothing(MyProvider) = False Then
                '    MyProvider.Value = "SQLOLEDB"
                'End If
                If logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider").Value <> "SQLOLEDB" Then
                    logOnInfo.ConnectionInfo.LogonProperties.Set("Provider", "SQLOLEDB")
                End If
                logOnInfo.ReportName = ReportDoc.Name
                logOnInfo.ConnectionInfo.ServerName = serverName
                logOnInfo.ConnectionInfo.DatabaseName = databaseName
                logOnInfo.ConnectionInfo.UserID = userName
                logOnInfo.ConnectionInfo.Password = password
                logOnInfo.TableName = t.Name
                t.ApplyLogOnInfo(logOnInfo)
                Try
                    t.Location = t.Name
                Catch ex As Exception
                    ErrorLog.ApplicationErrorLog("CrystalReportSetup:Location", "CrystalReportLogOn", ex.ToString)
                End Try
            Next
        Catch ex As Exception
            ErrorLog.ApplicationErrorLog("CrystalReportSetup", "CrystalReportLogOn", ex.ToString)
            Throw
        End Try

        sects = ReportDoc.ReportDefinition.Sections
        For Each sect As Section In sects
            ros = sect.ReportObjects
            For Each ro As ReportObject In ros
                If ro.Kind = ReportObjectKind.SubreportObject Then
                    sro = DirectCast(ro, SubreportObject)
                    subRd = sro.OpenSubreport(sro.SubreportName)

                    For Each t As CrystalDecisions.CrystalReports.Engine.Table In subRd.Database.Tables
                        logOnInfo = t.LogOnInfo
                      
                        
                        Try
                            '
                            ' Make sure the provider is set to SQLOLEDB
                            '
                            'Dim MyProvider As Object = logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider")
                            'If IsNothing(MyProvider) = False Then
                            '    MyProvider.Value = "SQLOLEDB"
                            'End If

                            If logOnInfo.ConnectionInfo.LogonProperties.LookupNameValuePair("Provider").Value <> "SQLOLEDB" Then
                                logOnInfo.ConnectionInfo.LogonProperties.Set("Provider", "SQLOLEDB")
                            End If

                            logOnInfo.ReportName = ReportDoc.Name
                            logOnInfo.ConnectionInfo.ServerName = serverName
                            logOnInfo.ConnectionInfo.DatabaseName = databaseName
                            logOnInfo.ConnectionInfo.UserID = userName
                            logOnInfo.ConnectionInfo.Password = password
                            logOnInfo.TableName = t.Name
                            t.ApplyLogOnInfo(logOnInfo)
                        Catch ex As Exception
                            ErrorLog.ApplicationErrorLog("CrystalReportSetup:SubReport", "CrystalReportLogOn", ex.ToString)
                        End Try

                    Next

                End If
            Next
        Next
    End Sub