Setting Datasource Location at Runtime in Crystal XI/Vb.NET

Crystal XI  with vb.NET

Is there a way of setting the "Datasource Location" at runtime using vb.NET code. I need the datasource location to be dynamic and based on the users sessionID. The datasource is a SQL View, which is generated each time before a  report is requested but with a different VIEW name when request is made. The SQL view has all the data filelds needed by the report but the VIEW name is derived from the so it is unknown at design time. The web user supplies the VIEW name which is the used as the set the datasource for the report. The report is then refreshed and then loaded on the report viewer. The view is dropped after report is refreshed and displayed rendered on the web page. All these parts are working for me except I am unable to set the location at runtime as part of the code

I am not sure what part of my current code one would need for this request so I have attached the code on the page containing the report viewer.


Imports System
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Web.UI.HtmlControls
Imports System.Web.Security
Imports System.Exception

Public Class MAINT_Rpt
    Inherits System.Web.UI.Page
    Dim SQLString, ShowMessage As String
    Protected WithEvents rptVwer As CrystalDecisions.Web.CrystalReportViewer
    '// Crystal Reports XI
    Private ShowCRXI As ReportDocument
#End Region

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
           Session("Module") = "System Admin-Reports"
            With rptVwer
                If Session("RptView") = "vw_nfsINV_DETP" Then
                       .DisplayToolbar = True
                    .DisplayToolbar = True
                End If
                .DisplayToolbar = True
                .Visible = True
            End With
        Catch exCept As Exception
               '////Error generating report. "
        End Try

    End Sub

#Region "Crystal Reports"

    Function ConfigureCrystalReports()

        Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()

        Dim NFSCn As New SqlConnection(nsNFSDB.dbNFSDB)
        Dim cmd As New SqlCommand(SQLString, NFSCn)
        Dim sqlr As SqlClient.SqlDataReader
        Dim CredUID, CredPWD As String

            SQLString = "select * from n_SYS_MSC where NFS_AREA = 'INFOS' AND NFS_KEY_DATA = '" & Session("SourcDB") & "'"
            cmd.CommandText = SQLString
            sqlr = cmd.ExecuteReader()
            Do While sqlr.Read()
                If Not IsDBNull(sqlr("NFS_DATA_01")) = True Then
                    CredUID = Trim(sqlr("NFS_DATA_01"))
                    CredPWD = Trim(sqlr("NFS_DATA_02"))
                    CredUID = ""
                    CredPWD = ""
                End If

            '   myConnectionInfo.DatabaseName = "SUN426"
            myConnectionInfo.UserID = CredUID
            myConnectionInfo.Password = CredPWD

            ShowCRXI = New ReportDocument()
            rptVwer.ReportSource = ShowCRXI


            SetDBLogonForReport(myConnectionInfo, ShowCRXI)

        Catch exCept As Exception
           '//// ShowMessage = "Unable to update access credentials & present report: Please contact your systems administrator:"
            Exit Function
        End Try

    End Function

    Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo, ByVal myReportDocument As ReportDocument)
        Dim myTables As Tables = myReportDocument.Database.Tables
        Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
        For Each myTable In myTables
            Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo = myConnectionInfo

    End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JenebyMAuthor Commented:
Please keep open. I have just returned to work and will be testing the suggested solution during this week.
Thank you
When do you expect to get to this?

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

JenebyMAuthor Commented:
Please close now. The solution provided works with database 'tables'. We have been unable to get this to work with 'views' - which is what we needed. Thanks for your indulgence in keeping this open.

What seems to be the problem with views?  Do the users have permissions for the views?

JenebyMAuthor Commented:
Users have the appropriate permissions fto access the tables and views.

I will restate the problem as follows:

The crystal report design time datasource location is a view. This is called vw_nfs_INVOICE and used only at design time.

At run time the a new view name is created by the application when a report is requested. This new view name is unique and random. ie the run time view name is vw_nfs_XXXXX where XXXXX is the last five characters of the users session ID. The Crystal XI report then needs to refresh the data after changing the location from vw_nfs_INVOICE to vw_nfs_XXXXX and displaying the data from that view.

After the data has been displayed on the crystal report, view vw_nfs_XXXXX is dropped.

The problem is finding the code snippet that will change the design datasource location from vw_nfs_INVOICE to vw_nfs_XXXXX at run time.

Did you try the idea?  As far as Crystal is concerned a view is a table so the same method should work.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JenebyMAuthor Commented:
This idea does not work with tables.

If you read the sypnosis, it addresses the issue of changing the database name NOT table/view name within the same database. I have attaempted to make changes to the location within the same database. NO joy there.

JenebyMAuthor Commented:
Finally solved this:

I have used a suggested solution from the business objects site:

This is

Your suggestion was quite close. The above KB article was closer since all I had to change.
1. The corrected connection info in the original code by using 'crConnectionInfo.ServerName = "DSN" instead of "Server Name"

2. Specify the view name required at run time using "crTable.Location = "Northwind.dbo." + session.ID"

Works perfectly for my purpose.

Your suggestion certainly led me to this solution

Please close

Since our dev and prod servers are named the same on separate networks we never had any problems with passing or changing the database.

Glad i could help

The link you suggested is no longer valid, can you give me another link or an alternate way to get the solution?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.