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>

Answer 2

You will want to use <cfqueryparam> to take care of nulls (and injection attacks)

Try

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

     VALUES(
        <cfqueryparam value = "#PhoneNumber#" cfsqltype = "CF_SQL_VARCHAR">,
        <cfqueryparam value = "#Company#" cfsqltype = "CF_SQL_VARCHAR" 
          null              = "#IIF(Company EQ "", 1, 0)#">,
        <cfqueryparam value = "#Date#" cfsqltype = "CF_SQL_TimeStamp"
          null              = "#IIF(Date EQ "", 1, 0)#" >
        )

</cfquery>

Also see: cfqueryparam

Answer 3

You either need to qualify your varchar entries (surround all varchar entries with single quotes, or, better would be change them to cfqueryparams;

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

         VALUES(<cfqueryparam value="#PhoneNumber#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Company#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#Date#" cfsqltype="CF_SQL_TIMESTAMP"> )

    </cfquery>