Solved

Cold Fusion - Form to update data

Posted on 2009-05-06
34
247 Views
Last Modified: 2012-05-06
I have a form that retrieves detail records, I then need to edit one of the fields.  I am having trouble getting the from to update the recordset.  Any help is greatly appreciated.

Thanks,
John
<!--- let's set another var that we'll check for at out put time instead of checking for form.employeeID --->

<cfset formsubmitted = false>

 

<cfif structkeyexists(form, 'employeeID')><!--- only execute if form has been submitted --->
 
 

 <cfquery name="getChecked_Out_Videos" datasource="#datasource#">

 SELECT Employees.EmployeeID, Employees.Email, Detail.VideoID, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date, Detail.Watched

FROM VideoList INNER JOIN (Employees INNER JOIN Detail ON Employees.EmployeeID = Detail.EmployeeID) ON VideoList.VideoID = Detail.VideoID

WHERE  Employees.EmployeeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.employeeID#"> order by Detail.In_Date 

</cfquery>
 

<!--- assert: form has been submitted. set formsubmitted to true --->

<cfset formsubmitted = true>

 

</cfif>
 

<cfif  isDefined("FORM.Edit")>

<cfquery datasource="#datasource#">

		UPDATE Detail

		SET EmployeeID='#FORM.EmployeeID#',

            In_Date='#FORM.In_Date#'

where EmployeeID=#FORM.EmployeeID#

</cfquery>
 
 

</cfif>
 
 

<cfparam name="form.employeeID" default="1">

 

<cfquery name="getAllEmployees" datasource="#datasource#">

SELECT  EmployeeID, First_Name, Last_Name, Email

FROM    Employees

ORDER BY Last_Name, First_Name

</cfquery>

 

<cfform action="#CGI.SCRIPT_NAME#" method="post" format="html">

        

        <table width="956">

       <tr>

        <td width="186">

       <cfselect name="employeeID" query="getAllEmployees" display="email" value="employeeID" selected="#form.employeeID#" onchange="this.form.submit();">

       </cfselect>

    </td>

    </tr>

 

<!--- check if the form has been submitted and show query output --->

<cfif formsubmitted is true>

          

                 

        <cfoutput query="getChecked_Out_Videos">

        <tr>

          <td>#EmployeeID#</td>

          <td width="100">#Email#</td>

          <td width="198">#VideoID#</td>

          <td width="198">#Video_Name#</td>

          <td width="154">#Out_Date#</td>

          <td width="177"><input type="text" name="In_Date" value="#In_Date#"></td> 

       <td width="136">

		<input type="submit" name="edit"

			value="EDIT" style="font-size:xx-small"> 

		</td>

       </tr>

         </cfoutput>

</cfif>

        

 </table>       

        

        

<input type="submit" name="submitButton"  value="Submit">

</cfform>

Open in new window

0
Comment
Question by:JohnMac328
  • 18
  • 16
34 Comments
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
You have two type="submit" buttons inside of a CFFORM which can be problematic.   I suggest removing one of them.

Your test for the update is this...

<cfif  isDefined("FORM.Edit")>

shouldn't it be..

<cfif  isDefined("FORM.submitButton")>

You also have this test..
<cfif formsubmitted is true>

and this test..
<cfif structkeyexists(form, 'employeeID')>


It looks like you're trying to do more than just save a form that's open for editing.  Can you give more detail .. ?
0
 

Author Comment

by:JohnMac328
Comment Utility
It is confusing.  I got it to display the records which is multiple records for one employee and then found another example of adding a form name with the name "edit".  The example also had a insert and delete button on the same form with different form names so I thought it would work.  At this point I am trying to edit one field of a detail record after they are displayed.  To be more specific, adding a "in_date" value when the video has been checked in.
0
 

Author Comment

by:JohnMac328
Comment Utility
I removed the <cfset formsubmitted = false> and it still returns the records I want.  What would be the best way to edit one of the fields after they are displayed?
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Perhaps something like this..
<cfparam name="form.employeeID" default="">

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

 

<cfif  isDefined("FORM.submitButton")>

   <cfloop index="aRow" from="1" to="#val(form.totalRecords)#">

      <cfset variables.InDate  = form['in_date' & aRow]>

      <cfset variables.VideoID = form['VideoID' & aRow]>

	  <cfif len(variables.InDate) and NOT isDate(variables.InDate)>

	     <cfthrow message="Invalid Date.. handle this error gracefully">

	  </cfif>

      <cfquery datasource="#datasource#">

        UPDATE Detail

           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>

        where VideoID = #val(variables.VideoID)#

      </cfquery>

   </cfloop>

</cfif>

 

 
 

<cfquery name="getAllEmployees" datasource="#datasource#">

  SELECT  EmployeeID, First_Name, Last_Name, Email

  FROM    Employees

  ORDER BY Last_Name, First_Name

</cfquery>

 

<cfform> <!--- change the employee viewed ---->

    <table width="956">

       <tr>

        <td width="186">

       <cfselect name="employeeID" query="getAllEmployees" display="email" value="employeeID" selected="#form.employeeID#" onchange="this.form.submit();">

       </cfselect>

    </td>

    </tr>

</cfform>
 

<cfif val(form.employeeID) eq 0>

   Please select an employee

  <cfexit>

</cfif>

 
 

 

<cfquery name="getEmp" datasource="#datasource#">

  SELECT  EmployeeID, First_Name, Last_Name, Email

  FROM    Employees

  where EmployeeID = #val(form.EmployeeID)#

</cfquery>
 

 <cfquery name="getChecked_Out_Videos" datasource="#datasource#">

   SELECT Detail.VideoID, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date, Detail.Watched

   FROM VideoList 

	 INNER JOIN Detail ON VideoList.VideoID = Detail.VideoID

   WHERE  Detail.EmployeeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.employeeID#"> 

   order by Detail.In_Date 

</cfquery>

 
 

<cfform>

   <h1>#getEmp.EmployeeID#) #getEmp.First_Name# #getEmp.Last_Name# #getEmp.Email#</h1>

   <table>

   <cfoutput query="getChecked_Out_Videos">

     <cfset rowNum = getChecked_Out_Videos.currentRow>

     <tr>

          <td></td>

          <td width="198">#VideoID#</td>

          <td width="198">#Video_Name#</td>

          <td width="154">#Out_Date#</td>

          <td width="177"><input type="text" name="In_Date#rowNum#" value="#getChecked_Out_Videos.In_Date#">

		  <input type="hidden" name="VideoID#rowNum#" value="#getChecked_Out_Videos.VideoID#">

		  </td> 

          <td width="136"><a href="employeeEdit.cfm?employeeID=#EmployeeID#">Edit</a></td>

       </tr>

   </cfoutput>

  </table>       

  <input type="hidden" name="totalRecords" value="#rowNum#">

  <input type="submit" name="submitButton"  value="Submit">

</cfform>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
hmmm, what is that button trying to edit?  The employee record or the video detail?

The code seems to list all videos for the selected employee, therefore the edit button, if on the list would be for the video.   An edit button for the employee would be next to the employee name...

I took out the employee ID and email from the list because they would all be the same..
0
 

Author Comment

by:JohnMac328
Comment Utility
The In_Date is in the Detail table, there are 3 tables with relationships.  That is the only field at this time that has to have a value entered.
0
 

Author Comment

by:JohnMac328
Comment Utility
On this line

<td width="136"><a href="employeeEdit.cfm?employeeID=#EmployeeID#">Edit</a></td>

I don't have a employeeEdit.cfm page.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> I don't have a employeeEdit.cfm page

Substitute it for the name of the template you'd like to go to (what is the "Edit" button for?  change it to where you are going ...)
0
 

Author Comment

by:JohnMac328
Comment Utility
Do I have to have another page.  What I was talking about earlier was that an example had several edit options on one page.  I was trying to create the edit portion.  I have attached that code
<cfif isDefined("FORM.inserting")>

	

	<cfquery datasource="#datasource#">

		INSERT INTO Employees

		(Email, First_Name, Last_Name, role)

		VALUES ('#FORM.Email#','#FORM.First_Name#','#FORM.Last_Name#','#FORM.role#')

	</cfquery>

<cfelseif isDefined("FORM.editing")>

	<cfquery datasource="#datasource#">

		UPDATE Employees

		SET First_Name='#FORM.First_Name#',

			Last_Name='#FORM.Last_Name#',

			Email='#Form.Email#',

			role='#FORM.role#'

where EmployeeID=#FORM.EmployeeID#

	</cfquery>

<cfelseif isDefined("FORM.deleting")>

	<cfquery datasource="#datasource#">

		DELETE FROM Employees

		WHERE EmployeeID=#FORM.EmployeeID#

	</cfquery>

</cfif>

<cfquery name="getEmployees" datasource="#datasource#">

	SELECT *

	FROM Employees

</cfquery>
 

<html>

<head>

<title>Admin Page</title>

<style>

	.admin {color:#000099}

	.user {color:#000000}

</style>

</head>
 

<body>

<cfoutput><form method="post" action="Admin.cfm"></cfoutput>

<input type="hidden" name="inserting" value="true">

<table cellpadding="3" cellspacing="0" id="maintable" width="700">

<tr>

	<th>First Name</th>

	<th>Last Name</th>

	<th>Email</th>

	<th>Role</th>

	<th colspan="2">Action</th>

</tr>

<tr>

	<td><input type="text" name="First_Name" size="15"></td>

	<td><input type="text" name="Last_Name" size="15"></td>

	<td><input type="text" name="Email" size="30"></td>

	<td>

		<select name="role">

			<option value='1' class="admin">Admin</option>

			<option value='0' selected>User</option>

		</select>

	</td>

	<td colspan="2" align="center" width="120">

		<input name="add" type="submit" value="Add User"

										style="font-size:xx-small">

	</td>

</tr>

<tr><td colspan="6"><hr></td></tr>

</table>

</form>

<cfoutput query="getEmployees">

<form method="post" style="margin:0px" action="Admin.cfm">

	<input type="hidden" name="EmployeeID" value="#EmployeeID#">

	<table width="700">

		<tr>

		<td>

		<input type="text" name="First_Name"

			value="#First_Name#" size="15" class="#role#">

		</td>

		<td>

		<input type="text" name="Last_Name"

			value="#Last_Name#" size="15" class="#role#">

		</td>

		<td>

		<input type="text" name="Email"

			value="#Email#" size="30" class="#role#">

		</td>

		<td>

			<cfif role EQ 1>

				<cfset adminselected =  "selected">

				<cfset userselected = "">

			<cfelse>

				<cfset userselected = "selected">

				<cfset adminselected =  "">

			</cfif>

			<select name="role">

				<option value='1' #adminselected# >Admin</option>

				<option value='0' #userselected# >User</option>

			</select>

		</td>

		<td width="120">

		<input type="submit" name="editing"

			value="EDIT" style="font-size:xx-small"> 

		<input type="submit" name="deleting"

			value="DELETE" style="font-size:xx-small">

		</td>

		</tr>

	</table>

</form>

</cfoutput>
 

</body>

</html>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> Do I have to have another page

What do you mean by 'another' page?   Is the employee edit page you just posted supposed to be in the same file as the video checkin page?  If so, I don't think that would be very clean, too many completely different tasks in one file.


> that an example had several edit options on one page

what are the edit options?  Insert, Update, delete?   If so, that's typical to do in one file.   But combining with the video edit just seems like a stretch, one is a list the other is a form for a single employee..

0
 

Author Comment

by:JohnMac328
Comment Utility
Ok, let's backup.  I have one page that lists detail records for one employee.  I wanted to know if it is possible to edit one field of the detail records.  So you are saying by your example that I need an additional page, which I don't know how to code since I am new to CF.
0
 

Author Comment

by:JohnMac328
Comment Utility
How does this code transfer to the employeeEdit.cfm page?
<cfif  isDefined("FORM.submitButton")>

   <cfloop index="aRow" from="1" to="#val(form.totalRecords)#">

      <cfset variables.InDate  = form['in_date' & aRow]>

      <cfset variables.VideoID = form['VideoID' & aRow]>

          <cfif len(variables.InDate) and NOT isDate(variables.InDate)>

             <cfthrow message="Invalid Date.. handle this error gracefully">

          </cfif>

      <cfquery datasource="#datasource#">

        UPDATE Detail

           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>

        where VideoID = #val(variables.VideoID)#

      </cfquery>

   </cfloop>

</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
>  I wanted to know if it is possible to edit one field of the detail records

one field?  You're referring to the IN_DATE field for the video check in?   Yes, I think our first example did just that.  It showed a list of the videos that the person has out and you enter a date next to the videos being returned.   That one field is saved for each record that you displayed.

Now generally speaking...
A typical site would have a page listing of an entity, let's say contacts and then a page to edit one contact.   So, the user would perform a search, see a list of contacts, click the one he wants, and then view the detail and be able to edit it.   So, that's two pages:  a list page and an edit page.   That's the convention and is sufficient most of the time.

The first page we discussed, is a page where you select an employee and below it, you see a list of videos that employee has rented.  You check off those that have been returned.    


If you wanted to, I guess you could have the employee fields to edit the employee info (name, email, phone, etc) on the top and then list the videos below it.  When you save it, you would save both the employee's info (name, email, etc) and the check in of the videos... is that what you are after?


I babbled a lot here in hopes of answering the question, not sure if I got it right :)
0
 

Author Comment

by:JohnMac328
Comment Utility
I appreciate your patience.  What is happening is I choose the employee from the dropdown.  It then displays the video that they have checked out.  I want to enter a date in the In_Date field and have it updated in the table.  How would that be accomplished with this code
<!--- let's set another var that we'll check for at out put time instead of checking for form.employeeID 

<cfset formsubmitted = false>--->

 

<cfif structkeyexists(form, 'employeeID')><!--- only execute if form has been submitted --->
 
 

 <cfquery name="getChecked_Out_Videos" datasource="#datasource#">

 SELECT Employees.EmployeeID, Employees.Email, Detail.VideoID, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date, Detail.Watched

FROM VideoList INNER JOIN (Employees INNER JOIN Detail ON Employees.EmployeeID = Detail.EmployeeID) ON VideoList.VideoID = Detail.VideoID

WHERE  Employees.EmployeeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.employeeID#"> order by Detail.In_Date 

</cfquery>
 

<!--- assert: form has been submitted. set formsubmitted to true--->

<cfset formsubmitted = true> 

 

</cfif>
 

<cfif  isDefined("FORM.Edit")>

<cfquery datasource="#datasource#">

		UPDATE Detail

		SET EmployeeID='#FORM.EmployeeID#',

            In_Date='#FORM.In_Date#'

where EmployeeID=#FORM.EmployeeID#

</cfquery>
 
 

</cfif>
 
 

<cfparam name="form.employeeID" default="1">

 

<cfquery name="getAllEmployees" datasource="#datasource#">

SELECT  EmployeeID, First_Name, Last_Name, Email

FROM    Employees

ORDER BY Last_Name, First_Name

</cfquery>

 

<cfform action="#CGI.SCRIPT_NAME#" method="post" format="html">

        

        <table width="956">

       <tr>

        <td width="186">

       <cfselect name="employeeID" query="getAllEmployees" display="email" value="employeeID" selected="#form.employeeID#" onchange="this.form.submit();">

       </cfselect>

    </td>

    </tr>

 

<!--- check if the form has been submitted and show query output --->

<cfif formsubmitted is true>

          

                 

        <cfoutput query="getChecked_Out_Videos">

        <tr>

          <td>#EmployeeID#</td>

          <td width="100">#Email#</td>

          <td width="198">#VideoID#</td>

          <td width="198">#Video_Name#</td>

          <td width="154">#Out_Date#</td>

          <td width="177"><input type="text" name="In_Date" value="#In_Date#"></td> 

       <td width="136">

		<input type="submit" name="edit"

			value="EDIT" style="font-size:xx-small"> 

		</td>

       </tr>

         </cfoutput>

</cfif>

        

 </table>       

        

        

<input type="submit" name="submitButton"  value="Submit">

</cfform>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> How does this code transfer to the employeeEdit.cfm page

that code takes a list of fields and loops around to save it.

If you have a list, like you do for the videos that are checked out, each video is a record that needs to be saved separately.  So you add a counter to the end of each field....

<input type="text' name="IN_DATE1" ...
<input type="text' name="IN_DATE2" ...
<input type="text' name="IN_DATE3" ...
<input type="text' name="IN_DATE4" ...

Then when you submit it, you loop through each of the fields 1..4 and save each one.  That's what the code you posted does.

If you have just ONE employee, that is not a list, so you don't need to loop through.   With the videos, it's a list of records you have to save one by one, so I added the counter...

0
 

Author Comment

by:JohnMac328
Comment Utility
It is one employee and one detail record like I mentioned in the last post, we may have cross talked.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
>  What is happening is I choose the employee from the dropdown.  It then displays the video that they have checked out.  I want to enter a date in the In_Date field and have it updated in the table.  How would that be accomplished with this code..


The code I attached in my posted where I said

 "Perhaps something like this... "

does exactly that.   It will allow you to save each of the IN_DATE fields into the "DETAIL" table.

Let's put aside that one little <A HREF=""> Edit</a> in that block of code that sent us on a tangent and get that code working.   It does just what you're asking..

Select an employee,

View the Employee Name, with a list of Videos.  Each video has a text box next to it.  
Each text box populated with a date, will be updated in the DETAIL table...

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:JohnMac328
Comment Utility
I tried creating the page.  What I get is just a submit button on the page with no detail selected.  Please point out what I am missing.
<cfparam name="form.employeeID" default="">

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

 

<cfif  isDefined("FORM.submitButton")>

   <cfloop index="aRow" from="1" to="#val(form.totalRecords)#">

      <cfset variables.InDate  = form['in_date' & aRow]>

      <cfset variables.VideoID = form['VideoID' & aRow]>

          <cfif len(variables.InDate) and NOT isDate(variables.InDate)>

             <cfthrow message="Invalid Date.. handle this error gracefully">

          </cfif>

      <cfquery datasource="#datasource#">

        UPDATE Detail

           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>

        where VideoID = #val(variables.VideoID)#

      </cfquery>

   </cfloop>

</cfif>

 

 

 
 

 

 

<cfquery name="getEmp" datasource="#datasource#">

  SELECT  EmployeeID, First_Name, Last_Name, Email

  FROM    Employees

  where EmployeeID = #val(form.EmployeeID)#

</cfquery>

 

 <cfquery name="getChecked_Out_Videos" datasource="#datasource#">

   SELECT Detail.VideoID, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date, Detail.Watched

   FROM VideoList 

         INNER JOIN Detail ON VideoList.VideoID = Detail.VideoID

   WHERE  Detail.EmployeeID = <cfqueryparam value="#form.employeeID#"> 

   order by Detail.In_Date 

</cfquery>

 

 

<cfform>
 

   <table>

   <cfoutput query="getChecked_Out_Videos">

     <cfset rowNum = getChecked_Out_Videos.currentRow>

     <tr>

          <td></td>

    

          <td width="198">#Video_Name#</td>

          <td width="154">#Out_Date#</td>

          <td width="154">#Watched#</td>

          <td width="177"><input type="text" name="In_Date#rowNum#" value="#getChecked_Out_Videos.In_Date#">

                  <input type="hidden" name="VideoID#rowNum#" value="#getChecked_Out_Videos.VideoID#">

                  </td> 

          <td width="136"></td>

       </tr>

   </cfoutput>

  </table>       

  <input type="hidden" name="totalRecords" value="#rowNum#">

  <input type="submit" name="submitButton"  value="Submit">

</cfform>

Open in new window

0
 

Author Comment

by:JohnMac328
Comment Utility
And if I don't use the "Edit" link and hit submit, then the field does not get updated.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Since you removed the select list to choose an employee, the employee ID field has no value and no employees are found.   You can default the employeeID in the first line.  I changed it to 1.

<cfparam name="form.employeeID" default="1">

I also added a couple of CFIF statements to check if the employee is found, etc.


I also added a CFOUTPUT I was missing at the very end..

<cfparam name="form.employeeID" default="1">

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

 

<cfif  isDefined("FORM.submitButton")>

   <cfloop index="aRow" from="1" to="#val(form.totalRecords)#">

      <cfset variables.InDate  = form['in_date' & aRow]>

      <cfset variables.VideoID = form['VideoID' & aRow]>

          <cfif len(variables.InDate) and NOT isDate(variables.InDate)>

             <cfthrow message="Invalid Date.. handle this error gracefully">

          </cfif>

      <cfquery datasource="#datasource#">

        UPDATE Detail

           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>

        where VideoID = #val(variables.VideoID)#

      </cfquery>

   </cfloop>

</cfif>

 

 

 

<cfquery name="getEmp" datasource="#datasource#">

  SELECT  EmployeeID, First_Name, Last_Name, Email

  FROM    Employees

  where EmployeeID = #val(form.EmployeeID)#

</cfquery>

<cfif getEmp.recordCount eq 0>

   Employee ID number #form.employeeID# was not found.<br>

   <cfexit>

</cfif> 
 

 <cfquery name="getChecked_Out_Videos" datasource="#datasource#">

   SELECT Detail.VideoID, VideoList.Video_Name, Detail.In_Date, Detail.Out_Date, Detail.Watched

   FROM VideoList 

         INNER JOIN Detail ON VideoList.VideoID = Detail.VideoID

   WHERE  Detail.EmployeeID = <cfqueryparam value="#form.employeeID#"> 

   order by Detail.In_Date 

</cfquery>

 

<cfif getChecked_Out_Videos.recordCount eq 0>

   Employee has no videos found.

   <cfexit>

</cfif> 
 

<cfform>

 

   <table>

   <cfoutput query="getChecked_Out_Videos">

     <cfset rowNum = getChecked_Out_Videos.currentRow>

     <tr>

          <td></td>

    

          <td width="198">#Video_Name#</td>

          <td width="154">#Out_Date#</td>

          <td width="154">#Watched#</td>

          <td width="177"><input type="text" name="In_Date#rowNum#" value="#getChecked_Out_Videos.In_Date#">

                  <input type="hidden" name="VideoID#rowNum#" value="#getChecked_Out_Videos.VideoID#">

                  </td> 

          <td width="136"></td>

       </tr>

   </cfoutput>

  </table>       

  <cfoutput>

  <input type="hidden" name="totalRecords" value="#rowNum#">

  </cfoutput>

  <input type="submit" name="submitButton"  value="Submit">

</cfform>

Open in new window

0
 

Author Comment

by:JohnMac328
Comment Utility
I can't bring up the page, all I get is

Employee ID number #form.employeeID# was not found.
0
 

Author Comment

by:JohnMac328
Comment Utility
If I am passing

employeeEdit.cfm?employeeID=140

Why does it not see the record?
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
when you pass a value on the URL, the variable uses the URL scope.

So if you did this...
employeeEdit.cfm?employeeID=140


You would use the variable  URL.employeeID

If you do a form submit, you use the variable FORM.employeeID

To cover both, you could do something like this.  That will use the URL or the FORM employee ID...
<cfparam name="url.employeeID" default="">
<cfparam name="form.employeeID" default="#url.employeeID#">


From here on reference it as FORM.employeeID....


0
 

Author Comment

by:JohnMac328
Comment Utility
Ok, it is adding the In_Date for the employee and I noticed that it is entering the same date to several other employees.  If it is displaying one employee, how is the date being added to several?
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
The answer is probably in this update statement...

        UPDATE Detail
           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>
        where VideoID = #val(variables.VideoID)#


What is the unique key (the primary key) of the DETAIL table?

You want to update only one record of this table, I don't know the table layout and made an assumption that VideoID is the Primary Key.   Substitute the real primary key for this column in the update and hidden field below as well as the <CFSET statements..
0
 

Author Comment

by:JohnMac328
Comment Utility
The detail table has the employeeID and the VideoID fields which are the primary key fields from the Employee table and the VideoList table.  Should I add a detail primary key field?
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
Yes, every table should have a primary key.  The only exception is a join table which only has two foreign keys and no other columns.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
If you can, I would change the name of the table as well.   DETAIL could be the detail of anything...  Change it to a name that let's you know what's in it..  

If it's too late for that, it's ok, just some advice..
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
A name like   EmployeeVideos   may be good :)

The primary key looks like the table name:   employeeVideo_ID

0
 

Author Comment

by:JohnMac328
Comment Utility
I may have a problem with my configuration

Employee table EmployeeID to the EmployeeID field in detail table

VideoList table VideoID to the VideoID field in detail table

Should one or both of these have a relationship to a field other than the ones that they are connected to now?  I know I have to add a DetailID but I want to make sure if the current config is correct or not.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> Should one or both of these have a relationship to a field other than the ones that they are connected to now

Not exactly sure what you're asking.

But, it seems like a logical design based on what I know.

You have a video table which is all about the video itself:  the video's name, length, author, actors, perhaps date purchased and anything else about the video.

You have a table about the employee which is about the person him/her self.  Name, email, phone etc.

This detail table, is about the employee's use of the video.   So you need the employeeID and the videoID to identify which person has which video.  Other attributes of the table would be when it was checked out and when it was checked back in.  Perhaps the cost or anything else about the employee's use of the video.

But again, I would rename it to   EmployeeVideos or something that clearly states what the table is about.
And it should have a primary key that matches the table name..
0
 

Author Comment

by:JohnMac328
Comment Utility
The name doesn't matter since it makes sense to me.  I have a detailID field which is autonumber.  Do I change where VideoID is to DetailID?

UPDATE Detail
           SET In_Date = <cfif isDate(variables.InDate)>#createODBCdate(variables.InDate)#<cfelse>NULL</cfif>
        where VideoID = #val(variables.DetailID)#   ???
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility
yes you have to change VideoID to DetailID in several locations...  including the hidden field and the CFSET statements
0
 

Author Closing Comment

by:JohnMac328
Comment Utility
Thanks for all your help, that seemed to do it.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now