Logon code does not work with SQL

27 Views

I have written the code that sets the logon information for Crystal reports and allows user to run the report. My problem is the code works fine with Oracle database, but not with SQL. I have created an ODBC connections for both SQL and Oracle, but the SQL does not work. If I run the report in Crystal, everything works without any issues. Could any one help. I use VS2005 and Crystal2008. when I run the aspx form, system asks again for the user and password. When I enter this and click OK, it displays the report parameters.

Any help is greatly appreciated.

Form Load code:

Dim reportPath As String = "C:\Inetpub\wwwroot\sbc\SQL_Report.rpt"

Dim rpt As New ReportDocument

rpt.Load(reportPath)

Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

myConnectionInfo.ServerName = "SQL_ODBC"

myConnectionInfo.UserID = "repuser"

myConnectionInfo.Password = "rep123"

SetDBLogonForReport(myConnectionInfo, rpt)

SetDBLogonForSubreports(myConnectionInfo, rpt)

CrystalReportViewer1.ReportSource = rpt

Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)

Dim myTables As Tables = myReportDocument.Database.Tables

For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables

Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo

myTableLogonInfo.ConnectionInfo = myConnectionInfo

myTable.ApplyLogOnInfo(myTableLogonInfo)

Private Sub SetDBLogonForSubreports(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)

Dim mySections As Sections = myReportDocument.ReportDefinition.Sections

For Each mySection As Section In mySections

Dim myReportObjects As ReportObjects = mySection.ReportObjects

For Each myReportObject As ReportObject In myReportObjects

If myReportObject.Kind = ReportObjectKind.SubreportObject Then

Dim mySubreportObject As SubreportObject = CType(myReportObject, SubreportObject)

Dim subReportDocument As ReportDocument = mySubreportObject.OpenSubreport(mySubreportObject.SubreportName)

SetDBLogonForReport(myConnectionInfo, subReportDocument)

End If

End Sub