Changing datasource location and type at runtime in a Windows Forms app

[Former Member]

This works on the development machine. I can change both the location and type of the datasource between SQL Server and Access without error. The deployed Windows forms applications generates a "Log On Failed." error when deployed. I am using Crystal Reports 2008 with SP0 and FP1 applied. The application is developed in Visual Studio .NET 2005. The install set is built with InstallShield 2009 from within VS2005 and The Crystal Reports 12 runtime merge module, MDAC, JET, SQL Server Native Client, OLEDB, etc. are included. The application appears to install without any problems. I have also tried installing Crystal Reports 2008 on the target machine and still get the error.

Has anyone else seen this problem or anything similar? Does anyone have any insight on how to correct the problem?

[former_member183750 · Accepted solution]

Since you have Crystal Reports 2008 designer on that computer, are you able to run the report in the designer, or is it specific to the app?

Ludek

[Former Member · Accepted solution]

It is specific to the app. I have narrowed the problem down to the report attempting to connect to the datasource using the information saved in the rpt before using the updated connection info. It appears that if the attempt using the saved info fails the report never runs. If the attmept using the saved info is successful then the connection is updated and the report is run using the new connection info. Here is the code.

ReportDocument rpt = new ReportDocument();

rpt.Load(_InstallDir + @"Reports\" + strReportFilename + @".rpt");

SetReportDataSourceLocation(rpt);

SetReportParameters(rpt);

crViewer.ReportSource = rpt;

[former_member208657 · Accepted solution]

Please provide your code you are using to change databases at runtime.

[Former Member · Accepted solution]

Here is the code.

private void SetReportDataSourceLocation(ReportDocument ReportDoc)

{

for (int table = 0; table < ReportDoc.Database.Tables.Count; table++)

{

string strTableName = ReportDoc.Database.Tables[table].Name.ToUpper();

if (m_db.IsSqlServer())

{

...

...

...

}

else

{

ReportDoc.Database.Tables[table].LogOnInfo.ConnectionInfo.ServerName = m_db.Database;

ReportDoc.Database.Tables[table].LogOnInfo.ConnectionInfo.DatabaseName = string.Empty;

ReportDoc.Database.Tables[table].LogOnInfo.ConnectionInfo.UserID = "Admin";

ReportDoc.Database.Tables[table].LogOnInfo.ConnectionInfo.Password = string.Empty;

}

}

try

{

ReportDoc.VerifyDatabase();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

[former_member208657 · Accepted solution]

I'm not surprised that doesn't work. We have a lot of connection properties in the back end that are different between MS Access and SQL server. For example - design two reports with the first connected to MS Access and the second connected to SQL Server both using OLE DB. Now go into the Database menu and select Set Datasource. Expand the Properties section and compare the two. You'll notice some significant differences.

You'll likely need to use the RAS InProc SDK to get this done.

For example - this is some code I have for changing from an ODBC connection to an OLE DB connection using the InProc RAS SDK. It won't solve your solution for you but the theory is there for you to build on.

			CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
			CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;
			CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;			
			CrystalDecisions.ReportAppServer.Controllers.DatabaseController boDatabaseController;			
			CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo;
			CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boAttributesPropertyBag;
			CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boLogonPropertyBag;


			// Load the report and get the ReportClientDocument			
			boReportDocument.Load(Application.StartupPath + "\\..\\..\\Report ODBC.rpt");			
			boReportClientDocument = boReportDocument.ReportClientDocument;
			
			boDatabaseController = boReportClientDocument.DatabaseController;
			boDatabase = boDatabaseController.Database;
			boTables = boDatabase.Tables; 

			
			foreach(CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableOld in boTables)
			{
				CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableNew = boTableOld.Clone(true);
            
				boConnectionInfo = boTableNew.ConnectionInfo;
			
				boAttributesPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag) boConnectionInfo.Attributes;

				// Change the attributes and QE_LogonProperties to an ODBC connection
				boAttributesPropertyBag["Database DLL"] = "crdb_ado.dll";
				boAttributesPropertyBag["QE_DatabaseName"] = "Xtreme";
				boAttributesPropertyBag["QE_DatabaseType"] = "OLE DB (ADO)";
				boAttributesPropertyBag["QE_ServerDescription"] = "servername\\sqlexpress";
				boAttributesPropertyBag["QE_SQLDB"] = "True";
				boAttributesPropertyBag["SSO Enabled"] = "False";

				// Get the QE_LogonProperties and remove all the properties and add them back for the ODBC type
				boLogonPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag) boAttributesPropertyBag["QE_LogonProperties"];
				boLogonPropertyBag.RemoveAll();
				boLogonPropertyBag.Add("Auto Translate", "-1");
				boLogonPropertyBag.Add("Connect Timeout", "15"); 
				boLogonPropertyBag.Add("Data Source", "servername\\sqlexpress");
				boLogonPropertyBag.Add("General Timeout", "0");
				boLogonPropertyBag.Add("Initial Catalog", "Xtreme");
				boLogonPropertyBag.Add("Integrated Security", "False");
				boLogonPropertyBag.Add("Locale Identifier", "1033");
				boLogonPropertyBag.Add("OLE DB Services", "-5");
				boLogonPropertyBag.Add("Provider", "SQLOLEDB");
				boLogonPropertyBag.Add("Tag with columb collation when possible", "0");
				boLogonPropertyBag.Add("Use DSN Default Properties", "False");
				boLogonPropertyBag.Add("Use Encryption for Data", "0");                
        	
				// Set the QualifiedName 
				boTableNew.QualifiedName = "Xtreme.dbo." + boTableNew.Name;

				// The username and password must be set before using SetTableLocation. Secure databases
				// will throw an error if the logon info is incorrect.
				boTableNew.ConnectionInfo.UserName = "MyUserName";
				boTableNew.ConnectionInfo.Password = "MyPassword";
												
				boDatabaseController.SetTableLocation(boTableOld, boTableNew);
			}
            
			crystalReportViewer1.ReportSource = boReportDocument;		

[Former Member]

How change datasource location and type to the subreports?

[former_member183750]

Fernando, essentially, you have to loop through the report, looking for subreports and pass the logon info to the subreport(s). If you have just one subreport, code like this will work too:

Dim crSubreportObject As SubreportObject

Dim crSubreportDocument As ReportDocument

crSubreportDocument = crSubreportObject.OpenSubreport("XXX")

crSubreportDocument.SetDatabaseLogon("", "") 'If you are using the RAS code provided by

'David, insert that here

To loop through the report use code along these lines:

Dim crSections As Sections

Dim crSection As Section

Dim crReportObjects As ReportObjects

Dim crReportObject As ReportObject

Dim crSubreportObject As SubreportObject

Dim crReportDocument As CrystalReport1

Dim crSubreportDocument As ReportDocument

Dim crDatabase As Database

Dim crTables As Tables

Dim crTable As Table

Dim crTableLogOnInfo As TableLogOnInfo

Dim crConnectioninfo As ConnectionInfo

'set the crSections object to the current report's sections

crSections = crReportDocument.ReportDefinition.Sections

'loop through all the sections to find all the report objects

For Each crSection In crSections

crReportObjects = crSection.ReportObjects

'loop through all the report objects to find all the subreports

For Each crReportObject In crReportObjects

If crReportObject.Kind = ReportObjectKind.SubreportObject Then

'you will need to typecast the reportobject to a subreport

'object once you find it

crSubreportObject = CType(crReportObject, SubreportObject)

'open the subreport object

crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'set the database and tables objects to work with the subreport

crDatabase = crSubreportDocument.Database

crTables = crDatabase.Tables

'loop through all the tables in the subreport and

'set up the connection info and apply it to the tables

For Each crTable In crTables

With crConnectioninfo

.ServerName = ServerName

.DatabaseName = DatabaseName

.UserID = UserID

.Password = Password

End With

crTableLogOnInfo = crTable.LogOnInfo

crTableLogOnInfo.ConnectionInfo = crConnectioninfo

crTable.ApplyLogOnInfo(crTableLogOnInfo)

Next

End If

Next

Again, if you are using RAS, use the RAS database logon code.

Ludek

[Former Member]

How can David's code be used with an embeded sub-report? the ReportClientDocument property of the sub-reports ReportDocument is not accessible.

[former_member208657]

You'll need to use the SubreportController in the RAS SDK to manipulate the Tables in the subreport. Here's a little demo.

DatabaseController crDatabaseController;
SubreportController crSubreportController;
PropertyBag crAttributes, crLogonInfo;
Table crTable, crTableNew;
string strTableName;

	
	
crSubreportController = m_crReportClientDocument.SubreportController;			

// use this code to pass the same info to all the subreports.
foreach (string strSubreport in crSubreportController.QuerySubreportNames())
	foreach(Table crTableOld in crSubreportController.GetSubreportDatabase(strSubreport).Tables)
	{
		// clone the old table then set the userID and password
		crTableNew = (Table) crTableOld.Clone(true);						
		crTableNew.ConnectionInfo.UserName = userID;
		crTableNew.ConnectionInfo.Password = password;

		// get the attributes from the property bag and check to see that 
		// the Kind is a CRQE database.
		crAttributes = crTableNew.ConnectionInfo.Attributes;
		if (crTableNew.ConnectionInfo.Kind == CrConnectionInfoKindEnum.crConnectionInfoKindCRQE)
		{
			// get the QE logon properties and then set the 
			// serverName and databaseName.
			crLogonInfo = (PropertyBag) crAttributes["QE_LogonProperties"];
			crLogonInfo["Data Source"] = serverName;
			crLogonInfo["Initial Catalog"] = databaseName;
		}

		// apply the changes using the subreport controller.
		crSubreportController.SetTableLocation(strSubreport, crTableOld, crTableNew);                    

		crTableNew = null;
	}            
}

Edited by: David Hilton on Oct 1, 2008 1:02 PM

Edited by: David Hilton on Oct 1, 2008 1:09 PM

[Former Member]

Hi,

What should i do if i don't have a initial catalog or connectioninfo for a datasource like created in Project Data, .Net Objects(where in we pass the Namespace.ClassName as datasource).

As i am fetching the information from an xml file and through Project Data, .NET Objects i am getting the property values in the crystal report datasource, but the report gives an error as Database Logon Failed, what is the initial catalog that i have to pass when i don't have it or what connection info should i pass.

The datasource is the namespace.classname for the crystal report and all others will be property values.

Else can you show me a demo of code where i can access the crystal report controls in .NET as we access sections or subreports.

So that i can directly assign the values to the crystal report controls from .NET only, instead of sending the datasource to the crystal report and then assigning it right.

Can you please provide a sample application with this scenario.

Let me know if i am not clear enough to explain my scenario.

Thanks,

Madhav

[former_member183750]

As this thread is marked as "Answered", pls copy and paste this to a new thread.

Additionally specify version of CR and version of .NET

- Ludek

[Former Member]

Please disregard last post. Everything is working as intended now. Thank you for your help.

[former_member208657]

Awesome. Glad to hear it worked for you.

[Former Member]

Here is the updated code. The problem still exists.

It works as intended on the dev machine.

but when deployed, a log on prompt is displayed with the information saved in the rpt. if this connection can made then, and only then, will the report actually run with the updated connection information. If the connection can not be made then the report does not run.

CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;

CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

CrystalDecisions.ReportAppServer.Controllers.DatabaseController boDatabaseController;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo;

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boAttributesPropertyBag;

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boLogonPropertyBag;

// Get the ReportClientDocument

boReportClientDocument = ReportDoc.ReportClientDocument;

boDatabaseController = boReportClientDocument.DatabaseController;

boDatabase = boDatabaseController.Database;

boTables = boDatabase.Tables;

foreach (CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableOld in boTables)

{

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable boTableNew = boTableOld.Clone(true);

boConnectionInfo = boTableNew.ConnectionInfo;

boAttributesPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag)boConnectionInfo.Attributes;

if (m_db.IsSqlServer())

{

// Change the attributes and QE_LogonProperties to an ODBC connection

boAttributesPropertyBag["Database DLL"] = "crdb_ado.dll";

boAttributesPropertyBag["QE_DatabaseName"] = m_db.Database;

boAttributesPropertyBag["QE_DatabaseType"] = "OLE DB (ADO)";

boAttributesPropertyBag["QE_ServerDescription"] = m_db.ServerName;

boAttributesPropertyBag["QE_SQLDB"] = "1";

boAttributesPropertyBag["SSO Enabled"] = "0";

// Get the QE_LogonProperties and remove all the properties and add them back for the ODBC type

boLogonPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag)boAttributesPropertyBag["QE_LogonProperties"];

boLogonPropertyBag.RemoveAll();

boLogonPropertyBag.Add("Auto Translate", "-1");

boLogonPropertyBag.Add("Connect Timeout", "15");

boLogonPropertyBag.Add("Data Source", m_db.ServerName);

boLogonPropertyBag.Add("General Timeout", "0");

boLogonPropertyBag.Add("Initial Catalog", m_db.Database);

boLogonPropertyBag.Add("Integrated Security", db.UseIntegratedSecurity.ToString());

boLogonPropertyBag.Add("Locale Identifier", "1033");

boLogonPropertyBag.Add("OLE DB Services", "-5");

boLogonPropertyBag.Add("Provider", "SQLOLEDB");

boLogonPropertyBag.Add("Tag with columb collation when possible", "0");

boLogonPropertyBag.Add("Use DSN Default Properties", "0");

boLogonPropertyBag.Add("Use Encryption for Data", "0");

// Set the QualifiedName

boTableNew.QualifiedName = boTableNew.Name;

// The username and password must be set before using SetTableLocation. Secure databases

// will throw an error if the logon info is incorrect.

boTableNew.ConnectionInfo.UserName = m_db.UseIntegratedSecurity ? string.Empty : m_db.UserName;

boTableNew.ConnectionInfo.Password = m_db.UseIntegratedSecurity ? string.Empty : m_db.Password;

boDatabaseController.SetTableLocation(boTableOld, boTableNew);

}

else

{

// Change the attributes and QE_LogonProperties to an ODBC connection

boAttributesPropertyBag["Database DLL"] = "crdb_ado.dll";

boAttributesPropertyBag["QE_DatabaseName"] = string.Empty;

boAttributesPropertyBag["QE_DatabaseType"] = "OLE DB (ADO)";

boAttributesPropertyBag["QE_ServerDescription"] = m_db.Database;

boAttributesPropertyBag["QE_SQLDB"] = "1";

boAttributesPropertyBag["SSO Enabled"] = "0";

// Get the QE_LogonProperties and remove all the properties and add them back for the ODBC type

boLogonPropertyBag = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag)boAttributesPropertyBag["QE_LogonProperties"];

boLogonPropertyBag.RemoveAll();

boLogonPropertyBag.Add("Data Source", m_db.Database);

boLogonPropertyBag.Add("Database Type", "Access");

boLogonPropertyBag.Add("Locale Identifier", "1033");

boLogonPropertyBag.Add("OLE DB Services", "-6");

boLogonPropertyBag.Add("Provider", "Microsoft.Jet.OLEDB.4.0");

boLogonPropertyBag.Add("Use DSN Default Properties", "0");

boLogonPropertyBag.Add("Jet System Database", string.Empty);

boLogonPropertyBag.Add("Jet Database Password", string.Empty);

// Set the QualifiedName

boTableNew.QualifiedName = boTableNew.Name;

// The username and password must be set before using SetTableLocation. Secure databases

// will throw an error if the logon info is incorrect.

boTableNew.ConnectionInfo.UserName = "Admin";

boTableNew.ConnectionInfo.Password = string.Empty;

boDatabaseController.SetTableLocation(boTableOld, boTableNew);

}

}

[Former Member]

Hello Steve,

for those deployment issues we have a tool [MODULES|https://smpdl.sap-ag.de/~sapidp/012002523100006252802008E/modules.zip] which you can use to compare your dev machine with the target machine.

A full docu is provided. This tool will quickly show you all differences in terms of dll versions etc to understand where to start investigating.

Hope this helps

Falk