Crystal Reports passing credentials to the report so that it only shows data for the logged in user

I've created a report in Visual Studio that is reading data from a SalesLogix (Infor CRM) database. The report works okay but no matter what I pass it the results show only the data that was saved for the user credentials used when creating the report. When creating the report if I connect to the database using the Admin account I see everything, and if I connect to the database using one of the user accounts I see only their data. That's perfect.

So the idea is that I use the credentials the user logged into the site with, pass that data over to Crystal and then only get data for that user. But for some reason it seems that the information I'm passing to the report for the user credentials is being ignored.

Here's the body.

<body>
    <form id="form1" runat="server">
    <div id="wrapper" style="width: 110%">
        <div id="header">
            <UC:TopNav ID="TopNav" runat="server" />
        </div>
        <div class="hrDiv">
        </div>
        <div id="container">
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
                Height="815px" ReportSourceID="CrystalReportSource1" Width="1023px" EnableDatabaseLogonPrompt="True"
                EnableParameterPrompt="False" HasToggleGroupTreeButton="False" PrintMode="PDF" />
            <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
                <Report FileName="OrdersBooked.rpt">
                </Report>
            </CR:CrystalReportSource>
        </div>
        <div class="hrDiv">
        </div>
        <UC:Footer ID="Footer" runat="server" />
    </div>
    </form>
</body>

And here's the VB code.

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.web.Security


Partial Class Orders_Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init

        Dim fname As String
        fname = Request.PhysicalApplicationPath & "orders\OrdersBooked.rpt"
        Dim user As MembershipUser = Membership.GetUser()
        Dim un As String = user.UserName
        Dim pw As String = user.GetPassword("Tom")
        Dim sn As String = ConfigurationManager.AppSettings("SLXServerName")
        Dim dn As String = ConfigurationManager.AppSettings("SLXDatabaseName")

        ' Load the report
        Me.CrystalReportSource1.ReportDocument.Load(fname)

        Dim myReport As New ReportDocument
        myReport.Load(fname)

        Dim conninfo As New ConnectionInfo
        With conninfo
            .ServerName = sn
            .DatabaseName = dn
            .UserID = un
            .Password = pw
        End With
        myReport.DataSourceConnections.Clear()
        CrystalReportViewer1.ReportSource = myReport
        CrystalReportViewer1.PrintMode = CrystalDecisions.Web.PrintMode.Pdf
        SetDBLogonForReport(conninfo, myReport)

    End Sub

    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

End Class

I've tried debugging the VB and I can see the username and password are correct and appear to be passing to Crystal but it simply does not work.

Any ideas would be greatly appreciated.

Solution

I noticed that you are missing the refresh command, after you set the new report source you should refresh the report. I would change the last part of init_page in this way

myReport.DataSourceConnections.Clear()
SetDBLogonForReport(conninfo, myReport)
CrystalReportViewer1.ReportSource = myReport
CrystalReportViewer1.PrintMode = CrystalDecisions.Web.PrintMode.Pdf
CrystalReportViewer1.RefreshReport()