ADOQuery - not displaying columns after creation.

Wodzu

Hello,

I am using FR 5.5.11 and I often encounter a problem in which after creating new ADOQuery in designer I don't see its columns. I mean on the DataTree panel I see only ADOQuery object and no columns for it.
So I need to setup ADOQuery.DataField name manually in band.



ADOQuery4 and ADOQuery1 do not display its columns. They are valid queries, they have parameters.

Am I the only one having this problem?, is there any solution to this?

Thanks.

gpi

wrote:

they have parameters

Does parameters have default values?

Wodzu

gpi wrote: »

gpi wrote: »

they have parameters

Does parameters have default values?

Yes, please take a look at this example:

First, create these two tables in MS SQL.

CREATE TABLE [dbo].[T1](
	[ID] [int] NOT NULL,
	[Station] [varchar](50) NOT NULL,
	[StartDateTime] [datetime] NOT NULL
)

CREATE TABLE [dbo].[T2](
	[camera_id] [varchar](30) NOT NULL,
	[description] [varchar](80) NOT NULL
)

Tables do not need to have any data.

Now, create a new report, put ADODatabase1 object and ADOQuery1 object.

In ADOQuery1 put this code:

select 'BusLane' as ContraventionType, 'ISSUED' as ContraventionStatus ,ca.description,COUNT(c.station) from T2 CA
left outer join T1 C on c.Station = ca.description
where c.startdatetime between '08-01-2017' and '10-01-2017'
AND c.Station in (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990'))
group by ca.description
UNION
select 'BusLane'as ContraventionType, 'ISSUED'as ContraventionStatus ,ca.description,'0' from T2 Ca      
where ca.description not in
(
select c.Station from T2 CA
left outer join T1 C on c.Station = ca.description
where c.startdatetime between '08-01-2017' and '10-01-2017'
AND c.Station in (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990')))
AND description IN (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990'))

Close ADOQuery1, you will see that columns are visible:



Now, put ADOQuery2 and fill it with this SQL code:

select 'BusLane' as ContraventionType, 'ISSUED' as ContraventionStatus ,ca.description,COUNT(c.station) from T2 CA
left outer join T1 C on c.Station = ca.description
where c.startdatetime between :P1 and :P2    
AND c.Station in (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990'))
group by ca.description
UNION
select 'BusLane'as ContraventionType, 'ISSUED'as ContraventionStatus ,ca.description,'0' from T2 Ca      
where ca.description not in
(
select c.Station from T2 CA
left outer join T1 C on c.Station = ca.description
where c.startdatetime between :P3' and :P4    
AND c.Station in (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990')))
AND description IN (select description from T2 where description IN ('LW0975','LW0978','LW0984','LW0987','LW0988','LW0991','LW1000','LW1001','LW1002','LW1007','LW0990'))

As you can see, the code is exactly the same but with four parameters, fill values for these parameters:



Close the window, and you will see that columns for ADOQuery2 are not visible.

gpi

Try to use StrtoDate in the params expressions