Avatar of Bob_Stan
Bob_Stan
 asked on

Crystal Reports Datasource Question

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

Crystal ReportsProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
Bob_Stan

8/22/2022 - Mon
Mike McCracken

How are you connecting to the database in the report?

How is the report object declared?

mlmcc
Bob_Stan

ASKER
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 McCracken

What assemblies are included in the application?

DO you have the CR10 standalone version?

mlmcc
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Bob_Stan

ASKER
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 McCracken

Which version of the assemblies?
VS2010 or CR10?

mlmcc
Bob_Stan

ASKER
I am using the VS2010.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
adriankohws

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.
adriankohws

If you are using Integrated WIndows Authentication, then why you have all the logon information in your Crystal Report?
Éric Moreau

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
adriankohws

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.
Bob_Stan

ASKER
Unfortunately, they are not reports I created and there are over 200 of them.  Rewriting them is out of the question.
adriankohws

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bob_Stan

ASKER
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
Bob_Stan

ASKER
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 McCracken

Did you try to change the connection when inside VS2010?

mlmcc
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Bob_Stan

ASKER
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 McCracken

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
Bob_Stan

ASKER
You might be right - I will check it out and see
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bob_Stan

ASKER
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