Master / detail ADO query problems
Hello,
I'm just starting to work with FastReport 3.x in an application using SQL Server tables and ADO. The problem that I am having is when I want to create a master / detail report.
The report in question draws data from two tables: TICKETS (the master) and TRUCKS (the detail). The SQL statements are as follows:
SELECT * FROM TICKETS
SELECT * FROM TRUCKS WHERE NUMBER = TRUCK
The detail query's Master property is set to TicketQuery.
I do not receive any error messages but it will not display the list of fields for the detail data set (though the master fields are available).
I believe that this is due to the fact that the TICKETS table also has an unrelated NUMBER field. I say this because if I change the parameter from TRUCK to NUMBER then the truck table fields will come up. The truck number is a string field called NUMBER in the TRUCKS table and TRUCK in the TICKETS table. The NUMBER field of the TICKETS table is an integer ticket number.
In addition, I tried adding a second detail data set for the hauling companies in the HAULERS table:
SELECT * FROM HAULERS WHERE HAULER = HAULER
The Master property is set to the TicketQuery data set. The hauler detail data set will then make the hauler table's fields available to add to the report.
However, when I run the report the detail data set does not change - it is always showing the data from the first record / match. In other words, it always shows the name of the hauler associated with the first ticket irregardless of the the hauler was for each individual ticket.
Any advice?
Thank you,
Brian Wheatley
I'm just starting to work with FastReport 3.x in an application using SQL Server tables and ADO. The problem that I am having is when I want to create a master / detail report.
The report in question draws data from two tables: TICKETS (the master) and TRUCKS (the detail). The SQL statements are as follows:
SELECT * FROM TICKETS
SELECT * FROM TRUCKS WHERE NUMBER = TRUCK
The detail query's Master property is set to TicketQuery.
I do not receive any error messages but it will not display the list of fields for the detail data set (though the master fields are available).
I believe that this is due to the fact that the TICKETS table also has an unrelated NUMBER field. I say this because if I change the parameter from TRUCK to NUMBER then the truck table fields will come up. The truck number is a string field called NUMBER in the TRUCKS table and TRUCK in the TICKETS table. The NUMBER field of the TICKETS table is an integer ticket number.
In addition, I tried adding a second detail data set for the hauling companies in the HAULERS table:
SELECT * FROM HAULERS WHERE HAULER = HAULER
The Master property is set to the TicketQuery data set. The hauler detail data set will then make the hauler table's fields available to add to the report.
However, when I run the report the detail data set does not change - it is always showing the data from the first record / match. In other words, it always shows the name of the hauler associated with the first ticket irregardless of the the hauler was for each individual ticket.
Any advice?
Thank you,
Brian Wheatley
Comments
assuming you are working with the frxado components internaly
set query2 master to query1, paramaterize the query, in the parameter editor
set the type in the value box select the function button at the right select the desired function dblclick in the lower window you will see
IntToStr()
place the cursor between the ()
now select the desired datafield of the first query and dblclk
the resultit should look some thing like this depending on the name you gave your first query
IntToStr(<Internal query1."Truck">)
make sure your where clause is constructed correctly =:paramname
another method is to use one joined query across all tables concerned with an orderby clause to retreive all data in one result set and use nested group headers and footers above and below the master data band, and set each groupheader's condition property to the appropriate field of the orderby clause faking a master/detail/subdetail report.
Yes, I am using the frxADO components (TfrxADOComponents).
The truck number is not an integer. Truck numbers are typically of the form AB1234.
The SQL syntax for the truck query is (as mentioned previously):
SELECT * FROM TRUCKS WHERE NUMBER = :TRUCK
As you can see, the parameter name is correct - a colon plus the field name
The fields for the truck table will not show up in the FastReport designer, but I do not receive an error message.
At first I thought it had to do with the fact that the master data set has an unrelated field also called NUMBER. However, I've found that it doesn't matter if the Master property of the truck query is set. It also doesn't help so set the parameter type to String an put in a value.
The only way I've been able to get the field list to show up is to remove the WHERE clause or to speficy an absolute value:
SELECT * FROM TRUCKS
SELECT * FROM TRUCKS WHERE NUMBER = 'AB1234'
Either of these two statements will work display the field list.
The problem is in the parameters for the ADO components.
As a test, I tried another query:
SELECT * FROM CUSTOMERS WHERE CUSTOMER = :ACCT
The data fields are not listed for this query either! Again, this holds true even if I go back again and add the parameter type and a value.
my apologies i haven't worked with ado mich so i tried a test using the MSoffice northwind .mdb sample db using the jet 4 provider and frxado components.
and i have to agree with you. i now see your problems. tried 2 querys on customers and orders tables
IMHO there is a lot of bugs that need fixing and i have passed them on to TZ
the querybuilder produces bad syntax on anything other than a * selection
if you parmeterize a query the fieldlist disappears, as you have seen.
However i did find a workaround for now.
adoquery1
SELECT *
FROM customers
this gave me all the fields in the data list as expected
adoquery2
SELECT *
FROM orders
this gave me all the fields in the data list as expected.
i then set up the master and detail bands with the appropriate datafields.
Next i went back to query 2 set its master property to adoquery1 and added a where clause to the sql prop
WHERE CustomerID =
in the param editor of adoquery2 i set the type as required and pointed the value to the customerid field of adoquery1
This should be all that is required, but the detail fails to select as it should just gives all records for every master.
solved that by adding a line of code to the oap event of the master band
adoquery2.close;
now all works ok.
this might be a shorter route and no additional code required
using the same sample db as earlier
adoquery1 customers table ok adoquery2 on orders table
initially set the where clause of adoquery2to
Where CustomerID = CustomerID
this let me see all the datafields in the datapane
after placing all the fields in the band
modified sql of query2(parameterized it) to
"Where CustomerID =:CustomerID" and set the master prop to query1
in params editor pointed parameter to the field of query1
BTW the datafields will disappear from the datapane until the report has been previewed once.