ADOQuery - not displaying columns after creation.

Comments

• gpi

  November 2017 edited 11:28AM

  wrote:

  they have parameters

  Does parameters have default values?
• Wodzu

  November 2017 edited November 2017

  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

  November 2017 edited 11:28AM

  Try to use StrtoDate in the params expressions