Subject Re: invalid subscript reference From Gaetano <gaetanodd@hotmail.com> Date Wed, 23 Sep 2020 06:51:27 +1000 Newsgroups dbase.getting-started Yessss Maurizio, that was it! Strange because the += on the SQL property worked perfectly as-is before I introduced the params from my CGI feed to replace temporary fixed values. When there are params in a query, the code must be looking for the SQL statement to evaluate "consistency" between the params and the SQL statement. There has to be some glitch with the way it looks for the params in the statement. It doesn't just look for the line including [<queryObject>.SQL = ] because it does find the first params on line 2 for query 1. However, it is as if it stops looking for params on the line where it finds the first params, then evaluates subsequent params against that same string which only includes the first params. That would explain why the first params doesn't generate the error while the second params errors out for query 2 because it is on the 3rd SQL statement line while for the first query, all params are on the same line, so that one works fine? Anyway, now I know to always build the complete string before assigning it to the SQL statement... On 22/09/2020 21:36, Maurizio S. wrote: > avoid qADO.sql += > I had similar problem in the past and maybe concatenated string is > > > IO = 'SELECT count(distinct cast(eTimestamp as date)) as Days ' > IO+= 'from energydata where siteNum =:nCGIsiteNum and eTimestamp between > :dStart and :dEnd' > > > qADO.sql  := IO > > > IO  = "SELECT count(distinct cast(eTimestamp as date)) as Days " > IO += "from energydata where siteNum = :nCGIsiteNum2 and eTimestamp > between " > IO += ":dStart2 and :dEnd2 order by Days" > qADO2.sql := IO > > > > > tks > Maurizio > > Il 22/09/20 10:49, Gaetano ha scritto: >> >> HI All, >> >> I am getting an invalid subscript error that I can't troubleshoot... >> >> Here is the code, the first ADO query works fine, the second one gives >> me an invalid subscript error on the second param, while it  is >> exactly the same as the previous one, I put in a msgbox to verify the >> values and they are correct. >> >> Is there something obvious I am overlooking? >> >> -------first query >> // ADO query to count days in data set >>    qADO = new adoquery() >>    qADO.database = d >>    qADO.sql  = 'SELECT count(distinct cast(eTimestamp as date)) as Days ' >>      qADO.sql += 'from energydata where siteNum =:nCGIsiteNum and >> eTimestamp between :dStart and :dEnd' >>      qADO.params["nCGIsiteNum"].value = oCGI["CGIsiteNum"] >>      qADO.params["dStart"].value = oCGI["CGIdStart"] >>      qADO.params["dEnd"].value = oCGI["CGIdEnd"] >>      qADO.active = true >> >>      _app.cQTYDAYS := qADO.rowset.fields["days"].value >>      msgbox("app_cQTYDAYS= "+_app.cQTYDAYS) >>      nTotDays=qADO.rowset.fields["days"].value >>        qADO.active=false >>      release qADO >> >>      //msgbox("CGIs= >> "+oCGI["CGIsiteNum"]+"/"+oCGI["CGIdStart"]+"/"+oCGI["CGIdEnd"]) >> >> ----------second query >> initenv() >> //inactivate query, change SQL and count missing days in data set >> //qADO.active = false >>      qADO2 = new adoquery() >>      qADO2.database = d >>      qADO2.sql  = "SELECT count(distinct cast(eTimestamp as date)) as >> Days " >>       qADO2.sql += "from energydata where siteNum = :nCGIsiteNum2 and >> eTimestamp between " >> qADO2.sql += ":dStart2 and :dEnd2 order by Days" >> qADO2.params["nCGIsiteNum2"] = oCGI["CGIsiteNum"] >> qADO2.params["dStart2"] = oCGI["CGIdStart"] >> qADO2.params["dEnd2"] = oCGI["CGIdEnd"] >> qADO2.active = true