I've researched many different ways of how to dynamically change the datasource connection here on StackOverflow. I used and verified almost every c# and vb.net example I could find, but somehow things won't work out as they should.
The idea of our project is to change the datasource connections from the old reports - that are using an xBase dll - to crdb_ado.dll with the VFPOLEDB-provider to connect to Visual Foxpro DBF files (each file representing one table).
I've downloaded the latest Crystal Developer Version for Visual Studio (2012) here: http://scn.sap.com/docs/DOC-35074 To be able to use those components directly in VS without referencing dll's in the Program Files - Business Object directory (as seen in other examples).
I tried verifying the contents via the VS debugger of an older "updated" report (see code below) together with a newly created report in Crystal Reports (latest version, which uses the correct path and settings to connect to the dbf's) so they match up.
However, I encountered these problems:
- The code is able to change all parameters and attributes, but saving it discards all changes (file is NOT read only)
- Changing table.location throws a com exception (translated: could not load database data, error in report)
- Same happens when "attaching" the report document object to the crystal report viewer.
- reportDocument.VerifyDatabase() clearly fails
- For as far as I can see, all setting are the seem in both files
The dev environment is windows 7/64bit/VS 2012 Pro. Crystal Reports (XI or 2011) is installed on this machine. All our reports were created with version 9 and 11 of Crystal Reports.
Below is one of the adapted code examples that did change all attributes or parameters (except table location). I've also used examples that used Propertybag objects, but those didn't work.
reportDocument1.Load("path to document");
// also tried adding these two lines as a test
reportDocument1.DataSourceConnections.Clear();
reportDocument1.DataSourceConnections[0].SetConnection(@"c:\testreports","",false);
//changing of table data connections
foreach (Table table in reportDocument1.Database.Tables)
{
ChangeTableLogonInfo(table);
}
// ---
private void ChangeTableLogonInfo(Table table)
{
// server = place containing *.DBF files
table.LogOnInfo.ConnectionInfo.ServerName = @"c:\testreports\";
// set to empty string (looking at generated report in CrRep)
table.LogOnInfo.ConnectionInfo.DatabaseName = "";
table.LogOnInfo.ConnectionInfo.UserID = "";
table.LogOnInfo.ConnectionInfo.Password = "";
// create logon properties
var connectionAttributes = new DbConnectionAttributes();
connectionAttributes.Collection.Set("Collating Sequence","Machine");
connectionAttributes.Collection.Set("Data Source", @"c:\testreports");
connectionAttributes.Collection.Set("Locale Identifier", 1033);
connectionAttributes.Collection.Set("OLE DB Services", -5);
connectionAttributes.Collection.Set("Provider", "VFPOLEDB"); //eg: SQLOLEDB
connectionAttributes.Collection.Set("Use DSN Default Properties",false);
// CLEAR and SET NEW attributes for the given table
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Clear();
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "Database DLL", Value = "crdb_ado.dll" });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "QE_DatabaseName", Value = "" });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "QE_DatabaseType", Value = "OLE DB (ADO)" });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "QE_LogonProperties", Value = connectionAttributes });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "QE_ServerDescription", Value = @"c:\testreports" });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "QE_SQLDB", Value = true });
table.LogOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2 { Name = "SSO Enabled", Value = false });
// gives a COM error
try
{
table.Location = "some-table-name";
}
catch (Exception e)
{
// handling
}
}
We did notice something "annoying" in Crystal Reports itself. I wonder if it has something to do with it:
When we wanted to change the xbase connection via CR itself, we cannot point it from an "xbase connection dbf" to an "vfpoledb connection dbf" (if you understand my point) without losing all fields in the report.
I tried installing 32 and 64 bit versions of the VS CR developer edition, but it doesn't seem to change anything.
Also, the next piece of code does work in Visual FoxPro (and I am able to read those converted files in my Crystal Reports Document viewer). It's just annoying it cannot be easily done in c# (it seems :) )
lotest = CREATEOBJECT("crystalruntime.application.9")
lorap = lotest.openreport("c:\factuur.rpt")
loData = loRap.Database
LOCAL lnI
lnI = 1
FOR EACH loTable IN lodata.tables
loconn = loTable.connectionproperties
loTable.dllname = "crdb_odbc.dll"
loConn.DeleteAll
IF lnI = 1
loCOnn.Add("Database", "Hoofding")
ELSE
loCOnn.Add("Database", "Detail")
ENDIF
loConn.Add("Database Type","ODBC")
loConn.Add("DSN","DBFACTw")
lnI = lnI + 1
ENDFOR
loRap.Saveas("c:\Factuur2.rpt",2048)
Any ideas or suggestions? Thanks
