Tackling Null Values while Inserting data in data base

Question

I have the following cfquery:

<cfquery name="CQuery" datasource="XX.X.X.XXX">
        INSERT INTO DatabaseName 
            (PhoneNumber_vch,
             Company_vch,
             date_dt)

         VALUES(#PhoneNumber#,
            #Company#,
            #Date# )

    </cfquery>

There are null values in case Company name doesnt' exist and I believe becasue of that Iam getting the following error:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','. 

The comma , pointed in the error message is after #Company# field. Please let me know is it's because of null values and best approach to fix it?

The values in the PhoneNumber, company and Date are extracted from a XML SOAP response with proper usage of trim function as discussed in my previous post.

Using cfif in coldfusion

Thanks

Answer 1

If you use CFQueryParam like you should on any database SQL that accepts dynamic parameters you can kill two birds with one stone. First and most important, prevent SQL Injection Attacks and second you can use the attribute of the null="" to insert a NULL value into your record.

 <cfquery name="CQuery" datasource="XX.X.X.XXX">
      INSERT INTO DatabaseName (PhoneNumber_vch, Company_vch, date_dt)
      VALUES(
           <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(PhoneNumber)#" null="#NOT len(trim(PhoneNumber))#" />
           ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(Company)#" null="#NOT len(trim(Company))#" />
           ,<cfqueryparam cfsqltype="cf_sql_timestamp" value="#Date#" null="#NOT len(trim(Date))#" />
     )
</cfquery>