How can you change the xml datasource location at runtime in VS.NET 2010

[Former Member]

Hi,

Could any one please tell me as to how you can change the xml datasource location at runtime in VB.NET 2010, where the location of the datasource keeps on changing as well.

We have built an application in vb.net 2010 in which when you save the data it saves it .xml files as well as .xsd files. I have built a crystal report based on the .xml source and .xsd schema.

The problem is when the user is done with their data input and is ready for submission when they click on Submit Work Order it will create a folder based on lets say today's date and time which would be 20110106_083200 and another one lets say 20110106_083500 the .xml file of the save data will be stored in both these folders and my .xml datasource is supposed to come from the latest folder which would be 20110106_083500 in this case. Whatever data is save I'm supposed to redirect it to the latest folder.

I have managed to grab the latest folder, but just can't seem to change the location of my datasource to be displayed in the crystal reports. I know there are similar scenarios out there, and I have gone through them, they are still missing what I need. On another note, there is no login required. My code is as follows:

Private Sub ViewSubData_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim dsXMLDataset As New DataSet()

'Dim dirSubData As DirectoryInfo = New DirectoryInfo(cNM_LocalDataFolder)

'dirSubData.GetDirectories.OrderByDescending()

Dim lastHigh As DateTime = New DateTime(1900, 1, 1)

Dim highDir As String

Dim subdir As String = ""

For Each subdir In Directory.GetDirectories(cNM_LocalDataFolder)

Dim dirSubData As DirectoryInfo = New DirectoryInfo(subdir)

Dim created As DateTime = dirSubData.LastWriteTime

If (created > lastHigh) Then

highDir = subdir

lastHigh = created

End If

Next

Dim oRptdoc As New ReportDocument

Try

'dsXMLDataset.ReadXml(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

oRptdoc.Load(cNM_LocalDataFolder & "\rptViewLastSubData.rpt")

crvViewSubData.ReportSource = oRptdoc

oRptdoc.SetDataSource(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

'oRptdoc.Database.Tables(0).SetDataSource(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

Catch ex As Exception

MessageBox.Show(ex.Message.ToString())

Finally

End Try

End Sub

Any help would be greatly appreciated.

Thanks,

Adnan.

[former_member11696 · Accepted solution]

Hi Adnan,

Use the RAS engine to set the data source. Sorry I only have this n C#:

private void SetToXML_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

string connString = "Provider=SQLOLEDB;Data Source=dwcb12003;Database=xtreme;User ID=sb;Password=pw";

string sqlString = "Select * From Orders";

//string sqlString = "SELECT 'Orders'.'Customer ID', 'Orders'.'Order Date' FROM 'xtreme'.'dbo'.'Orders' 'Orders'";

//string sqlString2 = "Select * From \"Orders Detail\"";

OleDbConnection oleConn = new OleDbConnection(connString);

OleDbDataAdapter oleAdapter = new OleDbDataAdapter(sqlString, oleConn);

//OleDbDataAdapter oleAdapter2 = new OleDbDataAdapter(sqlString2, oleConn);

DataTable dt1 = new DataTable("Orders");

//DataTable dt2 = new DataTable("\"Orders Detail\"");

oleAdapter.Fill(dt1);

//oleAdapter2.Fill(dt2);

System.Data.DataSet ds = new System.Data.DataSet();

ds.Tables.Add(dt1);

//ds.Tables.Add(dt2);

ds.WriteXml("c:
sc.xml", XmlWriteMode.WriteSchema);

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

PropertyBag boMainPropertyBag = new PropertyBag();

//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

//In the main property bag (boMainPropertyBag)

PropertyBag boInnerPropertyBag = new PropertyBag();

//Set the attributes for the boInnerPropertyBag

boInnerPropertyBag.Add("File Path ", @"C:\sc.xml");

boInnerPropertyBag.Add("Internal Connection ID", "{680eee31-a16e-4f48-8efa-8765193dccdd}");

//Set the attributes for the boMainPropertyBag

boMainPropertyBag.Add("Database DLL", "crdb_adoplus.dll");

boMainPropertyBag.Add("QE_DatabaseName", "");

boMainPropertyBag.Add("QE_DatabaseType", "");

//Add the QE_LogonProperties we set in the boInnerPropertyBag Object

boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

boMainPropertyBag.Add("QE_ServerDescription", "NewDataSet");

boMainPropertyBag.Add("QE_SQLDB", "False");

boMainPropertyBag.Add("SSO Enabled", "False");

//Create a new ConnectionInfo object

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Pass the database properties to a connection info object

boConnectionInfo.Attributes = boMainPropertyBag;

//Set the connection kind

boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

//*EDIT* Set the User Name and Password if required.

boConnectionInfo.UserName = "";

boConnectionInfo.Password = "";

//Pass the connection information to the table

boTable.ConnectionInfo = boConnectionInfo;

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables = rptClientDoc.DatabaseController.Database.Tables;

//For each table in the report:

// - Set the Table Name properties.

// - Set the table location in the report to use the new modified table

boTable.Name = "Orders";

boTable.QualifiedName = "Orders";

boTable.Alias = "Orders";

rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored Procedures.

rptClientDoc.VerifyDatabase();

MessageBox.Show("Data Source Set", "RAS", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

And be sure to add this to your app.config file:

When using ADO.NET with the Microsoft .NET Framework 4.0, the following setting must be added to the applicationu2019s configuration file:

<configuration>

<startup useLegacyV2RuntimeActivationPolicy="true">

<supportedRuntime version="v4.0"/>

</startup>

</configuration>

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

Thank you so much for a prompt reply. Do I still need the username and password for an .xml file. Do I still need to do a select * I mean, I just need to read the data in the .xml file with a changed location. Apart from the code do I need to correct any classpaths or paths. Is this the only way we can read the .xml file.

Thanks,

Adnan

[former_member11696 · Accepted solution]

Hi Adnan,

No and No.... It's up to you, the first part is just getting the data and saving it as an XML/XSD so you can ignore that part, you have already created and saved the data to the hard drive.

The important part is replacing the existing connection from what is in the RPT to your new location. Most of those properties are not required. I just included them for reference and not specific to your needs.

I assume you are using the Report Engine as it's the easiest way and the only way in previous version included with Visual Studio, with CR for VS 2010 you now have full access to inProc RAS and it's the reporting engine of choice and has the most functionality. It replaced our old RDC and crpe reporting engines.

CrystalReports.Engine is basic functionality and limited in it's ability, you can use both in your app, for simple functions use the engine for complex functions use the RAS and this gives you access to our various Controllers which are used to do most of the replacing.

Here's some great examples to get you more info:

Root Page

http://wiki.sdn.sap.com/wiki/display/BOBJ/BusinessObjectsHome

Enterprise Samples (including managed and unmanaged ras)

http://wiki.sdn.sap.com/wiki/display/BOBJ/BusinessObjectsSDKSampleApplications

Non-Enterprise Samples

http://wiki.sdn.sap.com/wiki/display/BOBJ/CrystalReportsSDKSampleApplications

Exporting Samples (RAS)

http://wiki.sdn.sap.com/wiki/display/BOBJ/NETRASSDK+Samples#NETRASSDKSamples-Exporting%2FPrinting

They are not version specific so you'll have to upgrade them to VS 2010 and CR 2010 but the functions are the same. Other than there is no access to BOE ( Managed RAS ) so ignore or comment out that part of the code.

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

Thank you again for your reply. Could you please tell me how can I remove the rassdk:// from my crystal reports path, I think that is something which is causing the report not to show up.

Thanks,

Adnan

[former_member11696 · Accepted solution]

Hello,

That option is for Managed and Unmanaged RAS, using BOE or CRSE Server only. There is no connectivity to Enterprise in CR for VS 2010.

To open your report just use:

rpt.Load(rptName.ToString());

Thank you

Don

[Former Member · Accepted solution]

The following is the code I am using:

Dim dsXML As New DataSet()

Dim lastHigh As DateTime = New DateTime(1900, 1, 1)

Dim highDir As String

Dim subdir As String = ""

For Each subdir In Directory.GetDirectories(cNM_LocalDataFolder)

Dim dirSubData As DirectoryInfo = New DirectoryInfo(subdir)

Dim created As DateTime = dirSubData.CreationTime

If (created > lastHigh) Then

highDir = subdir

lastHigh = created

End If

Next

Dim oRptdoc As New ReportDocument

Try

'oRptdoc.Load(cNM_LocalDataFolder & "\rptHoursWorked.rpt") 'rptHoursWorked 'rptTest1

'crvHoursWorked.ReportSource = oRptdoc

dsXML.ReadXml(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

'dsXML.ReadXml(cNM_LocalDataFolder & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

'DataGridView1.DataSource = ds.Tables(0).Rows.Item(0).ToString()

oRptdoc.Load(cNM_LocalDataFolder & "\rptHoursWorked.rpt".ToString())

oRptdoc.SetDataSource(dsXML.Tables(0))

'oRptdoc.SetDataSource(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

crvHoursWorked.ReportSource = oRptdoc

Catch ex As Exception

MessageBox.Show(ex.Message.ToString())

Finally

End Try

And when I run it, I get the following error in a pop-up dialogue:

Failed to load database information.

Error in File rptHoursWorked {C38F292E-641B-43DD-A6AC-CACB8131EF60}.rpt:

Failed to load database information.

I have no idea how to solve this problem, if you can I would appreciate it so much.

Thanks,

Adnan

[former_member11696 · Accepted solution]

First thing I would do is remove all your variables from the path and hard code them in as I did in my sample. If that works then you know where the problem lies.

If it works then try running in debug mode also and verify the paths are correct.

You are also not using anything from my sample other than the setlocation API. Try adding the table name parts.

[Former Member · Accepted solution]

Hi Don,

I have put in your code into the one I had, what am I doing wrong could you please tell me:

Dim dsXML As New DataSet()

Dim boTable As CrystalDecisions.ReportAppServer.DataDefModel.Table

Dim rcd As ISCDReportClientDocument

Dim rptClientDoc As ReportClientDocument

Dim lastHigh As DateTime = New DateTime(1900, 1, 1)

Dim highDir As String

Dim subdir As String = ""

For Each subdir In Directory.GetDirectories(cNM_LocalDataFolder)

Dim dirSubData As DirectoryInfo = New DirectoryInfo(subdir)

Dim created As DateTime = dirSubData.CreationTime

If (created > lastHigh) Then

highDir = subdir

lastHigh = created

End If

Next

rcd = rptClientDoc

Dim boTables As CrystalDecisions.ReportAppServer.DataDefModel.Tables

boTables = rptClientDoc.DatabaseController.Database.Tables

boTable.Name = "WorkOrders"

boTable.QualifiedName = "WorkOrders"

boTable.Alias = "WorkOrders"

rptClientDoc.DatabaseController.SetTableLocation(boTables(0), boTable)

rptClientDoc.VerifyDatabase()

MessageBox.Show("Data Source Set", "RAS", MessageBoxButtons.OK, MessageBoxIcon.Information)

Dim oRptdoc As New ReportDocument

Try

'oRptdoc.Load(cNM_LocalDataFolder & "\rptHoursWorked.rpt") 'rptHoursWorked 'rptTest1

'crvHoursWorked.ReportSource = oRptdoc

dsXML.ReadXml(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

'dsXML.ReadXml(cNM_LocalDataFolder & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

'DataGridView1.DataSource = ds.Tables(0).Rows.Item(0).ToString()

oRptdoc.Load(cNM_LocalDataFolder & "\rptHoursWorked.rpt")

oRptdoc.FileName = "rptHoursWorked.rpt"

oRptdoc.SetDataSource(dsXML.Tables(0))

'oRptdoc.SetDataSource(subdir & "\" & cNM_LocalDataFileWO & cNM_LocalDataFileExtension)

crvHoursWorked.ReportSource = oRptdoc

Catch ex As Exception

MessageBox.Show(ex.Message.ToString())

Finally

End Try

Actually for the variables, I have checked by putting a break point to check whether I am getting the right path or not and I am, but when I run your code I am getting the following message:

"Object reference not set to an instance of an object"

I can feel it I am very close.

Thanks,

Adnan

[Former Member · Accepted solution]

Hi Don,

I figured out that I had not initialized the object, but since that I have, it gives me a message saying:

"The document has not been opened"

Thanks,

Adnan

[former_member11696 · Accepted solution]

Hi Adnan,

I don't see where you are loading the report?

Do this and then it should work for either the engine or RAS:

rpt.Load(rptName.ToString());

rptClientDoc = rpt.ReportClientDocument;

Thanks again

Don

[Former Member · Accepted solution]

Hi Don,

I have tried multiple things, but nothing seems to work. I just wanted to let you know that I am using Crystal Reports XI, and Visual Studio.NET 2010. XML as the datasource and XSD as the schema file, with xml datasource's path that will keep on changing, all the other paths will remain as is. When I try to launch the report it says, "Failed to load database information."

I am using the report without any saved data. I just thought I'd let you know all the factors. Any more ideas that you might have, please do let me know.

Thanks,

Adnan

[former_member183750 · Accepted solution]

In one of the previous posts, Don mentioned that only CRVS2010 is supported in VS 2010. Perhaps that is the place to start; get [crvs2010|http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0.exe] on that box, update your references so they point to version 13.x.

Other CRVS2010 resources can be found here:

http://www.sdn.sap.com/irj/sdn/crystalreports-dotnet

- Ludek

[Former Member · Accepted solution]

Hi Don and Ludek,

I so much apologize for the late response, but I wanted to thank you both for amazing code and amazing advice. Ludek you are quite right about XML. Even though this is supposed to be the new standard, but there are so many problems that have been hidden or not discussed on other forums about the problems of working with XML as datasource with Visual Studio 2010 and Crystal Reports XI. I was able to solve the problem by creating a crystal report from within Visual Studio, although limited functionality, but still worked. I would like to however provide a link that helped me out.

The link is as follows:

http://dotnetslackers.com/articles/xml/ReportingXMLDataUsingCrystalReportsAndWindowsForms.aspx

Sorry the code is in C#, so for those who would like in VB, unfortunately you will have to translate the code.

Thanks,

Adnan

[Former Member · Accepted solution]

Hi Don,

I've implemented what you post here but am having an intermittent issue:  on some reports I create (not all), when I run this code and set the updated location information (i.e., the line rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);), one of the data fields is blank on the report, that is I can verify the data is in the XML source but does not appear on the report.  If I do NOT execute that line and use the same design-time connection properties, it works fine.

In my scenario, neither the name of the Table nor Fields change from design time to run time.  I've verified that the fields present in the data table before and after I execute the code you provided are identical.  I've verified that if I point from my production to test instances, the connection URL is getting updated properly.

Even when I execute your code and provide the identical information as during design time, this issue occurs.  It is not due to using the Crystal Reports Viewer (compared to the designer), since if I comment out that one line of code, the report loads properly using the design-time data source.

I'm completely stumped.  Can you think of a reason why using identical design and run time properties there would be this intermittent and random loss of data?  Am I missing some "bind"-type method I should be invoking after setting all the properties?

Thanks in advance,

Richard

[Former Member · Accepted solution]

As an update, to help prevent anyone else suffering like I did: If you are using an XML Web data source, and that data source changes in any way (new fields, changed data type, reordering of fields, etc.) when you "Verify Database", CR does not pick up the changes at first, even when a schema URL is specified and the schema has changed.  You need to close your project and reopen it, and even then it is sporadic; after some time it will refresh.  In my case, only a new field was added so in the design environment, everything previewed correctly, but when the report was moved into production and viewed using the Crystal Report viewer control, there would be some missing data (on fields that did not change) and w3wp.exe exceptions thrown on the server.  So always make sure your final report design is updated and based on the exact latest version of your XML schema!