Avatar of JohnMac328
JohnMac328
Flag for United States of America asked on

CF - Form Update

I have two forms on a page.  The first one has a select box that the user picks the employee record from.  The second one displays the details of the record.  I am try to add a edit button so they can change a value.  When I click the edit button, the page refreshes and then another choosing of an employee is required.  If you bring up the same employee the record has not changed.  
Any help is appreciated.

<cfparam name="form.ID" default="">
<cfparam name="form.totalRecords" default=0>
<cfquery name="getEmployees" datasource="datasource">
SELECT     ID, client_id, LTRIM(RTRIM(First_Name))+ ' ' + LTRIM(RTRIM(Last_Name)) As FullName , active_account
FROM         employee_data
WHERE     (active_account = 1) 
ORDER BY  First_Name, Last_Name
</cfquery>
</head>
<body>
<div id="container">
  <div id="content">
    <div id="content_main">
      <div id="header"></div>
      <h1>Performance Evaluations</h1>
      <cfform>
        <!--- change the employee viewed ---->
        
        <table class="displayTable" width="600">
        
          <th>Select Employee</th>
        <tr>
          <td width="186"><cfselect name="ID"   query="getEmployees" display="FullName" value="ID" selected="#form.ID#" onchange="this.form.submit();"> </cfselect></td>
        </tr>
      </cfform>
      <cfif val(form.ID) eq 0>
        <h3>Please select an employee</h3>
        <cfexit>
      </cfif>
      <cfquery name="getAnswers" datasource="datasource">
SELECT     TOP (41) EmployeeQuestions.QuestionNumber, EmployeeQuestions.Question, EmployeeResponse.UserResponse, EmployeeResponse.EmployeeID, 
                      employee_data.ID
FROM         EmployeeQuestions INNER JOIN
                      EmployeeResponse ON EmployeeQuestions.QuestionNumber = EmployeeResponse.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponse.EmployeeID = employee_data.ID
 WHERE     EmployeeResponse.EmployeeID = #val(form.ID)#
</cfquery>
      <cfif getAnswers.recordCount eq 0>
        <h3>Selected employee has not filled out their evaluation</h3>
        <cfexit>
      </cfif>
      <cfif isDefined("FORM.editing")>
        <cfquery datasource="#datasource#">
		UPDATE EmployeeResponse
		SET UserResponse='#FORM.UserResponse#'
where EmployeeID=#FORM.ID# and QuestionNumber=#FORM.QuestionNumber#
	</cfquery>
      </cfif>
      <p align="center"><img src="images/ratings.jpg" width="762" height="50" />
      <table  class="displayTable" border="0"  align="center"cellpadding="0" cellspacing="0">
        <thead>
          <tr>
            <th>Number</th>
            <th>Question</th>
            <th>Answer</th>
          </tr>
        </thead>
        <tbody>
        <cfform action="http://localhost/Evaluation/quiz_evaltest.cfm"  method="post">
          <cfoutput query="getAnswers">
            <cfset rowNum = getAnswers.currentRow>
            <cfinput type="hidden" name="totalRecords" value="#rowNum#">
            <tr>
              <td>#QuestionNumber#</td>
              <td>#Question#</td>
              <td><cfinput type="text" name="UserResponse" value="#UserResponse#" size="2">
                <cfinput type="hidden" name="QuestionNumber#rowNum#" value="#getAnswers.QuestionNumber#"></td>
              <td><cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"></td>
            </tr>
          </cfoutput>
        </cfform>

Open in new window

ColdFusion LanguageMicrosoft SQL Server

Avatar of undefined
Last Comment
JohnMac328

8/22/2022 - Mon
Bruce Cadiz

It looks like you have your database "update block" in the wrong place (causing you to not see the update). See if this doesn't fix it.

<cfparam name="form.ID" default="" />
<cfparam name="form.totalRecords" default=0 />

<!--- Start Employee Select Form Query--->
<cfquery name="getEmployees" datasource="datasource" />
	SELECT ID, 
	       client_id, 
	       LTRIM(RTRIM(First_Name))+ ' ' + LTRIM(RTRIM(Last_Name)) As FullName , 
	       active_account
	  FROM employee_data
	 WHERE  (active_account = 1) 
         ORDER BY  First_Name, Last_Name
</cfquery>
<!--- End Employee Select Form Query--->
</head>

<body>
<div id="container">
  <div id="content">
    <div id="content_main">
      <div id="header"></div>
      <h1>Performance Evaluations</h1>
      
      <!--- Start Employee Select Form --->
      <cfform>
        <!--- change the employee viewed --->
        <table class="displayTable" width="600">
          <th>Select Employee</th>
        <tr>
          <td width="186">
          
          <cfselect name="ID"  
                    query="getEmployees" 
                    display="FullName" 
                    value="ID" 
                    selected="#form.ID#" 
                    onchange="this.form.submit();">
          </cfselect>
          </td>
        </tr>
      </cfform>
      <!--- End  Employee Select Form --->
      
      <!--- Start Error Message Block --->
      <cfif val(form.ID) eq 0>
        <h3>Please select an employee</h3>
        <cfexit>
      </cfif>
      <!--- End  Error Message Block --->
      
      <!--- Start Database Update Block --->
      <cfif isDefined("FORM.editing")>
      	<cfquery datasource="#datasource#">
      		UPDATE EmployeeResponse
      		SET UserResponse='#FORM.UserResponse#'
      	      WHERE EmployeeID=#FORM.ID# and QuestionNumber=#FORM.QuestionNumber#
      	</cfquery>
      </cfif>
      <!--- End Database Update Block --->
      
      <!--- Start Answers Query / Check / Response --->
      <cfquery name="getAnswers" datasource="datasource">
	         SELECT TOP (41) 
	                EmployeeQuestions.QuestionNumber, 
	                EmployeeQuestions.Question, 
	                EmployeeResponse.UserResponse, 
	                EmployeeResponse.EmployeeID, 
			employee_data.ID
	           FROM EmployeeQuestions 
	          INNER JOIN EmployeeResponse ON EmployeeQuestions.QuestionNumber = EmployeeResponse.QuestionNumber INNER JOIN employee_data ON EmployeeResponse.EmployeeID = employee_data.ID
	 WHERE EmployeeResponse.EmployeeID = #val(form.ID)#
      </cfquery>
       
      <cfif getAnswers.recordCount eq 0>
      
        <h3>Selected employee has not filled out their evaluation</h3>
        <cfexit>
        
      </cfif>
      
      <p align="center"><img src="images/ratings.jpg" width="762" height="50" />
      
      <table  class="displayTable" border="0"  align="center"cellpadding="0" cellspacing="0">
        <thead>
          <tr>
            <th>Number</th>
            <th>Question</th>
            <th>Answer</th>
          </tr>
        </thead>
        <tbody>
        <!--- End Answers Query / Check / Response --->
        
        <!--- Start Answers Form --->
        <cfform action="http://localhost/Evaluation/quiz_evaltest.cfm"  method="post">
          <cfoutput query="getAnswers">
            <cfset rowNum = getAnswers.currentRow>
            <cfinput type="hidden" name="totalRecords" value="#rowNum#" />
            <tr>
              <td>#QuestionNumber#</td>
              <td>#Question#</td>
              <td>
              <cfinput type="text" name="UserResponse" value="#UserResponse#" size="2" />
              <cfinput type="hidden" name="QuestionNumber#rowNum#" value="#getAnswers.QuestionNumber#" />
              </td>
              <td>
              <cfinput type="submit" name="editing" value="EDIT" style="font-size:xx-small" />
	      </td>
            </tr>
          </cfoutput>
        </cfform>
	<!--- End Answers Form --->        
                

Open in new window


Just an observation, putting all your code / logic in one file make this hard to follow structurally. You may want to re-think how you're approaching this so it is more maintainable (especially for others who have to work with it)
JohnMac328

ASKER
Still does not update - I am open to suggestions.  I have to be able to select an employee and have their related records come up for possible editing.
Bruce Cadiz

Have you considered using multiple pages / frames?

Example empSelect.cfm -> empDisplayEdit.cfm -> empUpdate.cfm
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
JohnMac328

ASKER
Looks like that is what I will have to do - I will do a little research
Bruce Cadiz

What you have can work but it has to be structured properly with some embedded logic using conditional statement i.e.

(pseudo code)

<cfif form_action = "" >

 <!--- first entry into form no form variables passed -->
 retrieve employee list select
 form_action = "formView"

<cfelseif form_action = "formView" >

  <!--- entry into form from user select --->
   retrieve user data based on id
   display edit form
   form_action = formEdit

<cfelseif form_action = "formEdit">
  
  <!--- entry into form after edit --->
   update user data based on id
   display status of update (success/failure)
   retrieve user data based on id for confirmation of update
   retrieve employee list select for next update
   form_action = formView
<cfelse>
<!--- handle other sitiaution here --->

 </cfif>

Open in new window


....
JohnMac328

ASKER
I created another page to do the edit - the employee ID gets passed correctly and does automatically fill in the employee name in the select box.  However the detail records do not show - even if I hard code the employee ID

<cfform>
        <!--- change the employee viewed ---->
       
        <table class="displayTable" width="600">
       
          <th>Select Employee</th>
        <tr>
          <td width="186"><cfselect name="ID"   query="getEmployees" display="FullName" value="ID" selected="#url.employeeID#" onchange="this.form.submit();"> </cfselect></td>
        </tr>
      </cfform>

 <cfform>
        <!--- change the employee viewed ---->
        
        <table class="displayTable" width="600">
        
          <th>Select Employee</th>
        <tr>
          <td width="186"><cfselect name="ID"   query="getEmployees" display="FullName" value="ID" selected="#url.employeeID#" onchange="this.form.submit();"> </cfselect></td>
        </tr>
      </cfform>
      <cfif val(form.ID) eq 0>
        <h3>Please select an employee</h3>
        <cfexit>
      </cfif>
            <cfif isDefined("FORM.editing")>
        <cfquery datasource="#Objects#">
		UPDATE EmployeeResponse
		SET UserResponse='#FORM.UserResponse#'
where EmployeeID=#FORM.ID# and QuestionNumber=#FORM.QuestionNumber# 
    </cfquery>
      </cfif>
      <cfquery name="getAnswers" datasource="Objects">
SELECT     TOP (41) EmployeeQuestions.QuestionNumber, EmployeeQuestions.Question, EmployeeResponse.UserResponse, EmployeeResponse.EmployeeID, 
                      employee_data.ID
FROM         EmployeeQuestions INNER JOIN
                      EmployeeResponse ON EmployeeQuestions.QuestionNumber = EmployeeResponse.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponse.EmployeeID = employee_data.ID
 WHERE     EmployeeResponse.EmployeeID = #url.employeeID#
</cfquery>

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JohnMac328

ASKER
And the rest of the form that is supposed to display the detail records

 <cfform>
          <cfoutput query="getAnswers">
            <cfset rowNum = getAnswers.currentRow>
            <cfinput type="hidden" name="totalRecords" value="#rowNum#">
            <tr>
              <td>#QuestionNumber#</td>
              <td>#Question#</td>
              <td><cfinput type="text" name="UserResponse" value="#UserResponse#" size="2">
                <cfinput type="hidden" name="QuestionNumber#rowNum#" value="#getAnswers.QuestionNumber#"></td>
              <td><cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"></td>
            </tr>
          </cfoutput> 
        </cfform>

Open in new window

Bruce Cadiz

Do you have data in the database for the specific user(s) / does it display input fields even if the user hasn't started / completed a questionnaire or survey answers?

If not (i.e. no records are entered yet) you'll have to handle that with an outer join on your employee table so you actually get a result set.
JohnMac328

ASKER
Yes it displays when there are no records - it's acting strange - when I select another name it refreshes and the original person is displayed - still does not update the record though
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
JohnMac328

ASKER
I got it working to a point - it updates the record but goes back to the main list after the form update processes - i am putting in this cflocation code to get it to stay with the same employee after the form updates but it is not working - any ideas?

<cfif isDefined("FORM.editing")>
      <cfquery datasource="#datasource#">
UPDATE EmployeeResponse
        SET UserResponse='#FORM.UserResponse#'
where EmployeeID=#FORM.EmployeeID# AND QuestionNumber=#FORM.QuestionNumber#
      </cfquery>
<cflocation url = "http://localhost/Evaluation/VideoListEdit_Evaluation.cfm?employeeID=#ID#">
</cfif>
ASKER CERTIFIED SOLUTION
Bruce Cadiz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JohnMac328

ASKER
I am trying something different - I was hoping I could just have one form at a time  performing the action or at least get it to go back to the same record.

<cfparam name="form.ID" default="">

      <cfquery name="getEmployees" datasource="#datasource#">
SELECT     ID, client_id, LTRIM(RTRIM(First_Name))+ ' ' + LTRIM(RTRIM(Last_Name)) As FullName , active_account
FROM         employee_data
WHERE     (active_account = 1) 
ORDER BY  First_Name, Last_Name
</cfquery>     
           
<cfif isDefined("FORM.editing")>
	<cfquery datasource="#datasource#">
UPDATE EmployeeResponse
        SET UserResponse='#FORM.UserResponse#'
where EmployeeID=#FORM.EmployeeID# AND QuestionNumber=#FORM.QuestionNumber#
	</cfquery>
<cflocation url = "http://localhost/Evaluation/VideoListEdit_Evaluation.cfm?employeeID=#form.ID#">
</cfif>

<cfquery name="getDetails" datasource="#datasource#">
SELECT     TOP (41) EmployeeQuestions.QuestionNumber, EmployeeQuestions.Question, EmployeeResponse.UserResponse, EmployeeResponse.EmployeeID, 
                      employee_data.ID
FROM         EmployeeQuestions INNER JOIN
                      EmployeeResponse ON EmployeeQuestions.QuestionNumber = EmployeeResponse.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponse.EmployeeID = employee_data.ID
 WHERE     EmployeeResponse.EmployeeID = #val(form.ID)#
</cfquery>


<cfform>
        <!--- change the employee viewed ---->
        
        <table class="displayTable" width="600">
        
          <th>Select Employee</th>
        <tr>
          <td width="186"><cfselect name="ID"   query="getEmployees" display="FullName" value="ID" selected="#form.ID#" onchange="this.form.submit();"> </cfselect></td>
        </tr>
      </cfform>

<table class="displayTable">
<tr>
	<tr>
	<th>ID</th>
	<th>Video</th>
	<th>Length</th>
    <th>Credits</th>
    <th>Availability</th>
</tr>
</tr>
<cfoutput query="getDetails">
<cfform>
<cfinput type="hidden" name="EmployeeID" value="#EmployeeID#"> 
<tr>
		<td>
		<cfinput type="text" name="QuestionNumber"
			value="#QuestionNumber#" size="3">
		</td>
		<td>
		<cfinput type="text" name="Question"
			value="#Question#" size="70">
		</td>
		<td>
		<cfinput type="text" name="UserResponse"
			value="#UserResponse#" size="3">
		</td>
		<td>&nbsp;</td>
         <td>&nbsp;</td>
		<td>
		<cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"> 
       	  </td>
 		</tr>
</cfform>
</cfoutput>
    </table>

Open in new window

JohnMac328

ASKER
Ok - I have the two page system now - it goes to the next page and passes the ID but does not display the records.  When I hard code '15' (the formID) in the getDetails query it does display the records and edits correctly.  Why won't it take the passed id? Here is the url that is passed and the code

http://localhost/Evaluation/VideoListEdit_EvaluationEdit.cfm?employeeID=15

<cfparam name="form.ID" default="">

  
           
<cfif isDefined("FORM.editing")>
	<cfquery datasource="#datasource#">
UPDATE EmployeeResponse
        SET UserResponse='#FORM.UserResponse#'
where EmployeeID=#FORM.EmployeeID# AND QuestionNumber=#FORM.QuestionNumber#
	</cfquery>
</cfif>

<cfquery name="getDetails" datasource="#datasource#">
SELECT     TOP (41) EmployeeQuestions.QuestionNumber, EmployeeQuestions.Question, EmployeeResponse.UserResponse, EmployeeResponse.EmployeeID, 
                      employee_data.ID
FROM         EmployeeQuestions INNER JOIN
                      EmployeeResponse ON EmployeeQuestions.QuestionNumber = EmployeeResponse.QuestionNumber INNER JOIN
                      employee_data ON EmployeeResponse.EmployeeID = employee_data.ID
 WHERE     EmployeeResponse.EmployeeID = #val(form.ID)#
</cfquery>

<cfoutput query="getDetails">
<cfform>
<cfinput type="hidden" name="EmployeeID" value="#EmployeeID#"> 
<cfinput type="hidden" name="ID" value="#ID#"> 
<tr>
		<td>
		<cfinput type="text" name="QuestionNumber"
			value="#QuestionNumber#" size="3">
		</td>
		<td>
		<cfinput type="text" name="Question"
			value="#Question#" size="70">
		</td>
		<td>
		<cfinput type="text" name="UserResponse"
			value="#UserResponse#" size="3">
		</td>
		<td>&nbsp;</td>
         <td>&nbsp;</td>
		<td>
		<cfinput type="submit" name="editing"
			value="EDIT" style="font-size:xx-small"> 
       	  </td>
 		</tr>
</cfform>
</cfoutput>

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
JohnMac328

ASKER
Wait - got it

I needed this

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

and this in the query

 EmployeeResponse.EmployeeID = #url.employeeID#
Bruce Cadiz

Nice, so all is working as desired now?
JohnMac328

ASKER
Yes but I don't know what question to select
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23