Change Datasource in CrystalReport - VB.NET

remya1000

I'm using VB.NET 2003 windows application.

i'm trying to display Crystal Reports using CrystalReportViewer.

Code i'm using to display "ZTab.rpt" in CrystalReportViewer when Form_Load

Expand|Select|Wrap|Line Numbers

 
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

          CrystalReportViewer1.ReportSource = "ZTab.rpt"

          CrystalReportViewer1.Zoom(1)

End Sub

i have checkbox that contain list of crystal report. if i select one crystal report and hit button "Run", it displays that crystal report.
Code i'm using to display another (differenet) crystal report in CrystalReportViewer when Button (btnRun) clicked.

Expand|Select|Wrap|Line Numbers

 
Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRunNow.Click

        CrystalReportViewer1.ReportSource = combobox1.SelectedItem

        CrystalReportViewer1.Zoom(1)

End Sub

so when Form_load, it displays "ZTab.rpt" and when i select one crystal report in combobox and hit button "Run", it displays corresponding crystal report. that works fine.

how can i change the datasource of a report during run time using codes (programming).

for example: if we are using datasource "work.mdb" for "ZTab.rpt" when form_load. when i hit a button, how come i change the datasource to "Employee.mdb" for "ZTab.rpt" during run time using codes. so when form_load it will display ZTab.rpt with datasource "work.mdb" and when i hit a button, it will display ZTab.rpt with datasource "Employee.mdb".

If you have any idea how to do this, please help me. if you can provide an example then it will be great help for me.

Thanks in advance.

OuTCasT

You need to tell the crystal report that the datasource has changed.
You could change the database or even sql server if u want.

Expand|Select|Wrap|Line Numbers

  
If CheckBox1.Checked = True Then

Dim connection As IConnectionInfo

Dim oldServerName As String = ".\SQLEXPRESS"

Dim oldDatabaseName As String = "Old Database Name"

Dim newServerName As String = strOldServerName

Dim newDatabaseName As String = strCompanyName

Dim UserID As String = ""

Dim Password As String = ""
 
Monthlyreport.Load(Application.StartupPath + "\Reports\EarningsSummaryRpt.rpt")
 
'Change the server name and database in main report

For Each connection In Monthlyreport.DataSourceConnections

Monthlyreport.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)

Next

'Change the server name and database subreports

Dim subreport As ReportDocument

For Each subreport In Monthlyreport.Subreports

For Each connection In subreport.DataSourceConnections

If (String.Compare(connection.ServerName, oldServerName, True) = 0 _

And String.Compare(connection.DatabaseName, oldDatabaseName, True) = 0) Then

subreport.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)

End If

Next

Next

This is how you would change the database and connection of the report

remya1000

Thanks OutCast. That code worked. Thanks a lot.

Expand|Select|Wrap|Line Numbers

 
Dim myreport As New ReportDocument

    Dim strDSN As System.String

    Dim strDB As System.String

    Dim strUID As System.String

    Dim strPWD As System.String

   Private Sub frmMain_Load(ByVal sender........

           CrystalReportViewer1.ReportSource = "ZTab.rpt"

   End Sub

   Private Sub btnChangeDataBase_Click(ByVal sender ........        

        strNewPath = "C:\Projects\Employee.mdb"

        myreport.Load("ZTab.rpt")

        SetupReport(myreport)

   End Sub

   Private Function SetupReport(ByRef objCrystalReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument) As System.Boolean

        Dim crTableLogOnInfo As CrystalDecisions.Shared.TableLogOnInfo

        Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database

        Dim crTables As CrystalDecisions.CrystalReports.Engine.Tables

        Dim aTable As CrystalDecisions.CrystalReports.Engine.Table

        Dim bTable As CrystalDecisions.CrystalReports.Engine.Table

        Dim blnTest As System.Boolean

        Dim strLocation As System.String
 
        crDatabase = objCrystalReportDocument.Database

        crTables = crDatabase.Tables

        For Each aTable In crTables

            crTableLogOnInfo = aTable.LogOnInfo

            strDSN = crTableLogOnInfo.ConnectionInfo.ServerName

            strDB = crTableLogOnInfo.ConnectionInfo.DatabaseName

            strUID = crTableLogOnInfo.ConnectionInfo.UserID

            strPWD = crTableLogOnInfo.ConnectionInfo.Password

            OutputDebugLine("BEFORE")

            OutputDebugLine("TABLE NAME: " & aTable.Name)

            OutputDebugLine("TABLE LOC: " & aTable.Location)

            OutputDebugLine("SERVER: " & strDSN)

            OutputDebugLine("DB: " & strDB)

            OutputDebugLine("UID: " & strUID)

            OutputDebugLine("PWD: " & strPWD)

            OutputDebugLine("REPORT NAME: " & crTableLogOnInfo.ReportName)

            OutputDebugLine("Table Name: " & crTableLogOnInfo.TableName)

            aTable.ApplyLogOnInfo(crTableLogOnInfo)
 
            strLocation = strNewPath   'pass new mdb name
 
            OutputDebugLine("New Location: " & strLocation)

            Try

                aTable.Location = strLocation

            Catch ex As Exception

                OutputDebugLine("Set Location Error: " & ex.ToString)

            End Try

            OutputDebugLine("AFTER")

            OutputDebugLine("TABLE NAME: " & aTable.Name)

            OutputDebugLine("TABLE LOC: " & aTable.Location)

            OutputDebugLine("SERVER: " & strDSN)

            OutputDebugLine("DB: " & strDB)

            OutputDebugLine("UID: " & strUID)

            OutputDebugLine("PWD: " & strPWD)

            OutputDebugLine("REPORT NAME: " & crTableLogOnInfo.ReportName)

            OutputDebugLine("Table Name: " & crTableLogOnInfo.TableName)

            Try

                blnTest = aTable.TestConnectivity()

                OutputDebugLine("CONNECTED? " & blnTest.ToString())

            Catch ex As Exception

                OutputDebugLine("CONNECTED? NO")

                OutputDebugLine(ex.ToString)

            End Try

        Next aTable

        myWrite.Close()

        myWrite = Nothing

        myFile = Nothing

        CrystalReportViewer1.ReportSource = myreport

    End Function

OuTCasT

Glad to be of some assistance. I struggled with this for quite a while so i know how frustrating it can be.

Change Datasource in CrystalReport - VB.NET

re: Change Datasource in CrystalReport - VB.NET

re: Change Datasource in CrystalReport - VB.NET

re: Change Datasource in CrystalReport - VB.NET

Similar Visual Basic .NET bytes

/bytes/development

/bytes/it

/bytes/about