Insert Null Values into Data Base using cfqueryparam

It is always a good practice to use cfqueryparamparam while passing parameter for any data base operation in ColdFusion. It helps us SQL injection attack.

During insert and update operation of data base sometimes we need to save null values into the data base and these null value insertion decision is made in run time.

Like if var x = 0 then insert null value to column P else insert some other value.

So now the point is that how to insert null using cfqueryparam???

The cfqueryparam has a attribute called "null",if we make null="yes/true" then null value will be entered into the DB without looking for the value in the "value" attribute. But the question is that how make that null attribute field dynamic, so that when a input field or query returns empty string it will fill that field as null in data base.

For to make it dynamic we can use a function called YesNoFormat(arg) .Which returns "No" for following values

1.If arg=0

2.If arg=""

3.If arg="false"

4.If arg=false

Anything else will return Yes. So by using any expression within that function we can enter null value as per our requirement.The example is given as follows.




The seasonId value will be the value of the fetched data, if it exists else it will take null value. Because qry_getSeasonID.recordCount gives zero(0) when no record exists and the boolean not operation gives true so the yesNoFormat() returns yes .Means the null value will be entered into the particular field.