Sql coldfusion error ( field not getting updated)

Hi,
  I am trying to insert some numeric data from a few form fields into the database table(MS SQL 2000). Now if one of those fields in blank it should not matter to me however it is trhowing me an error in such a case if a field or two is blank. I do not get such an error with the fields that are inserted into the varchar type in database.

Here is the error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to numeric.  
 
The error occurred in E:\docs_secure\icepe\act_update.cfm: line 133
Called from E:\docs_secure\icepe\act_update.cfm: line 1
Called from E:\docs_secure\icepe\act_update.cfm: line 133
Called from E:\docs_secure\icepe\act_update.cfm: line 1
 
131 :        voltage='#Form.voltage#',
132 :       calc_gpr_peak_asym='#Form.gprpeaka#'          
133 :    WHERE site_id='#Form.sid#'
134 : </CFQUERY>
135 : <cfelse>


Initially i did as follows which was not working

<cflock name="insertrecord_gpr" timeout="10">
<cfquery   datasource="sqldatas" username="****" password="***">
insert into gpr_info (grid_area, grid_resistance, gro_fau_duty, site_id, xr_ratio, voltage, calc_gpr_peak_asym)
values('#EVALUATE('form.gridarea')#','#EVALUATE('form.grid_resistance')#','#EVALUATE('form.gro_fau_duty')#','#EVALUATE('form.sid')#','#EVALUATE('form.xrratio')#'
,'#EVALUATE('form.voltage')#','#EVALUATE('form.gpr_asym')#')
</cfquery>
</cflock>

Then after some guidance from EE i did the folllowing but insert worked and update did not
This is what i did

Follwing update query did not work as it is not replacing(updating) the value with a blank or null when the user leves the value as blank in the submitting form field.

<CFQUERY datasource="sqldatas" name="updategpr" username="**** password="***">
   UPDATE gpr_info
   SET grid_area=<cfqueryparam value="#FORM.gridarea#" null="#iif( len( form.gridarea ), dE( "no" ), dE("yes" ) )#">,
   grid_resistance=<cfqueryparam value="#FORM.gridresist#" null="#iif( len( form.gridresist ), dE( "no" ), dE("yes" ) )#">,
      gro_fau_duty=<cfqueryparam value="#FORM.groduty#" null="#iif( len( form.groduty ), dE( "no" ), dE("yes" ) )#">,
      xr_ratio=<cfqueryparam value="#FORM.xrratio#" null="#iif( len( form.xrratio ), dE( "no" ), dE("yes" ) )#">,
       voltage=<cfqueryparam value="#FORM.voltage#" null="#iif( len( form.voltage ), dE( "no" ), dE("yes" ) )#">,
      calc_gpr_peak_asym=<cfqueryparam value="#FORM.gprpeaka#" null="#iif( len( form.gprpeaka ), dE( "no" ), dE("yes" ) )#">          
   WHERE site_id='#Form.sid#'
</CFQUERY>


However it works for insert as it enters blank or null in the database if i enter blank field in the submitting form

<cfquery datasource="sqldatas" username="****" password="****">
insert into gpr_info (grid_area, grid_resistance, gro_fau_duty, site_id, xr_ratio, voltage, calc_gpr_peak_asym)
values(<cfqueryparam value="#FORM.gridarea#" null="#iif( len( form.gridarea ), dE( "no" ), dE("yes" ) )#">,<cfqueryparam value="#FORM.gridresist#" null="#iif( len( form.gridresist ), dE( "no" ), dE("yes" ) )#">,<cfqueryparam value="#FORM.groduty#" null="#iif( len( form.groduty ), dE( "no" ), dE("yes" ) )#">,'#EVALUATE('form.sid')#',<cfqueryparam value="#FORM.xrratio#" null="#iif( len( form.xrratio ), dE( "no" ), dE("yes" ) )#">
,<cfqueryparam value="#FORM.voltage#" null="#iif( len( form.voltage ), dE( "no" ), dE("yes" ) )#">,<cfqueryparam value="#FORM.gprpeaka#" null="#iif( len( form.gprpeaka ), dE( "no" ), dE("yes" ) )#">)
</cfquery>

Thanks
Anup
anup001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

adonis1976Commented:
it says error converting data type on line 133 and on 133 you have,

where site_id = '#form.site_id#'

try this

where site_id = #form.site_id#
adonis1976Commented:
i mean

where site_id = #form.sid#
anup001Author Commented:

This is question is already solved in some other question. Please do not answer for this anymore. I am requesting to delete it.
Thanks for your help though.And adonis1976 what u told did not work. It is not that simple . Thanks for your help though. Much appreciated.

Anup
trailblazzyr55Commented:
you have to give the value of "" a value to update properly in your database...

for instance form.value="" if you tried to insert/update this it would give an error, but if you assign a param ie:

<cfparam name="form.value" default=" "> <-- notice the space between the " " 's then you should be fine ;o)

so...

<input type="text" name="Value" value="#something.dynamic#">

and on your action page, you'd need to do something like this...

<cfif form.value EQ "">
   <cfset form.value = " ">
</cfif>

or

<cfparam name="form.value" default=" ">

hope that helps ;o)

~trail

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.