469,323 Members | 1,615 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Problem with Insert/update query with null values

139 100+
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.

Expand|Select|Wrap|Line Numbers
  1. PowerRating = <cfif IsDefined("form.PowerRating") AND #form.PowerRating# NEQ "">
  2.         #form.PowerRating#
  3.         <cfelse>
  4.         NULL
  5.         </cfif>,
Any idea what I am doing wrong?
Thanks
Neil
May 11 '09 #1

✓ answered by acoder

The following code:
Expand|Select|Wrap|Line Numbers
  1. PowerRating = <cfif IsDefined("form.PowerRating")>
  2.     <cfif #form.PowerRating# NEQ "">
  3.         <cfset PowerRating = #form.PowerRating#>
  4.     <cfelse>
  5.         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.

8 5666
acoder
16,027 Expert Mod 8TB
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?
May 11 '09 #2
ndeeley
139 100+
@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:

Expand|Select|Wrap|Line Numbers
  1. <cfif IsDefined("form.PowerRating") AND #form.PowerRating# NEQ ""> 
  2.     #form.PowerRating# 
  3.       <cfelse> 
  4.     NULL 
  5.   </cfif>,
This is the code that updates it if their is a change:

Expand|Select|Wrap|Line Numbers
  1. PowerRating = <cfif IsDefined("form.PowerRating")>
  2.     <cfif #form.PowerRating# NEQ "">
  3.         <cfset PowerRating = #form.PowerRating#>
  4.     <cfelse>
  5.         NULL
  6.     </cfif>
..which works if the valeu is NULL, but not if it has retrieved any other value.
May 11 '09 #3
acoder
16,027 Expert Mod 8TB
The following code:
Expand|Select|Wrap|Line Numbers
  1. PowerRating = <cfif IsDefined("form.PowerRating")>
  2.     <cfif #form.PowerRating# NEQ "">
  3.         <cfset PowerRating = #form.PowerRating#>
  4.     <cfelse>
  5.         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.
May 11 '09 #4
ndeeley
139 100+
It does!

Thanks acoder!
May 11 '09 #5
acoder
16,027 Expert Mod 8TB
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.
May 11 '09 #6
ndeeley
139 100+
No problem - are there any tutorials on using it?
May 11 '09 #7
acoder
16,027 Expert Mod 8TB
Yes, there's a few. Here's one from the horse's mouth.
May 11 '09 #8
ndeeley
139 100+
That's grand - thanks!
May 12 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Gunnar Vřyenli | last post: by
13 posts views Thread by kieran | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
6 posts views Thread by PW | last post: by
2 posts views Thread by mob1012 via DBMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.