Problem with Insert/update query with null values

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.

Any idea what I am doing wrong?
Thanks
Neil

re: Problem with Insert/update query with null values

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?

re: Problem with Insert/update query with null values

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:

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

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

re: Problem with Insert/update query with null values

The following code:
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.

re: Problem with Insert/update query with null values

It does!

Thanks acoder!

re: Problem with Insert/update query with null values

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.

re: Problem with Insert/update query with null values

No problem - are there any tutorials on using it?

re: Problem with Insert/update query with null values

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

re: Problem with Insert/update query with null values

That's grand - thanks!