How do I insert blank/null entries into my database?

If a user leaves a field in your form blank, you could have problems when you attempt to insert the data into your database. If the field in question is some kind of string datatype then you may be OK as the empty string ("") is a valid string. However, if your field is a date or a number, your query may be invalid. Take the following query as an example, and assume none of the fields are required.

INSERT INTO Bookings (
              Name,
              Email,
              BookingDate,
              Passengers
            )
    VALUES  (
              '#form.name#',
              '#form.email#',
              #form.bookingDate#,
              #form.passengers#		
            )

If all the fields were left blank then the second half of the query would be passed to the DBMS as:

    VALUES  (
              '',
              '',
              ,
              		
            )

The first three lines are OK, but that comma hanging there on its own is invalid SQL. The query would fail.

There are several solutions to this problem, but the best is to make use of cfqueryparam. There are compelling reasons for using this ColdFusion tag anyway: see Ben Forta's article, "Faster and safer database queries using the <CFQUERYPARAM> tag" for details. Using cfqueryparam, the query above might read as follows:

 

INSERT INTO Bookings (
              Name,
              Email,
              BookingDate,
              Passengers
            )
    VALUES  (
              <cfqueryparam
                value="#form.name#"
                cfsqltype="CF__VARCHAR"
                maxlength="50"
              >,
              <cfqueryparam
                value="#form.email#"
                cfsqltype="CF__VARCHAR"
                maxlength="50"
              >,
              <cfqueryparam
                value="#form.bookingDate#"
                cfsqltype="CF__DATE"
              >,
              <cfqueryparam
                value="#form.passengers#"
                cfsqltype="CF__INTEGER"
              >
            )>

You'll notice the commas are still there, but the single quotes are gone. This in itself does not solve our problem. But we will make use of an extra feature of cfqueryparam and some information supplied by TerraForm to solve it.

TerraForm provides a structure containing all the form's field names along with whether or not they are null entries. This structure is request.terraForm.null . Also, cfqueryparam provides a boolean attribute null to specify that a given field is null. If this attribute is set to true then cfqueryparam ignores the value attribute. So all we need to do is insert the TerraForm value into the cfqueryparam attribute, as follows:

 

INSERT INTO Bookings (
              Name,
              Email,
              BookingDate,
              Passengers
            )
    VALUES  (
              <cfqueryparam
                value="#form.name#"
                cfsqltype="CF__VARCHAR"
                maxlength="50"
                null="#request.terraForm.null.name#"
              >,
              <cfqueryparam
                value="#form.email#"
                cfsqltype="CF__VARCHAR"
                maxlength="50"
                null="#request.terraForm.null.email#"
              >,
              <cfqueryparam
                value="#form.bookingDate#"
                cfsqltype="CF__DATE"
                null="#request.terraForm.null.bookingDate#"
              >,
              <cfqueryparam
                value="#form.passengers#"
                cfsqltype="CF__INTEGER"
                null="#request.terraForm.null.passengers#"
              >
            )>

This looks complex, but it's simple once you get used to using cfqueryparam. You could of course write your own decision-making code based on the values in request.terraForm.null or based on inspection of the field's value.