title: fixing "more columns in insert than values specified"
body: <p>A user got this error</p>
  
  <blockquote>
    <p>DIAGNOSTICS: Error Executing Database Query. [Macromedia][SQLServer
    JDBC Driver][SQLServer]There are more columns in the INSERT statement
    than values specified in the VALUES clause. The number of values in
    the VALUES clause must match the number of columns specified in the
    INSERT statement.  The error occurred on line 252. MESSAGE: Error
    Executing Database Query. ROOT CAUSE:
    coldfusion.tagext.sql.QueryTag$DatabaseQueryException: Error Executing
    Database Query. Server: server2</p>
  </blockquote>
  
  <p>There should be around 60 values/columns. Possible solution is adding <code>&lt;CFIF isDefined("VARIABLES.xxx")&gt;</code> to each of the values in <code>INSERT</code> and <code>VALUES</code> but this would take a long time. Is there a better way? Here is some of the code</p>
  
  <pre><code> &lt;CFQUERY name="addAgency" datasource="#REQUEST.dsn#"&gt;
      INSERT INTO agency (agency_name, code, address1, address2, city_id, postal, phone, alternativePhone, fax, provincialRegistration,
          &lt;!--- contact, ---&gt; firstname, lastname, username, password, relationship_id, editdate, adddate, email, URL_link,Airport_id,
          header, teaser, full_desc, &lt;CFIF isDefined("VARIABLES.specialty")&gt;specialty,&lt;/CFIF&gt; hours_1, hours_2, hours_3, hours_4, 
          hours_5, hours_6, hours_7, merchant_ID,
          region_ID, fcApproved, agencyType_ID, agencystatus, crossview, insp_Approved, rbc_Approved,
          branch_number, gp_entity_ID,
          &lt;CFIF VARIABLES.alias1 gt 100&gt; alias1,&lt;/CFIF&gt;
          &lt;CFIF VARIABLES.alias2 gt 100&gt; alias2,&lt;/CFIF&gt;
          &lt;CFIF Trim(VARIABLES.alias3) NEQ ""&gt; alias3,&lt;/CFIF&gt;
          default_agent_id,LOCAL_PASSWORD,LOCAL_USERNAME,NATIONAL_PASSWORD,NATIONAL_USERNAME,VACATIONCLUB_PASSWORD,VACATIONCLUB_USERNAME,toll_free_number,display_toll_free ,MARKETINGREGIONID, searchPreference, bookingOption,customer_can_choose
          &lt;CFIF isDefined("variables.logo_filedata")&gt;,logo_fileName,logo_fileData,logo_mimeType&lt;/CFIF&gt;
          ,gds, pseudo_city, acv_affiliate_code, vip_ext,owner_manager_title_en,owner_manager_title_fr)
  
      VALUES (&lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.agency_name))#"  cfsqltype = "cf_sql_varchar" maxlength="150"&gt;, 
          &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.code))#"  cfsqltype = "cf_sql_varchar" maxlength="50"&gt;, 
          &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.address1))#"  cfsqltype = "cf_sql_varchar" maxlength="255"&gt;,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.address2))#"  cfsqltype = "cf_sql_varchar" maxlength="255"&gt;,
              &lt;CFQUERYPARAM value="#sanitize(VARIABLES.city_id,true,true,false,true,false)#"  cfsqltype = "cf_sql_integer"&gt;,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.postal))#"  cfsqltype = "cf_sql_varchar" maxlength="10"&gt;,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.phone))#"  cfsqltype = "cf_sql_varchar" maxlength="20"&gt;,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.fax))#"  cfsqltype = "cf_sql_varchar" maxlength="20"&gt;,    
          &lt;!--- &lt;CFIF IsDefined("VARIABLES.contact")&gt;#Trim(VARIABLES.contact)#, &lt;CFELSE&gt; ' ', &lt;/CFIF&gt; ---&gt;
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.firstname))#"  cfsqltype = "cf_sql_varchar" maxlength="40"&gt;,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.lastname))#"  cfsqltype = "cf_sql_varchar" maxlength="40"&gt;,
              '-', '-',   
          &lt;!--- Dummy un/pw ---&gt;
              &lt;CFQUERYPARAM value="#sanitize(VARIABLES.relationship_ID,true,true,false,true,false)#"  cfsqltype = "cf_sql_integer"&gt;,
              #CreateODBCDateTime(Now())#,
          #CreateODBCDateTime(Now())#,
              &lt;CFQUERYPARAM value="#Trim(sanitize(VARIABLES.email))#" cfsqltype = "cf_sql_varchar" maxlength="40"&gt;,
  </code></pre>