Subject Re: ADO Query Parameters
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 16 Sep 2020 09:59:11 +0200
Newsgroups dbase.getting-started

On 2020/09/15 22:40, Kinsley wrote:
> Hi all,
 >
> I need to move a dbase application over to a new server - the servers were 2002 on the old one and 2016 on the new one.
 >
> I've tried to recompile the application and it appears to work up to the point that it gets the data. The request fails to populate a Grid as I am setting parameters in the Form_Open function
You don't say what RDBM you are using nor do we know what version of
dBASE you are using.

Before getting to your data selection problem, you should not be using
live queries with a RDBM.  Your queries should be read-only i.e with the
requestLive property set false.  You should be using INSERT, UPDATE and
DELETE commands executed by the ADOdatabase object's executeSQL() method
to manipulate data in the tables.

There are cases where using the LIKE predicate is the only way to get
what you need but generally speaking you should avoid this.

Both of these issues have been discussed in the sql-servers newsgroup.

> What I have is as follows:
 >
........
>   this.ADOQUERY1 = new ADOQUERY(this)
 >     with (this.ADOQUERY1)
 >                 left = 91.0
 >                 top = 21.0
 >        database = form.adodatabase1
 >        connected = true
 >        l1 = "select * from com forms  t WHERE c.tutref LIKE :TT AND processed LIKE :PP AND monthlist LIKE :MM AND (commyear LIKE :YY OR commyear LIKE :YY2)"        
 >        sql = l1
The ADO documentation shows that an ADOquery can either use params['TT']
= '%' (which is the same as required by a standard query object) or
parameters['TT'].value = '%'

In practice the parameters['TT'].value = '%' option is required for
ADOqueries.

The parameters array needs to be constructed before the query is made
active so it needs to be done in the query's constructor code.  I'm
surprised that an error is not being flagged.

You've only shown part of the SQL select statement.  Normally the form
designer should stream out the following but, perhaps because your SQL
select statement is so long that it has been moved into a separate
memory variable, this hasn't been done.  You will, therefore, need to do
this yourself.

       with (parameters["TT"])
          type = "Boolean"
       endwith
       with (parameters["PP"])
          type = "Boolean"
       endwith
       with (parameters["MM"])
          type = "Boolean"
       endwith
       with (parameters["YY"])
          type = "Boolean"
       endwith
       with (parameters["YY2"])
          type = "Boolean"
       endwith

>        active = true
 >     endwith

I have no idea why the designer streams out "Boolean" but it doesn't
seem to upset things



>
>     with (this.ADOQUERY1.rowset)
 >        onNavigate = class::ROWSET_ONNAVIGATE
 >     endwith
 >
>     function form_onOpen()
 >                 nthisyear = year(date())
 >                 nlastyear = year(date())-1
 >                 with (form.adoquery1)
 >                         params["TT"].value = "%"
 >                         params["PP"].value = "F"
 >                         params["MM"].value = "%"
 >                         params["YY"].value = nthisyear
 >                         params["YY2"].value = nlastyear
This needs to be
                    parameter["TT].value = "%"
                    ....
                    parameter["YY2"].value = nlastyear

>                 endwith
 >                 form.adoquery1.requery()
 >
> This does not work.
Mervyn.