Change Datasource in CrystalReport - VB.NET

[remya1000]

I'm using VB.NET 2003 windows application.

i'm trying to display Crystal Reports using CrystalReportVi ewer.

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

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 CrystalReportVi ewer when Button (btnRun) clicked.

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.m db" 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.m db".

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.

 
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.

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.

[nivabeath]

Check th following link that create a subreport..

Crystal Report subreport

It will help you