Dynamically changing MySQL connection string to Crystal Reports

Question

I am using CrystalReportViewer and CrystalReportSource to load and display an .rpt file in my application.

The situation I have is this:

Say a person created a Crystal Reports report outside of my application and set its datasource to database. Then I use that .rpt file in my application, but I need to bind it to a different database (identical to the original one in terms of table structure and column names but with a different connection string and user name and password).

How do I do that in VB.NET code?

Currently I load the report using:

Public Function SetReportSource(ByVal RptFile As String) As ReportDocument

    Try
        Dim crtableLogoninfo As New TableLogOnInfo()
        Dim crConnectionInfo As New ConnectionInfo()
        Dim CrTables As Tables
        Dim CrTable As Table

        If System.IO.File.Exists(RptFile) Then
            Dim crReportDocument As New ReportDocument()
            crReportDocument.Load(RptFile)

            With crConnectionInfo
                .ServerName = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;Port=3306;UID=root;"
                .DatabaseName = gDatabaseName
                .UserID = gServerUser
                .Password = gServerPassword
            End With

            CrTables = crReportDocument.Database.Tables
            For Each CrTable In CrTables
                CrTable.ApplyLogOnInfo(crtableLogoninfo)
                CrTable.LogOnInfo.ConnectionInfo.ServerName = crConnectionInfo.ServerName
                CrTable.LogOnInfo.ConnectionInfo.DatabaseName = crConnectionInfo.DatabaseName
                CrTable.LogOnInfo.ConnectionInfo.UserID = crConnectionInfo.UserID
                CrTable.LogOnInfo.ConnectionInfo.Password = crConnectionInfo.Password
                'Apply the schema name to the table's location
                CrTable.Location = gDatabaseName & "." & CrTable.Location
            Next

            crReportDocument.VerifyDatabase()
            SetReportSource = crReportDocument
        Else
            MsgBox("Report file not found...", MsgBoxStyle.Critical, proTitleMsg)
        End If
    Catch ex As Exception
        System.Windows.Forms.MessageBox.Show("Error Found..." & vbCrLf & "Error No : " & Err.Number & vbCrLf & "Description :" & Err.Description & vbCrLf & vbCrLf & "Line no : " & Err.Erl & vbCrLf & "Procedure name : SetReportSource" & vbCrLf & "Module name : GeneralFunctions", proTitleMsg)
    End Try

End Function

Deep in the Code Deep in the Code · Accepted Answer · 2013-02-17T22:45:01

This is how I did it. I was using Oracle with ODBC on ASP.NET, but you should be able to do the same with MySQL and ODBC:

As part of the legacy application upgrade I've been doing, I decided to move the Crystal Reports to a Web application rather that having the users access them directly on Crystal Reports XI via Citrix, which has been the method they have been using. This has several advantages, the primary one being speed. One problem that plagued me was how to change the logon information at run-time, such that the application would automatically point at the correct Oracle database (development, test, or production) based on which server the report was being accessed from.

The solution I found was to set up the Oracle Database connection as a DSN in ODBC, and connecting to the ODBC DSN rather than using the Oracle Client directly. This is not the only way to do it, but it seems to be the best way for my purposes.

In the code-behind file for the page that contains the Crystal Reports Viewer, I placed the following code that handles the same event that renders the viewer.

Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
Dim connInfo As New ConnectionInfo
Dim rptDoc As New ReportDocument

' setup the connection
With connInfo
.ServerName = "oracledsn" ' ODBC DSN in quotes, not Oracle server or database name
.DatabaseName = "" ' leave empty string here
.UserID = "username" ' database user ID in quotes
.Password = "password"  'database password in quotes
End With

' load the Crystal Report
rptDoc.Load(Server.MapPath(Utilities.AppSettingsFunction.getValue("ReportFolder") & ddlReports.SelectedValue))

' add required parameters
If pnlstartdates.Visible Then
rptDoc.SetParameterValue("REPORT_DATE", txtSingleDate.Text)
End If

' apply logon information

For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
repTblLogonInfo.ConnectionInfo = connInfo
tbl.ApplyLogOnInfo(repTblLogonInfo)
Next

' Set, bind, and display Crystal Reports Viewer data source
Session("rptDoc") = rptDoc
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
 End Sub

The logon info above can easily be stored in web.config instead of hard-coding it as above.

Incidentally, I chose to put my Crystal Reports Viewer in an ASP.NET AJAX Update Panel, which is why the ReportSource of the viewer is stored in a Session variable. If you choose to do this, the viewer must be databound in the Init event (not the Load event) to show up properly.

Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
If Not Page.IsPostBack Then
txtSingleDate.Text = Now.Date()
ElseIf Session("rptDoc") IsNot Nothing Then
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
End If
 End Sub

Dynamically changing MySQL connection string to Crystal Reports

1 Answers