468,140 Members | 1,436 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

Change Crystal Report DATA TABLE not Database

OuTCasT
374 256MB
I know how to change the database and sqlserver for a crystal report

Expand|Select|Wrap|Line Numbers
  1. Dim report As New ReportDocument
  2.         Dim connection As IConnectionInfo
  3.         Dim oldServerName As String = ".\SQLEXPRESS"
  4.         Dim oldDatabaseName As String = oldDatabaseName
  5.         Dim newServerName As String = newServerName
  6.         Dim newDatabaseName As String = newDatabaseName
  7.         Dim UserID As String = ""
  8.         Dim Password As String = ""
  9.  
  10.  
  11.         report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
  12.         CrystalReportViewer1.ReportSource = report
  13.  
  14.         'Change the server name and database in main report
  15.         For Each connection In report.DataSourceConnections
  16.             report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  17.         Next
  18.         'Change the server name and database subreports
  19.         Dim subreport As ReportDocument
  20.         For Each subreport In report.Subreports
  21.             For Each connection In subreport.DataSourceConnections
  22.                 If (String.Compare(connection.ServerName, oldServerName, True) = 0 _
  23.                    And String.Compare(connection.DatabaseName, oldDatabaseName, True) = 0) Then
  24.                     subreport.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  25.                 End If
  26.             Next
  27.         Next
  28.         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.
Mar 6 '09 #1
7 9735
QVeen72
1,445 Expert 1GB
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. Dim crxTable As CRAXDRT.DatabaseTable
  2. For Each crxTable In Report.Database.Tables
  3.     crxTable.Location = Replace (crxTable.Location, "VS2010", "VS2009")
  4. Next
  5.  
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
Mar 9 '09 #2
OuTCasT
374 256MB
Thanks i will give it a go later today.
The tablename will be stored in a variable so it can be changed whenever.
Mar 9 '09 #3
OuTCasT
374 256MB
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....?
Mar 10 '09 #4
OuTCasT
374 256MB
Sorry.....I had to add the Crystal Reports ActiveX Designer Run time Library before i could reference.

Thanks,
Mar 10 '09 #5
OuTCasT
374 256MB
@QVeen72
Hi Veena

This is my code

Expand|Select|Wrap|Line Numbers
  1. Dim report As New ReportDocument
  2.         Dim connection As IConnectionInfo
  3.         Dim oldServerName As String = "DONOVANPC\SQLEXPRESS"
  4.         Dim oldDatabaseName As String = "PayDay10"
  5.         Dim newServerName As String = strOldServerName
  6.         Dim newDatabaseName As String = strCompanyName
  7.         Dim UserID As String = ""
  8.         Dim Password As String = ""
  9.  
  10.         Dim crxTable As CRAXDDRT.DatabaseTable
  11.  
  12.         report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
  13.         CrystalReportViewer1.ReportSource = report
  14.  
  15.         For Each crxTable In report.Database.Tables
  16.             crxTable.Location = Replace(crxTable.Location, "IRPV2010", "IRPV2009")
  17.         Next
  18.         'Change the server name and database in main report
  19.         For Each connection In report.DataSourceConnections
  20.             report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  21.         Next
  22.         'Change the server name and database subreports
  23.         Dim subreport As ReportDocument
  24.         For Each subreport In report.Subreports
  25.             For Each connection In subreport.DataSourceConnections
  26.                 If (String.Compare(connection.ServerName, oldServerName, True) = 0 _
  27.                    And String.Compare(connection.DatabaseName, oldDatabaseName, True) = 0) Then
  28.                     subreport.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  29.                 End If
  30.             Next
  31.         Next
  32.         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 ?
Mar 10 '09 #6
OuTCasT
374 256MB
Expand|Select|Wrap|Line Numbers
  1. Dim report As New ReportDocument
  2.         Dim connection As IConnectionInfo
  3.         Dim oldServerName As String = "DONOVANPC\SQLEXPRESS"
  4.         Dim oldDatabaseName As String = "PayDay10"
  5.         Dim newServerName As String = strOldServerName
  6.         Dim newDatabaseName As String = strCompanyName
  7.         Dim UserID As String = ""
  8.         Dim Password As String = ""
  9.  
  10.         'Dim crxTable As CRAXDDRT.DatabaseTable
  11.  
  12.         Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table
  13.  
  14.         report.Load(Application.StartupPath + "\Reports\ValidationReport.rpt")
  15.         CrystalReportViewer1.ReportSource = report
  16.  
  17.         For Each crxTable In report.Database.Tables
  18.             crxTable.Location = Replace(crxTable.Location, "IRPV2010", "IRPV2010")
  19.         Next
  20.         'Change the server name and database in main report 
  21.         For Each connection In report.DataSourceConnections
  22.             report.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  23.         Next
  24.         'Change the server name and database subreports 
  25.         Dim subreport As ReportDocument
  26.         For Each subreport In report.Subreports
  27.             For Each connection In subreport.DataSourceConnections
  28.                 If (String.Compare(connection.ServerName, oldServerName, True) = 0 _
  29.                    And String.Compare(connection.DatabaseName, oldDatabaseName, True) = 0) Then
  30.                     subreport.DataSourceConnections(oldServerName, oldDatabaseName).SetConnection(newServerName, newDatabaseName, UserID, Password)
  31.                 End If
  32.             Next
  33.         Next
  34.         CrystalReportViewer1.RefreshReport()
Used this line instead

Expand|Select|Wrap|Line Numbers
  1. 'Dim crxTable As CRAXDDRT.DatabaseTable
  2.  
  3. Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table
Now its changing the database tables.

Thanks Veena
Mar 10 '09 #7
OuTCasT
374 256MB
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

Expand|Select|Wrap|Line Numbers
  1.  Dim crxTable As CrystalDecisions.CrystalReports.Engine.Table
  2.         For Each crxTable In report.Database.Tables
  3.             crxTable.Location = Replace(crxTable.Location, "IRP2010", table)
  4.         Next
  5.         Dim crxTable2 As CrystalDecisions.CrystalReports.Engine.Table
  6.         For Each crxTable2 In subreport.Database.Tables
  7.             crxTable2.Location = Replace(crxTable2.Location, "IRPD2010", table2)
  8.         Next
am i being stupid cause this doesnt work. lol
Mar 12 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Grant Stanley | last post: by
2 posts views Thread by Tim Burda | last post: by
2 posts views Thread by airkart | last post: by
3 posts views Thread by Gheaci Maschl | last post: by
3 posts views Thread by Khurram | last post: by
27 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.