Problems with Joins

rbuser

I use the WebTier - ISAPI and the SQLServer.

I have trouble with tables which have a field with the same name and the
same type. For example, when the field is a timestamp type I get a Data
Error.

At first I have check the TppDataDictionary settings and I believe it's ok.
As next I have delete all joins in my Join-table.
And then I have delete all fields in the Field-table that have the same name
and type.

But I get still the same error.
The only way i find is to delete all fields in all Tables which have the
same name and type. But I can't use this solution.

Any ideas?

Beach

nardmoseley

This is not a known issue.

1. First step would be to get these reports working properly in a stand
alone reporting application. I do not think this issue is related to the
server or web tier at all.

2. Most database tables have foreign key relationships that by definition
have the same field name and datatype in the master and detail tables. For
example, in DBDemos the Customer and Orders tables both have a CustNo field
that is used to define the relationship between them.

3. As a test, try to creating some reports without using the
TppDataDictionary. Then later add it back as a second step.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nard,


The reports work well in the application with TppDataDictionary.
Without the TppDataDictionary I get the same Error (DataError).

field

I have not problems with the key fields in the master and detail tables. I
get the problems with the (normal) fields in the master and detail tables
that have the same name and datatype.


Without the TppDataDictionary I get the same Error (DataError).

I believe I have found the problem.
I have in my reports a OnCreate event. For example:
Report.CreateAutoSearchCriteria('qMain', 'Gj', soEqual,
Report.Parameters['GJ'], True);

When I delete the OnCreate event the reports works well. But I need the
AutoSearchCriteria.
I hope you have a idea for my problem Nard.

Beach

nardmoseley

Okay, now I understand the issue.

The Report.CreateAutoSearchCriteria method is limited because is accepts the
DataPipeline and FieldName as parameter, but the query may contain the same
field name from 2 different tables.

An alternative approach is to extract the TdaSQL object from the SQL
DataView and use the TdaSQL.AddCriteriaField method.

You can download an example from

http://www.digital-metaphors.com/tips/AddSearchCriteria.zip

In the example you will find the line of code shown below and add the second
line so that the search criteria is AutoSearch.

lCriteria := lSQL.AddCriteriaField(lTable, lField.FieldName, dacoLike,
lField.SearchExpression);
lCriteria.Field.AutoSearch := True;





--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nard,
thank you for your help. But I believe I have not describe my problem
correct. I try it again.

- I use tables to save and to get the reports from my database. This five
tables are item, folder, join, table and field.
- The fields-table contain only the fields which i need for the reports.
- Join-table is empty.

Since i appended parameters i get the following error in the ISAPI-Solution
(translated german error message):
DATA error: The implicit conversion of data type varchar in data type
timestamp in the VisionicSQL.dbo.tblZiele table upsize_ts column is
implemented illegally using the CONVERT function to execute the query.

I use tblZiele in my report, but the field tblZiele.upsize_ts is not used
and not included in the query-object for the report. So i do not understand
why the report tries to access and link the upsize_ts field from tblZiele.
I think that the report scans the involved tables and makes something like a
autojoin between fields which have the same name and type. But autojoin is
not activated.

Through further tests i have resolved this:
1. ISAPI Solution: if i call report.createautosearchcriteria in the
report.OnCreate event i get the described error.
2. Stand-alone: If i remove the datadictionary i get also the described
error.

I hope you have a idea.

Regards
Beach

rbuser

3. I could reproduce this for any report if i it uses two tables with two
same named fields and if the fields have a type which the reportbuilder
could not link, like fieldtype Timestamp.
These two fields are normal datafields and are not used to link two tables
through joins.

nardmoseley

Please create a simple, minimal Delphi example using DBDemos and e-mail to
support@digital-metaphors.com and we can take at a look at it. If the
DBDemos data is not sufficient, create some simple Paradox tables using
Database Desktop.

ReportBuilder does not try to "autojoin" for you, unless you have autojoin
turned on. There must be something else going on....



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com