Home > Crystal Report > Set Crystal Database Configuration

Set Crystal Database Configuration

Following my previous post for printing a Crystal Report file connected to Oracle, this is a more generic GetConnectionInfo function which support three databases: Access, SQL and ORACLE

Again what needs to be changed is highlighted in bold text.

Private Function GetConnectionInfo(ByVal config As Business.Configuration) As CrystalDecisions.Shared.ConnectionInfo
      Dim dbAttributes As CrystalDecisions.Shared.DbConnectionAttributes
      Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = Nothing

      Select Case config.DatabaseType
        Case Common.EnterpriseLibrary.Data.DatabaseType.Access

          Dim reportFullPath As String = String.Concat(config.ReportsPath, “\”, “DatabaseName“)
          If Not System.IO.File.Exists(reportFullPath) Then
            Throw New ApplicationException(String.Format(“The report [{0}] does not exist”, reportFullPath))
          End If

          ‘use DAO instead of ADO
          ‘setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Data Source”, “ReportFullPath“)
          dbAttributes.Collection.Set(“Database Type”, “Access”)
          ‘setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “”
          crConnectionInfo.ServerName = “ReportFullPath
          crConnectionInfo.UserID = “ReportsUserName
          crConnectionInfo.Password = “ReportsPassword
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_dao.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, reportFullPath)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “Access/Excel (DAO)”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, reportFullPath)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Common.EnterpriseLibrary.Data.DatabaseType.Oracle
          ‘setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Server”, “OracleServiceName“)
          dbAttributes.Collection.Set(“Trusted_Connection”, False)
          ‘setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “”
          crConnectionInfo.ServerName = “OracleServiceName
          crConnectionInfo.UserID = “ReportsUserName
          crConnectionInfo.Password = “ReportsPassword”
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_oracle.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, “”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “Oracle Server”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, config.ServerName)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Common.EnterpriseLibrary.Data.DatabaseType.SqlServer
          ‘setup the attributes for the connection
          dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
          dbAttributes.Collection.Set(“Auto Translate”, “-1”)
          dbAttributes.Collection.Set(“Connect Timeout”, “15”)
          dbAttributes.Collection.Set(“Data Source”, “SqlServerName“)
          dbAttributes.Collection.Set(“General Timeout”, “0”)
          dbAttributes.Collection.Set(“Initial Catalog”, “DatabaseName“)
          dbAttributes.Collection.Set(“Integrated Security”, False)
          dbAttributes.Collection.Set(“Locale Identifier”, “5129”)
          dbAttributes.Collection.Set(“OLE DB Services”, “-5”)
          dbAttributes.Collection.Set(“Provider”, “SQLOLEDB”)
          dbAttributes.Collection.Set(“Tag with column collation when possible”, “0”)
          dbAttributes.Collection.Set(“Use DSN Default Properties”, False)
          dbAttributes.Collection.Set(“Use Encryption for Data”, “0”)
          ‘setup the connection
          crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
          crConnectionInfo.LogonProperties.Clear()
          crConnectionInfo.Attributes.Collection.Clear()
          crConnectionInfo.DatabaseName = “DatabaseName
          crConnectionInfo.ServerName = “SqlServerName
          crConnectionInfo.UserID = “ReportsUserName
          crConnectionInfo.Password = “ReportsPassword”
          crConnectionInfo.Attributes.Collection.Set(“Database DLL”, “crdb_ado.dll”)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseName”, config.DatabaseName)
          crConnectionInfo.Attributes.Collection.Set(“QE_DatabaseType”, “OLE DB (ADO)”)
          crConnectionInfo.Attributes.Collection.Set(“QE_LogonProperties”, dbAttributes)
          crConnectionInfo.Attributes.Collection.Set(“QE_ServerDescription”, config.ServerName)
          crConnectionInfo.Attributes.Collection.Set(“QE_SQLDB”, True)
          crConnectionInfo.Attributes.Collection.Set(“SSO Enabled”, False)
          crConnectionInfo.LogonProperties = dbAttributes.Collection

        Case Else
          Throw New ApplicationException(“Unknown Database”)
      End Select

      Return crConnectionInfo
    End Function

Advertisements
Categories: Crystal Report
  1. Edwin
    October 2, 2008 at 8:47 am

    Hi,

    Can you tell us more about the ‘config’ parameter that got pass into this procedure?

    Thanks

  2. Mario
    January 11, 2009 at 2:20 am

    What datasource is saved with the report? I am having issues changing the database configuration to Oracle if the report is originally set to SQL Server. Did you have any similiar issues?

  3. January 12, 2009 at 11:06 am

    Think about a Configuration parameter as a class which contains database type, Server Name and Database Name. You should have these informations written somewhere in the system, i.g., Registry or XML file. In this case I have a custom developed class called Configuration which read this information from the registry. This is not a generic code and you should be replace it with some equal codes which suits your need.

  4. January 12, 2009 at 11:11 am

    I tested this code against reports which are originally designed with ODBC data source set either for SQL Server or Oracle and having flexibility to switch between SQL and Oracle was the main intention of having such a function. I haven’t tested this code for reports which developed with original direct connections to Oracle, but I think the code should work without any problem.

    I suggest to check this section of code on a test project and try to run the report:

    My notes about Microsoft .Net, J2EE, Apple Cocoa and other development toys
    Set Crystal Database Configuration
    with 2 comments

    Following my previous post for printing a Crystal Report file connected to Oracle, this is a more generic GetConnectionInfo function which support three databases: Access, SQL and ORACLE

    Again what needs to be changed is highlighted in bold text.

    Private Function GetConnectionInfo(ByVal config As Business.Configuration) As CrystalDecisions.Shared.ConnectionInfo
          Dim dbAttributes As CrystalDecisions.Shared.DbConnectionAttributes
          Dim crConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = Nothing
    
          Select Case config.DatabaseType
            Case Common.EnterpriseLibrary.Data.DatabaseType.Access
    
              Dim reportFullPath As String = String.Concat(config.ReportsPath, “\”, “DatabaseName“)
              If Not System.IO.File.Exists(reportFullPath) Then
                Throw New ApplicationException(String.Format(”The report [{0}] does not exist”, reportFullPath))
              End If
    
              ‘use DAO instead of ADO
              ’setup the attributes for the connection
              dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
              dbAttributes.Collection.Set(”Data Source”, “ReportFullPath“)
              dbAttributes.Collection.Set(”Database Type”, “Access”)
              ’setup the connection
              crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
              crConnectionInfo.LogonProperties.Clear()
              crConnectionInfo.Attributes.Collection.Clear()
              crConnectionInfo.DatabaseName = “”
              crConnectionInfo.ServerName = “ReportFullPath“
              crConnectionInfo.UserID = “ReportsUserName“
              crConnectionInfo.Password = “ReportsPassword“
              crConnectionInfo.Attributes.Collection.Set(”Database DLL”, “crdb_dao.dll”)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseName”, reportFullPath)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseType”, “Access/Excel (DAO)”)
              crConnectionInfo.Attributes.Collection.Set(”QE_LogonProperties”, dbAttributes)
              crConnectionInfo.Attributes.Collection.Set(”QE_ServerDescription”, reportFullPath)
              crConnectionInfo.Attributes.Collection.Set(”QE_SQLDB”, True)
              crConnectionInfo.Attributes.Collection.Set(”SSO Enabled”, False)
              crConnectionInfo.LogonProperties = dbAttributes.Collection
    
            Case Common.EnterpriseLibrary.Data.DatabaseType.Oracle
              ’setup the attributes for the connection
              dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
              dbAttributes.Collection.Set(”Server”, “OracleServiceName“)
              dbAttributes.Collection.Set(”Trusted_Connection”, False)
              ’setup the connection
              crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
              crConnectionInfo.LogonProperties.Clear()
              crConnectionInfo.Attributes.Collection.Clear()
              crConnectionInfo.DatabaseName = “”
              crConnectionInfo.ServerName = “OracleServiceName“
              crConnectionInfo.UserID = “ReportsUserName“
              crConnectionInfo.Password = “ReportsPassword”
              crConnectionInfo.Attributes.Collection.Set(”Database DLL”, “crdb_oracle.dll”)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseName”, “”)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseType”, “Oracle Server”)
              crConnectionInfo.Attributes.Collection.Set(”QE_LogonProperties”, dbAttributes)
              crConnectionInfo.Attributes.Collection.Set(”QE_ServerDescription”, config.ServerName)
              crConnectionInfo.Attributes.Collection.Set(”QE_SQLDB”, True)
              crConnectionInfo.Attributes.Collection.Set(”SSO Enabled”, False)
              crConnectionInfo.LogonProperties = dbAttributes.Collection
    
            Case Common.EnterpriseLibrary.Data.DatabaseType.SqlServer
              ’setup the attributes for the connection
              dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes
              dbAttributes.Collection.Set(”Auto Translate”, “-1″)
              dbAttributes.Collection.Set(”Connect Timeout”, “15″)
              dbAttributes.Collection.Set(”Data Source”, “SqlServerName“)
              dbAttributes.Collection.Set(”General Timeout”, “0″)
              dbAttributes.Collection.Set(”Initial Catalog”, “DatabaseName“)
              dbAttributes.Collection.Set(”Integrated Security”, False)
              dbAttributes.Collection.Set(”Locale Identifier”, “5129″)
              dbAttributes.Collection.Set(”OLE DB Services”, “-5″)
              dbAttributes.Collection.Set(”Provider”, “SQLOLEDB”)
              dbAttributes.Collection.Set(”Tag with column collation when possible”, “0″)
              dbAttributes.Collection.Set(”Use DSN Default Properties”, False)
              dbAttributes.Collection.Set(”Use Encryption for Data”, “0″)
              ’setup the connection
              crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
              crConnectionInfo.LogonProperties.Clear()
              crConnectionInfo.Attributes.Collection.Clear()
              crConnectionInfo.DatabaseName = “DatabaseName“
              crConnectionInfo.ServerName = “SqlServerName“
              crConnectionInfo.UserID = “ReportsUserName“
              crConnectionInfo.Password = “ReportsPassword”
              crConnectionInfo.Attributes.Collection.Set(”Database DLL”, “crdb_ado.dll”)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseName”, config.DatabaseName)
              crConnectionInfo.Attributes.Collection.Set(”QE_DatabaseType”, “OLE DB (ADO)”)
              crConnectionInfo.Attributes.Collection.Set(”QE_LogonProperties”, dbAttributes)
              crConnectionInfo.Attributes.Collection.Set(”QE_ServerDescription”, config.ServerName)
              crConnectionInfo.Attributes.Collection.Set(”QE_SQLDB”, True)
              crConnectionInfo.Attributes.Collection.Set(”SSO Enabled”, False)
              crConnectionInfo.LogonProperties = dbAttributes.Collection
    
    

    I hope this helps.

  5. Y, Wang
    September 7, 2009 at 5:22 pm

    Hi,

    In my case,reports are originally designed with ADO.NET(XML) data source, and I have to use ODBC(Microsoft Text Driver (*.txt, *.csv))on the runtime.
    Could you tell me how to set the ConnectionInfo or tableLogOnInfo?
    (DSN and CSV Schema file are available.)

    Thanks!

  6. September 7, 2009 at 5:38 pm

    If the connection info recorded in an XML such as App.Config file then you should be able to read the values of the server and database name and pass them to the given function at run time. I am not aware of any way to pass the connection string directly to crConnectionInfo object.

  7. Y, Wang
    September 7, 2009 at 10:25 pm

    Hi,

    I just don’t know what shoud be set into connectionInfo in the case when ODBC(CSV) is used as a data source within the RPT.
    I tried the followings, but I always got an error indicating “Failed to get data from database”. I checked the DSN and schema file of the CSV Data file, they are correct.
    Could you show me what was wrong or what else should I do?

    Dim crTableLogonInfo As New TableLogonInfo
    Dim crConnectionInfo As New ConnectionInfo
    Dim crDbAttributes As New DbConnectAttributes

    crDbAtributes.Collection.Set(“DSN”,””)
    crDbAttibutes.Collection.Set(“UseDSNProperties”,”False”)
    With crConnectionInfo
    .ServerName = “”
    .Database = “”
    .UserID = “”
    .Password = “”
    .Attributes.Collection.Set(“Database DLL”,”crdb_odbc.dll”)
    .Attributes.Collection.Set(“QE_DatabaseName”,””)
    .Attributes.Collection.Set(“QE_DatabaseType”,”ODBC (RDO)”)
    .Attributes.Collection.Set(“QE_LogonProperies”, crDbAtributes)
    .Attributes.Collection.Set(“QE_ServerDescription”,””)
    .Attributes.Collection.Set(“QE_SQLDB”,”True”)
    .Attributes.Collection.Set(“SSO Enabled”,”False”)
    End With
    For Each crTable As Table In rd.Database.Tables ‘rd is an Instance of ReportDocument
    crTableLogonInfo = crTable.LogonInfo
    crTableLogonInfo.ConnectionInfo = crConnectionInfo
    crTable.ApplyLogonInfo(crTableLogonInfo)
    Next

    crViewer.ReportSource = rd
    crViewer.RefreshReport() ‘Error ocures here
    crViewer.Show()

    Thank you!

  8. Mark Olszowka
    March 20, 2010 at 3:51 am

    I have been trying to use your code, but without success. When I run my report I get [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
    Can I ask how you setup your original ODBC DSN for your report? I create a System DSN using the Oracle in XE driver (personal Oracle). I looks like it is expecting a DSN name instead of the Oracle Service Name that I supplied.

  9. March 23, 2010 at 10:48 am

    In order to create an ODBC DSN, you need to create a Local Net Service Name. The following is the instruction based on Oracle 9i and the assumption that you have Oracle Net Configuration Assistant installed on your machine:

    1. Click on Start> Oracle Home > Configuration and Migration Tools > Net Configuration Assistant
    2. Choose the third option: Local Net Service Name configuration and click on Next.
    3. Select add and click on Next.
    4. Leave the pre selected option (Oracle 8i or later …) and click on Next.
    5. Type your Oracle database service name and click Next.
    6. Choose TCP and click Next.
    7. Enter your host name (server name), leave the preselected port number and click Next.
    8. Perform a Test.
    9. Enter a service name. This is the name you need to enter in the Windows ODBC DSN screen.
    10. Click on Next, Next and Finish.

    I hope you can follow this instruction and help you setup the connection.

  1. No trackbacks yet.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: