House of Fusion
Search over 2,500 ColdFusion resources here
  
Home of the ColdFusion Community

Mailing Lists
Home /  Groups /  ColdFusion Talk (CF-Talk)

CFIF and null values

  << Previous Post |  RSS |  Sort Oldest First |  Sort Latest First |  Subscribe to this Group Next >> 
Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Richard Colman
03/24/2006 03:44 PM

I have a null value being returned from a query. I would like to catch it and give it a value before proceeding. So, assuming that CF returns a null value as a null string, I tried the following: <cfif len(expr3) eq 0>   <CFSET expr3 = 0> </cfif> Also <cfif expr3 = "">   <CFSET expr3 = 0> </cfif> However, neither of these are working. Any suggestions appreciated. Rick Colman

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Rob Wilkerson
03/24/2006 03:49 PM

I use len ( trim ( the_null_value ) ) often and it's never failed. Any chance the value returned is actually 'NULL'?  Try dumping expr3, but wrap the output with some sort of delimiter so you can tell whether there's any space, maybe... ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Richard Colman
03/24/2006 03:53 PM

Did that, no spaces ... I use len ( trim ( the_null_value ) ) often and it's never failed. Any chance the value returned is actually 'NULL'?  Try dumping expr3, but wrap the output with some sort of delimiter so you can tell whether there's any space, maybe... ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Bryan Stevenson
03/24/2006 03:50 PM

<cfif NOT len(trim(expr3))>  will ctach NULLs Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Rob Wilkerson
03/24/2006 03:56 PM

I've found that it's a good practice to trim any strings whose length is being tested - especially when testing for zero-length.  You never know when a space is going to sneak in there and I find random spaces that I'm not expecting to be a very time consuming thing to debug because it's never the first (or even the thirty-first) thing I try. Take it for what it's worth.  Maybe I'm just not a quick learner.  :-) ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Richard Colman
03/24/2006 04:01 PM

<cfif NOT len(trim(expr3))><CFSET expr3 = 10></cfif> <cfoutput>expr3=[#expr3#]<br></cfoutput> Results in: expr3=[]   on the web page. I can see the NULL in the database. Datatype is SQL-Server numeric. I am mystified why this does not work???? Maybe a typo?? <cfif NOT len(trim(expr3))>  will ctach NULLs Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Michael E. Carluen
03/24/2006 04:14 PM

Try this, works for me all the time <cfif trim(queryname.expr3) is "">   <cfset newvar = 0> <cfelse>   <cfset newvar = queryname.expr3> </cfif> <cfoutput>#newvar#<cfoutput> <cfif NOT len(trim(expr3))><CFSET expr3 = 10></cfif> <cfoutput>expr3=[#expr3#]<br></cfoutput> Results in: expr3=[]   on the web page. I can see the NULL in the database. Datatype is SQL-Server numeric. I am mystified why this does not work???? Maybe a typo?? <cfif NOT len(trim(expr3))>  will ctach NULLs Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Richard Colman
03/24/2006 04:37 PM

For the record, this worked. Apparently, changing the variable name is required of CF can't keep track ot it. Try this, works for me all the time <cfif trim(queryname.expr3) is "">   <cfset newvar = 0> <cfelse>   <cfset newvar = queryname.expr3> </cfif> <cfoutput>#newvar#<cfoutput> <cfif NOT len(trim(expr3))><CFSET expr3 = 10></cfif> <cfoutput>expr3=[#expr3#]<br></cfoutput> Results in: expr3=[]   on the web page. I can see the NULL in the database. Datatype is SQL-Server numeric. I am mystified why this does not work???? Maybe a typo?? <cfif NOT len(trim(expr3))>  will ctach NULLs Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Michael E. Carluen
03/24/2006 04:53 PM

Actually, if your using the expr3 variable on <cfoutput query="queryname"> , just be mindful of where the var is coming from- either from a query column or as locally cfset var... always helps to be a little bit more explicit. For the record, this worked. Apparently, changing the variable name is required of CF can't keep track ot it. Try this, works for me all the time <cfif trim(queryname.expr3) is "">   <cfset newvar = 0> <cfelse>   <cfset newvar = queryname.expr3> </cfif> <cfoutput>#newvar#<cfoutput> <cfif NOT len(trim(expr3))><CFSET expr3 = 10></cfif> <cfoutput>expr3=[#expr3#]<br></cfoutput> Results in: expr3=[]   on the web page. I can see the NULL in the database. Datatype is SQL-Server numeric. I am mystified why this does not work???? Maybe a typo?? <cfif NOT len(trim(expr3))>  will ctach NULLs Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. phone: 250.480.0642 fax: 250.480.1264 cell: 250.920.8830 e-mail: bryan@electricedgesystems.com web: www.electricedgesystems.com

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Steve Milburn
03/24/2006 03:53 PM

What is the data type of the field in the database?  If it is a char, it could be an empty string of the length of the field specification, ie char(10).  In which case, adding a trim to the function should work - trim(len(expr3)) eq 0 Steve Richard Colman wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Mike Klostermeyer
03/24/2006 03:58 PM

Use the isNull() function directly in the SQL. Mike What is the data type of the field in the database?  If it is a char, it could be an empty string of the length of the field specification, ie char(10).  In which case, adding a trim to the function should work - trim(len(expr3)) eq 0 Steve Richard Colman wrote: ----- Excess quoted text cut - see Original Post for more -----

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Adkins, Randy
03/24/2006 03:51 PM

If the value is a Query value two options you could possibly do <CFOUTPUT QUERY="myQ">    <!--- Assign value to a new variable --->    <CFSET myNewVar = myQ.expr3>    <!--- Run validation for Null and 0 --->    <cfif len(expr3) EQ 0 OR expr3 EQ "">        <cfset myNewVar = 0>    <cfelse>        <cfste myNewVar = expr3>    </cfif> Then use the new variable. Now something I have not tried is since the Query is actually a structure, If you know the column and row, you can update the structure. Someone on here mentioned that earlier but not had a chance to test it. I have a null value being returned from a query. I would like to catch it and give it a value before proceeding. So, assuming that CF returns a null value as a null string, I tried the following: <cfif len(expr3) eq 0>   <CFSET expr3 = 0> </cfif> Also <cfif expr3 = "">   <CFSET expr3 = 0> </cfif> However, neither of these are working. Any suggestions appreciated. Rick Colman

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Dave Watts
03/24/2006 03:56 PM

> I have a null value being returned from a query. I would like > to catch it and give it a value before proceeding. > > So, assuming that CF returns a null value as a null string, I > tried the following ... CF does return NULL values as empty strings (not "null" strings, since there are no such things!) However, if you don't want this to happen, your best bet may be to handle it in the database. For example, if you're using SQL Server, you can use functions such as ISNULL or COALESCE to replace NULL values with something else. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information!

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Munson, Jacob
03/24/2006 04:01 PM

> <cfif expr3 = ""> This should be 'eq' not '='.  You knew that, I'm sure, just a slip.  :) ------------ [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format.  Thank you.   A2

Top  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Munson, Jacob
03/24/2006 04:03 PM

> Use the isNull() function directly in the SQL. FYI, coalesce() is ANSI standard, isNull() is proprietary Microsoft syntax. ----------- [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format.  Thank you.   A2

Top  |   Parent  |   Reply  |   Original Post  |   RSS Feed  |   Subscribe to this Group
Author:
Mike Klostermeyer
03/24/2006 05:27 PM

Sorry, you are right.  Meant to mention that, but forgot. Mike > Use the isNull() function directly in the SQL. FYI, coalesce() is ANSI standard, isNull() is proprietary Microsoft syntax. ----------- [INFO] -- Access Manager: This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format.  Thank you.   A2


<< Previous Thread Today's Threads Next Thread >>

Search cf-talk

August 22, 2014

<<   <   Today   >   >>
Su Mo Tu We Th Fr Sa
           1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31             

Designer, Developer and mobile workflow conference