locked
Changing crystal reports connection string dynamically RRS feed

  • Question

  • I need to switch between the databases at runtime for my crystal reports in .NET 2.0.

    Is it possible to change the crystal reports connection string at run time in .NET 2.0

    Please provide me any sample code which illustrates dynamically changing the connection string of crystal reports at rune time.

    Thanks in advance,

    Vaishu

     

    Monday, March 20, 2006 11:45 AM

Answers

  • Thanks for your prompr reply.

    I'll try this code sample and let you know.

    Once again thank you for giving me the reply.

    Tuesday, March 21, 2006 11:03 AM

All replies

  • Hi,

    If I understand well uor cristal report is linked with Datatbase 1 and in run time u want to change the source ...?

    In thant case the return data have to get same struct becouse in other way the Creport reise the error.

    So u have to make new conection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As

    System.EventArgs) Handles MyBase.Load

    Dim myReport As New worldsales_northwind()

    CrystalReportViewer1.ReportSource = myReport

    myReport.Load()

    Dim myDBConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()

    With myDBConnectionInfo

    .ServerName = “localhost”

    .DatabaseName = “Northwind”

    .UserID = “sa”

    .Password = “”

    End With

     

    and when waht to change in run time conection all u have to do is set differen pram for server name and data base name with corect user and password

    If this is not a righ case tell me with more details what u want to continiue ...:-)

     

    Monday, March 20, 2006 1:21 PM
  • Thanks for your prompr reply.

    I'll try this code sample and let you know.

    Once again thank you for giving me the reply.

    Tuesday, March 21, 2006 11:03 AM
  • Is it possible to implement the above scenario using the auto-attach feature of SQL’05 with Crystal reports.

    We are attempting to move the database from a full install of SQL'05 to a SQLEXPRESS with an AUTOATTACH.

    Here we need to do it at runtime for our .Net crystal reports.

    Thanks,

    vaishu

     

     

    Monday, March 27, 2006 5:30 AM
  • hi, plesae i wanna know how can i change my source (to my database) connection to my crystal report in vb, not in crystal report; with oledb;i don't want to make a connection to my database from crystal but from vb because i have many database and i want to connect to them from vb dynamiclly and thank you a lot
    Monday, April 3, 2006 8:10 PM
  • Try this code sample:

                Dim crtableLogoninfos As New TableLogOnInfos()
                Dim crtableLogoninfo As New TableLogOnInfo()
                Dim crConnectionInfo As New ConnectionInfo()
                Dim CrTables As Tables
                Dim CrTable As Table

                'Declare ReportDocument object and load your existing report
                'Make sure that you give the correct path for the document else it will give exception
                Dim crReportDocument As New ReportDocument()
                crReportDocument.Load("C:\MyApp\Crystal\Crystal\WorldSalesReport.rpt")
                CrystalReportViewer1.ReportSource = crReportDocument

                CrTables = crReportDocument.Database.Tables

                Dim crLoc As String
                crLoc = txtDb.ToString & ".dbo"

                For Each CrTable In CrTables

                    crtableLogoninfo = CrTable.LogOnInfo
                    'Read MachineName\InstanceName,Database details from User interface
                    'and load them into crConnectionInfo object
                    crConnectionInfo.ServerName = txtMac.Text
                    crConnectionInfo.DatabaseName = txtDb.Text
                    crConnectionInfo.IntegratedSecurity = True
                    crtableLogoninfo.ConnectionInfo = crConnectionInfo
                    CrTable.ApplyLogOnInfo(crtableLogoninfo)
                    CrTable.Location.Substring(CrTable.Location.LastIndexOf(".") + 1)

                Next

                crReportDocument.ReportOptions.EnableSaveDataWithReport = False
                'Refresh the ReportViewer Object
                CrystalReportViewer1.RefreshReport()
                'Bind the ReportDocument to ReportViewer Object
                CrystalReportViewer1.ReportSource = crReportDocument

    Here, we are moving from one DB to another DB at runtime using Vb code.

    hope this is what you are looking for.

     

    Thanks,

    Vaishu

     

     

    Tuesday, April 4, 2006 7:25 AM
  • This works great for database connections, but if we use an XML as a data source and I want to generate the xml at runtime along with supplying the location of the xml file and xsd file, it prompts me for login.  I have tried this below and it prompts for user id and password for an xml file, doesn't make sense.

    I'm running CR XI 2.

    TableLogOnInfo lrptTableLogin;

    foreach (CrystalDecisions.CrystalReports.Engine.Table lrptTable in this.doc.Database.Tables)

    {

    lrptTableLogin = lrptTable.LogOnInfo;

    if (lrptTableLogin.ConnectionInfo.Attributes.Collection.ContainsKey("QE_SQLDB"))

    {

    if (!Convert.ToBoolean(lrptTableLogin.ConnectionInfo.Attributes.Collection.LookupNameValuePair("QE_SQLDB").Value))

    {

    string dbtype = lrptTableLogin.ConnectionInfo.Attributes.Collection.LookupNameValuePair("QE_DatabaseType").Value.ToString();

    if (dbtype.Equals("XML"))

    {

    lrptTableLogin.ConnectionInfo.ServerName = filePath;

    lrptTableLogin.ConnectionInfo.LogonProperties[0] = new NameValuePair2("Local Schema File",filePath.Substring(0, filePath.IndexOf(".xml")+4));

    lrptTableLogin.ConnectionInfo.LogonProperties[1] = new NameValuePair2("Local XML File",filePath.Substring(filePath.IndexOf(".xml")+5));

    lrptTableLogin.ConnectionInfo.Attributes.Collection.LookupNameValuePair("QE_ServerDescription").Value = filePath;

    lrptTable.ApplyLogOnInfo(lrptTableLogin);

    }

    }

    }

    }

     

     

    Thursday, June 8, 2006 9:38 PM
  • If I need to pass a parameters??

    In adittion I  do this:

     

    If Not _Parameters Is Nothing Then

    'PASs  PARAMETers

    For x = _Parameters.GetLowerBound(0) To _Parameters.GetUpperBound(0)

    Dim dVal As New ParameterDiscreteValue

    dVal.Value = _Parameters(x).ToString

    form.crystalViewer.ParameterFieldInfo(x).CurrentValues.Add(dVal)

     next

    End If

    dVal not is empty, always read the parameter

    but my report say : procedure "xxxx" expects @parameter1 which was not supplied!

    WHY ????

     

     

     

     

    Monday, November 13, 2006 4:27 PM
  •  vaishali.mspp wrote:

    Try this code sample:

                Dim crtableLogoninfos As New TableLogOnInfos()
                Dim crtableLogoninfo As New TableLogOnInfo()
                Dim crConnectionInfo As New ConnectionInfo()
                Dim CrTables As Tables
                Dim CrTable As Table

                'Declare ReportDocument object and load your existing report
                'Make sure that you give the correct path for the document else it will give exception
                Dim crReportDocument As New ReportDocument()
                crReportDocument.Load("C:\MyApp\Crystal\Crystal\WorldSalesReport.rpt")
                CrystalReportViewer1.ReportSource = crReportDocument

                CrTables = crReportDocument.Database.Tables

                Dim crLoc As String
                crLoc = txtDb.ToString & ".dbo"

                For Each CrTable In CrTables

                    crtableLogoninfo = CrTable.LogOnInfo
                    'Read MachineName\InstanceName,Database details from User interface
                    'and load them into crConnectionInfo object
                    crConnectionInfo.ServerName = txtMac.Text
                    crConnectionInfo.DatabaseName = txtDb.Text
                    crConnectionInfo.IntegratedSecurity = True
                    crtableLogoninfo.ConnectionInfo = crConnectionInfo
                    CrTable.ApplyLogOnInfo(crtableLogoninfo)
                    CrTable.Location.Substring(CrTable.Location.LastIndexOf(".") + 1)

                Next

                crReportDocument.ReportOptions.EnableSaveDataWithReport = False
                'Refresh the ReportViewer Object
                CrystalReportViewer1.RefreshReport()
                'Bind the ReportDocument to ReportViewer Object
                CrystalReportViewer1.ReportSource = crReportDocument

     

    If I need to pass a parameters??

    In adittion I  do this:

     

    If Not _Parameters Is Nothing Then

    'PASs  PARAMETers

    For x = _Parameters.GetLowerBound(0) To _Parameters.GetUpperBound(0)

    Dim dVal As New ParameterDiscreteValue

    dVal.Value = _Parameters(x).ToString

    CrystalReportViewer1.ParameterFieldInfo(x).CurrentValues.Add(dVal)

     next

    End If

      CrystalReportViewer1.RefreshReport()
                        CrystalReportViewer1.ReportSource = crReportDocument

     

    dVal not is empty, always read the parameter

    but my report say : procedure "xxxx" expects @parameter1 which was not supplied!

    WHY ????

     

     

    Monday, November 13, 2006 4:36 PM
  •  

    Why I don't have the lrptTableLogin.ConnectionInfo.LogonProperties[0] property?  What namespace I missed?  I have

     

    using CrystalDecisions.CrystalReports.Engine;

    using CrystalDecisions.Shared;

    using CrystalDecisions.ReportSource;

    using CrystalDecisions.CrystalReports;

    using CrystalDecisions.Enterprise;

    using CrystalDecisions.ReportAppServer;

     

     

    Thursday, May 29, 2008 6:42 AM
  •  you can change the database at runtime , check withthe code and link

            Dim cryRpt As New ReportDocument
            Dim crtableLogoninfos As New TableLogOnInfos
            Dim crtableLogoninfo As New TableLogOnInfo
            Dim crConnectionInfo As New ConnectionInfo
            Dim CrTables As Tables
            Dim CrTable As Table

            cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

            With crConnectionInfo
                .ServerName = "YOUR SERVER NAME"
                .DatabaseName = "YOUR DATABASE NAME"
                .UserID = "YOUR DATABASE USERNAME"
                .Password = "YOUR DATABASE PASSWORD"
            End With

            CrTables = cryRpt.Database.Tables
            For Each CrTable In CrTables
                crtableLogoninfo = CrTable.LogOnInfo
                crtableLogoninfo.ConnectionInfo = crConnectionInfo
                CrTable.ApplyLogOnInfo(crtableLogoninfo)
            Next


    http://vb.net-informations.com/crystal-report/vb.net_crystal_report_load_dynamically.htm

    los
    Sunday, July 20, 2008 8:30 AM
  • The specified code works fine if Report created by using Direct table links.If the report Created with Sps code doesn't work.When DB changes occur That type reports Connection shows 'table spName not found'  What is the reason behind that
    Tuesday, September 29, 2009 11:58 AM
  • I am having this same issue.  I read through all of the posts that were made, but the solution given I have already tried.  I have the database information in my webconfig file.  I then get the information from my webconfig file as follows:

     

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

     

    Dim rpt As New ReportDocument

    rpt =

    New ESPReport

    rpt.SetParameterValue(

    "@SarID", SessionDataLib.SarCommonPlus.SarId)

    Connect(rpt)

     

    Me.CrystalReportViewer1.ReportSource = rpt

     

    End Sub

     

    Private Sub Connect(ByRef rpt As ReportDocument)

     

    Dim crConnectionInfo As ConnectionInfo = New ConnectionInfo

    crConnectionInfo.ServerName = ApplicationDataLib.db_Server

    crConnectionInfo.DatabaseName = ApplicationDataLib.db_Name

    crConnectionInfo.UserID = ApplicationDataLib.db_UserId

    crConnectionInfo.Password = ApplicationDataLib.db_Password

     

    End Sub


    The above code reads the webconfig file correctly and places the correct values into each of the variables.  However, my issue still exists that even though these variables have the correct values in them that the datasource is still pointing to my local database instead of the one that is located in the webconfig file rather than my test database.

    Any ideas?

    Monday, December 14, 2009 2:36 PM
  • Hey

     

    I am using the similar code and it works but when the user enters the wrong log in information, the report still runs. How do you tackle the issue when the log in for information is incorrect.

    Thnanks

    MA

    Friday, March 26, 2010 6:52 PM