runtime assignment of database connection gives error

[Chetanalm]

I have installed crystal reports 2011 for .net 4.0. I create crystal report using visual studio. I use command to get data. the command gets data from 2 database. the 2nd database has same user and password as the one provide during creating command. only name and content of 2nd database is different. name of the 2nd database is passed as parameter to the command. 

Everything works fine. but when i change the database connection settings from backend the reports give error [

Error in File temp_7eda6175-1a02-42ab-b517-bfef5dc33ba6 10524_3948_{C8BE61CE-D420-4BBB-8BFD-67B238688671}.rpt:

Failed to retrieve data from the database.

Details: [Database Vendor Code: 207 ]

]

but when i manually verify database, and run the project the report works again. 

my problem is i need to run this report on different client system. each time in each machine i cant verify database manually. 

I tried crystalreportobject.verifydatabase(). but that results in an exception 

[log on failed]

login credentials are correct. i have double checked 

please help

Code used to set the database connection at runtime

part1

With crConnectionInfo
.AllowCustomConnection = True
.IntegratedSecurity = False
.ServerName ="Server"
.DatabaseName = "Database"
.UserID = "User"
.Password ="password"
End With
CrTables = objcrRpt.Database.Tables
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crConnectionInfo.Type = crtableLogoninfo.ConnectionInfo.Type
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next

part 2

Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections
Dim crReportObjects As ReportObjects
Dim crSubreportObject As SubreportObject
Dim crSubreportDocument As ReportDocument
Dim crDatabase As Database
Dim tableIndex As Integer
Dim CrTables1 As Tables
crSections = objdayendRpt.ReportDefinition.Sections
For Each crSection As CrystalDecisions.CrystalReports.Engine.Section In crSections
crReportObjects = crSection.ReportObjects
For Each crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
crSubreportObject = CType(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
Dim mysubname As String = crSubreportObject.SubreportName.ToString()
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
crDatabase = crSubreportDocument.Database
CrTables1 = crDatabase.Tables
tableIndex = 0
For Each crTable1 As CrystalDecisions.CrystalReports.Engine.Table In CrTables1
Dim TableName As String = crTable1.Name.ToString()
tableIndex += 1
crtableLogoninfo = crTable1.LogOnInfo
crConnectionInfo.Type = crtableLogoninfo.ConnectionInfo.Type
crtableLogoninfo.ConnectionInfo = crConnectionInfo
crTable1.ApplyLogOnInfo(crtableLogoninfo)
Next
crSubreportDocument.SetDatabaseLogon("User","Password", "Server", "Database")
End If

Next
Next

[DellSC]

Would you please post the code you're using to set the database connection at runtime?

[DonWilliams]

Hello,

Go here:

https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html

On that page I added 2 test applications, one for setting log on a Parameters and the other for printing:

how-to-parameters-in-crystal-reports-for-visual-studio-net

Printing Crystal Reports in .NET

The Parameter test app has options to set the log on info for the main report and each subreport, you must do this because subreports is there for using a second data source.

Your issue could be in your use of the Command object, if your multiple data sources is linked in the Command this is not the way you do it, you must use a subreport per each DB connection and link the subreports to the main report.

Also, download SP 35, it's the latest versions, CR for VS is only supported with the latest version, see my blog on how to upgrade also:

https://community.sap.com/t5/technology-blogs-by-sap/upgrading-a-visual-studio-2008-gt-2022-net-proj...

Also, CR follow DB security, if the user you are logged in as doesn't have permissions to access the data for the report it will give you a log on error., not sure what error 207 is for your DB.

See if that helpos

Don

[Chetanalm]

part1

With crConnectionInfo
.AllowCustomConnection = True
.IntegratedSecurity = False
.ServerName ="Server"
.DatabaseName = "Database"
.UserID = "User"
.Password ="password"
End With
CrTables = objcrRpt.Database.Tables
For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crConnectionInfo.Type = crtableLogoninfo.ConnectionInfo.Type
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next

part 2

Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections
Dim crReportObjects As ReportObjects
Dim crSubreportObject As SubreportObject
Dim crSubreportDocument As ReportDocument
Dim crDatabase As Database
Dim tableIndex As Integer
Dim CrTables1 As Tables
crSections = objdayendRpt.ReportDefinition.Sections
For Each crSection As CrystalDecisions.CrystalReports.Engine.Section In crSections
crReportObjects = crSection.ReportObjects
For Each crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
crSubreportObject = CType(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
Dim mysubname As String = crSubreportObject.SubreportName.ToString()
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
crDatabase = crSubreportDocument.Database
CrTables1 = crDatabase.Tables
tableIndex = 0
For Each crTable1 As CrystalDecisions.CrystalReports.Engine.Table In CrTables1
Dim TableName As String = crTable1.Name.ToString()
tableIndex += 1
crtableLogoninfo = crTable1.LogOnInfo
crConnectionInfo.Type = crtableLogoninfo.ConnectionInfo.Type
crtableLogoninfo.ConnectionInfo = crConnectionInfo
crTable1.ApplyLogOnInfo(crtableLogoninfo)
Next
crSubreportDocument.SetDatabaseLogon("User","Password", "Server", "Database")
End If

Next
Next

[DonWilliams]

AS I said, you must set the parameter beforre setting the log on info

[Chetanalm]

I have tried assigning parameters before setting the log on info.. it did not work. crystal report seems to save logon info when we manually verify database. it dont seem to change that connection when we dynamically assign the new connection . i confirmed this in profiler.

[DonWilliams]

CR for VS is funny in that if any of the connection info is wrong it will fail to connect and it does not make any changes. Run in debug mode and mouse over each connection property and verify the values are correct.

[Chetanalm]

i have checked the connection values in debug mode also. there are no errors in connection. the same connection values and same query if run in vb code and assign the result to dataset and use that dataset in crystal report, its working. but the command mode is giveing error for same connection and same query

[Chetanalm]

same instance different database works without manually verifying database.