Loop SQL 1

[BigBadDave]

Hi, this dosn't seem to work:

<cfif IsDefined (&quot;form.update&quot;)>
  <cfloop index=&quot;i&quot; from=&quot;1&quot; to=&quot;#form.arrTotal#&quot;>
    <cfquery datasource=&quot;ema&quot;>
      UPDATE students SET class='#form[&quot;class&quot; & i]#', amount='#form[&quot;amount&quot; & i]#', paid='#form[&quot;paid&quot; & i]#', incs='#form[&quot;incs&quot; & i]#' WHERE sid = '#form[&quot;sid&quot; & i]#'
    </cfquery>
  </cfloop>
</cfif>

What is wrong? Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[r937]

"doesn't seem to work" is rather vague, don't you think?

doesn't run due to syntax error? runs but no rows updated?

actually, i think this may be a good example of one of the few instances where using the Evaluate function is warranted

something like

<CFSET thissid=&quot;form.sid&quot;&i>

and then

WHERE sid = #Evaluate(thissid)#

note, don't use single quotes around numeric values like amount and paid and maybe sid

rudy

http://rudy.ca/

 

[BigBadDave]

Yes nothing gets updated, and all the fields are text not numbers Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[csteinhilber]

Go with Rudy's suggestion.
Something like:

  <cfloop index=&quot;i&quot; from=&quot;1&quot; to=&quot;#form.arrTotal#&quot;>
    <CFSET thisClass = FORM[&quot;class#i#&quot;]>
    <CFSET thisAmount = FORM[&quot;amount#i#&quot;]>
    <CFSET thisPaid = FORM[&quot;paid#i#&quot;]>
    <CFSET thisIncs = FORM[&quot;incs#i#&quot;]>
    <CFSET thisSid = FORM[&quot;sid#i#&quot;]>

    <cfquery datasource=&quot;ema&quot;>
      UPDATE students SET class='#thisClass#', amount='#thisAmount#', paid='#thisPaid#', incs='#thisIncs#' WHERE sid = '#thisSid#'
    </cfquery>
  </cfloop>

(not sure why you'd need/want to use Evaluate... but I'm sure Rudy has his reasons ) Hope it helps,
-Carl

 

[BigBadDave]

Thanks for your input Carl but it still dosn't work:

<cfif IsDefined (&quot;form.update&quot;)>
  <cfloop index=&quot;i&quot; from=&quot;1&quot; to=&quot;#form.arrTotal#&quot;>
    <cfset class = form[&quot;class#i#&quot;]>
    <cfset amount = form[&quot;amount#i#&quot;]>
    <cfset paid = form[&quot;paid#i#&quot;]>
    <cfset incs = form[&quot;incs#i#&quot;]>
    <cfset sid = form[&quot;sid#i#&quot;]>
    <cfquery datasource=&quot;ema&quot;>
      UPDATE students SET class='#class#', amount='#amount#', paid='#paid#', incs='#incs#' WHERE sid = '#sid#'
    </cfquery>
  </cfloop>
</cfif>

Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[r937]

help figure out what's going on by temporarily changing the CFQUERY to CFOUTPUT so that it prints the UPDATE statements instead of trying to execute them

this should show what the CFSETs did for evaluating the form array elements

rudy

 

[BigBadDave]

I have done that and the SQL is fine but it dosn't execute

http://byngdesigns.dnsalias.net/studentse.cfm

Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com

 

[r937]

the sql looks okay, but each UPDATE has the same sid (the first one) for each student

WHERE sid = '160485-2002-1-0005'

so there's your problem why it doesn't seem to work -- it's updating poor Eliza Adams over and over and over...

rudy

 

[BigBadDave]

Yes it is I see that now, thanks alot rudy! Regards
David Byng

http://www.byngdesigns.co.uk

davidbyng@hotmail.com