Crystal Reports Datasource Question

Bob_Stan
Bob_Stan used Ask the Experts™
on
I have some old Crystal 10 reports I am trying to display in VS2010.  They work fine, until I need to run them on a SQL Server/Database different than the one on which they are created.  I have seen all the code and am using something like shown below.  It appears that the code is not actually making any changes although NO exception is raised.  For example if i trace through the code on my development machine aTable.location will be "Customer" and when I run it, it will change to "MyDatabase.dbo.Customer".  But if the original location is a different Server and or database, the aTable location remains "Customer" and when the viewer attempts to display the report, I get a viewer error stating the connection, server, database, user, and password are empty.  I am using integrated security so I shouldn't need user or password.  Any help appreciated.
crReport.Load(_reportName) 

Dim crDatabase As Database = crReport.Database
Dim crTables As Tables = crDatabase.Tables
Dim crConnectionInfo As ConnectionInfo = ChangeConnection()
Dim crTableLogOnInfo As New TableLogOnInfo
Dim strLocation As String

For Each aTable In crTables
   crTableLogOnInfo = aTable.LogOnInfo
   crTableLogOnInfo.ConnectionInfo.ServerName = crConnectionInfo.ServerName
   crTableLogOnInfo.ConnectionInfo.DatabaseName = crConnectionInfo.DatabaseName
   aTable.ApplyLogOnInfo(crTableLogOnInfo)
   strLocation = String.Format("{0}.dbo.{1}", crConnectionInfo.DatabaseName, aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1))
   Try
     aTable.Location = strLocation
   Catch ex As Exception
     'exception code here
   End Try
Next


Private Shared Function ChangeConnection() As ConnectionInfo
Dim objconnectioninfo As ConnectionInfo = New ConnectionInfo
      
objconnectioninfo.IntegratedSecurity = True
objconnectioninfo.ServerName = "MyServer"
objconnectioninfo.DatabaseName = "MyDatabase"
Return objconnectioninfo

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
How are you connecting to the database in the report?

How is the report object declared?

mlmcc

Author

Commented:
The reports were constructed using the database designer and  an ADO connection to the required SQL data tables in Crystal 10. The report is declared as a new report document, created by loading the reportpath in a CrystalReportViewer
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What assemblies are included in the application?

DO you have the CR10 standalone version?

mlmcc
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes I have the standalone version.  I am including the following Crystal Decisions assemblies: CrystalReports.Engine,ReportSource,Shared,WindowsForms, and Web (don't think I need this but VS2010 added it)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Which version of the assemblies?
VS2010 or CR10?

mlmcc

Author

Commented:
I am using the VS2010.
Your problem is that when the report was originally created, you are using different connection strings to connect and design the report. So now, the one trying to open the report is of different connection strings.

Give you an example, when I create a new report, I used ADRIAN\SQLEXPRESS to connect, when you attempt to read the report from another PC where it's not "ADRIAN", you will have a problem and start asking you all the variables like connection, userid, pwd etc.

So if all your SQLServers use the orignal Instance as "SQLEXPRESS", then my advise if you open the report and RECONNECT the report using  .\SQLEXPRESS ,  then you won't face this problem at all.
If you are using Integrated WIndows Authentication, then why you have all the logon information in your Crystal Report?
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
Yes, of course it's simple to use dataset but lots of time, it's just ineffecient to do that. Usually I use Stored Procedure to collect data into Crystal Report.

Author

Commented:
Unfortunately, they are not reports I created and there are over 200 of them.  Rewriting them is out of the question.
Not rewriting them, if the current connection string is different from the original, if you attempt to open it now, Crystal will show you the link, relinking them to the current database will solve the problem.
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
Have you opened the reports in VS2010 and saved them as VS2010 Crystal reports?

The only version of Crystal currently supported in VS2010 is the CR for VS2010.

mlmcc

Author

Commented:
adrian -  The reports are on clients' computers, not my own server, so relinking the reports is not an option

mimcc - I will try your suggestion, which I had not thought of

Author

Commented:
mimcc - making some progress.  After I opened and saved the report in visual studio 2010, the connections to the tables are definitely getting set correctly.  I verified this by mis-setting the server name and I get an error "failed to open a connection".  So far so good.  However, when the report opens it displays an error box with "server=;database=;user=;password=;,,".  And just as before, any report originally created on my local server continues to work just fine.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Did you try to change the connection when inside VS2010?

mlmcc

Author

Commented:
No, but i have no reason to believe that wouldn't work.  The problem is i have numerous clients using these reports on their servers.  while i can certainly update the reports to versions compatible with VS2010, I cannot change the connection for the reports for each client.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
My thought i the VS2010 viewer may be using a different connection method or driver so you have to change the report to use it.

mlmcc

Author

Commented:
You might be right - I will check it out and see

Author

Commented:
I am not sure what happened to my final post, but here it is again:
mimcc - you were exactly right.  I got the same error when I tried to preview the report after changing the datasource in the designer.  That let me discover the report was calling a custom Crystal user library - which wasn't compatible.  Once I removed it, everything worked.

Your first suggestion to save the report as a vs2010 solved the real problem and your second suggestion let me discover another roadblock.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial