While I am not terribly versed in MSSQL, it sounds like it may be treating the word "NULL" as a varchar or text value?
Troy Jones Dynapp Support Team 678-528-2952 [EMAIL PROTECTED] [EMAIL PROTECTED] ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Howard Sent: Thursday, March 29, 2007 1:26 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] cfqueryparam and NULL attribute SQL Server 2005 Tom, Syntax error on my part. It is an UPDATE that I'm doing and not an INSERT. The code I provided is the update statement. As far as the old code, it exists in a backup file but is not present at all in the live code. The behavior that the app is displaying is as follows: 1. If I click into the formfield and DELETE anything in the field and hit submit it passes an empty string to the action page 2. On the action page I have a CFDUMP and I output this "here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# <BR>" and this is displayed: "here-- 0 --Yes " 3. Using Management Studio I look into the DB and see NULL displayed there in the appropriate column and row. 4. I revisit my CFGRID/CFFORM and the field is blank (the behavior I am expecting) 5. I submit the form again and this time in the CFDUMP I see the word/string "null" 6. This time for the output of this code: here-- #len(trim(Form.assetgrid.equiptype[Row]))# --#yesNoFormat(NOT len(trim(Form.assetgrid.equiptype[Row])))# I get this: "here-- 4 --No" 7. When I then look at the DB in Management Studio I see the word/string "null" in the appropriate column and row. If I run a query against it looking for all with a value of NULL the record is NOT returned. 8. I then load the form again and in the formfield the word/string "null" now appears. Any ideas what I'm doing wrong or what I'm over looking? thanks in advance, Jeff On 3/29/07, Tom McNeer <[EMAIL PROTECTED]> wrote: Jeff, Bottom line, why is isnerting the string "null" into my db instead of <NULL>??? There's nothing in the code you showed that would insert that value. Just a guess, but -- is your old code in place, the code that tried to create "null" in a string value? You're experiencing the same behavior you describe when using your old code. And the only way that a string value of "null" would be inserted is if the variable held that value before you ran the INSERT. In that case, your "yesNoFormat" function would return "false," because the form variable would have a length. And the string "null" would be inserted. -- Thanks, Tom Tom McNeer MediumCool <http://www.mediumcool.com/> http://www.mediumcool.com 1735 Johnson Road NE Atlanta, GA 30306 404.589.0560 ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ <http://www.acfug.org/?fa=login.edituserform> http://www.acfug.org?fa=login.edituserform For more info, see <http://www.acfug.org/mailinglists> http://www.acfug.org/mailinglists Archive @ <http://www.mail-archive.com/discussion%40acfug.org/> http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com/> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------