Relation across multiple databases with parameters

[jcc_colin]

Hello,

I have the following situation with version 2012.1 (release version):

Database A (SQL Server)
- Table A
- Id (uniqueidentifier)
- B_Id (uniqueidentifier)

Database B (SQL Server)
- Table B
- Id (uniqueidentifier)

There's a one to one relationship between the tables in the different databases. Table A.A (B_Id) references Table B.B (Id).

I've created data sources for both tables (two different connections). I've added a relation from table A (child) to table B (parent).

I just put the datarow on the report (no master detail, no sub report, just one row with both parent and child data as it is 1-1). When I preview, it show all data, it works perfectly. But, to match the relationships, all data from B is queried (select * from B), which can become a huge table, so I don't want that.

I've searched these forums to make it so that it only collects the records from B which are actually referenced. This seems to be possible, but somehow I can't get it to work!

I've tried adding parameter with expression to Table B datasource, resulting in query: SELECT * FROM B WHERE Id = @Id. With parameter expression: A.B_Id. I've tried to change the reconnect on each row checkbox, but it all does not seem to help, it either seems to keep querying all records or I get errors like the following (I think because of the parameter with expression?):

"Specified cast is not valid."

It seems to have issues casting the values to guid or so? What I DO see is that there's a difference in type (columns seem to be guid, parameter I have set to uniqueidentifier manually).

Either way: questions:

1. Why the error and how to solve?
2a. Should it be possible to get this 1-1 relationship (no master detail) working the way I want (no select*, using a select per row?)
2b. If so, which are the exact steps to get this done? Or could it be working already if the error is solved?
3. About that 'reconnect on each row': if this setting is necessary, do I need to set it on the child datasource or the parent datasource or both?
4. Could this situation also work on two different database types? Like Oracle and SqlServer for the databases A and B?

Thanks in advance.

[Alex K.]

Hello,

We couldn't reproduce this bug.
Can you please send us a sample report with test data which reproduces the issue for analysis.

Thank you.

[jcc_colin]

Sorry for the late reply. Here is the report that shows the error on Preview. I tried to upload the sql for both databases as well, but I can't upload .sql files and changing the extension to .sq_l didn't work either, uploading kept hanging. Anyway, if you need more information, I'll gladly give it.

Also, another bug I noticed when reproducing, is that when adding a data source to a sql connection, and selecting uniqueidentifier columns by clicking the checkboxes and then clicking OK, it recognizes the column type as 'long' instead of 'guid', weird...

[HighAley]

Hello.

You are trying to use the same name for parameter as the field name.
Please, try to change it.

Thank you.

[jcc_colin]

Hi,

Thanks for the response. I couldn't test it again before my vacation, but now I'm back and I've changed the name of the parameter to IdPar and the result is still the same. Also I noticed that the previous report I uploaded depended on a dll of ours, but this one does not.

So, still the same error.

[HighAley]

Hello.

Thanks for the response. I couldn't test it again before my vacation, but now I'm back and I've changed the name of the parameter to IdPar and the result is still the same. Also I noticed that the previous report I uploaded depended on a dll of ours, but this one does not.

So, still the same error.

Please, remove the IdPar parameter from the data source B and try to view your report.
If the error will occurs then send us sample data for your report.

Thank you.

[jcc_colin]

Well, that works, and the report works, but then from both tables a SELECT * FROM A / B will be done (no where clause) and seeing as that can be a huge amount of data, we don't want that. The point of the parameter was to limit the amount of rows collected. From table B, we'd rather see one query per row.

In the attachment, I've now added both databases' table / data declarations.

[Alex K.]

Hello,

Please try to put the additional databand component and set the ReconnectOnEachRow property for datasource B to true.
Please check the modified report in attachment.

Thank you.

[jcc_colin]

Thanks for your response.

I've added a databand with on Table B with Master component Data A, just like your example.

I've set DataSource B's property 'Reconnect on each row' to True.

I click preview, same error:

"Specified cast is not valid."

Stacktrace:

at Reports.Report.ADataSource.get_B_Id()
at Reports.Report.GetB_SqlCommand(Object sender, EventArgs e)
at Stimulsoft.Report.Dictionary.StiDataSource.InvokeConnecting()
at Stimulsoft.Report.Dictionary.StiDataSource.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDataSourcesCollection.Connect(StiDataCollection datas, Boolean loadData)
at Stimulsoft.Report.Dictionary.StiDictionary.Connect(Boolean loadData)
at Stimulsoft.Report.Engine.StiRenderProviderV2.ConnectToData(StiReport report)
at Stimulsoft.Report.Engine.StiRenderProviderV2.Render(StiReport report, StiRenderState state)
at Stimulsoft.Report.Engine.StiReportV2Builder.RenderSingleReport(StiReport masterReport, StiRenderState renderState)
at Stimulsoft.Report.StiReport.RenderReport(StiRenderState renderState)
at Stimulsoft.Report.StiReport.Render(StiRenderState renderState, StiGuiMode guiMode)
at Stimulsoft.Report.Design.Controls.StiDesignerPreviewControl.btRefresh_Click(Object sender, EventArgs e)


Uploaded my new version of report.

[Alex K.]

Hello,

Please check the last prerelease build.

Thank you.