• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

coldfusion 5 / SQL Server 2000: why integer field saved as 0 when I want it to be NULL

Hi experts,

In SQL Server 2000, I have an integer field.  In the coldfusion form, I have a dropdown list and I save the values 1, 2, 3.  When the user selects a blank choice from the dropdown list, I don't want SQL Server 2000 to save the value 0; I prefer if it just leaves the field with a NULL value.  It's the line that says  <cfif isdefined("FORM.frmMain_Priority")>

The table tblPriority looks like:

PriorityID      PriorityDesc
1      1-High
2      2-Med
3      3-Low
4      4-Needs Review

Thanks.   RemoveEmbedint-field-saves-0-instead-of-NULL
Update 	Forms
SET 	FormNum='#frmMain_FormNumber#'
,FormName='#frmMain_FormName#'
<cfif isdefined("FORM.frmMain_Category")>,Category='#FORM.frmMain_Category#' <cfelse>,Category='' </cfif>						
<cfif isdefined("FORM.frmMain_Priority")>,PriorityID='#FORM.frmMain_Priority#' <cfelse>,Priority='' </cfif>	

<cfif isdefined("FORM.frmMain_Department")>,Dept='#FORM.frmMain_Department#' <cfelse>,Dept='' </cfif>						
<cfif isdefined("Form.frmMain_Status")>,StatusID='#frmMain_Status#'<cfelse>,StatusID=''	</cfif>		
,Future=<cfif IsDefined("Form.frmMain_Future") eq "1">1<cfelse>0</cfif>
<cfif isdefined("FORM.frmMain_Owner")>,Name='#FORM.frmMain_Owner#' <cfelse>,Name='' </cfif>						
,PhyName='#frmMain_PhyName#'

,EntryDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_EntryDate), "") EQ 0)#" value="#form.frmMain_EntryDate#">
,FormDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_FormDate), "") EQ 0)#" value="#form.frmMain_FormDate#">
,ZynxDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_ZynxDate), "") EQ 0)#" value="#form.frmMain_ZynxDate#">

<cfif isdefined("Form.frmMain_SourceFile")>	,SourceFileID='#Form.frmMain_SourceFile#'<cfelse>,SourceFileID=''</cfif>	

,InactiveDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_InactiveDate), "") EQ 0)#" value="#form.frmMain_InactiveDate#">
,POMADate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_POMADate), "") EQ 0)#" value="#form.frmMain_POMADate#">

,POMAUpload=<cfif IsDefined("Form.frmMain_POMAUpload") eq "1">1<cfelse>0</cfif>

,ReviewDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_ReviewDate), "") EQ 0)#" value="#form.frmMain_ReviewDate#">
,PNTDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_PNTDate), "") EQ 0)#" value="#form.frmMain_PNTDate#">

,Notes='#frmMain_Notes#'
,BuilderName='#frmMain_BuilderName#'


WHERE upper(FormID) = upper('#URL.id#')

Open in new window

0
paultran00
Asked:
paultran00
  • 4
  • 3
1 Solution
 
_agx_Commented:
> <cfif isdefined("FORM.frmMain_Priority")>,PriorityID='#FORM.frmMain_Priority#'

First, that type of select list will always be defined. So it's defaulting to your first condition. The result is 0 because it's converting your empty string to the number 0 implicitly.

> <cfelse>,Priority='' </cfif>      
Second, if you want it to be NULL then you need to specify NULL instead of an empty string "".

But you should also use cfqueryparam on all parameters to avoid sql injection.  Here's a single statement that will do both.

    ,   PriorityID= <cfqueryparam value="#FORM.frmMain_Priority#" cfsqltype="cf_sql_integer"
              null="#not IsNumeric(FORM.frmMain_Priority)#">
0
 
paultran00Author Commented:
Hi,  thanks for helping.  I replaced my code with this line but coldfusion didn't like the not:

,PriorityID= <cfqueryparam value="#FORM.frmMain_Priority#" cfsqltype="cf_sql_integer" null="#not IsNumeric(FORM.frmMain_Priority)#">



Error:

Error Diagnostic Information
Just in time compilation error

Invalid parser construct found on line 42 at position 94. ColdFusion was looking at the following text:

not
Invalid expression format. The usual cause is an error in the expression structure.
The last successfully parsed CFML construct was a CFQUERYPARAM tag occupying document position (42:14) to (42:26).

The specific sequence of files included or processed is:
D:\INETPUB\VIRTUAL_SITES\CF_SITES\IFORMS_TRACKING\INDEX.CFM      
  D:\INETPUB\VIRTUAL_SITES\CF_SITES\IFORMS_TRACKING\ACT_EDIT_FORMS.CFM      CFInclude

The error occurred while processing an element with a general identifier of (CFINCLUDE), occupying document position (9:1) to (9:36).

0
 
_agx_Commented:
Can you post your full update query?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
paultran00Author Commented:
Update       Forms
SET       FormNum='#frmMain_FormNumber#'
,FormName='#frmMain_FormName#'
<cfif isdefined("FORM.frmMain_Category")>,Category='#FORM.frmMain_Category#' <cfelse>,Category='' </cfif>                                    

<!---
<cfif isdefined("FORM.frmMain_Priority")>,PriorityID='#FORM.frmMain_Priority#' <cfelse>,Priority='' </cfif>            
 --->
,PriorityID= <cfqueryparam value="#FORM.frmMain_Priority#" cfsqltype="cf_sql_integer" null="#not IsNumeric(FORM.frmMain_Priority)#">


<cfif isdefined("FORM.frmMain_Department")>,Dept='#FORM.frmMain_Department#' <cfelse>,Dept='' </cfif>                                    
<cfif isdefined("Form.frmMain_Status")>,StatusID='#frmMain_Status#'<cfelse>,StatusID=''      </cfif>            
,Future=<cfif IsDefined("Form.frmMain_Future") eq "1">1<cfelse>0</cfif>
<cfif isdefined("FORM.frmMain_Owner")>,Name='#FORM.frmMain_Owner#' <cfelse>,Name='' </cfif>                                    
,PhyName='#frmMain_PhyName#'

,EntryDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_EntryDate), "") EQ 0)#" value="#form.frmMain_EntryDate#">
,FormDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_FormDate), "") EQ 0)#" value="#form.frmMain_FormDate#">
,ZynxDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_ZynxDate), "") EQ 0)#" value="#form.frmMain_ZynxDate#">

<cfif isdefined("Form.frmMain_SourceFile")>      ,SourceFileID='#Form.frmMain_SourceFile#'<cfelse>,SourceFileID=''</cfif>      

,InactiveDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_InactiveDate), "") EQ 0)#" value="#form.frmMain_InactiveDate#">
,POMADate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_POMADate), "") EQ 0)#" value="#form.frmMain_POMADate#">

,POMAUpload=<cfif IsDefined("Form.frmMain_POMAUpload") eq "1">1<cfelse>0</cfif>

,ReviewDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_ReviewDate), "") EQ 0)#" value="#form.frmMain_ReviewDate#">
,PNTDate=<cfqueryparam cfsqltype="cf_sql_timestamp" null="#YesNoFormat(Compare(Trim(form.frmMain_PNTDate), "") EQ 0)#" value="#form.frmMain_PNTDate#">

,Notes='#frmMain_Notes#'
,BuilderName='#frmMain_BuilderName#'

WHERE upper(FormID) = upper('#URL.id#')
0
 
_agx_Commented:
I don't see any syntax problems with CF8. Maybe it's just CF5 that doesn't like the syntax? Try splitting it back out into an cfif/cfelse. That should work. Assuming it does, you should really add cfqueryparam to all of the fields, so your code isn't vulnerable to sql injection.


<cfif IsNumeric(FORM.frmMain_Priority)>
, PriorityID= '#FORM.frmMain_Priority#'
<cfelse>
    ,Priority= NULL
</cfif>      

0
 
paultran00Author Commented:
Yep, that worked.  Thank you very much.  I greatly appreciated.
0
 
_agx_Commented:
> <cfelse>,Priority='' </cfif>      

Btw: I noticed the original UPDATE had 2 different column names.  It should probably be "PriorityID" in both places.

<cfif IsNumeric(FORM.frmMain_Priority)>
, PriorityID= '#FORM.frmMain_Priority#'
<cfelse>
    ,Priority = NULL
</cfif>      

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now