#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    6

    UPDATE INT with NULL


    Is there a way to over-write an INT field previously containing a legitimate number to NULL (I am using SQL server 2005 & ColdFusion MX7). Whether the user will enter something in the form field or not is based on a skip pattern which is determined by the previous question.

    i.e. something like this:

    1: Is user 18 or older?
    2: If yes, then enter value in txtBox1. If no, leave it blank.

    Now let's say that the SQL column corresponding to txtBox1 already had a value of 2 in it. I would like to UPDATE it to NULL if the front-end form left the text box blank. How do I do this?

    Using a regular UPDATE on a blank field is setting it to zero which is unacceptable.
    Last edited by sql_n00b; August 16th, 2011 at 11:18 AM.
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Assuming the column allows nulls, can't you just check to see if the value is '' and if it is, use null in your update query?
  4. #3
  5. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    970
    Originally Posted by kiteless
    Assuming the column allows nulls, can't you just check to see if the value is '' and if it is, use null in your update query?
    Wouldn't it just be like:

    Code:
    <cfif len(form.txtBox1) eq 0>
         <cfquery datasource="dsn" name="q">
               update <table> set <field> = null where id = <id>
         </cfquery>
    <cfelse>
         <cfquery datasource="dsn" name="q">
               update <table> set <field> = <input> where id = <id>
         </cfquery>
    </cfif>
    Obviously just an example but it seems like that should be all you would need to do.
    "Those who can make you believe absurdities can make you commit atrocities."
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Originally Posted by Jyncka
    Wouldn't it just be like:
    In concept yes. But no need to duplicate the whole sql statement. You could shortcut things by using cfqueryparam's null attribute.
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    Right:

    null="#Len( Trim( form.txtBox1 ) ) eq 0#"

IMN logo majestic logo threadwatch logo seochat tools logo