Creating Crystal Report with Dynamic Database

Question

I have created a report using crystal reports.
Currently, I am using User DSN as its datasource.
But the main problem is the report only opens from the Main PC as I set trustedconnection = TRUE.
I can change DSN from a LAN pc as Trusted Connection to FALSE and set UserId and Pwd manually through ODBC.
By doing so, reports work perfectly in LAN but my another problem is I have to change the Database on program runtime.
All Database structures(schema),Tables, procedures,Views all are same, just have different names.
Now when I change Default Database from DSN programmatically everything works fine.
But this works only in Main PC because I can change DSN when trusted Connection = TRUE.. and I cannot enter Password to DSN programatically(searched).

I tried the following code that change Database and the CrystalReportViewer1 opens the report successfully without any error.

Dim myDBConnectionInfo As New ConnectionInfo()
        With myDBConnectionInfo
            .ServerName = My.Settings.SysSvName
            .DatabaseName = Pubdbname
            .UserID = “sa”
            .Password = “sa123”
        End With

        orptname.Load("JVR.rpt")
        orptname.SetParameterValue("@CompYear", PubYear1)
        orptname.SetParameterValue("@CompNo", mComp1)
        orptname.SetParameterValue("@fdatee", Format(CDate(FromDate.Text), "MM/dd/yyyy"))
        orptname.SetParameterValue("@tdatee", Format(CDate(ToDate.Text), "MM/dd/yyyy"))
        orptname.SetParameterValue("cfdate", Format(CDate(FromDate.Text), "MM/dd/yyyy"))
        orptname.SetParameterValue("ctdate", Format(CDate(ToDate.Text), "MM/dd/yyyy"))
        orptname.SetParameterValue("CName", PubCName)
        orptname.SummaryInfo.ReportTitle = "JVR REPORT" & "_" & Format(CDate(FromDate.Text), "ddMMyyyy") & "-" & Format(CDate(ToDate.Text), "ddMMyyyy")
        CReports.CRViewer.ReportSource = orptname

        CReports.Show() '----- CrystalreportViewer1

But no Data is been retrived.
After this, I changed the database name to Test1 from which I created(used database to design) the Report and it showed the data successfully.
And again tried to change db to Test2 Shows no result(but Crystal Report Opens without any error Connection error.)

Changing DSN default database shows records successfully.
Currently, successful running code I am using is below.

 With orptname
            .DataSourceConnections.Item(0).SetConnection("DSNNAME", Pubdbname, True)
            .SetParameterValue("ctdate", Format(CDate(ToDate.Text), "MM/dd/yyyy"))
            .SetParameterValue("CName", PubCName)
            .SummaryInfo.ReportTitle = "TEST REPORT" & "_" & Format(CDate(FromDate.Text), "ddMMyyyy") & "-" & Format(CDate(ToDate.Text), "ddMMyyyy")
            CReports.CRViewer.ReportSource = orptname
            CReports.Show()
        End With

So what shall I do now ?
In short, I want that my Crystal Report opens in LAN PC also and I can set database dynamically. (All database have exact same schema)

KalaNag KalaNag · Accepted Answer · 2018-06-18T00:09:19

This might not work for you (since this procedure doesn't uses DSN) but it might give you an idea of what to do. This is for CR 2010.

        crxRpt.Load("report.rpt")
        Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables = crxRpt.Database.Tables
        'Login for 1st DB in the report. 
        crxRpt.SetDatabaseLogon(dbUser1, dbPwd1, dbServer1, dbDB1) 
        'Login for 2nd DB in the report. IGNORE IF YOU ONLY HAVE 1 DB
        crxRpt.SetDatabaseLogon(dbUser2, dbPwd2, dbServer2, dbDB2)
        Dim crConnInfo1 As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo
        'Again, ignore if you only use 1 DB
        Dim crConnInfo2 As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo

        crConnInfo1.DatabaseName = dbDB1
        crConnInfo1.UserID = dbUser1
        crConnInfo1.Password = dbPwd1
        crConnInfo1.ServerName = dbServer1
        'again, ignore if you only use 1 DB
        crConnInfo2.DatabaseName = dbDB2
        crConnInfo2.UserID = dbUser2
        crConnInfo2.Password = dbPwd2
        crConnInfo2.ServerName = dbServer2
        For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
            Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
            If myTable.LogOnInfo.ConnectionInfo.DatabaseName = dbDB1 Then
                myTableLogonInfo.ConnectionInfo = crConnInfo1
                myTable.ApplyLogOnInfo(myTableLogonInfo)
            ElseIf myTable.LogOnInfo.ConnectionInfo.DatabaseName = dbDB2 Then
                myTableLogonInfo.ConnectionInfo = crConnInfo2
                myTable.ApplyLogOnInfo(myTableLogonInfo)
            End If
        Next

THEN you load the parameters (if any), THEN the selection formula (if any), and finally you put the report object in the Viewer, if you want.

If you have subreports, this has to be done to the subreports too. Basically, when you change the connection info from what was in design time, you have to inform every table in the report and its subreports.

Hope this helps.

Creating Crystal Report with Dynamic Database

1 Answers