Set Crystal Database Configuration

Home > Crystal Report > Set Crystal Database Configuration

Set Crystal Database Configuration

July 30, 2008 Amir Aryani Leave a comment Go to 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

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

Return crConnectionInfo
End Function

Categories: Crystal Report

Comments (9) Trackbacks (0) Leave a comment Trackback

Edwin

October 2, 2008 at 8:47 am | #1

Reply | Quote

Hi,

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

Thanks

amiraryani

January 12, 2009 at 11:06 am | #2

Reply | Quote

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.

Mario

January 11, 2009 at 2:20 am | #3

Reply | Quote

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?

amiraryani

January 12, 2009 at 11:11 am | #4

Reply | Quote

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.

Mark Olszowka

March 20, 2010 at 3:51 am | #5

Quote

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.
Amir Aryani

March 23, 2010 at 10:48 am | #6

Quote

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.

Y, Wang

September 7, 2009 at 5:22 pm | #7

Reply | Quote

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!

amiraryani

September 7, 2009 at 5:38 pm | #8

Reply | Quote

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.

Y, Wang

September 7, 2009 at 10:25 pm | #9

Reply | Quote

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!

No trackbacks yet.

Amir Aryani