Problem with Insert/update query with null values

[ndeeley]

Hi,

My database user has just decided that a recent text field in my database needs to be numeric so that some calculations can be run. I've changed it to a double so I can keep the point formatting (ie. 4.1).

I have changed the data types and altered my variables to insert numbers. I have added a default value of 0 to new records added to the database.

Howevern, some of the existing records have a null field in this changed column.
CF can display them but won't update the field without erroring out if the field is left blank.

PowerRating = <cfif IsDefined("form.PowerRating") AND #form.PowerRating# NEQ "">
        #form.PowerRating#
        <cfelse>
        NULL
        </cfif>,

Any idea what I am doing wrong?
Thanks
Neil

[acoder]

What's the error message? Have the NULL values in the existing fields been set by the query or the default used to be null?

[ndeeley]

@acoder
It's a basic sytnx error:

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

The null values are set by the query.
This is the code that adds a new record:

<cfif IsDefined("form.PowerRating") AND #form.PowerRating# NEQ ""> 
    #form.PowerRating# 
      <cfelse> 
    NULL 
  </cfif>,

This is the code that updates it if their is a change:

PowerRating = <cfif IsDefined("form.PowerRating")>
    <cfif #form.PowerRating# NEQ "">
        <cfset PowerRating = #form.PowerRating#>
    <cfelse>
        NULL
    </cfif>

..which works if the valeu is NULL, but not if it has retrieved any other value.

[acoder]

The following code:

PowerRating = <cfif IsDefined("form.PowerRating")>
    <cfif #form.PowerRating# NEQ "">
        <cfset PowerRating = #form.PowerRating#>
    <cfelse>
        NULL

would result in the output "PowerRating = " when there's a value because you've set a variable but you haven't output anything. Your original code would work.

[ndeeley]

It does!

Thanks acoder!

[acoder]

Just a point to bear in mind: in queries where you accept user input as values, you should use cfqueryparam to insert/update values in the database.

[ndeeley]

No problem - are there any tutorials on using it?

[acoder]

Yes, there's a few. Here's one from the horse's mouth.

[ndeeley]

That's grand - thanks!