Coldfusion Inserting SQL Server Database Null Values

bc@zenmonkeys.com (Brendon) — Tue, 15 Aug 2006 18:16:36 GMT

This is just a quick tip, but if you ever have problems converting your Coldfusion form values to a SQL Server database NULL before using an Insert or Update statement, the following info might help.

When you request form values from the submitting page make sure to use CFPARAM and give each form value submitted a default value, or just set to "" if the value doesn't matter.

Then when you execute your SQL command use CFQUERYPARAM to pass the value to SQL Server. Set the cfsqltype to your required data type. The trick to inserting the database null is to use the "null" attribute of CFQUERYPARAM. If you know that the form value will be null you can just pass the value directly to the "null" attribute, but if you don't know if a user has passed any data then check to see if you are passing an empty string and use the Coldfusion function YesNoFormat to create a Yes or No value for the "null" attribute.

It's not really as confusing as I made it sound - just use the the code snippet below and Coldfusion will create your database null values for you.

Note:

For checkboxes you have to convert the value to 1 or 0 before using it in a SQL command. I just created a little function I call to do this:

function fixCheckbox(value) {
    var x = "";

    if (value EQ "on") {
        x = 1;
    }
    else {
        x = 0;
    }
    return x;
}

Zenmonkeys

Coldfusion Inserting SQL Server Database Null Values