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

Posted on 2006-04-08
Last Modified: 2012-05-09
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
Question by:JenebyM
    LVL 13

    Expert Comment


    Author Comment

    Please keep open. I have just returned to work and will be testing the suggested solution during this week.
    Thank you
    LVL 100

    Expert Comment

    When do you expect to get to this?


    Author Comment

    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.

    LVL 100

    Expert Comment

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


    Author Comment

    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.

    LVL 100

    Accepted Solution

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


    Author Comment

    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.


    Author Comment

    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

    LVL 100

    Expert Comment

    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


    Expert Comment

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now