4 Replies Latest reply on Nov 17, 2007 5:56 PM by cf_dev2

    Blank fields into DB

      I need to be able to have users leave the Time_Spent and Notes forms empty or with info. With the following code, it will populate the DB only if those two fields are filled out. I need them to also be able to pass empty text but it will not. Please help.
        • 1. Re: Blank fields into DB
          cf_dev2 Level 1
          Do you mean a NULL value? If so, set the table column to allow NULL values and use cfqueryparam's "null" attribute. Its better to use cfqueryparam for all of form parameters anyway.
          • 2. Re: Blank fields into DB
            Dan Bracuk Level 5
            to elaborate on cf_dev2's answer, this sort of thing often works

            null="#not (len(trim(form.field))"

            Yes, you need a double negative to get a positive.
            • 3. Re: Blank fields into DB
              Steve Sommers Level 4
              To elaborate even further:
              • 4. Re: Blank fields into DB
                cf_dev2 Level 1
                > null="#Not(Len(FORM.Time_Spent))#"

                All good suggestions. Though you should use trim(), as in Dan Bracuk's example, if you want to ignore whitespace.

                To elaborate to the nth degree, the technique works by setting the "null" attribute to true or false based on the results of the conditional test. When null="true" (or the equivalent) the cfqueryparam "value" is ignored and a NULL is sent to the database. Otherwise, the "value" is used.

                Here "null" will be true when FORM.Notes is an empty string or just whitespace.

                <cfqueryparam value="#FORM.Notes#"
                null="#not len(trim(FORM.Notes))#"
                cfsqltype=" your column type">

                Also, use a test that is appropriate for your value and column type. If your value should be numeric you might want to use IsNumeric instead of checking for an empty string.

                <cfquery value="#FORM.yourFieldName#"
                null="#not IsNumeric(FORM.yourFieldName)#"
                cfsqltype=" your column type">