decimal field in SQl 2000 error coldfusion

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>


Following is the code what i am trying to do.

<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>

I tried using cfparam default but it does not work. IS there any solution for this without having to change the database type?

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.

pinaldaveCommented:
this does not sound right.
the error which is displayed in your error msg is update code and the code you are mentioning that you are using is of insert.
Which one is the code which is creating error?
Regards,
---Pinal
BigSnazzCommented:
<CFPARAM> won't work since the form will still pass the empty fields and give them a blank value.  You could use <CFIF> blocks to set default values for those form fields on the processing page like this:

<CFIF Form.voltage EQ "">
   <CFSET Form.voltage = "0">
</CFIF>


You could also edit the value attribute of your <INPUT> tags on the form to include a default value just in case someone doesn't feel like entering something:

<INPUT TYPE="text" NAME="voltage" VALUE="0">

Let me know if either of those help.
anup001Author Commented:
Sorry Pinal This is what i did. I am actually updating the data that is existing however some columns might exist and some might not in a record. This is what i did and you alsready know what error i am getting.


<CFQUERY datasource="sqldatas" name="updategpr" username="*****"  password="****">
   UPDATE gpr_info
   SET grid_area='#Form.gridarea#',
   grid_resistance='#Form.gridresist#',
      gro_fau_duty='#Form.groduty#',
      xr_ratio='#Form.xrratio#',
        voltage='#Form.voltage#',
      calc_gpr_peak_asym='#Form.gprpeaka#'          
   WHERE site_id='#Form.sid#'
</CFQUERY>

One more thing i wanted to tell u is that this is an update form which retireves the data from the table in an editable form showing up in respective text fields.
So i guess i cannot set the default value to 0 in the previous form(can i ??? I am not tooo sure) becoz then is in this update form any value does exists from the daatbase then that will get overridded by the 0 from the previous form page.

Thanks
anup
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

BigSnazzCommented:
No, you don't want to set your default values to zero in that case, then.  You'd overwrite what was already in those fields in the database.  One thing you could do on your form processing page is run the same query that retrieves the values of the fields before your update and then use those <CFIF> statements that I described above to set the value of a form field that has been left blank to the value it already had in the database.  For example (assuming the query to retrieve the record in question is named 'originalgpr'):



<CFIF Form.voltage EQ "">
   <CFSET Form.voltage = originalgpr.voltage>
</CFIF>
jasco4617Commented:
in the query try using cfqueryparam as folowed:

<cfqueryparam value="#FORM.voltage#" cfsqltype="CF_SQL_NUMERIC">

And then repeat this for each value you are using in your query, make sure to set the correct cfsqltype for each value.
anup001Author Commented:
Jasco4617  <cfqueryparam value="#FORM.voltage#" cfsqltype="CF_SQL_NUMERIC">  is not working.


BigSnazz I tried what u told and it restores whatever was there in the database but thats not what we exactly want. What if i want to replace a value with no vlaue or just blank. In such a case if i use what u told it keeps the old value whereas we want new value. Let me know if there is anything else i can do?

Thanks
anup
mrichmonCommented:
If you want to replace with blank you can use either:

<cfqueryparam value="#FORM.voltage#" null="true">

Or simply

NULL  

No quotes or anything else around it.
Tacobell777Commented:
<cfqueryparam value="#FORM.voltage#" null="#iif( len( form.voltage ), dE( "no" ), dE("yes" ) )#">

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
anup001Author Commented:
Hi,
Do u guys want me to use the cfqueryparam thing on the top of that page? I mean is it like assigning the value of null if nothing comes from the submitting form? Also tacobell, what does dE mean here?

Thanks
Anup
jasco4617Commented:
Information on the DE() function can be found here: http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/functions-pt167.htm#1103833

You will want to use cfqueryparam with in the the query for example:
<cfquery name="something" datasource="someDSN">
    select *
    from someTable
    Where someCol = <cfqueryparam value="someValue" cfsqltype="CF_SQL_NUMERIC">
</cfquery>
Tacobell777Commented:
dE means delay evaluation, because the iif functionation tries to evaluate everything you need to delay it so it doesnt try and look for the variable yes or no

You need to use cfqueryparam everywhere you have a variable in your query

#Form.voltage# becomes
SELECT * FROM table WHERE (id = <cfqueryparam value="#FORM.voltage#" null="#iif( len( form.voltage ), dE( "no" ), dE("yes" ) )#"> )
for example
mrichmonCommented:
Just to directly answer your question cfqueryparam is not the same as cfparam

cfqueryparam is used in queries as TacoBell showed to properly encode/escape/handle variables sent into the database

cfparam is used at the top of a page to guarantee the existence of a variable by providing a default value - or to amke it required.
anup001Author Commented:
Thanks TacoBell and richmon. That helped.
anup001Author Commented:
However it worked only with isnert not with update as

<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>

This does not change anything if i make the field blank.

However it works for insert as

<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
mrichmonCommented:
With inserts it should work too.

An alternative is to construct the query and just leave out the null fields from both the column list and value list
anup001Author Commented:
This is working for INSERT. This is not working for UPDATE. What if I have to replace(update) anything in the database with a blank by keeping that particular text field blank in the form.

Thanks
Anup
mrichmonCommented:
It should work for both equally.

If it is not working to replace with blank then try this:
<cfif Len(Form.fieldname) EQ 0>NULL<cfelse><cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.fieldname#"></cfif>

Also I noticed you are not using cfsqltype in any of your cfqueryparams.

You should adjust each to use the correspoinding type

For a text field:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.fieldname#">

For an integer:
<cfqueryparam cfsqltype="cf_sql_integer" value="#Form.fieldname#">

For a decimal:
<cfqueryparam cfsqltype="cf_sql_float" value="#Form.fieldname#">

etc...
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.