CFML form will not insert into SQL Server 2014 database

Question

Lucee 5.2, SQL Server 2014

I have a form that will not insert a new record into my SQL Server 2014 database.

I've studied this problem for a couple of days and nights and cannot figure out what is going on. I do not get an error message of any kind. I fill out the form, click the Submit button, and I am returned to a blank form. No record is inserted into the database table.

I have tested this abridged version of my code, below, but I still get the same result: no insert happens; and Lucee does not give me back any error message at all.

<!--- set a default value "" for RegisterID in scope URL --->
<cfparam name="url.RegisterID" default="">

<!--- define the RegisterID in scope FORM, then set form.RegisterID equal to the RegisterID passed in the URL --->
<cfparam name="form.RegisterID" default="#url.RegisterID#">

<!--- set default values for other user-editable fields --->
<cfparam name="form.Title" default="">
<cfparam name="form.x_first_name" default="">
<cfparam name="form.DateCreated" default="">
<cfparam name="form.DateModified" default="">

<!--- query editRegister tells Lucee which record to edit--->
<cfquery datasource="nnvawi" name="editRegister">
SELECT RegisterID
,Title
,x_first_name
,DateCreated
FROM NNVAWIRegister
WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
</cfquery>


<cftry>  
<cfset variables.error = ""> 

<cfif IsDefined("FORM.doSave")>

<!--- when a RegisterID Exists, the action is UPDATE --->

<cfif val(form.RegisterID)>

<!--- query UpdateUser updates a page record in content table --->
            <cfquery name="UpdateUser" datasource="nnvawi">
                  UPDATE NNVAWIRegister
                  SET
Title = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Title,50))#">,
x_first_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.x_first_name,255))#">,
DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
            </cfquery>


<!--- CFELSE: if RegisterID does not exist, then create new Page --->
                <cfelse> 

<!--- query to insert new user record into content table --->
            <cfquery name="InsertRecord" datasource="nnvawi" result="newRegistrant">
                 INSERT INTO NNVAWIRegister
                        (
,Title
,x_first_name
,DateCreated
                        )                   

                 VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Title,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.x_first_name,255))#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
)         
                    </cfquery>


<!--- use the result attribute value (newRegistrant) to set form field value --->
      <cfset form.RegisterID = newRegistrant.IDENTITYCOL>

<!--- END cfif val(form.RegisterID) -- if a record needed to be updated or added, then it was done --->
                        </cfif>
                    </cfif>
            <cfcatch type="Any">
                 <cfset variables.error = cfcatch.message>
            </cfcatch>
            </cftry>     
<cfif len(variables.error) eq '0'>

            <cfloop index="aCol" list="#editRegister.columnList#">
                   <cfset "form.#aCol#" = editRegister[aCol][editRegister.currentRow]>
              </cfloop>

</cfif>


<cfinclude template="/admin/admin_header.cfm">


<!--- if there an error, display error in readable form --->

<cfif len(variables.error)> 
      <cfoutput>
        <div class="errorbox">#variables.error#</div>
        </cfoutput>

   <br />

             <div class="align-center">
               <input type=button value="Go Back" onClick="history.go(-1)">
             </div>

             <cfabort>
</cfif>



<cfparam name="url.cftoken" default="">

<cfif len(url.cftoken)> 

<div class="center"><button class="medium green"><i class="fa fa-check-square"></i> Update succeeded. Good work!</button></div>

</cfif>

<cfoutput>
<form method="post" name="ebwebworkForm" class="ebwebworkForm">

  <ul>
        <li>
<legend><h2>Registration Details</h2></legend>
      </li>

<input type="hidden" name="RegisterID" value="#form.RegisterID#" /><!--- Embed RegisterID (PK) to assign a value to it --->

<li>
<label for="Title"><h3>Title (Ms., Mr., Dr. etc.):</h3></label>
<input type="text" name="Title" placeholder="Title" value="#Trim(Left(form.Title,255))#" maxlength="255" tabindex="1" size="70" autofocus="true" />
</li>

<li>
<label for="x_first_name"><h3>First Name:</h3></label>
<input type="text" name="x_first_name" placeholder="First Name" value="#Trim(Left(form.x_first_name,255))#" maxlength="255" tabindex="2" size="70" required="yes" />
        <span class="form_hint">Enter First Name</span>
</li>


    <li>
<div class="submitButton">

   <button name="doSave" type="submit" class="green" tabindex="19">Save Record</button>

</div>  
</li>

    </ul>

</form>
</cfoutput>

Answer 1

Your INSERT SQL is invalid. The list of values starts with a comma, that means Title must not be preceded by a comma.

The reason you don't see an error message is that the queries are wrapped into a <cftry>/<cfcatch> block. Within the <cfcatch> there is a <cfset variables.error = cfcatch.message>, though the actual error message is stored in cfcatch.detail. So it is not output.