Solved

ColdFusion Update Number Type Field in Access

Posted on 2012-12-20
5
328 Views
Last Modified: 2012-12-20
My website accepts number values and I receive an error if no value is inputted. (only works when a value is present)

Problem:
I would like to delete this value if needed without receiving this error:
Data type mismatch in criteria expression

My Access database has a number type field and using cf_sql_varchar in my update statement.
I've tried using other cf_sql types: number, integer, etc without success.

Would the solution be to insert a default value of 0 if no data is present or to change the cf_sql statement that would accept a null value for the number type field in Access?
0
Comment
Question by:DJPr0
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:jdthedj
ID: 38710884
You could use the IsNumeric function to check the value before adding to your database.  It is not a CF_SQL_VARCHAR which is for text.  I assume you are using <CFQUERYPARAM>  If you are (and you should be) you don't specify any CFSQLTYPE
0
 

Author Comment

by:DJPr0
ID: 38710986
You could use the IsNumeric function to check the value before adding to your database.

How will this help to delete the value?

I want to update the field to null or 0.
0
 
LVL 3

Expert Comment

by:jdthedj
ID: 38711002
Try this

<CFFUNCTION NAME="NullVal" RETURNTYPE="Numeric">
  <CFARGUMENT NAME="Amt" DATATYPE="String" REQUIRED="True">
  <CFIF IsNumeric(Amt)>
    <CFRETURN Amt>
  <CFELSE>
    <CFRETURN 0>
  </CFIF>
</CFFUNCTION>

The function will change any null to a zero
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38711291
I want to update the field to null or 0.

EDIT To set the value to 0, use the val() function.  It automatically converts non-numeric values like an empty string to  0.  (I'm assuming your column type is INTEGER. Change if needed ...)

              UPDATE TableName
              SET  NumColumn = <cfqueryparam value="#val(form.yourVariable)#" cfsqltype="cf_sql_integer">
              WHERE ID = ....

To set the value to null, use the NULL attribute

              UPDATE TableName
              SET       NumColumn = <cfqueryparam value="#form.yourVariable#"
                                                      cfsqltype="cf_sql_integer"
                                                      null="#not IsNumeric(form.yourVariable)#" >
              WHERE ID = ....


Would the solution be to insert a default value of 0 if no data is present or to change the cf_sql statement that would accept a null value for the number type field in Access?

All depends on your needs.  Sometimes 0 is better, other times null is.  When I want to indicate something has no value, like for a date field, I prefer NULL instead of entering a bogus date like 12/31/1899. For numbers it can go either way.
0
 

Author Closing Comment

by:DJPr0
ID: 38711745
Worked perfectly, thanks _agx_!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question