Solved

ColdFusion Update Number Type Field in Access

Posted on 2012-12-20
5
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

751 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