Change Crystal Report DATA TABLE not Database

[OuTCasT]

I know how to change the database and sqlserver for a crystal report

Dim report As New ReportDocument
        Dim connection As IConnectionInfo
        Dim oldServerName As String = ".\SQLEXPRESS"
        Dim oldDatabaseName As String = oldDatabaseName
        Dim newServerName As String = newServerName
        Dim newDatabaseName As String = newDatabaseName
        Dim UserID As String = ""
        Dim Password As String = ""
 
 
        report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
        CrystalReportViewer1.ReportSource = report
 
        'Change the server name and database in main report
        For Each connection In report.DataSourceConnections
            report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
        Next
        'Change the server name and database subreports
        Dim subreport As ReportDocument
        For Each subreport In report.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
        CrystalReportViewer1.RefreshReport()

Now in the application the report must be able to change database tables according to the year that we are in. for instance i have a table VS2009 and VS2010 and i want the data from the VS2009 table instead of the VS2010 data. how do i change the table for the report. Both tables are identical.

[QVeen72]

Hi,

If you have designed the Report on database :VS2010, You have to Change the Location at runtime.. Use this code:

Dim crxTable As CRAXDRT.DatabaseTable
For Each crxTable In Report.Database.Tables
    crxTable.Location = Replace (crxTable.Location, "VS2010", "VS2009")
Next
 

Say, Location will be showing : VS2010.dbo.MyTableName
It need to be changed to VS2009.dbo.MyTableName
It has to be done for all tables in the report, as well as all Tables in sub report


Regards
Veena

[OuTCasT]

Thanks i will give it a go later today.
The tablename will be stored in a variable so it can be changed whenever.

[OuTCasT]

Hi. sorry man. i dont knw where to integrate your piece of code into my project.
Dim crxD as CRAXDRT.databasetable

what does CRAXDRT represent....?

[OuTCasT]

Sorry.....I had to add the Crystal Reports ActiveX Designer Run time Library before i could reference.

Thanks,

[OuTCasT]

@QVeen72
Hi Veena

This is my code

Dim report As New ReportDocument
        Dim connection As IConnectionInfo
        Dim oldServerName As String = "DONOVANPC\SQLEXPRESS"
        Dim oldDatabaseName As String = "PayDay10"
        Dim newServerName As String = strOldServerName
        Dim newDatabaseName As String = strCompanyName
        Dim UserID As String = ""
        Dim Password As String = ""
 
        Dim crxTable As CRAXDDRT.DatabaseTable
 
        report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
        CrystalReportViewer1.ReportSource = report
 
        For Each crxTable In report.Database.Tables
            crxTable.Location = Replace(crxTable.Location, "IRPV2010", "IRPV2009")
        Next
        'Change the server name and database in main report
        For Each connection In report.DataSourceConnections
            report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
        Next
        'Change the server name and database subreports
        Dim subreport As ReportDocument
        For Each subreport In report.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
        CrystalReportViewer1.RefreshReport()

When i run the the app it gives me this error

Unable to cast object of type 'CrystalDecisions.CrystalReports.Engine.Table' to type 'CRAXDDRT.DatabaseTable'.

Can u help ?

[OuTCasT]

Dim report As New ReportDocument
        Dim connection As IConnectionInfo
        Dim oldServerName As String = "DONOVANPC\SQLEXPRESS"
        Dim oldDatabaseName As String = "PayDay10"
        Dim newServerName As String = strOldServerName
        Dim newDatabaseName As String = strCompanyName
        Dim UserID As String = ""
        Dim Password As String = ""
 
        'Dim crxTable As CRAXDDRT.DatabaseTable
 
        Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table
 
        report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
        CrystalReportViewer1.ReportSource = report
 
        For Each crxTable In report.Database.Tables
            crxTable.Location = Replace(crxTable.Location, "IRPV2010", "IRPV2010")
        Next
        'Change the server name and database in main report 
        For Each connection In report.DataSourceConnections
            report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
        Next
        'Change the server name and database subreports 
        Dim subreport As ReportDocument
        For Each subreport In report.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
        CrystalReportViewer1.RefreshReport()

Used this line instead

'Dim crxTable As CRAXDDRT.DatabaseTable
 
Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table

Now its changing the database tables.

Thanks Veena

[OuTCasT]

Everthing is working 100%
Now ive come across that i need to change the tables for my subreports in the report....

i have 2 tables for the report Main report : IRP2010 and the subreport IRPD2010

what i did was

 Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table
        For Each crxTable In report.Database.Tables
            crxTable.Location = Replace(crxTable.Location, "IRP2010", table)
        Next
        Dim crxTable2 As CrystalDecisions.CrystalReports.Engine.Table
        For Each crxTable2 In subreport.Database.Tables
            crxTable2.Location = Replace(crxTable2.Location, "IRPD2010", table2)
        Next

am i being stupid cause this doesnt work. lol