Changing DB Driver using RAS

[Former Member]

I have a reporting tool that runs reports that have been designed in either MS Access or Oracle environments. I basically need to know how to set the report's DB driver at run-time. I can set up the log in information specifying which DB to actually point to, but I am having a "logon failed" issue when trying to run a report that was designed with a different driver than the target DB type.

I'm using Crystal 2008 in Visual Studio .NET 2003.

Thanks,

Chuck

[Former Member · Accepted solution]

Don,

Thanks for the response. I've looked through a good bit of the examples from the link you sent me, but I'm still not able to locate a sample of changing a datasource type via a connection string. Do you know which objects in particular this entails or, better yet, which of the examples has something similar to what you're talking about?

We had code that did this via RDC, but setting similar values here with RAS doesn't seem to work.

Thanks,

Chuck Reed

[former_member11696 · Accepted solution]

Hi Chuck,

Sorry about that, we had samples on the old site but they have not been migrated to the new site yet.

Here's sample code that should work for you, it appears you can use the methods you had initially tried but missed one key API:

// Set the QualifiedName

boTableNew.QualifiedName = "Xtreme.dbo." + boTableNew.Name;

ODBC or OLE DB works better for converting, it handles the field mappings much better than using the native drivers. Also, try this using simple reports first, create a report to Access and another to Oracle, see if you can set location from one to other in CR Designer first, then the steps are the same in your app you can then see what the various properties are being returned and set to:

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

namespace ras115_odbc_to_oledb

{

/// <summary>

/// Summary description for Form1.

/// </summary>

public class Form1 : System.Windows.Forms.Form

{

private CrystalDecisions.Windows.Forms.CrystalReportViewer crystalReportViewer1;

private CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

/// <summary>

/// Required designer variable.

/// </summary>

private System.ComponentModel.Container components = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.crystalReportViewer1 = new CrystalDecisions.Windows.Forms.CrystalReportViewer();

this.SuspendLayout();

//

// crystalReportViewer1

//

this.crystalReportViewer1.ActiveViewIndex = -1;

this.crystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill;

this.crystalReportViewer1.Location = new System.Drawing.Point(0, 0);

this.crystalReportViewer1.Name = "crystalReportViewer1";

this.crystalReportViewer1.Size = new System.Drawing.Size(872, 549);

this.crystalReportViewer1.TabIndex = 0;

//

// Form1

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(872, 549);

this.Controls.Add(this.crystalReportViewer1);

this.Name = "Form1";

this.Text = "Form1";

this.Load += new System.EventHandler(this.Form1_Load);

this.ResumeLayout(false);

}

#endregion

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main()

{

Application.Run(new Form1());

}

private void Form1_Load(object sender, System.EventArgs e)

{

CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;

CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

CrystalDecisions.ReportAppServer.Controllers.DatabaseController boDatabaseController;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo;

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boAttributesPropertyBag;

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boLogonPropertyBag;

// Load the report and get the ReportClientDocument

boReportDocument.Load(Application.StartupPath + "
..
..
Report ODBC.rpt");

boReportClientDocument = boReportDocument.ReportClientDocument;

boDatabaseController = boReportClientDocument.DatabaseController;

boDatabase = boDatabaseController.Database;

boTables = boDatabase.Tables;

foreach(CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableOld in boTables)

{

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableNew = boTableOld.Clone(true);

boConnectionInfo = boTableNew.ConnectionInfo;

boAttributesPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag) boConnectionInfo.Attributes;

// Change the attributes and QE_LogonProperties to an ODBC connection

boAttributesPropertyBag["Database DLL"] = "crdb_ado.dll";

boAttributesPropertyBag["QE_DatabaseName"] = "Xtreme";

boAttributesPropertyBag["QE_DatabaseType"] = "OLE DB (ADO)";

boAttributesPropertyBag["QE_ServerDescription"] = "van-w-11-dhilto
sqlexpress";

boAttributesPropertyBag["QE_SQLDB"] = "True";

boAttributesPropertyBag["SSO Enabled"] = "False";

// Get the QE_LogonProperties and remove all the properties and add them back for the ODBC type

boLogonPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag) boAttributesPropertyBag["QE_LogonProperties"];

boLogonPropertyBag.RemoveAll();

boLogonPropertyBag.Add("Auto Translate", "-1");

boLogonPropertyBag.Add("Connect Timeout", "15");

boLogonPropertyBag.Add("Data Source", "van-w-11-dhilto
sqlexpress");

boLogonPropertyBag.Add("General Timeout", "0");

boLogonPropertyBag.Add("Initial Catalog", "Xtreme");

boLogonPropertyBag.Add("Integrated Security", "False");

boLogonPropertyBag.Add("Locale Identifier", "1033");

boLogonPropertyBag.Add("OLE DB Services", "-5");

boLogonPropertyBag.Add("Provider", "SQLOLEDB");

boLogonPropertyBag.Add("Tag with columb collation when possible", "0");

boLogonPropertyBag.Add("Use DSN Default Properties", "False");

boLogonPropertyBag.Add("Use Encryption for Data", "0");

// Set the QualifiedName

boTableNew.QualifiedName = "Xtreme.dbo." + boTableNew.Name;

// The username and password must be set before using SetTableLocation. Secure databases

// will throw an error if the logon info is incorrect.

boTableNew.ConnectionInfo.UserName = "devtech";

boTableNew.ConnectionInfo.Password = "devtech";

boDatabaseController.SetTableLocation(boTableOld, boTableNew);

}

crystalReportViewer1.ReportSource = boReportDocument;

}

}

}

Thanks again

Don

[Former Member]

Don,

Thanks for your example. It seems setting the username/password right before SetTableLocation() fixed the issue I was having. I'm including an example of my routine to swap the report's current connection with either an ADO connection to oracle or a DAO connection to MS Access just in case it's helpful to anyone with similar needs.

    Private Sub ModifyDbDriver(ByRef boReport As ReportDocument)
        Dim boClientDoc As CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument = Nothing
        Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = Nothing
        Dim boTable As CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable = Nothing
        Dim boNewTable As CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable = Nothing
        Dim boDatabaseCtl As CrystalDecisions.ReportAppServer.Controllers.DatabaseController = Nothing
        Dim boDatabase As CrystalDecisions.ReportAppServer.DataDefModel.Database = Nothing
        Dim boAttributesPropertyBag As CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag = Nothing
        Dim boLogonPropertyBag As CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag = Nothing
        Dim boConnectionInfo As CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo = Nothing
        Dim boSubReport As CrystalDecisions.CrystalReports.Engine.ReportDocument = Nothing
        Dim i As Integer

        Try
            ' Get RAS tables collection from client document
            boClientDoc = boReport.ReportClientDocument
            boDatabaseCtl = boClientDoc.DatabaseController

            boDatabase = boDatabaseCtl.Database
            boTables = boDatabase.Tables

            ' Loop through tables
            For Each boTable In boTables
                ' Get connection info attributes for this table
                boNewTable = boTable.Clone(True)
                boConnectionInfo = boNewTable.ConnectionInfo
                boAttributesPropertyBag = boConnectionInfo.Attributes

                ' Set attributes based on DB type
                If isOracle Then
                    ' Use ADO driver for oracle type reports.
                    boAttributesPropertyBag("Database DLL") = "crdb_ado.dll"
                    boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                    boLogonPropertyBag.RemoveAll()
                    boLogonPropertyBag.Add("Provider", conn.Provider)
                    boLogonPropertyBag.Add("Data Source", conn.DataSource)
                    boLogonPropertyBag.Add("User ID", username)
                    boLogonPropertyBag.Add("Password", password)
                    boNewTable.QualifiedName = username.ToUpper & "." & boNewTable.Name.ToUpper
                Else
                    ' Set access specific driver info
                    boAttributesPropertyBag("Database DLL") = "crdb_dao.dll"
                    boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                    boLogonPropertyBag.RemoveAll()

                    ' This is a normal table, link it to regular DB
                    boLogonPropertyBag.Add("Provider", conn.Provider)
                    boLogonPropertyBag.Add("Data Source", conn.DataSource)
                    boLogonPropertyBag.Add("Database Name", conn.DataSource)

                    ' Use when access DB has a password
                    ' boLogonPropertyBag.Add("Database Password", "Some Password")

                    ' Set qualified name here
                    boNewTable.QualifiedName = boNewTable.Name
                End If

                ' We need to set the username/password here so SetTableLocation can log in and doesn't fail!
                boConnectionInfo.UserName = username
                boConnectionInfo.Password = password

                ' Update the table info
                boDatabaseCtl.SetTableLocation(boTable, boNewTable)
            Next

            ' Now do the same for subreports
            For i = 0 To boClientDoc.SubreportController.GetSubreportNames.Count - 1
                ' Get RAS tables collection from client document
                boDatabaseCtl = boClientDoc.SubreportController.GetSubreport(boClientDoc.SubreportController.GetSubreportNames(i)).DatabaseController
                boDatabase = boDatabaseCtl.Database
                boTables = boDatabase.Tables

                ' Loop through all tables
                For Each boTable In boTables
                    ' Get connection info attributes for this table
                    boNewTable = boTable.Clone(True)
                    boConnectionInfo = boNewTable.ConnectionInfo
                    boAttributesPropertyBag = boConnectionInfo.Attributes

                    ' Set attributes based on DB type
                    If isOracle Then
                        ' Set oracle specific driver info
                        boAttributesPropertyBag("Database DLL") = "crdb_ado.dll"
                        boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                        boLogonPropertyBag.RemoveAll()
                        boLogonPropertyBag.Add("Provider", conn.Provider)
                        boLogonPropertyBag.Add("Data Source", conn.DataSource)
                        boLogonPropertyBag.Add("User ID", username)
                        boLogonPropertyBag.Add("Password", password)
                        boNewTable.QualifiedName = username.ToUpper & "." & boNewTable.Name.ToUpper
                    Else
                        ' Set access specific driver info
                        boAttributesPropertyBag("Database DLL") = "crdb_dao.dll"
                        boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                        boLogonPropertyBag.RemoveAll()

                        ' This is a normal table, link it to regular DB
                        boLogonPropertyBag.Add("Provider", conn.Provider)
                        boLogonPropertyBag.Add("Data Source", conn.DataSource)
                        boLogonPropertyBag.Add("Database Name", conn.DataSource)

                        ' In case you have a password
                        ' boLogonPropertyBag.Add("Database Password", "etsupport")

                        ' Set qualified name here
                        boNewTable.QualifiedName = boNewTable.Name
                    End If

                    ' Set log in info so we can SetTableLocation()
                    boConnectionInfo.UserName = username
                    boConnectionInfo.Password = password

                    ' Update the table info
                    boDatabaseCtl.SetTableLocation(boTable, boNewTable)
                Next
            Next
        Catch ex As Exception
            MessageBox.Show("There was an error encountered while setting the report DB driver.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

[Former Member]

I've looked around a bit more and have found some examples of doing this with ODBC connections. I've tried to modify it for my purposes, but it keeps failing at the call to SetTableLocation(). The error is simply "Logon failed". Any suggestions would be greatly appreciated.

Here's the sample code I'm using to modify the DB driver:

    Private Sub SetDbDriver(ByRef boReport As ReportDocument)
        Dim boClientDoc As CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument = Nothing
        Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables = Nothing
        Dim boTable As CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable = Nothing
        Dim boNewTable As CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable = Nothing
        Dim boDatabaseCtl As CrystalDecisions.ReportAppServer.Controllers.DatabaseController = Nothing
        Dim boDatabase As CrystalDecisions.ReportAppServer.DataDefModel.Database = Nothing
        Dim boAttributesPropertyBag As CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag = Nothing
        Dim boLogonPropertyBag As CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag = Nothing
        Dim boConnectionInfo As CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo = Nothing

        Try
            ' Get RAS tables collection from client document
            boClientDoc = boReport.ReportClientDocument
            boDatabaseCtl = boClientDoc.DatabaseController
            boDatabase = boDatabaseCtl.Database
            boTables = boDatabase.Tables

            ' Loop through tables
            For Each boTable In boTables
                ' Get connection info attributes for this table
                boNewTable = boTable.Clone(True)
                boConnectionInfo = boNewTable.ConnectionInfo
                boAttributesPropertyBag = boConnectionInfo.Attributes

                ' Set attributes based on DB type
                If isOracle Then
                    ' Set table connection info
                    boNewTable.ConnectionInfo.UserName = username
                    boNewTable.ConnectionInfo.Password = password

                    ' Set oracle specific driver info
                    boAttributesPropertyBag("Database DLL") = "crdb_oracle.dll"
                    boAttributesPropertyBag("QE_DatabaseName") = ""
                    boAttributesPropertyBag("QE_DatabaseType") = "Oracle Server"
                    boAttributesPropertyBag("QE_ServerDescription") = conn.DataSource
                    boAttributesPropertyBag("QE_SQLDB") = "True"
                    boAttributesPropertyBag("SSO Enabled") = "False"
                    boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                    boLogonPropertyBag.RemoveAll()
                    boLogonPropertyBag.Add("Server", conn.DataSource)
                    boLogonPropertyBag.Add("Service", conn.DataSource)
                    boLogonPropertyBag.Add("User ID", username)
                    boLogonPropertyBag.Add("Password", password)

                    ' Set table's fully qualified name
                    If boNewTable.Name.ToLower = "delete" Or boNewTable.Name.ToLower = "date" Then
                        boNewTable.QualifiedName = username.ToUpper & "." & boNewTable.Name
                    Else
                        boNewTable.QualifiedName = username.ToUpper & "." & boNewTable.Name.ToUpper
                    End If
                Else
                    ' Set access specific driver info
                    boAttributesPropertyBag("Database DLL") = "crdb_dao.dll"
                    boAttributesPropertyBag("QE_DatabaseName") = ""
                    boAttributesPropertyBag("QE_DatabaseType") = "Access/Excel (DAO)"
                    boAttributesPropertyBag("QE_ServerDescription") = conn.DataSource
                    boAttributesPropertyBag("QE_SQLDB") = "True"
                    boAttributesPropertyBag("SSO Enabled") = "False"
                    boLogonPropertyBag = boAttributesPropertyBag("QE_LogonProperties")
                    boLogonPropertyBag.RemoveAll()
                    boLogonPropertyBag.Add("Provider", conn.Provider)
                    boLogonPropertyBag.Add("Data Source", conn.DataSource)
                    boLogonPropertyBag.Add("User ID", "Admin")
                    boLogonPropertyBag.Add("Password", "")
                End If

                ' Update the table info (ALWAYS FAILS HERE!!!)
                boDatabaseCtl.SetTableLocation(boTable, boNewTable)
            Next
        Catch ex As Exception
            MessageBox.Show("Fail! " & ex.Message)
        End Try
    End Sub

Edited by: Chuck Reed on Jan 27, 2009 4:21 PM

[former_member11696]

Hi Chuck,

Rather than use the property bag and setting each member manaully if you use the ConnectionBuffer or connection string then RAS will convert all the field types for you, mostly. There may be issues with date fields but it should work.

Go to this link: https://www.sdn.sap.com/irj/boc/samples and select Crystal Reports .NET SDK link and it will list samples using RAS. Any of the Samples with Database Connections should have code to use the Connection string.

Thank you

Don