Subject Re: ADO Query Parameters
From Kinsley <kinsleyr@gmail.com>
Date Wed, 16 Sep 2020 03:55:59 -0400
Newsgroups dbase.getting-started

100% correct I am connecting to a MySQL DB 5.7.

I am using DBase 11 (client not able to afford increase to 12).

I am just creating a form for testing now.

Kinsley

Akshat Kapoor Wrote:

> Good morning Kinsley,
 > >        connectionString = "Provider=MSDASQL.1;****" //taken out for security
 >
> We have no indication to what is the rdbms used. You should blank out
> servername userid password etc but rdbms name/driver name should not be
> an issue.
 > >   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)"        
 > >
> >        l1 = "select * from com forms  t WHERE c.tutref LIKE '%' AND processed LIKE 'F' AND monthlist LIKE '%' AND (commyear LIKE 2020 OR commyear LIKE 2019)"        
 > >
> > Then I get records back that I would expect. Any ideas what I am doing wrong please as I have looked at the language reference and don't see any issues with my code.
 >
> I would suggest using the inspector to set the default values to
> parameters. As having some initial values helps at times. The values may
> not be returning data. Eg you have data from 2015 onwards then set the
> year to 2000 It will not return the data but give the query something to
> work on.
 >
> Apart from this I would suggest setting up a prg on the following lines
> to check sql specfically.
 >
> adodb = new adodatabase()
 > //Set up connection string and make it active
 >
> adoquery = new adoquery()
 > adoquery.database = adoddb
 > adoquery.sql = "select * from com forms  t WHERE c.tutref LIKE '%' AND
> processed LIKE 'F' AND monthlist LIKE '%' AND (commyear LIKE 2020 OR
> commyear LIKE 2019)"        
 >
> adoquery.requestlive = false
 > adoquery.active = true
 >
> ?adoquery.rowset.count()
 > adoquery.active = false
 > adodb.actie =  false.
 >
> This will return some rows in the rowset.
 > Now insert 1 parameter at a time with required values and run the program.
 >
> There will be a stage where rowset returns 0 rows.
 >
> The last parameter inserted will be the culprit.
 > Give us that parameter and then we may be able to help further.
 >
> Debugging query as part of wfm is troublesome.
 >
> Once it is working you can paste it in wfm
 >
> Regards
 > Akshat