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.
just before I ran the report, I printed the following.

rpt.DataSourceConnections.Item(0).UserID
rpt.DataSourceConnections.Item(0).Password

I always get the userid, but the password is blank. Not sure why though

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)
        Next
    End Sub

    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
            Next
        Next
    End Sub

I got the solution from businessobjects. This works only with SP1 for Crystal. Following is what they suggested and it works like a charm. Hope this will help some one else.

Symptom
 
Using Visual Studio .NET, database logon code is not recognized.
Crystal reports Viewer database logon parameter screen prompts for login.
This symptom is only observed when using Crystal Reports 2008 Service Pack 2.
Crystal Reports 2008 with Service Pack 1 works as expected.
 
 
Reproducing the Issue
 
Use Crystal Reports 2008 SP2 to create a report with dynamic parameter(s)
Use the following code from the Crystal Reports SDK for VS .NET
 
Dim crDatabase As Database
Dim crTables As TablesDim crTable As Table
Dim crTableLogOnInfo As TableLogOnInfo
Dim crConnectionInfo As ConnectionInfo
 
crReportDocument.Load("<path>")
crReportDocument.Refresh()
crConnectionInfo = New ConnectionInfo()
 
With crConnectionInfo
.ServerName = "<New Server Name>"
.Password = "<password>"
End With
 
crDatabase = crReportDocument,Database
crTables = crDatabase.Tables
 
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next
 
CrystalReportViewer1.ReportSource = crReportDocument
 
The above code works with Crystal Reports 2008 SP 1
 
Resolution
 
Add .DataSourceConnections(0).SetConnection to the code as follows:
 
.
.
.
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectionInfo
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next
 
''This is the line of code to be added ****
crReportDocument.DataSourceConnections(0).SetConnection("Sever Name", "Database Name", "Use ID", "Password")
 
CrystalReportViewer1.ReportSource = crReportDocument