ColdFusion is giving error when executing database query

Question

Can somebody please suggest what is wrong with this code?

<cfquery name="getPreviousDBentries" datasource="#application.datasource#">
    select image, image_b,
    image_c,image_d,image_e, image_f from used_listings_v2 where id = '#edit#'
</cfquery>

<cfdump var="#getPreviousDBentries#">
<cftry>
    <cfquery name="updateToNull" datasource="#application.datasource#">
        <cfif len(getPreviousDBentries.image) neq 0>
            Update used_listings_v2 SET image = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_b) neq 0>
            Update used_listings_v2 SET image_b = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_c) neq 0>
            Update used_listings_v2 SET image_c = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_d) neq 0>
            Update used_listings_v2 SET image_d = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_e) neq 0>
            Update used_listings_v2 SET image_e = NULL where id = '#edit#'
        </cfif>

        <cfif len(getPreviousDBentries.image_f) neq 0>
            Update used_listings_v2 SET image_f = NULL where id = '#edit#'
        </cfif>
    </cfquery>

<cfcatch>
<cfdump var="#cfcatch#">
</cfcatch>

</cftry>

I am getting correct output when the image column has some value i.e. image, image_b,image_c, image_d,image_e, image_f are not null.

But it's giving me an error saying error executing database query when image, image_b,image_c, image_d,image_e, image_f are null.

Please suggest possible solutions.

Answer 1

The reason why you're getting an error message is because all of the <cfif> conditions are false, it's like executing a <cfquery> with no code inside of it. So you will need to add an additional condition outside the <cfquery> block to test and make sure at least one of your UPDATE commands will execute.

<cfquery name="getPreviousDBentries" datasource="#application.datasource#">
    select image, image_b,
    image_c,image_d,image_e, image_f from used_listings_v2 where id = '#edit#'
</cfquery>

<cfif NOT (len(getPreviousDBentries.image) eq 0> AND 
      len(getPreviousDBentries.image_b) eq 0 AND
      len(getPreviousDBentries.image_c) eq 0 AND
      len(getPreviousDBentries.image_d) eq 0 AND
      len(getPreviousDBentries.image_e) eq 0 AND
      len(getPreviousDBentries.image_f) eq 0)>

    <cftry>
        <cfquery name="updateToNull" datasource="#application.datasource#">
            <cfif len(getPreviousDBentries.image) neq 0>
                Update used_listings_v2 SET image = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_b) neq 0>
                Update used_listings_v2 SET image_b = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_c) neq 0>
                Update used_listings_v2 SET image_c = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_d) neq 0>
                Update used_listings_v2 SET image_d = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_e) neq 0>
                Update used_listings_v2 SET image_e = NULL where id = '#edit#'
            </cfif>

            <cfif len(getPreviousDBentries.image_f) neq 0>
                Update used_listings_v2 SET image_f = NULL where id = '#edit#'
            </cfif>
        </cfquery>

        <cfcatch>
            <cfdump var="#cfcatch#">
        </cfcatch>

    </cftry>
</cfif>