We help IT Professionals succeed at work.

a better way to update two tables in the same transaction

Eric Bourland
Eric Bourland used Ask the Experts™
on
ColdFusion 9 / MS SQL Server 2005
Three tables:
PNWContractors (contains data about contractors -- name, address, etc.)
PNWCat (contains a list of work categories)
PNWContractorsCat (relational table, relates PNWContractors to PNWCat)

Question: Hi again. I need to update two tables (PNWContractors and PNWContractorsCat) in the same transaction, so I am using the cftransaction tag. But am I overthinking this? Is there an easier way to do this than the code I have below?

My strategy:
* update PNWContractors by itself, in one query
* delete values Contractor_ID, Work_Type_ID from PNWContractorsCat
* update PNWContractorsCat with new values entered in the SELECT menu

An administrator might want to enter entirely new work categories for a contractor. I need to make sure that old categories are removed, and new ones entered, when the form processes.

If anyone has a moment, could you take a look at the code below and tell me if I could make it simpler, or if I'm missing anything important? Thank you for your time as always. I've studied and commented the code at length.

Eric B


<!--- if a value for form.Int_Contractor_ID exists, then update the record --->
   
<cfif val(form.Int_Contractor_ID)>

<!--- We are updating two tables at the same time, so enclose the Update statements in cftransaction --->
<cftransaction>

    <cfquery name="UpdateRecord" datasource="#application.datasource#">
	  UPDATE PNWContractors
	  SET Contractor_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
           Contractor_Address = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
           Contractor_Address2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
           Contractor_City = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
           Contractor_State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
           Contractor_Zip = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,40))#">, 
           Contractor_Email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
           Contractor_ContactName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
           Contractor_Phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
           Contractor_Website = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
            Contractor_Inactive = <cfqueryparam cfsqltype="cf_sql_bit"  value="#Trim(form.Contractor_Inactive)#">,
            UpdateTimeStamp = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
	  WHERE Int_Contractor_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.Int_Contractor_ID)#">
			</cfquery>
            
            
  <!----- delete all Contractor_ID, Work_Type_ID from PNWContractorsCat ---->  
        <cfquery name="DeletePNWContractorsCat" datasource="#application.datasource#">  
           DELETE FROM PNWContractorsCat  
           WHERE Contractor_ID = <cfqueryparam value="#form.Contractor_ID#" cfsqltype="cf_sql_varchar">  
        </cfquery>  
            
            <!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
        INSERT INTO PNWContractorsCat (   
            Contractor_ID   
          , Work_Type_ID  
        )
        VALUES	(
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ID,36))#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Work_Type_ID,36))#">
        )
   </cfquery> 

<!--- close cftransaction --->
</cftransaction>


<!--- ELSE: if a value for form.IntWork_Type_ID does not exist, then create a record --->
<cfelse> 

<!--- We are updating two tables at the same time, so enclose the Update statements in cftransaction --->
<cftransaction>
<!--- statement to insert new record into table PNWContractors --->
			<cfquery name="InsertPNWCat" datasource="#application.datasource#" result="newPNWContractors">
				 INSERT INTO PNWContractors
     					(
                        Contractor_Name
      				   ,Contractor_Address
    			       ,Contractor_Address2
    			       ,Contractor_City
			           ,Contractor_State
			           ,Contractor_Zip
			           ,Contractor_Email
			           ,Contractor_ContactName
			           ,Contractor_Phone
			           ,Contractor_Website
			           ,Contractor_Inactive
			           ,InsertTimeStamp
                        )
			     VALUES(
	          	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,40))#">, 
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
				<cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.Contractor_Inactive)#">,
				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
      
			<!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
        INSERT INTO PNWContractorsCat (   
            Contractor_ID   
          , Work_Type_ID  
        )
        VALUES	(
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ID,36))#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Work_Type_ID,36))#">
        )
   </cfquery>               
    
    </cftransaction>
                    
<!--- use the result attribute value (newPNWContractors) to set form field value --->
      <cfset form.Int_Contractor_ID = newPNWContractors.IDENTITYCOL>
              
<!--- END queries to update or insert database records ---> 

<!--- END cfif val(form.Int_Contractor_ID) --->
					    </cfif>  
      

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
Your strategy sounds fine except for the last step. IIRC you users can assign multiple categories at one time.  Your current INSERT would not handle that. It only inserts 1 record.

The insert of the contractor categories should be more like this.  Use a WHERE IN (...) to get all of the selected categories from PNWCat and insert them directly into  PNWContractorsCat. (No looping)

          INSERT INTO PNWContractorsCat ( Contractor_ID   , Work_Type_ID  )
          SELECT <cfqueryparam value="#FORM.YourContractorID#" cfsqltype="(your type)">
                      ,   Work_Type_ID  
           FROM    PNWCat
           WHERE  Work_Type_ID  IN (
                      <cfqueryparam value="#ListOfWork_Type_IDs#" list="true" cfsqltype="(your type)">
                 )

>>        WHERE Int_Contractor_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.Int_Contractor_ID)#">

Just an observation, but is there a reason you are not using the UUID here? The only place you really need to use the integer record ID is right after an INSERT.  Beyond that, you should be able to use the UUID everywhere.  While integer record id's are simpler and more comfortable to use at first, switching back and forth will just end up making things more confusing for you.  

Author

Commented:
I am finding out that I need to rethink this edit page, as I test it and run into errors.

I do need to use the UUID, too.

OK, I am going to work on this. More in a while. Thank you!

E
Most Valuable Expert 2015
Commented:
IMO the overall structure is fine.  It's just details like which id are you using, etc... you need troubleshoot

ie Existing record
    * update PNWContractors
    * delete current categories from PNWContractorsCat  
    * insert new values into PNWContractorsCat
New record
    * insert into PNWContractors ( get integer contractorID from "result")
    * get contractor UUID from PNWContractors
    * insert new values into PNWContractorsCat
 

Author

Commented:
I'm going to work on this for a while and see what I can figure out for myself.

Author

Commented:
Dang it, hit Submit too soon. I also meant to say that your notes make sense, and I will troubleshoot as you recommend:

ie Existing record
    * update PNWContractors
    * delete current categories from PNWContractorsCat  
    * insert new values into PNWContractorsCat
New record
    * insert into PNWContractors ( get integer contractorID from "result")
    * get contractor UUID from PNWContractors
    * insert new values into PNWContractorsCat

And thank you. =) More soon. E

Author

Commented:
I finally got time to work on this task again.

I am working first on the statement to update an existing record. Most of your example makes sense. But I am not sure how to get the list of ListOfWork_Type_IDs:

WHERE  Work_Type_ID  IN (
                      <cfqueryparam value="#ListOfWork_Type_IDs#" list="true" cfsqltype="(your type)">

Should I use a query to get the selected categories for the current record:

  <cfquery datasource="#application.datasource#" name="getSelectedCategories">
     SELECT Work_Type_ID
     FROM   PNWContractorsCat 
     WHERE Contractor_ID = <cfqueryparam value="#form.Contractor_ID#" cfsqltype="cf_sql_varchar">
</cfquery>

Open in new window


and then use the list of results from that query for the value in <cfqueryparam value="#ListOfWork_Type_IDs#" ...> ?

I feel like I am missing something obvious here. =)

Have a good evening. Thanks again for your help.

Eric
<cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
     WHERE  Work_Type_ID  IN ( 
                      <cfqueryparam value="#ListOfWork_Type_IDs#" list="true" cfsqltype="(your type)">
                 )

   </cfquery>

Open in new window

why not to use the Stored Procedure for this Task, That might be easier to handle and you can even use the rollback/commit and let database do the task for yu!

Most Valuable Expert 2015
Commented:
>> But I am not sure how to get the list of ListOfWork_Type_IDs:

From your form field.  If your categories list allows multiple selections the value will contain multiple id's.  

 WHERE  Work_Type_ID  IN (
                      <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="(your type)">
                 )

Author

Commented:
myselfrandhawa, thanks for your note. A stored procedure could be a useful solution -- I will look into that. I need to learn more about stored procedures. They can be very useful, I know. For now I'll continue with _agx_'s solution. Thank you for your input.

_agx_,

That makes sense. I'm making progress. Currently I get this error from my INSERT statement:



Error Executing Database Query.



[Macromedia][SQLServer JDBC Driver][SQLServer]Cannot insert the value NULL into column 'ID', table 'ebwebwork.dbo.PNWContractorsCat'; column does not allow nulls. INSERT fails.



 



 The error occurred in C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 234
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 186
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 128
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 117
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 1
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 234
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 186
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 128
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 117
Called from C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 1




232 :         VALUES      (
233 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ID,36))#">,
234 :         <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Work_Type_ID,36))#">
235 :         )
236 :    </cfquery>              
 

ColdFusion is trying to send a NULL value to column Work_Type_ID. I wonder why that is. I am going to check out the database column. I believe we told database column Work_Type_ID to update automatically with a UUID.
<cftransaction>
<!--- statement to insert new record into table PNWContractors --->
			<cfquery name="InsertPNWCat" datasource="#application.datasource#" result="newPNWContractors">
				 INSERT INTO PNWContractors
     					(
                        Contractor_Name
      				   ,Contractor_Address
    			       ,Contractor_Address2
    			       ,Contractor_City
			           ,Contractor_State
			           ,Contractor_Zip
			           ,Contractor_Email
			           ,Contractor_ContactName
			           ,Contractor_Phone
			           ,Contractor_Website
			           ,Contractor_Inactive
			           ,InsertTimeStamp
                        )
			     VALUES(
	          	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,40))#">, 
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
				<cfqueryparam cfsqltype="cf_sql_bit" value="#val(form.Contractor_Inactive)#">,
				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
      
			<!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
        INSERT INTO PNWContractorsCat (   
            Contractor_ID   
          , Work_Type_ID  
        )
        VALUES	(
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ID,36))#">,
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Work_Type_ID,36))#">
        )
   </cfquery>               
    
    </cftransaction>

Open in new window

Most Valuable Expert 2015

Commented:
1) First, remember creating a new contractor record is a 2 step process. The insert into PNWContractors (which looks good).  But then you have to use the "result" to lookup the new record's UUID.  Not tested. But something like
             
               SELECT  TheUUIDColumnNameHere
               FROM    PNWContractors
               WHERE Int_Contractor_ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(newPNWContractors.IDENTITYCOL)#">

Once you have the contractor's UUID, you can go on to adding the categories.

2) It looks like you're using the old category insert.  Remember you're replacing it with the INSERT / SELECT we talked about?

3)  But what is this "ID" column in the error? Are there other columns in the PNWContractorsCat's table besides: Contractor_ID  and Work_Type_ID  

Author

Commented:
>>>2) It looks like you're using the old category insert.  Remember you're replacing it with the INSERT / SELECT we talked about?

Rats. I copied over the wrong code. In fact I am using
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
		WHERE  Work_Type_ID  IN ( 
                      <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
                 )
   </cfquery> 

Open in new window

Sorry to be confusing. I need to slow down.

>>>3)  But what is this "ID" column in the error? Are there other columns in the PNWContractorsCat's table besides: Contractor_ID  and Work_Type_ID  

That is a good question and I should have realized this column needs to be accounted for.

PNWContractorsCat has an ID column called ID. Its data type is varchar(36) and it is populated with UUIDs. It is a primary key. The thing is, I don't think I even need this ID column. Why have an ID column in a table whose only function is to relate two other tables? I think table PNWContractorsCat needs only two columns:

Work_Type_ID nvarchar(36) (foreign key in table PNWCat)
Contractor_ID nvarchar(36) (foreign key in table PNWContractors)

Can I just delete the ID column from PNWCat?
Most Valuable Expert 2015

Commented:
>> In fact I am using
>> SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">

Ok. But for a "new" contractor, the form field wont' contain the UUID yet. You have to do the 2 step process to create/retrieve it.  

>> Can I just delete the ID column from PNWCat?

>> Why have an ID column in a table whose only function is to relate two other tables?
>> Can I just delete the ID column from PNWCat?

It can be useful for uniquely identify entries if duplicates are possible. Some people love them, others hate them.  As far as deleting, I don't know.  It's *probably* safe, but if its the table's primary key you may to drop the PK constraint first.  You could also just add a CONSTRAINT so it's populated automatically.  Then revisit the issue later.  Whatever you do, make backups first.


Most Valuable Expert 2015

Commented:
>> you may to drop the PK constraint first

correction:  you may NEED to drop the PK constraint first

Author

Commented:
Got it. I understand about the two-step update process and my update procedure is now:

<cftransaction .....>
...update PNWContractors ...

  <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
		WHERE  Work_Type_ID  IN ( 
                      <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
                 )
   </cfquery>              

     <cfquery name="getNewUUID"  datasource="#application.datasource#">        
            SELECT  Work_Type_ID
            FROM     PNWCat    
            WHERE  IntWork_Type_ID = #val(newPNWContractorsUUID.IDENTITYCOL)#
         </cfquery>

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


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

    </cftransaction>

Open in new window


That makes much more sense now.

I am going to be conservative and keep the PK ID column in PNWContractorsCat. =)

I made a backup copy of table PNWContractorsCat, then ran this command in the SQL interface:
ALTER TABLE PNWContractorsCat
ADD CONSTRAINT ConstrainUUID
DEFAULT NEWID() FOR ID

and that worked fine. So, I test the application.... and I get a syntax error that points out an errant apostrophe:


[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.



 



 The error occurred in C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 233
231 :      FROM    PNWCat
232 :             WHERE  Work_Type_ID  IN (
233 :                       <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
234 :                  )
235 :    </cfquery>  

... hmm. So now I am closely looking at the syntax of my queries.

Author

Commented:
The <!--- use the result attribute value (newPNWContractors) to set form field value --->        <cfset form.Work_Type_ID = newPNWContractors.IDENTITYCOL>

should not be in the code above.

Author

Commented:
I found and fixed the syntax error, a missing (
Most Valuable Expert 2015

Commented:
>> Got it. I understand about the two-step update process and my update procedure is now:

You mean INSERT, not update, yes? For updates, the contractor record already exists, and you already know the uuid value ;)



Author

Commented:
I did mean INSERT, sorry.

I get this error now:

Element IDENTITYCOL is undefined in NEWPNWCONTRACTORSUUID.
 The error occurred in C:/websites/www.pnwrccsearch.org/admin/editContractors.cfm: line 242
240 :             SELECT  Work_Type_ID
241 :             FROM     PNWContractorsCat    
242 :             WHERE  IntWork_Type_ID = #val(newPNWContractorsUUID.IDENTITYCOL)#
243 :          </cfquery>

I'm getting confused between Int_Contractor_ID (which is an integer identity column in PNWContractors) and ContractorID; and between IntWork_Type_ID and Work_Type_ID ... and you have explained this already and I need to review.
At the top of the page I have:
<!--- Note: column Int_Contractor_ID is identity column, datatype integer, in table PNWContractors --->

<!--- Set default value for Int_Contractor_ID in scope URL --->
<cfparam name="url.Contractor_ID" default="">

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

Here is the full Insert statement:

<!--- INSERT new values Contractor_ID, Work_Type_ID --->
<!--- into relational table PNWContractorsCat --->
<!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
      	,Work_Type_ID  
     FROM    PNWCat 
	WHERE  Work_Type_ID  IN ( 
         ( <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
                 )
   </cfquery>          
   
<!---  use result of query UpdateCategory to look up new record's UUID --->    

<cfquery name="getNewUUID"  datasource="#application.datasource#">        
            SELECT  Work_Type_ID
            FROM     PNWContractorsCat    
            WHERE  IntWork_Type_ID = #val(newPNWContractorsUUID.IDENTITYCOL)#
         </cfquery>

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

    </cftransaction>

Open in new window

Most Valuable Expert 2015

Commented:
>> I'm getting confused between Int_Contractor_ID (which is an integer
>> identity column in PNWContractors) and ContractorID;

Yeah, it is confusing. That's why I was suggesting you might use only 1 of those values throughout.  With the exception of 1 query, I think you can UUID everywhere.

So getting back to the error, for new constractors it's 3 steps:

        1) create the contractor. ie insert into PNWContractors table
        2) lookup the new contractor's UUID ie select from PNWContractors
        3) insert UUID and categories ie insert into PNWContractorsCat    

Starting with step 1,  where's the insert into the Contractor table? That's what generates the ID. If it's missing, that explains your error.

Author

Commented:
Got it. I am thinking it through. Thank you for your patience. I need to be smarter about this.
Most Valuable Expert 2015

Commented:
No, just take it in smaller pieces :)  It's often tempting to try and run everything at once (believe me I know..). But the more code, the longer it takes to trouble shoot when there's a problem.  Start with running 1 piece. Verify it.  Add the next piece. Re-verify, and so on.  Then if it breaks, you'll know exactly where the problem is.

Author

Commented:
I've been trying to just do an insert, and have removed the update code for now.

I've a question. This has been puzzling me. I have the code at the top of my page; the code might be redundant or misguided, but I ~think~ this code is required for me to create a new value in columns Contractor_ID (in table PNWContractors)  and Work_Type_ID (in table PNWContractors ):

<!--- Set default value for column Contractor_ID in table PNWContractors --->
<!--- column Contractor_ID in PNWContractors is a UUID, datatype varchar(36) --->

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

<!--- Define Contractor_ID in scope FORM, then set form.Contractor_ID equal to the Contractor_ID passed in the URL --->
<!--- This steps allows us to insert a new value in column Contractor_ID in table PNWContractors --->

<cfparam name="form.Contractor_ID" default="#url.Contractor_ID#">

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

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

Open in new window


But I think I have this wrong. I do not want to use this code at the top of my page to set default values in columns Contractor_ID and Work_Type_ID (both of which are UUIDs, and not IDENTITY); instead I want to use this code to set default values for the IDENTITY columns Int_Contractor_ID and Int_Work_Type_ID.

I hope this makes sense.
<!-----
Name:        editContractors.cfm
Author:      Eric Bourland / _agx_ / gdemaria / brij
Description: Edit records in table PNWContractors, relational table PNWContractorsCat
Created:     August 2011
ColdFusion Version 9
MS SQL Server 2005
----->


<!--- Set default value for column Contractor_ID in table PNWContractors --->
<!--- column Contractor_ID in PNWContractors is a UUID, datatype varchar(36) --->

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

<!--- Define Contractor_ID in scope FORM, then set form.Contractor_ID equal to the Contractor_ID passed in the URL --->
<!--- This steps allows us to insert a new value in column Contractor_ID in table PNWContractors --->

<cfparam name="form.Contractor_ID" default="#url.Contractor_ID#">

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

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


<!--- Set default values for variables in table PNWContractors --->
<cfparam name="form.Contractor_Name" default="">
<cfparam name="form.Contractor_Address" default="">
<cfparam name="form.Contractor_Address2" default="">
<cfparam name="form.Contractor_City" default="">
<cfparam name="form.Contractor_State" default="">
<cfparam name="form.Contractor_Zip" default="">
<cfparam name="form.Contractor_Email" default="">
<cfparam name="form.Contractor_ContactName" default="">
<cfparam name="form.Contractor_Phone" default="">
<cfparam name="form.Contractor_Website" default="">
<cfparam name="form.Contractor_Inactive" default="">
<cfparam name="InsertTimeStamp" default="">
<cfparam name="UpdateTimeStamp" default="">
<cfparam name="Int_Contractor_ID" default="">

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

<!--- Set default values for variables in getRecordDetails --->
<cfparam name="getRecordDetails.Contractor_Name" default="">
<cfparam name="getRecordDetails.Contractor_Address" default="">
<cfparam name="getRecordDetails.Contractor_Address2" default="">
<cfparam name="getRecordDetails.Contractor_City" default="">
<cfparam name="getRecordDetails.Contractor_State" default="">
<cfparam name="getRecordDetails.Contractor_Zip" default="">
<cfparam name="getRecordDetails.Contractor_Email" default="">
<cfparam name="getRecordDetails.Contractor_ContactName" default="">
<cfparam name="getRecordDetails.Contractor_Phone" default="">
<cfparam name="getRecordDetails.Contractor_Website" default="">
<cfparam name="getRecordDetails.Contractor_Inactive" default="">


 <!--- for fields in which a variable can be entered, set up protection against XSS  --->
<cfset form.Contractor_Name = ReReplaceNoCase (form.Contractor_Name, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Address = ReReplaceNoCase (form.Contractor_Address, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Address2 = ReReplaceNoCase (form.Contractor_Address2, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_City = ReReplaceNoCase (form.Contractor_City, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_State = ReReplaceNoCase (form.Contractor_State, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Zip = ReReplaceNoCase (form.Contractor_Zip, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Email = ReReplaceNoCase (form.Contractor_Email, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_ContactName = ReReplaceNoCase (form.Contractor_ContactName, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Phone = ReReplaceNoCase (form.Contractor_Phone, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Website = ReReplaceNoCase (form.Contractor_Website, "<script.*?>.*?</script>", "", "all")>
<cfset form.Contractor_Inactive = ReReplaceNoCase (form.Contractor_Inactive, "<script.*?>.*?</script>", "", "all")>
<cfset form.Work_Type = ReReplaceNoCase (form.Work_Type, "<script.*?>.*?</script>", "", "all")>


<!--- ListContractors lists contractors and contractors' work types --->
<cfquery datasource="#application.datasource#" name="ListContractors">
           SELECT
           con.Contractor_ID
           ,Contractor_Name
           ,Contractor_Address
           ,Contractor_Address2
           ,Contractor_City
           ,Contractor_State
           ,Contractor_Zip
           ,Contractor_Email
           ,Contractor_ContactName
           ,Contractor_Phone
           ,Contractor_Website
           ,Contractor_Inactive
           ,con.InsertTimeStamp
           ,con.UpdateTimeStamp
           ,con.Int_Contractor_ID
           ,cat.Work_Type_ID
           ,cat.Work_Type
           ,cat.Work_Type_Description
           ,cat.Display_Color
           ,cat.IntWork_Type_ID 
           ,cc.Contractor_ID
           ,cc.Work_Type_ID   
       
           FROM PNWContractors con
           
           LEFT OUTER JOIN PNWContractorsCat cc
           ON con.Contractor_ID = cc.Contractor_ID
           
           LEFT OUTER JOIN PNWCat cat
           ON cat.Work_Type_ID = cc.Work_Type_ID
           
WHERE con.Contractor_ID = <cfqueryparam value="#url.Contractor_ID#" cfsqltype="cf_sql_varchar">
</cfquery>

<!---- begin CFTRY; catch errors ---->
<cftry>  
 
<!---- populate cftry with error message ---->
<cfset variables.error = ""> 
 
<!--- BEGIN: Save action --->

<!--- begin form.doSave --->

<!--- when the user clicks the Submit Button, these events occur: Add a Record; or, Update a Record --->

<cfif IsDefined("FORM.doSave")>


<!--- if a value for form.Contractor_ID exists, then update the record --->
   
<cfif len(form.Contractor_ID)>

<!--- We are updating two tables at the same time, so enclose the Update statements in cftransaction --->
<cftransaction>

    <cfquery name="UpdateRecord" datasource="#application.datasource#">
	  UPDATE PNWContractors
	  SET Contractor_Name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
           Contractor_Address = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
           Contractor_Address2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
           Contractor_City = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
           Contractor_State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
           Contractor_Zip = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,40))#">, 
           Contractor_Email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
           Contractor_ContactName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
           Contractor_Phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
           Contractor_Website = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
            Contractor_Inactive = <cfqueryparam cfsqltype="cf_sql_bit"  value="#Trim(form.Contractor_Inactive)#">,
            UpdateTimeStamp = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
	  WHERE Contractor_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ID,36))#">
			</cfquery>
            
            
  <!----- delete all Contractor_ID, Work_Type_ID from PNWContractorsCat ---->  
        <cfquery name="DeletePNWContractorsCat" datasource="#application.datasource#">  
           DELETE FROM PNWContractorsCat  
           WHERE Contractor_ID = <cfqueryparam value="#form.Contractor_ID#" cfsqltype="cf_sql_varchar">  
        </cfquery>  
            
            <!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#form.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
		WHERE  Work_Type_ID  IN ( 
            <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
                 )
   </cfquery> 

<!--- close cftransaction --->
</cftransaction>


<!--- ELSE: if a value for form.IntWork_Type_ID does not exist, then create a record --->
				<cfelse> 
                
       
       <!--- We are updating two tables at the same time, so enclose the Update statements in cftransaction --->
<cftransaction>
<!--- statement to insert new record into table PNWContractors --->
			<cfquery name="InsertPNWCat" datasource="#application.datasource#" result="newPNWContractors">
				 INSERT INTO PNWContractors
     					(
                        Contractor_Name
      				   ,Contractor_Address
    			       ,Contractor_Address2
    			       ,Contractor_City
			           ,Contractor_State
			           ,Contractor_Zip
			           ,Contractor_Email
			           ,Contractor_ContactName
			           ,Contractor_Phone
			           ,Contractor_Website
			           ,Contractor_Inactive
			           ,InsertTimeStamp
                        )
			     VALUES(
	          	<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Name,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Address2,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_City,50))#">,  
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,50))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_State,40))#">, 
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Email,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_ContactName,255))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Phone,20))#">,
				<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.Contractor_Website,100))#">,
				<cfqueryparam cfsqltype="cf_sql_bit" value="#val(form.Contractor_Inactive)#">,
				<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
				         )         
					</cfquery>
      
			<!--- INSERT new values Contractor_ID, Work_Type_ID --->
            <!--- into relational table PNWContractorsCat --->
            <!--- Remember that columns Contractor_ID and Work_Type_ID are UUIDs, not integers --->
   <cfquery name="UpdateCategory" datasource="#application.datasource#">   
     INSERT INTO PNWContractorsCat ( 
     		Contractor_ID
        	,Work_Type_ID  
        )
     SELECT <cfqueryparam value="#FORM.Contractor_ID#" cfsqltype="cf_sql_varchar">
	      	,Work_Type_ID  
     FROM    PNWCat 
		WHERE  Work_Type_ID  IN ( 
                     ( <cfqueryparam value="#form.Work_Type_ID#" list="true" cfsqltype="cf_sql_varchar"> )
                 )
   </cfquery>          
   
<!---  use result of query UpdateCategory to look up new record's UUID --->    

     <cfquery name="getNewUUID"  datasource="#application.datasource#">        
            SELECT  Work_Type_ID
            FROM     PNWContractorsCat    
            WHERE  IntWork_Type_ID = #val(newPNWContractorsUUID.IDENTITYCOL)#
         </cfquery>

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

    </cftransaction>


              
<!--- END queries to update or insert database records ---> 

<!--- END cfif val(form.Int_Contractor_ID) --->
					    </cfif>  


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

<!--- record updated? return to edit page --->
<cfif val(url.Int_Contractor_ID)>
<cflocation url="/admin/editContractors.cfm?IntWork_Type_ID=#url.IntWork_Type_ID#" addtoken="yes">

<!--- record created? return to main admin page that lists all records --->
<cfelse>                     
<cflocation url="/admin/manageContractors.cfm" addtoken="no">
				     
</cfif>

             
<!--- 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>
                 <cfrethrow>
            </cfcatch>

<!--- END CFTRY --->  
			</cftry>
       
       
<!--- fetch the data from the database only when there are no errors; let the form variables pass back from the data table into the form to display ---->
 
<cfif len(variables.error) eq 0>
    
<!--- get data from tables PNWContractors, PNWCat, PNWContractorsCat and convert the data into form variables --->
	  <cfquery name="getRecordDetails" datasource="#application.datasource#">
	      SELECT
           con.Contractor_ID
           ,Contractor_Name
           ,Contractor_Address
           ,Contractor_Address2
           ,Contractor_City
           ,Contractor_State
           ,Contractor_Zip
           ,Contractor_Email
           ,Contractor_ContactName
           ,Contractor_Phone
           ,Contractor_Website
           ,Contractor_Inactive
           ,con.InsertTimeStamp
           ,con.UpdateTimeStamp
           ,con.Int_Contractor_ID
           ,cat.Work_Type_ID
           ,cat.Work_Type
           ,cat.Work_Type_Description
           ,cat.Display_Color
           ,cat.IntWork_Type_ID 
           ,cc.Contractor_ID
           ,cc.Work_Type_ID   
       
           FROM PNWContractors con
           
           LEFT OUTER JOIN PNWContractorsCat cc
           ON con.Contractor_ID = cc.Contractor_ID
           
           LEFT OUTER JOIN PNWCat cat
           ON cat.Work_Type_ID = cc.Work_Type_ID
           
WHERE con.Contractor_ID = <cfqueryparam value="#URL.Contractor_ID#" cfsqltype="cf_sql_varchar">
 			  </cfquery>

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


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

<cfif len(variables.error)> 
			 <cfoutput>
			 <div style="border: 1px solid red; padding: 10px; margin:20px; width:400px;">#variables.error#</div>
			 </cfoutput>
</cfif>


<!----- if record already exists (it will have a Int_Contractor_ID) then update record; otherwise, add new record ----->
				<cfif val(url.Contractor_ID)>
					  <cfset FormTitle="Update Contractor Record">
					  <cfset ButtonText="Update This Contractor Record">
				<cfelse>
						<cfset FormTitle="Create Contractor Record">
						<cfset ButtonText="Create Contractor Record">

				</cfif>

       
       
       <!--- BEGIN HTML / CSS PAGE HEADER --->
<cfinclude template="/admin/admin_header.cfm">

<p><strong>Manage:</strong>

<a href="/admin/manageCategory.cfm">Work Categories</a>

<img src="/admin/img/bronzestar.gif" width="14" height="13" alt="star" /> <a href="/admin/manageContractors.cfm">Contractors</a>

<img src="/admin/img/bronzestar.gif" width="14" height="13" alt="star" /> <a href="/admin/managePages.cfm">Return to Admin</a>

<img src="/admin/img/bronzestar.gif" width="14" height="13" alt="star" /> <a href="/logout.cfm">Log Out</a>

</p>


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

      <!--- did CF add a token to the URL? then show friendly Record Updated message --->

<cfif len(url.cftoken)> 

			 <div class="recordupdated">Record updated.</div>
</cfif>



	<!--- Add or Update Record Form begins here --->
<cfform method="post" enctype="multipart/form-data" scriptsrc="#Request.CFFORM_JS_LIB#">
                
 
 <!--- Embed IntWork_Type_ID (column IntWork_Type_ID is PK) to assign a value to it --->
 <cfoutput>
<input type="hidden" name="Int_Contractor_ID" value="#form.Int_Contractor_ID#" />
   </cfoutput>


<div class="edit_page_title">

<div class="float-left">
<cfoutput>
<h2>#FormTitle#</h2>
</cfoutput>
</div>

<cfoutput query="ListContractors" group="Contractor_ID">

<div class="float-right"><p><strong>Created:</strong> #DateFormat(ListContractors.InsertTimeStamp, "mmmm d, yyyy")# | <strong>Modified:</strong> #DateFormat(ListContractors.UpdateTimeStamp, "mmmm d, yyyy")#</p></div>

</cfoutput>
</div>

<div class="clear-both"></div>

<div class="float-left-img width300px">
   
  <p><strong>Contractor Name:</strong>
  	 <cfinput
     		type="text"
			name="Contractor_Name"
			value="#Trim(form.Contractor_Name)#"
            message="Please enter a Contractor Name; maximum length is 255 characters."
            required="Yes"
            validateAt="onSubmit,onServer" 
			size="36"
			maxlength="255"
            tabindex="1" /></p>
            
            <p><strong>Address Line 1:</strong>
            <cfinput
            	type="text"
                size="20"
                name="Contractor_Address"
                value="#Trim(form.Contractor_Address)#"
                message="Please enter an address; maximum length is 50 characters."
            required="Yes"
            validateAt="onSubmit,onServer" 
                maxlength="50"
                tabindex="2" /></p>
                
                    <p><strong>Address Line 2:</strong>
            <cfinput
            	type="text"
                size="20"
                name="Contractor_Address2"
                value="#Trim(form.Contractor_Address2)#"
                maxlength="50"
                tabindex="3" /></p>
                
                

                <p><strong>Contractor Email:</strong><br />

            <cfinput
            	type="text"
                size="20"
                name="Contractor_Email"
                value="#Trim(form.Contractor_Email)#"
                maxlength="255"
                tabindex="7" /></p>
            
    <p><strong>Contractor Contact Name:</strong>
  	 <cfinput
     		type="Text"
			name="Contractor_ContactName"
			value="#Trim(form.Contractor_ContactName)#"
			size="36"
			maxlength="255"
            tabindex="8" /></p>
            
            
           <p><strong>Telephone or Voice Number:</strong>
  	 <cfinput
     		type="Text"
			name="Contractor_Phone"
			value="#Trim(form.Contractor_Phone)#"
			size="20"
			maxlength="20"
            tabindex="9" /></p>     
  
  
      <p><strong>Contractor Web Site:</strong>
  	 <cfinput
     		type="Text"
			name="Contractor_Website"
			value="#Trim(form.Contractor_Website)#"
			size="36"
			maxlength="100"
            tabindex="9" /></p>
            
         
         
<p><strong>Mark Contractor Inactive:</strong> 
       <cfinput type="checkbox" name="Contractor_Inactive" value="1" checked="#val(ListContractors.Contractor_Inactive)#" tabindex="10" />  </p>


 <!--- query getAllCategories requests all work categories --->       
  <cfquery datasource="#application.datasource#" name="getAllCategories">
       SELECT  Work_Type_ID, Work_Type
       FROM    PNWCat cat
       ORDER BY Work_Type
  </cfquery>

       
       
 <!--- query getSelectedCategories requests already assigned to the current contractor --->
  <cfquery datasource="#application.datasource#" name="getSelectedCategories">
     SELECT Work_Type_ID
     FROM   PNWContractorsCat 
     WHERE Contractor_ID = <cfqueryparam value="#form.Contractor_ID#" cfsqltype="cf_sql_varchar">
</cfquery>

  
  
<!--- convert assigned Contractor_ID values into a comma separated value list --->
     <cfset selectedWorkTypes = valueList(getSelectedCategories.Work_Type_ID)>

          
 <p><strong>Select Contractor Work Categories:</strong><br />
 <span class="smallred">Press the Ctrl or Apple-Command key (on your keyboard) and select multiple categories with your mouse cursor.</span><br />

 <cfoutput>
<cfselect class="adminSelect" size="19" name="Work_Type_ID" value="Work_Type_ID" display="Work_Type" multiple="yes" query="getAllCategories" queryPosition="below" selected="#selectedWorkTypes#" style="width:250px;" tabindex="11">
</cfselect>
 </cfoutput>     
    	 </p>       
  
                
</div>

<div class="float-left-img width300px">            
                
          <p><strong>City:</strong>
            <cfinput
            	type="text"
                size="20"
                name="Contractor_City"
                value="#Trim(form.Contractor_City)#"
                maxlength="50"
                tabindex="4" /></p>

          
<!--- this query selects State Titles from tbl_NBPTS_Lookup_State --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT  State_Post_Abbr
        FROM    tbl_NBPTS_Lookup_State
</cfquery>


<p><strong>State:</strong><br />



<span class="smallred">The state will be pre-selected for an existing contractor record.</span>

</p>
     <cfselect size="20" class="adminSelect" name="Contractor_State" value="State_Post_Abbr" display="State_Post_Abbr" multiple="no" query="GetStates" queryPosition="below" selected="#getRecordDetails.Contractor_State#" tabindex="5">
     <option value=""> Select State: </option>
     </cfselect>
     
     <p>&nbsp;</p>
     
     
     
     
            <p><strong>ZIP or Postal Code:</strong><br />

            <cfinput
            	type="text"
                size="10"
                name="Contractor_Zip"
                value="#Trim(form.Contractor_Zip)#"
                maxlength="40"
                tabindex="6" /></p>
  
     
     </div>
     
     
           

<div class="clear-both"></div>


<!--- submit form to ColdFusion for processing; this is the DoSave function, which will add or edit a record --->

<div class="edit_button">
  <cfoutput>
      <input name="doSave" type="submit" value="#ButtonText#" /> 
  </cfoutput>
  </div>

</cfform>




<!--- Page footer --->
<cfinclude template="/admin/admin_footer.cfm">

Open in new window

Author

Commented:
I've found a typo in my code, and also a mismatch between

<cfquery name="InsertPPNWContractors" datasource="#application.datasource#" result="newPNWContractors">

and

#val(newPNWContractorsUUID.IDENTITYCOL)#

I think I can figure this out.

Author

Commented:
I got it to insert a record. Now, an error with the update procedure. But: progress.

Author

Commented:
OK, the application updates and inserts without error. I would say YAHOO except I am not completely 100% why this works. So I am studying this. And I might ask a couple of questions to make this work in my head.

=)

I hope your Friday evening is going wonderful and has nothing at all to do with ColdFusion.

E

Author

Commented:
It is working really well, and I understand it.

At the beginning of the application I had to set these parameters (in code below).

url.Contractor_ID
form.Contractor_ID
url.Work_Type_ID
form.Work_Type_ID

Then I had to proofread carefully and fix two typos in the code. Then it worked. I can create and edit records.

Next I am working on a search interface for these records, but I have a good start on that and I think I know how wrap it up.

=)
<!--- Set default value for column Contractor_ID in table PNWContractors --->
<!--- column Contractor_ID in PNWContractors is a UUID, datatype varchar(36) --->

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

<!--- Define Contractor_ID in scope FORM, then set form.Contractor_ID equal to the Contractor_ID passed in the URL --->
<!--- This steps allows us to insert a new value in column Contractor_ID in table PNWContractors --->

<cfparam name="form.Contractor_ID" default="#url.Contractor_ID#">



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

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

Open in new window

Author

Commented:
Thanks as always to _agx_. I learned some really valuable skills these past few days.

Here's to a ColdFusion-free weekend. Take care.

Eric
Most Valuable Expert 2015

Commented:
Sorry I was off doing stuff :)  Glad you worked it out.

Honestly I didn't understand the purpose of some of the defaults, like these for example. But I haven't run the whole thing. So it may become clear later.

      <!--- Set default values for variables in getRecordDetails --->
     <cfparam name="getRecordDetails.Contractor_Name" default="">
     <cfparam name="getRecordDetails.Contractor_Address" default="">
     <cfparam name="getRecordDetails.Contractor_Address2" default="">
     <cfparam name="getRecordDetails.Contractor_City" default="">

>> Then it worked. I can create and edit records.

Great.  So what did you end up using: UUID or the integer ID?  

Author

Commented:
It was good for me to stick to it and figure out that last part for myself.

I edit and create records using UUID.

      <!--- Set default values for variables in getRecordDetails --->
     <cfparam name="getRecordDetails.Contractor_Name" default="">
     <cfparam name="getRecordDetails.Contractor_Address" default="">
     <cfparam name="getRecordDetails.Contractor_Address2" default="">
     <cfparam name="getRecordDetails.Contractor_City" default="">

I thought I needed these variables to set default values for the getRecordDetails query?

<!--- get data from tables PNWContractors, PNWCat, PNWContractorsCat and convert the data into form variables --->
	  <cfquery name="getRecordDetails" datasource="#application.datasource#">
	      SELECT
           con.Contractor_ID
           ,Contractor_Name
           ,Contractor_Address
           ,Contractor_Address2
           ,Contractor_City
           ,Contractor_State
           ,Contractor_Zip
           ,Contractor_Email
           ,Contractor_ContactName
           ,Contractor_Phone
           ,Contractor_Website
           ,Contractor_Inactive
           ,con.InsertTimeStamp
           ,con.UpdateTimeStamp
           ,con.Int_Contractor_ID
           ,cat.Work_Type_ID
           ,cat.Work_Type
           ,cat.Work_Type_Description
           ,cat.Display_Color
           ,cat.IntWork_Type_ID 
           ,cc.Contractor_ID
           ,cc.Work_Type_ID   
       
           FROM PNWContractors con
           
           LEFT OUTER JOIN PNWContractorsCat cc
           ON con.Contractor_ID = cc.Contractor_ID
           
           LEFT OUTER JOIN PNWCat cat
           ON cat.Work_Type_ID = cc.Work_Type_ID
           
WHERE con.Contractor_ID = <cfqueryparam value="#URL.Contractor_ID#" cfsqltype="cf_sql_varchar">
 			  </cfquery>

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

Open in new window

Author

Commented:
Although when I take away the default values:
      <!--- Set default values for variables in getRecordDetails --->
     <cfparam name="getRecordDetails.Contractor_Name" default="">
     <cfparam name="getRecordDetails.Contractor_Address" default="">
     <cfparam name="getRecordDetails.Contractor_Address2" default="">
     <cfparam name="getRecordDetails.Contractor_City" default="">

...etc...

and update a record, there is no error, even if I leave some fields blank.

I will leave them out. I think the regular defaults in scope form are working to to supply default values "" for empty fields when I update:
<cfparam name="form.Contractor_Name" default="">
<cfparam name="form.Contractor_Address" default="">
<cfparam name="form.Contractor_Address2" default="">
<cfparam name="form.Contractor_City" default="">
<cfparam name="form.Contractor_State" default="">
Most Valuable Expert 2015

Commented:
>> I thought I needed these variables to set default values for the getRecordDetails query?

As long you run the SELECT query, whatever columns you selected will always exist.  The query may be empty, but you won't get an error if you reference the columns

            #getRecordDetails.Contractor_Name#

I haven't looked that closely but you shouldn't need to set defaults for a query,  unless it's not always executing .. in which case I'd start questioning the logic ;)