1214028 - How to pass Database logon info to a Crystal Report at runtime in VB.NET or C#.NET

A VB.NET application uses Crystal Reports for Visual Studio .NET as the reporting development tool.

How do you pass database logon information to a Crystal Report at runtime in this VB.NET application?

 

Crystal Reports for Visual Studio

To pass logon information to a Crystal Report at runtime, use the following code sample:

VB.NET

---------

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Dim crtableLogoninfos As New TableLogOnInfos()

Dim crtableLogoninfo As New TableLogOnInfo()

Dim crConnectionInfo As New ConnectionInfo()

Dim CrTables As Tables

Dim CrTable As Table

Dim TableCounter

'If you are using a Strongly Typed report (Imported in 'your project) named CrystalReport1.rpt use the 'following:

Dim crReportDocument As New CrystalReport1()

'If you are using a Non-Typed report, and loading a report outside of the project, use the following:

Dim crReportDocument As New ReportDocument()

crReportDocument.Load("c:\myReports\myReport.rpt")

'Set the ConnectionInfo properties for logging on to the Database If you are using ODBC, this should be the DSN name NOT the physical server name. If you are NOT using ODBC, this should be the physical server name 

With crConnectionInfo

.ServerName = "DSN or Server Name"

'If you are connecting to Oracle there is no 'DatabaseName. Use an empty string.

'For example,

.DatabaseName = ""

.DatabaseName = "DatabaseName"

.UserID = "Your User ID"

.Password = "Your Password"

End With

'This code works for both user tables and stored procedures. Set the CrTables to the Tables collection of the report  

CrTables = crReportDocument.Database.Tables

'Loop through each table in the report and apply the LogonInfo information

For Each CrTable in CrTables

CrTableLogonInfo = CrTable.LogonInfo

CrTableLogonInfo.ConnectionInfo = crConnectionInfo

CrTable.ApplyLogOnInfo(crtableLogoninfo)

'If your DatabaseName is changing at runtime, specify the table location.

'For example, when you are reporting off of a Northwind database on SQL server you should have the following line of code:

crTable.Location = "Northwind.dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

Next

'Set the viewer to the report object to be previewed.

CrystalReportViewer1.ReportSource = crReportDocument

C#.NET

---------

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

private CrystalReport1 crReportDocument = new

CrystalReport1 ();

private Database crDatabase;

private Tables crTables;

private Table crTable;

private TableLogOnInfo crTableLogOnInfo;

private ConnectionInfo crConnectionInfo = new ConnectionInfo ();

//Setup the connection information structure to log on to the data source for the report.

// If using ODBC, this should be the DSN. If using OLEDB, etc, this should be the physical server name

crConnectionInfo.ServerName = "DSN or Server Name";

// If you are connecting to Oracle there is no DatabaseName. Use an empty string i.e. crConnectionInfo.DatabaseName = "";

crConnectionInfo.DatabaseName = "DatabaseName";

crConnectionInfo.UserID = "Your UserID";

crConnectionInfo.Password = "Your Password";

// This code works for both user tables and stored procedures. Get the table information from the report

crDatabase = crReportDocument.Database;

crTables = crDatabase.Tables;

//Loop through all tables in the report and apply the connection information for each table.

for (int i = 0; i < crTables.Count; i++)

{

crTable = crTables [i];

crTableLogOnInfo = crTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConnectionInfo;

crTable.ApplyLogOnInfo(crTableLogOnInfo);

//If your DatabaseName is changing at runtime, specify the table location. For example, when you are reporting off of a Northwind database on SQL server you should have the following line of code:

crTable.Location = "Northwind.dbo." + crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

}

//Set the viewer to the report object to be previewed.

crystalReportViewer1.ReportSource = crReportDocument;

Additional Information

--------------------------

" If you are using a web application make sure that you do not specify or call the DataBind in your code as this will nullify the code above.

" If the above code still results in a logon failure in a web application (ASP.NET), refer to knowledge base article c2010867 (For Windows 2000) or c2013758 (Windows 2003 Server (IIS6)), and ensure that the ASPNET account has sufficient access to the database, reports, and the application directories.

 

====================

NOTE:

The additional knowledgebase articles referenced can be found at:

https://www.sdn.sap.com/irj/scn/advancedsearch?cat=sdn_ossnotes&query=&adv=true

====================

" If you are changing database at runtime, it is important that you specify the table location after you apply logon information (this is a case sensitive property). You can either specify the table name only or the fully qualified table name such as  

crTable.location = "databaseName.dbo.tablename";

//Refer to knowledge base article c2010275 if you wish to change database logon information in the main and subreport.

" If you are reporting off an Access database, then specify either the 'ServerName' or 'DatabaseName' to the 'ConnectionInfo' Object depending on how you are connecting to Access.

For example, if you are connecting to Access through ODBC, then set the 'DatabaseName' for the 'ConnectionInfo' object as follows:

With crConnectionInfo

.DatabaseName = "C:\mydatabase\mydata.mdb"

End With

If you are connecting to Access through OLE DB, then set set the 'ServerName':

With crConnectionInfo

.ServerName = "C:\mydatabase\mydata.mdb"

End With

It is not possible to report of a secured Access database using a native connection. See knowledge base article C2010460 for more information.

" If you are using more than one database with different usernames and passwords, use a loop to pass in the different values.

 

DOTNET VB.NET C# CSHARP LOGGING ON LOG ON C2014128 DATABASE CHANGE LOGON Crystal Reports for Visual Studio Logon Information TableLogonInfo , c2010371 , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , How To

SAP Crystal Reports, version for Visual Studio .NET 2005 ; SAP Crystal Reports, version for Visual Studio .NET 2008 ; SAP Crystal Reports, version for Visual Studio .NET 9.1