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>