Problem: Lucee (beginning recently) does not insert record into MSSQL database


#1

Lucee 5.2.4.37
Microsoft SQL Server 2014

Problem: Lucee (beginning recently) does not insert record into MSSQL database

I have refined this question, after getting more information. The error I get is:

Cannot insert the value NULL into column ‘CareplannersUUID’, table ‘careplanners.dbo.CareplannersMembers’; column does not allow nulls. INSERT fails.

So, my real question is: is there a way to ask Lucee to automatically insert a valid, unique UUID value in a new record?

Thank you as always.

Eric


#2

I have isolated the problem: the column UUID. Lucee throws this error:

Cannot insert the value NULL into column ‘CareplannersUUID’, table ‘careplanners.dbo.CareplannersMembers’; column does not allow nulls. INSERT fails.

Adobe ColdFusion used to automatically insert a valid, unique UUID in column CareplannersUUID. Is there a way to get Lucee to automatically insert a valid, unique UUID in column CareplannersUUID? Thank you again!

Eric


#3

Is this a primary key field?
Which SQL driver? Microsoft or jTDS?


#4

Quick test with Lucee 5.2.2 and 5.2.4 with jTDS and Microsoft driver and get same error. Don’t have CF installed. Maybe had NEWID() as default for CareplannersUUID field?


#5

kabutotx, good to hear from you!

Microsoft SQL Server Driver.

This is the INSERT code that I use. (Please see below.) It works fine with Adobe ColdFusion.

The Primary Key is CareplannersID, which is just a simple integer value. This is also the identity column in the database. This column gives me no trouble. This is just FYI.

Why do I even have a UUID value in this database? Because the original database, which I inherited about ten years ago, was a Microsoft Access Database with a UUID column.

To avoid undoing a whoooole bunch of code, I have kept the UUID column.

Here’s the code with the INSERT statement. I don’t think I use the NEWID() function.

Do you have a suggestion for a way to auto-populate a valid, 36-character UUID value for new records? Thank you tons.

Eric

<!-----
Name:        joinCareplanners.cfm
Author:      Eric Bourland / gdemaria / _agx_
Description: Join careplanners
Created:     August - October 2011
Modifed: April 2017 for use with Lucee CFML Processor
----->

<!--- Set default value for column CareplannersUUID in table CareplannersMembers --->
<!--- column CareplannersUUID in CareplannersMembers is a UUID, datatype varchar(36) --->
<cfparam name="url.CareplannersUUID" default="">

<!--- Define CareplannersUUID in scope FORM, then set form.CareplannersUUID equal to the CareplannersUUID passed in the URL --->
<!--- This steps allows us to insert a new value in column CareplannersUUID in table CareplannersMembers --->
<cfparam name="form.CareplannersUUID" default="#url.CareplannersUUID#">


<!---======= set default values for degreeID =======--->
<!--- Set default value for column degreeID in table careplanners_has_degrees --->
<cfparam name="url.degreeID" default="">

<!--- Define degreeID in scope FORM, then set form.degreeID equal to the degreeID passed in the URL --->
<!--- This steps allows us to insert a new value in column degreeID in table careplanners_has_degrees (relational table) --->
<cfparam name="form.degreeID" default="#url.degreeID#">


<!---======= set default values for certificationID =======--->
<!--- Set default value for column certificationID in table careplanners_has_certifications --->
<!--- column certificationID in careplanners_has_certifications is a UUID, datatype varchar(36) --->
<cfparam name="url.certificationID" default="">

<!--- Define certificationID in scope FORM, then set form.certificationID equal to the certificationID passed in the URL --->
<!--- This steps allows us to insert a new value in column certificationID in table careplanners_has_certifications (relational table) --->
<cfparam name="form.certificationID" default="#url.certificationID#">


<!---======= set default values for pediatricID =======--->
<!--- Set default value for column pediatricID in table careplanners_has_pediatric --->
<!--- column pediatricID in careplanners_has_pediatric is a UUID, datatype varchar(36) --->
<cfparam name="url.pediatricID" default="">

<!--- Define pediatricID in scope FORM, then set form.pediatricID equal to the pediatricID passed in the URL --->
<!--- This steps allows us to insert a new value in column pediatricID in table careplanners_has_pediatric (relational table) --->
<cfparam name="form.pediatricID" default="#url.pediatricID#">


<!---======= set default values for adultID =======--->
<!--- Set default value for column adultID in table careplanners_has_adult --->
<!--- column adultID in careplanners_has_adult is a UUID, datatype varchar(36) --->
<cfparam name="url.adultID" default="">

<!--- Define adultID in scope FORM, then set form.adultID equal to the adultID passed in the URL --->
<!--- This steps allows us to insert a new value in column adultID in table careplanners_has_adult (relational table) --->
<cfparam name="form.adultID" default="#url.adultID#">


<!---======= set default values for regionID =======--->
<!--- Set default value for column regionID in table careplanners_has_regions --->
<!--- column regionID in careplanners_has_regions is a UUID, datatype varchar(36) --->
<cfparam name="url.regionID" default="">

<!--- Define regionID in scope FORM, then set form.regionID equal to the regionID passed in the URL --->
<!--- This steps allows us to insert a new value in column regionID in table careplanners_has_regions (relational table) --->
<cfparam name="form.regionID" default="#url.regionID#">


<!--- Set default values for variables in table CareplannersMembers, in scope form --->

<cfparam name="form.CareplannersID" default="">
<cfparam name="form.prefix" default="">
<cfparam name="form.firstname" default="">
<cfparam name="form.middleinit" default="">
<cfparam name="form.lastname" default="">
<cfparam name="form.suffix" default="">
<cfparam name="form.company" default="">
<cfparam name="form.primaryphone" default="">
<cfparam name="form.UserEmail" default="">
<cfparam name="form.UserPassword" default="">
<cfparam name="form.UserRoleID" default="">
<cfparam name="form.website" default="">
<cfparam name="form.street1" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.zip1" default="">
<cfparam name="form.country1" default="">
<cfparam name="form.phone1" default="">
<cfparam name="form.fax1" default="">
<cfparam name="form.street2" default="">
<cfparam name="form.city2" default="">
<cfparam name="form.state2" default="">
<cfparam name="form.zip2" default="">
<cfparam name="form.country2" default="">
<cfparam name="form.phone2" default="">
<cfparam name="form.fax2" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.info" default="">
<cfparam name="form.DateModified" default="">
<cfparam name="form.sign_up" default="0"> <!--- radio --->
<cfparam name="form.verified" default="0"> <!--- checkbox --->

<!---===== set default values for child tables, in scope form =====--->
<!--- Set default values for variables in table careplanners_degrees --->
<cfparam name="form.DegreeTitle" default="">

<!--- Set default values for variables in table careplanners_certifications --->
<cfparam name="form.certificationTitle" default="">

<!--- Set default values for variables in table careplanners_pediatric --->
<cfparam name="form.pediatricTitle" default="">

<!--- Set default values for variables in table careplanners_adult --->
<cfparam name="form.adultTitle" default="">

<!--- Set default values for variables in table careplanners_regions --->
<cfparam name="form.region" default="">


<!--- in user-editable fields, set up protection against XSS  --->
    <cfloop collection="#FORM#" item="field">
      <cfset FORM[ field ] = ReReplaceNoCase (FORM[ field ], "<script.*?>.*?</script>", "", "all")>
    </cfloop>


<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- begin cfif IsDefined("FORM.doSave") --->
<cfif IsDefined("FORM.doSave")>


<!---=========== spam honeypot message; humans should not see this ===========--->
<cfif len(trim(form.email_address))>
<cfthrow message="This input has been identified as spam.">
</cfif>

 <!--- in this query select NOTHING from table CareplannersMembers, and simply check if UserEmail exists --->
 
 <cfquery datasource="careplanners" name="CheckUserEmail">
  SELECT 'Nothing' FROM CareplannersMembers
  WHERE UserEmail = <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.UserEmail#">
  AND CareplannersUUID <> <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#form.CareplannersUUID#">
  </cfquery>

  
   <!--- if UserEmail exists, display error; refuse record insert --->
   
	<cfif CheckUserEmail.recordcount GT 0>
		   

<cfthrow message="That email address is already in the database, which means it has been used before to register for the Life Care Planner Network. That means you are already registered with the Life Care Planner Network. Please retrieve your LCP Network password associated with the email address that you just entered. Please point your web browser to https://www.careplanners.net/GetPassword.cfm.">
           
           
	</cfif>



<!--- We are inserting new data into many tables at the same time: careplanners parent table, plus five related tables --->
<!--- so enclose the Insert statements in cftransaction --->
<cftransaction>
 <!--- statement to insert new record into table CareplannersMembers --->
 <!--- use result attribute, newCareplannersID, to populate column CarePlannersID in all relational "has" tables --->
  
<cfquery name="InsertCareplannersMembers" datasource="careplanners" result="newCareplannersID">
				 INSERT INTO CareplannersMembers
     					(
prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,UserPassword
,UserRoleID
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info
,verified
,sign_up
,DateModified
                        )
			     VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.prefix,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.firstname,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.middleinit,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.lastname,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.suffix,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.company,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.primaryphone,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserPassword,32))#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="9">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.website,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street1,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip1,15))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone1,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax1,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street2,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip2,15))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone2,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax2,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.practice,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.info,2048))#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="0">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.sign_up)#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
 
    <!--- use the result attribute value (newCareplannersID) to set form field value --->
        <cfset form.CareplannersID = newCareplannersID.IDENTITYCOL>
 
 
 			<!--- INSERT new values CareplannersID, degreeID --->
            <!--- into relational table careplanners_has_degrees --->
  
    <cfquery name="InsertDegree" datasource="careplanners">   
     INSERT INTO careplanners_has_degrees ( 
     CareplannersID
     ,degreeID  
        )
     SELECT <cfqueryparam value="#val(newCareplannersID.IDENTITYCOL)#" cfsqltype="cf_sql_integer">
            ,degreeID  
     FROM    careplanners_degrees 
      WHERE  degreeID  IN ( 
                     <cfqueryparam value="#form.degreeID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>   


 			<!--- INSERT new values CareplannersID, certificationID --->
            <!--- into relational table careplanners_has_certifications --->
  
    <cfquery name="InsertCertification" datasource="careplanners">   
     INSERT INTO careplanners_has_certifications ( 
     CareplannersID
     ,certificationID  
        )
     SELECT <cfqueryparam value="#val(newCareplannersID.IDENTITYCOL)#" cfsqltype="cf_sql_integer">
            ,certificationID  
     FROM    careplanners_certifications 
      WHERE  certificationID  IN ( 
                     <cfqueryparam value="#form.certificationID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>   
   
   
   
   	<!--- INSERT new values CareplannersID, regionID --->
            <!--- into relational table careplanners_has_regions --->
  
    <cfquery name="InsertRegion" datasource="careplanners">   
     INSERT INTO careplanners_has_regions ( 
     CareplannersID
     ,regionID  
        )
     SELECT <cfqueryparam value="#val(newCareplannersID.IDENTITYCOL)#" cfsqltype="cf_sql_integer">
            ,regionID  
     FROM    careplanners_regions 
      WHERE  regionID  IN ( 
                     <cfqueryparam value="#form.regionID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>  
   
   
   
   
      	<!--- INSERT new values CareplannersID, pediatricID --->
            <!--- into relational table careplanners_has_pediatric --->
  
    <cfquery name="InsertPediatric" datasource="careplanners">   
     INSERT INTO careplanners_has_pediatric ( 
     CareplannersID
     ,pediatricID  
        )
     SELECT <cfqueryparam value="#val(newCareplannersID.IDENTITYCOL)#" cfsqltype="cf_sql_integer">
            ,pediatricID  
     FROM    careplanners_pediatric
      WHERE  pediatricID  IN ( 
                     <cfqueryparam value="#form.pediatricID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>   




      	<!--- INSERT new values CareplannersID, adultID --->
            <!--- into relational table careplanners_has_adult --->
  
    <cfquery name="InsertPediatric" datasource="careplanners">   
     INSERT INTO careplanners_has_adult ( 
     CareplannersID
     ,adultID  
        )
     SELECT <cfqueryparam value="#val(newCareplannersID.IDENTITYCOL)#" cfsqltype="cf_sql_integer">
            ,adultID  
     FROM    careplanners_adult
      WHERE  adultID  IN ( 
                     <cfqueryparam value="#form.adultID#" list="true" cfsqltype="cf_sql_integer">
                 )
   </cfquery>   
   


<!--- notify Susan of new application --->
   <cfmail
      from="mailAdmin@ebwebwork.com"
      to="sgrisham@careplanners.net"
      subject="New Careplanners Application">Hello, Susan! There is a new application to the Careplanners Directory from #form.UserEmail#. Please log in to the careplanners.net control panel, and review the application in the Careplanners Administrative Control Panel. You can approve or reject the application. If you have any trouble, please contact Eric at eric@ebwebwork.com.</cfmail>


 </cftransaction>

 <!--- END queries to insert record ---> 

                        
         <!--- done? relocate --->


<cflocation url="/thankyou.cfm" addtoken="no">

<!--- END: Save action --->

<!--- END form.doSave --->
                    </cfif>
       
<!--- END queries to update or insert database records ---> 
        

<!--- this CFCATCH will trap errors -- the ones you threw or just regular database issues --->
            <cfcatch type="Any">
                 <cfset variables.error = cfcatch.message>
            </cfcatch>

<!--- END CFTRY --->  
			</cftry>

#6

kabutotx,

Maybe had NEWID() as default for CareplannersUUID field?

Do you mean:

<cfparam name="form.CareplannersUUID" default="NewID()">

Or something else? Thank you again.

Eric


#7

No. In SQL Server you can add NEWID() as the default for that field in table design mode.


#8

Got it. I’ll try this later today and let you know what happens.

Will NewID() auto-create a UUID value?


#9

NewID() in this context is a MSSQL function, and yes it creates new unique identifiers. It’s safe to use in single or multiple row updates and inserts. (It will create a new UUID for each row)

Best thing to do would be to set a default on the MSSQL column. Assuming table is the table name and id is your uniqueidentifier column

ALTER TABLE table ADD CONSTRAINT DF_table_id DEFAULT (NEWID()) FOR id

Will create a default constraint.

Proper way to insert things would be

  INSERT INTO table (col1, col2, col3) VALUES (queryparam,queryparam,queryparam);

Since the id column isn’t in the column list it will follow the default value.

If you want to be explicit:

  INSERT INTO table (id,col1,col2,col3) VALUES(DEFAULT, queryparam,queryparam,queryparam)

Or:

  INSERT INTO table (id,col1,col2,col3) VALUES(NEWID(), queryparam,queryparam,queryparam)

(but not <Cfqueryparam value="newid()" /> or <cfqueryparam value="#newid()#" /> as using cfparam would end up causing)

In our environment we’d always use NEWID() to generate the value; if you wanted to use CF to generate the ID, you use CreateUUID(), but then you need to move the -'s so they conform with MSSQL, and it’s just not as clean.

(See https://cfdocs.org/createuuid - the first paragraph explains the different formats)

IMHO, regardless of what works in ACF or Lucee, I’d always move to make my code as resilient on its own as possible. Better to do a solution as a convention that will work in both cases than to rely on vendor specific behavior.


#10

Joe, this makes a lot of sense, everything you said. It seems like I should use NEWID() in MSSQL to create the UUID value in column CareplannersUUID.

So, in MS SQL Server, I will enter:

ALTER TABLE CareplannersMembers ADD CONSTRAINT DF_table_id DEFAULT (NEWID()) FOR CareplannersUUID

Does that look correct? Is there another value I should add for DF_table_id?

Some notes about this table:
table name: CareplannersMembers
UUID Column name: CareplannersUUID
Identity column, and primary key: CareplannersID (which I don’t think we need to worry about; just mentioning it)

Thank you for your help. I am learning a ton. =)

Eric


#11
ALTER TABLE CareplannersMembers ADD CONSTRAINT DF_CareplannersMembers_CarePlannersUUID DEFAULT (NEWID()) FOR CareplannersUUID

Constraint names are unique per database - if you do it in SSMS in design view, then it’ll name that way. If you don’t give it an explicit name, it’ll do something ugly like DF_SomeRandomHexValue… It may just be my OCD, but we also have lots of databases we keep in sync and when I compare schema I want the constraints to be consistent. :slight_smile:

OK so the UUID isn’t the primary key - I’m hoping it’s not your clustered key either… Do you have a unique constraint on that column?

I’m guessing you use CareplannersID as your lookup in most cases? (I’d assume it’s also your clustered key/index)


#12

ALTER TABLE CareplannersMembers ADD CONSTRAINT DF_CareplannersMembers_CareplannersUUID DEFAULT (NEWID()) FOR CareplannersUUID

Dear Joe,

yes! That makes more sense. I was studying your comment, above, very carefully but was still not sure if I were doing the right thing. =) Thank you for helping me understand.

I do use CareplannersID (not UUID) as my lookup in most cases.

I do not think CareplannersUUID is a clustered key; in truth, I do not know what a clustered key is. I do not think I use a clustered key in this very simple application.

Here is my initial code, down to the INSERT statement. Do you think this code will work as-is, if I just implement the MSSQL command as noted above?

Thank you so much for your time and help. I am learning a great deal.

All best,

Eric

<!-----
Name:        joinCareplanners.cfm
Author:      Eric Bourland / gdemaria / _agx_
Description: Join careplanners
Created:     August - October 2011
Modifed: April 2017 for use with Lucee CFML Processor
----->

<!--- Set default value for column CareplannersUUID in table CareplannersMembers --->
<!--- column CareplannersUUID in CareplannersMembers is a UUID, datatype varchar(36) --->
<cfparam name="url.CareplannersUUID" default="">

<!--- Define CareplannersUUID in scope FORM, then set form.CareplannersUUID equal to the CareplannersUUID passed in the URL --->
<!--- This steps allows us to insert a new value in column CareplannersUUID in table CareplannersMembers --->
<cfparam name="form.CareplannersUUID" default="#url.CareplannersUUID#">


<!--- Set default values for variables in table CareplannersMembers, in scope form --->

<cfparam name="form.CareplannersID" default="">
<cfparam name="form.prefix" default="">
<cfparam name="form.firstname" default="">
<cfparam name="form.middleinit" default="">
<cfparam name="form.lastname" default="">
<cfparam name="form.suffix" default="">
<cfparam name="form.company" default="">
<cfparam name="form.primaryphone" default="">
<cfparam name="form.UserEmail" default="">
<cfparam name="form.UserPassword" default="">
<cfparam name="form.UserRoleID" default="">
<cfparam name="form.website" default="">
<cfparam name="form.street1" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.zip1" default="">
<cfparam name="form.country1" default="">
<cfparam name="form.phone1" default="">
<cfparam name="form.fax1" default="">
<cfparam name="form.street2" default="">
<cfparam name="form.city2" default="">
<cfparam name="form.state2" default="">
<cfparam name="form.zip2" default="">
<cfparam name="form.country2" default="">
<cfparam name="form.phone2" default="">
<cfparam name="form.fax2" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.info" default="">
<cfparam name="form.DateModified" default="">
<cfparam name="form.sign_up" default="0"> <!--- radio --->
<cfparam name="form.verified" default="0"> <!--- checkbox --->


<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- begin cfif IsDefined("FORM.doSave") --->
<cfif IsDefined("FORM.doSave")>


<!--- We are inserting new data into many tables at the same time: careplanners parent table, plus five related tables --->
<!--- so enclose the Insert statements in cftransaction --->
<cftransaction>
 <!--- statement to insert new record into table CareplannersMembers --->
 <!--- use result attribute, newCareplannersID, to populate column CarePlannersID in all relational "has" tables --->
  
<cfquery name="InsertCareplannersMembers" datasource="careplanners" result="newCareplannersID">
				 INSERT INTO CareplannersMembers
     					(
prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,UserPassword
,UserRoleID
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info
,verified
,sign_up
,DateModified
                        )
			     VALUES(
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.prefix,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.firstname,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.middleinit,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.lastname,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.suffix,5))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.company,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.primaryphone,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserPassword,32))#">,
<cfqueryparam cfsqltype="cf_sql_integer" value="9">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.website,255))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street1,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip1,15))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country1,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone1,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax1,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street2,100))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip2,15))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country2,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone2,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax2,20))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.practice,50))#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.info,2048))#">,
<cfqueryparam cfsqltype="cf_sql_bit" value="0">,
<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.sign_up)#">,
<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
 
    <!--- use the result attribute value (newCareplannersID) to set form field value --->
        <cfset form.CareplannersID = newCareplannersID.IDENTITYCOL>

#13

Yep, that should work. CareplannersID will increment because it’s an identity column, and CareplannersUUID will be populated because it has a default constraint.


#14

Dear Joe, that was it! It’s working again. Thank you very much for your patient help. I understand the solution, and I have learned a great deal this weekend. I love Lucee. :wink: Wishing you a peaceful and productive week.

Eric