Parameter's being ignored

Results 1 to 5 of 5

Thread: Parameter's being ignored

  1. #1
    Ian Jerrett Guest

    Default Parameter's being ignored

    I have a problem where the parameters (period,period1 and origac) are all being ignored in this statement. If I hardcode the details the query returns the expected results.<BR>The parameters are being successfully passed to the page as they are used in previous queries that have been created using Database Region Wizard in Frontpage 98.<BR><BR>Any help would be greatly appreciated either to this page or by email ijerrett@principality.co.uk<BR><BR>&#060;% <BR>&#039 ASP program that displays a database in table form<BR>myDSN="DSN=arrstat"<BR>mySQL="SELECT ORIGACTYPEBYADVANCE.month, Sum(ORIGACTYPEBYADVANCE.[tot2+]) as [tot2+],([tot2+]/[totacs]) AS [2+perc], Sum(ORIGACTYPEBYADVANCE.normalacs) as [normalacs], Sum(ORIGACTYPEBYADVANCE.totacs) as [totacs] FROM ORIGACTYPEBYADVANCE WHERE (((ORIGACTYPEBYADVANCE.period) &#062;= %%period%%) and ((ORIGACTYPEBYADVANCE.period) &#060;= %%period1%%) AND ((ORIGACTYPEBYADVANCE.origactype) = %%origac%%)) GROUP BY ORIGACTYPEBYADVANCE.month ORDER BY ORIGACTYPEBYADVANCE.month DESC;"<BR>showblank=0<BR>shownull=""<BR><BR>set conntemp=server.createobject("adodb.connection")<B R>conntemp.open myDSN<BR>set rstemp=conntemp.execute(mySQL)<BR>If rstemp.eof then<BR>response.write "No records matched"<BR>response.write mySQL & "So cannot make table..."<BR>conntemp.close<BR>set conntemp=nothing<BR>response.end<BR>end if<BR>%&#062; &#060;/p&#062;<BR><BR>&#060;table border="0"&#062;<BR> &#060;tr&#062;<BR>&#060;% &#039Put Headings On The Table of Field Names<BR>for each whatever in rstemp.fields%&#062;<BR> &#060;td bgcolor="#cccccc"&#062;&#060;b&#062;&#060;font size="5"&#062;&#060;%=whatever.name%&#062;&#060;/font&#062;&#060;/b&#062;&#060;/td&#062;<BR>&#060;% next %&#062;<BR> &#060;/tr&#062;<BR>&#060;% &#039 Now lets grab all the records<BR>DO UNTIL rstemp.eof %&#062;<BR> &#060;tr&#062;<BR>&#060;% for each whatever in rstemp.fields<BR>thisfield=whatever.value<BR>if isnull(thisfield) then<BR>thisfield=showblank<BR>end if<BR>if trim(thisfield)="" then<BR>thisfield=showblank<BR>end if%&#062;<BR>&#060;%Select case whatever.name%&#062;<BR>&#060;%case "2+perc"%&#062;<BR> &#060;td valign="top" bgcolor="#ececdc"&#062;&#060;font size="4"&#062;&#060;%=formatpercent(thisfield,2)%& #062; &#060;%case else%&#062; &#060;/font&#062;&#060;/td&#062;<BR> &#060;td valign="top" bgcolor="#ececdc"&#062;&#060;font size="4"&#062;&#060;%=thisfield%&#062; &#060;%End Select%&#062; &#060;% next %&#062; &#060;/font&#062;&#060;/td&#062;<BR> &#060;/tr&#062;<BR>&#060;%rstemp.movenext<BR>LOOP%&#062;< BR>&#060;/table&#062;<BR>&#060;%<BR>rstemp.close<BR>set rstemp=nothing<BR>conntemp.close<BR>set conntemp=nothing<BR>%&#062;<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    572

    Default RE: Parameter's being ignored

    Try this:<BR><BR>"SELECT ORIGACTYPEBYADVANCE.month, Sum(ORIGACTYPEBYADVANCE.[tot2+]) as [tot2+],([tot2+]/[totacs]) AS [2+perc], Sum(ORIGACTYPEBYADVANCE.normalacs) as [normalacs], Sum(ORIGACTYPEBYADVANCE.totacs) as [totacs] FROM ORIGACTYPEBYADVANCE WHERE (((ORIGACTYPEBYADVANCE.period) &#062;= " & period & ") and ((ORIGACTYPEBYADVANCE.period) &#060;= " & period1 & ") AND ((ORIGACTYPEBYADVANCE.origactype) = " & origac & ")) GROUP BY ORIGACTYPEBYADVANCE.month ORDER BY ORIGACTYPEBYADVANCE.month DESC;"<BR>

  3. #3
    Ian Jerrett Guest

    Default RE: Parameter's being ignored

    The double quote with ampersand " & period & " didn&#039t work. The variable disappeared altogether from the error message. I don&#039t know whether it makes any difference, but it is an access database and all of the parameters are numeric.

  4. #4
    Join Date
    Dec 1969
    Posts
    572

    Default RE: Parameter's being ignored

    In case with access try this(single quotes around variables):<BR>"SELECT ORIGACTYPEBYADVANCE.month, Sum(ORIGACTYPEBYADVANCE.[tot2+]) as [tot2+],([tot2+]/[totacs]) AS [2+perc], Sum(ORIGACTYPEBYADVANCE.normalacs) as [normalacs], Sum(ORIGACTYPEBYADVANCE.totacs) as [totacs] FROM ORIGACTYPEBYADVANCE WHERE (((ORIGACTYPEBYADVANCE.period) &#062;= &#039" & period & "&#039) and ((ORIGACTYPEBYADVANCE.period) &#060;= &#039" & period1 & "&#039) AND ((ORIGACTYPEBYADVANCE.origactype) = &#039" & origac & "&#039)) GROUP BY ORIGACTYPEBYADVANCE.month ORDER BY ORIGACTYPEBYADVANCE.month DESC;"<BR>


  5. #5
    Ian Jerrett Guest

    Default RE: Parameter's being ignored

    That didn&#039t work either.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •