Symptom
How to change the database (Pervasive Database) of a Crystal Report at runtime using a connection string.
Environment
- Visual Studio 2010
- Crystal Reports for Visual Studio 2010
- Pervasive 11 Database
- In-Proc RAS SDK
Reproducing the Issue
The Crystal Report connects to the Pervasive database using the ODBC connectivity through a connection string.
Resolution
Here is the VB.NET code using In-Proc RAS SDK to change the Pervasive Database at runtime.
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports CrystalDecisions.ReportAppServer.DataDefModel
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim boReportDocument As New ReportDocument
'**EDIT** Change the path and report name to the report you want to change.
boReportDocument.Load("CrystalReport1.rpt", OpenReportMethod.OpenReportByTempCopy)
'Create a new Database Table to replace the reports current table.
Dim boTable As New CrystalDecisions.ReportAppServer.DataDefModel.Table
'boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
Dim boMainPropertyBag As New PropertyBag
'boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
'In the main property bag (boMainPropertyBag)
Dim boInnerPropertyBag As New PropertyBag
'Set the attributes for the boInnerPropertyBag
boInnerPropertyBag.Add("Connection String", "Driver={Pervasive ODBC Client Interface};ServerName=ServerName;dbq=DatabaseName")
boInnerPropertyBag.Add("Server", "ServerName")
boInnerPropertyBag.Add("UseDSNProperties", "False")
'Set the attributes for the boMainPropertyBag
boMainPropertyBag.Add("Database DLL", "crdb_odbc.dll")
boMainPropertyBag.Add("QE_DatabaseName", "")
boMainPropertyBag.Add("QE_DatabaseType", "ODBC (RDO)")
'Add the QE_LogonProperties we set in the boInnerPropertyBag Object
boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag)
boMainPropertyBag.Add("QE_ServerDescription", "ServerName")
boMainPropertyBag.Add("QE_SQLDB", "True")
boMainPropertyBag.Add("SSO Enabled", "False")
'Create a new ConnectionInfo object
Dim boConnectionInfo As New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo
'Pass the database properties to a connection info object
boConnectionInfo.Attributes = boMainPropertyBag
'Set the connection kind
boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
'**EDIT** Set the User Name and Password if required.
boConnectionInfo.UserName = "UserName"
boConnectionInfo.Password = "Password"
'Pass the connection information to the table
boTable.ConnectionInfo = boConnectionInfo
'Get the Database Tables Collection for your report
Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = _
boReportDocument.ReportClientDocument.DatabaseController.Database.Tables
'For each table in the report:
' - Set the Table Name properties.
' - Set the table location in the report to use the new modified table
boTable.Name = "TableName"
boTable.QualifiedName = "TableName"
boTable.Alias = "TableName"
boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables(0), boTable)
'Verify the database after adding substituting the new table.
'To ensure that the table updates properly when adding Command tables or Stored Procedures.
boReportDocument.VerifyDatabase()
CrystalReportViewer1.ReportSource = boReportDocument
End Sub
End Class
Keywords
KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , How To
Product
SAP Crystal Reports, developer version for Microsoft Visual Studio