update cfquery within a cfloop over a query

Question

I am going through all the records in the column vals of the Values table and tranforming any absolute urls to relative urls. I get this error

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.

On the 3rd to last line of this block of code:

<cfquery name="getVals" datasource="#dataBase#">
  Select vals
  FROM Values
</cfquery>

<cfloop 
  query = "getVals">

  <cfset val=#vals#>
    <cfset valEdited= REReplace(
      val, 
      '"(https?:\/\/)?(www\.)?(example\.com)(\/)?"', 
      "'index.php'", 
      'ALL'
      )>

    <cfquery name="update" datasource="#dataBase#">
      UPDATE   Values
      SET      vals = <cfqueryPARAM value = #valEdited#>
      WHERE    ID = <cfqueryPARAM value = #getVals.currentRow#>
    </cfquery>
</cfloop>

Any ideas on how to fix it?

Answer 1

The first thing I would look at is you have a semi colon at the end of the CFSET for ValEdited. That will blow up first.

Then check your code, to see if it gives you the same error.

A best practice you might want to consider, is only use # when you are outputting values... so

<cfset val = getVals.vals> 

is sufficient.

The next thing I would look at is you where statement. This assumes all of your values have ID, 1 to the number of records in the table. If this is not so, you might have to update this to

where ID = #getVals.ID#

Hope this helps. By the time I wrote it up, everyone else pretty much said the same thing :P