AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=490&pId=-1
Troubleshooting Database Login Errors using Crystal Reports with .NET
page
by Eric Landes
Feedback
Average Rating: 
Views (Total / Last 10 Days): 105957/ 148

Introduction

I participate in a few Crystal Reports centric mailing lists and forums.  One common area of issues I see are problems when logging onto a database in an ASP.NET application.  To help with these issues, I thought I would write this article with some troubleshooting techniques for database logon problems.

I assume that you are familiar with ASP.NET, and Crystal Reports (either .Net or 8.5 and greater).  The databases covered will be SQL Server and DataSets. 

 

Logon Error when Binding DataSet

Logon Error When Binding a DataSet

I have seen this error posted more than a few times.  And it's a deceiving to some users, because, you shouldn't have to log onto your dataset.  And that statement is true.  You only have to logon when you are filling the dataset.  The first step to troubleshooting this error is to find out exactly what line your code is erroring out on.  Below is some standard code used to fill a dataset, and then to bind it to your report.  I've included an error in this code to show how we might troubleshoot it.

Protected CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer 

Private CrystalReport1 As myReport= New myReport() 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 

Dim objConn As SqlConnection = m_oSystem.OpenConnection() 
Dim strSQL As String = "SELECT a.*, b.Corporation_Name " & _ 
"FROM Projects a, Corporations b " & _ 
"WHERE a.Corporation_ID=b.Corporation_ID " & _ 
"Order By a.Project_ID" 

Dim objDA As SqlDataAdapter = New SqlDataAdapter(strSQL, objConn) 
Dim objDS As myDataset= New myDataset() 
objDA.Fill(objDS, "myDataset") 

CrystalReport1.SetDataSource(objDS.Tables(0)) 

CrystalReportViewer1.ReportSource = CrystalReport1 

Since I don't believe a login error can be caused by the dataset, I'm going to make sure that dataset is actually filled.  I might initially think the problem occurs before binding to the viewer.  One method to test this theory would be to use the WriteXML method of the dataset to write an actual xml file to disk.  Put this method in before setting the datasource.  If it works, I know that the problem is either in setting the datasource, or in binding to the viewer.

objDA.Fill(objDS, "myDataset") 


objDS.WriteXML("testfile.xml")

CrystalReport1.SetDataSource(objDS.Tables(0)) 

In this particular case however, once I write that file, I discover that it works.  So the problem appears to be in the SetDataSource.  My next method would be to experiment with what I'm setting.  Why not set the datasource to just the dataset without specifying a table?  When I do that, I discover that this solves the problem.  Crystal wants a DATASET, not a DATATABLE.  Seems simple now that we know that, but it's a bit frustrating when you are trying to debug the problem.

LogOnException: Logon failed. when binding to database

Another problem that I've seen mentioned a lot on the message boards involves binding directly to the data via SQL, a Stored Procedure, a view, etc.  The ASP.NET page gives out this error: Exception Details: CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed. 

This one is pretty hard to troubleshoot.  When I got this error, I would step through the code in Visual Studio in debug mode. In doing this, I discover no errors are encountered when stepping through the initial code.  I then tried using a technique to log onto all tables in the report documents tables collection.  The code to do this is below:

Dim oReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
oReport.Load(reportName)
DoCRLogin(oReport)


 


   Public Sub DoCRLogin(ByRef oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        Dim _applyLogin As New ApplyCRLogin


        ' use ApplyLogin object to apply login info to all tables in CR object
        _applyLogin._dbName = "Northwind"
        _applyLogin._passWord = "CrystalUser"
        _applyLogin._serverName = "(local)"
        _applyLogin._userID = "CrystalUser"
        _applyLogin.ApplyInfo(oRpt)


        ' clean up
        _applyLogin = Nothing
    End Sub



Public Class ApplyCRLogin
    Public _dbName As String
    Public _serverName As String
    Public _userID As String
    Public _passWord As String
    Public Sub ApplyInfo(ByRef _oRpt As CrystalDecisions.CrystalReports.Engine.ReportDocument)
        Dim oCRDb As CrystalDecisions.CrystalReports.Engine.Database = _oRpt.Database
        Dim oCRTables As CrystalDecisions.CrystalReports.Engine.Tables = oCRDb.Tables
        Dim oCRTable As CrystalDecisions.CrystalReports.Engine.Table
        Dim oCRTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo
        Dim oCRConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()
        oCRConnectionInfo.DatabaseName = _dbName
        oCRConnectionInfo.ServerName = _serverName
        oCRConnectionInfo.UserID = _userID
        oCRConnectionInfo.Password = _passWord
        For Each oCRTable In oCRTables
            oCRTableLogonInfo = oCRTable.LogOnInfo
            oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo
            oCRTable.ApplyLogOnInfo(oCRTableLogonInfo)


        Next


    End Sub


End Class
 

The class ApplyCRLogin has the method that applies the login to each table in the report.  This is instantiated via the DoCRLogin method, which sets the user name, password, server name and database.  In this instance, the login info is the same for all.  A different method would have to be created to accommodate different tables to login.  This seems to solve a lot of login problems similar to what I've mentioned. 

If that still doesn't solve the issue, I've seen folks change from OLEDB to ODBC drivers, along with this login technique.  That seems to solve some tricky login problems as well.

Summary

These are a couple of login problems I have seen on the mailing lists.  Keep in mind the object oriented nature of the CR document when working on login problems.  That may help in solving login problems not covered by this article.  I hope this helps, and keep on coding!


Product Spotlight
Product Spotlight 

©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-03 3:55:25 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search