Skip to Content
avatar image
Former Member

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

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    Aug 12, 2008 at 02:40 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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;		

  • avatar image
    Former Member
    Aug 12, 2008 at 07:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 15, 2008 at 04:39 PM

    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);

    }

    }

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 15, 2008 at 05:08 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 01, 2008 at 11:22 AM

    How change datasource location and type to the subreports?

    Add comment
    10|10000 characters needed characters exceeded