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