INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This forum is the most helpful site I've ever used. I used to use Deja.com; but, this site is better - hands down!..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Reporting Solutions > Business Objects: Crystal Reports 4 Other topics > FAQs

Business Objects: Crystal Reports 4 Other topics FAQ

Data Access

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

faq149-4870

Posted: 20 Feb 04

When we design reports in Crystal Report designer, it fixs the complete information about Database location with table or stored procedures and if we want to run same reports with different datasource then we have to change logon information and as well as the location of the database. The main purpose of this article is to explain how we change logon information of our reports to run them with different databases. I noticed that it is a most common problem which most of the developer face in development.

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

Solution

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 = crConnectionInfo.DatabaseName & ".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 ;
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 = crConnectionInfo.DatabaseName + ".dbo." +
crTable.Location.Substring(crTable.Location.LastIndexOf
(".") + 1)
}

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

crystalReportViewer1.ReportSource =
crReportDocument;

=============
NOTE:

· If you're 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 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 tablename only or
the fully qualified tablename such as:

crTable.location = "databaseName.dbo.tablename"

If you are reporting off of an Access Database, then
you only need to 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 you just need to set the DatabaseName for the
ConnectionInfo object as the following:

With crConnectionInfo
.DatabaseName = "C:\mydatabase\mydata.mdb"
End With

If you are connecting to Access via OLEDB, then you
just need to set the ServerName:

With crConnectionInfo
.ServerName = "C:\mydatabase\mydata.mdb"
End With

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

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

Product:
CR for Visual Studio .Net

Reported Version:
9.1.9360.0 Crystal Reports for Visual Studio .Net

Thanks.
Muhammad Essa Mughal
Software Developer
Canada

Back to Business Objects: Crystal Reports 4 Other topics FAQ Index
Back to Business Objects: Crystal Reports 4 Other topics Forum

My Archive

My FAQ Archive