Avoid asking Logon Information

[spidydotnet]

Hi Folks..

I've created a Crystal Report in VB.NET. When the report is generated its fine when u click for next page. It asks for Login information for the Database.. can anyone help me please.. I am using ODBC DSN for connecting Database and my DB is SQL2000

thanks

Spidy.NET
'Discussion is Knowledge'

 

[pgtek]

Hi
try this

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(&quot;c:\myReports\myReport.rpt&quot

'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 = &quot;DSN or Server Name&quot;

'If you are connecting to Oracle there is no
'DatabaseName. Use an empty string.
'For example, .DatabaseName = &quot;&quot;

.DatabaseName = &quot;DatabaseName&quot;
.UserID = &quot;Your User ID&quot;
.Password = &quot;Your Password&quot;
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 = &quot;Northwind.dbo.&quot; &
crTable.Location.Substring(crTable.Location.LastIndexOf(
&quot;.&quot + 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 = &quot;DSN or
Server Name&quot;;

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

crConnectionInfo.DatabaseName = &quot;DatabaseName&quot;;
crConnectionInfo.UserID = &quot;Your UserID&quot;;
crConnectionInfo.Password = &quot;Your Password&quot;;

// 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 = &quot;Northwind.dbo.&quot; +
crTable.Location.Substring(crTable.Location.LastIndexOf
(&quot;.&quot + 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 the above code still results in a LogOn Failure in
a Web application (ASP.NET), please refer to the
Knowledge base article c2010867 (For Windows 2000) or
c2013758 (Windows 2003 server (IIS6)), and make sure
ASPNET account has enough access to the database,
reports, and the application directories.

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 = &quot;databaseName.dbo.tablename&quot;

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 = &quot;C:\mydatabase\mydata.mdb&quot;
End With

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

With crConnectionInfo
.ServerName = &quot;C:\mydatabase\mydata.mdb&quot;
End With

cheers

pgtek

 

[dilwalle]

Hi Pgtek!
Can u pls. help me out. It seems u have knowledge in this area. I m facing the following problem in CR.NET, & not getting any answer from any forum. Pls. help me .

PROBLEM: Crystal Report in Web Application Fails to Log On to SQL Server
The information in this article applies to:
Microsoft Visual Studio .NET (2003), Professional Edition
Microsoft SQL Server 2000 (all editions) 8.0
Microsoft SQL Server 2000 (all editions) SP2


SYMPTOMS :
When you use SQL Server 2000 to obtain live data for a Crystal Report in a Visual Studio .NET Web application, the Crystal Report fails to log on to the database. This occurs even if you provide accurate credentials at design time. You receive the following error message in your Web browser:

Server Error in '/Crystal' Application.
-----------------------------------------------------------
Logon failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed.
CAUSE

CODE GENERATING ERROR :

Code Written in the crystalreportviewer1_load event
-----------------------------------------------------------------------------------------
Try
'for changing the data source at run time
'*********************************************
Dim strCon1 = &quot;Data Source=Mac05;Database=Immigration;User Id=sa;Password=;&quot;
Dim objCon1 As New SqlConnection(strCon1)
objCon1.Open()
Dim objAdapter As New SqlDataAdapter(&quot;select * from personaldetailsskilled&quot;, objCon1)
Dim objDs As New DataSet
objAdapter.Fill(objDs)

Dim objr As New sr1 'sr1.rpt is the report created previously

objr.SetDataSource(objDs)
'*****************************************
'code to specify connection for each table of the report
'*****************************************************

Dim crLogonInfo As TableLogOnInfo = New TableLogOnInfo

crLogonInfo.ConnectionInfo.ServerName = &quot;Mac05&quot;

crLogonInfo.ConnectionInfo.DatabaseName = &quot;Immigration&quot;

crLogonInfo.ConnectionInfo.UserID = &quot;sa&quot;

crLogonInfo.ConnectionInfo.Password = &quot;&quot;


Dim str As String = crLogonInfo.ConnectionInfo.ServerName

For Each myTable As Table In objr.Database.Tables

myTable.ApplyLogOnInfo(crLogonInfo)


Next



'*********************************************
'Code to bind the crystalreportviewer with the report
'**********************************************

Dim crTableLogoninfos = New TableLogOnInfos
crTableLogoninfos.Add(crLogonInfo)

CrystalReportViewer1.LogOnInfo = crTableLogoninfos
CrystalReportViewer1.ReportSource = objr
CrystalReportViewer1.DataBind()
'*******************************************************

Catch ex As Exception
Label1.Text = ex.Source & &quot; &quot; & ex.Message
End Try
------------------------------------------------------------

Pls. either post it here or give me ansewr in my following id: sugata_chakrabortyATTHERATEOFyahooDOTcom

Thanx & regards.

 

[pgtek]

hi dilwalle (MIS)
check this

http://support.crystaldecisions.com/library/kbase/articles/c2012018.asp

cheers



pgtek

 

[ddnh]

anyone know how to set logon info for two datasources in one report? one is SQL, one is Oracle. Using the above method, it's no problem for either server when I am only using one datasource, but some of my reports require both. I am using c#.net.