asked on

Multiple Records Update from a Table in ColdFusion

As you can see in the picture I attached to this, I am pulling data from multiple tables using a variable (#URL.SelectVolumeID#) from the previous page to filter the results of the cfquery 'getEmployees' below; (#URL.SelectMeetingDate#) is also from the previous page.

What I'm trying to do is display a list of names, and allow the user to use the check boxes to take attendance. I then want the data to be inserted as new records into tblDDRAttendance. However, I do not want the data that is being displayed to be inserted into the table, I only want the ID values related to the displayed information inserted into the table, plus I want the two values that are posted in the URL from the previous page to be inserted into each record too.

I've tried to do as much of it as I can, and I took out all of the fluff; so, hopefully one of you can help me get this done by tomorrow morning ;)

p.s. Just in case, here is what the URL, including the posted values, looks like for this page:
http://evtspatialpre.web.boeing.com/Attendance/DDRAttNewP1S2.cfm?SelectVolumeID=1&SelectMeetingDate=4%2F15%2F2009&Continue=Continue+%3E%3E%3E

<!--- Get Employees --->
<CFQUERY DATASOURCE="#Application.DSN#" NAME="getEmployees">
SELECT     ss974c.tblEmployees.BEMSID AS BEMSID, ss974c.tblEmployees.Name AS Name, ss974c.tblListCommodity.Commodity AS Commodity, ss974c.tblListVolumes.VolumeName AS Volume
FROM     ss974c.tblListVolumes
		INNER JOIN ss974c.tblListDDR_DL ON ss974c.tblListVolumes.VolumeID = ss974c.tblListDDR_DL.VolumeID
		INNER JOIN ss974c.tblEmployees ON ss974c.tblListDDR_DL.BEMSID = ss974c.tblEmployees.BEMSID
		INNER JOIN ss974c.tblListCommodity ON ss974c.tblEmployees.CommodityID = ss974c.tblListCommodity.CommodityID
WHERE     (ss974c.tblListVolumes.VolumeID = #URL.SelectVolumeID#)
</CFQUERY>
 
 
<!--- Attendance Table --->
<CFFORM METHOD="post" NAME="DDRAttF1">
<input name="formSubmitted" type="hidden" value="true"/>
<table border="1" width="*" cellpadding=5 cellspacing=0 bordercolor="black" class="left">
	<tr>
	<th nowrap="nowrap" valign="middle" align="center"class="required" colspan=4>MEETING INFORMATION </th>
	</tr>
 
	<tr>
	<td nowrap="nowrap" width="*" valign="middle" align="center" class="headerrow">Present</td>
	<td nowrap="nowrap" width="*" valign="middle" align="center" class="headerrow" colspan=1>Name</td>
	<td nowrap="nowrap" width="*" valign="middle" align="center" class="headerrow">BEMSID</td>
	<td nowrap="nowrap" width="*" valign="middle" align="center" class="headerrow">Commodity</td>
	</tr>
 
	<CFOUTPUT QUERY="getEmployees"> <tr>
	<td nowrap="nowrap" width="*" valign="middle" align="center" class="Normal"><input name="PresentCMD" type="checkbox" value="" /></td>
	<td nowrap="nowrap" width="*" valign="middle" align="Left" class="Normal" colspan=1>#Name#</td>
	<td nowrap="nowrap" width="*" valign="middle" align="Left" class="Normal">#BEMSID#</td>
	<td nowrap="nowrap" width="*" valign="middle" align="Left" class="Normal">#Commodity#</td>
	</tr></CFOUTPUT>
 
	<tr>
	<th class="required" colspan=4>
		<input type="submit" name="AddGuests" value="Add Guests &gt;&gt;&gt;" />
&nbsp; or&nbsp;  <input type="submit" name="RecordAttendance" value="Record Attendance &gt;&gt;&gt;">
	</th>
	</tr>
</table>
</CFFORM>
<!--- End of Attendance Table --->
 
 
<!--- Insert Data Into tblDDRAttendance --->
<cfif isDefined('FORM.formSubmitted')>
	<CFQUERY NAME="insertAttendance" DATASOURCE="#Application.DSN#">
		INSERT INTO ss974c.tblDDRAttendance (BEMSID, CommodityID, VolumeID, DDRDate)
		VALUES ('#FORM.BEMSID#', '#FORM.CommodityID#', '#FORM.VolumeID#', #URL.SelectMeetingDate#)
	</CFQUERY>
</cfif>
<!--- End of Insert Data Into tblDDRAttendance --->

Open in new window

Record-DDR-Attendance.jpg
DDR-Attendance-DB-Tables.jpg

gdemaria

what ID value uniquely represents the checked item? If it is #BEMSID# then put it into the checkbox's value

<input name="PresentCMD" type="checkbox" value="#BEMSID#" />

When submitted PresentCMD will be a comma delimited list of all checked IDs 453,698,1023

The loop through the PresentCMD and insert each ID one at a time...

<cfif isDefined('FORM.formSubmitted')>

<cfloop index="theID" list="#presentCMD#">
<CFQUERY NAME="insertAttendance" DATASOURCE="#Application.DSN#">
INSERT INTO ss974c.tblDDRAttendance (BEMSID, CommodityID, VolumeID, DDRDate)
VALUES ('#theID#', '#FORM.CommodityID#', '#FORM.VolumeID#', #URL.SelectMeetingDate#)
</CFQUERY>
</cfloop>

</cfif>

I am guess which ID you need and which field it goes into... you may need to adjust that

Jon Bredensteiner

ASKER

The checkbox will populate the FocalPresent field in tblDDRAttendance. Here is a little more info on the fields that will be populated in tblDDRAttendance:

* AttendanceID = Auto ID field - primary key for this table
* ProgramID = Ignore for now
* VolumeID = primary key for tblListVolumes - (#URL.SelectVolumeID#)
* CommodityID = primary key for tblListCommoditys - returned by "getEmployees"
* CommodityPresent = Ignore for now
* FocalBEMSID = primary key for tblEmployees - returned by "getEmployees"
* FocalPresent = this is the field that should be populated if the checkbox is checked
* DDRDate = this field should be populated with (#URL.SelectMeetingDate#)

gdemaria

To create the insert statement, list each column you want to populated (leave out the ones to skip and the primary key field if it's autopopulated)

(BEMSID, CommodityID, VolumeID, DDRDate)

Then include the corresponding value in the field...

VALUES ('#?????#', '#FORM.CommodityID#', '#theID#', #URL.SelectMeetingDate#)

You've included a column BEMSID which is not on the list... just clean up the insert and you should be good to go..

Jon Bredensteiner

ASKER

#BEMSID# is comming from tblEmployees
and is going to be inserted into #FocalBEMSID# in tblDDRAttendance

I will work on this and let you know how it goes... Thanks again, Jon

Jon Bredensteiner

ASKER

I haven't forgot about this question... I only work as a developer one day each week; the rest of the time I am a project manager.

Anyways, the database developer has changed all of the tables, so I might not be ready to actually try this code again until next week. Sorry,

gdemaria

no worries

Jon Bredensteiner

ASKER

I should be able to work on this today or tomorrow; I just need to figure out a couple of things that build up to this step first.

https://www.experts-exchange.com/questions/24412644/Multiple-Related-Selects-with-ColdFusion-and-JavaScript.html

Jon Bredensteiner

ASKER

Okay, I am finally to a point now that I can work on this. As I said before, the database fields changed (see below table). Also, I have updated my page now, and it works pretty good, but as I'm sure you know, it submits the values for each field in a comma delimited string, and I can only submit the data to another page (not insert it into the database).

I inserted the code that was suggested in post 24281353, and it didn't seem to do anything when I submitted the form; however, the data was passed from the form to the next page.

Thank you for your help, and sorry it took me so long to get caught up... Jon

AttendanceID      int      Unchecked
Program      nvarchar(15)      Checked
Volume      nvarchar(60)      Checked
Date      smalldatetime      Checked
IPT      nvarchar(50)      Checked
Commodity      nvarchar(120)      Checked
BEMSID      varchar(15)            Checked
Present      bit            Checked

<!--- Put an ! before "DOCTYPE" below, and the page will look better in FireFox, but worse in IE --->
<DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML XMLNS="http://www.w3.org/1999/xhtml" XML:LANG="en" LANG="en">
 
<!-------------------------------------------------- Session Variables ------------------------------------------->
<!-- Session From: Authenticate.cfm --> <!-- Verify that the user has permission to perform this task -->
	<CFLOCK SCOPE="Session" TYPE="readonly" TIMEOUT=2>
		<CFSET AccessLevel = Session.AccessLevel>
	</CFLOCK>
		
<!-- If they do not have access, send them to AccessDenied.cfm -->
		<CFIF AccessLevel LT 1>
			<CFLOCATION URL="AccessDenied.cfm">
		</CFIF>
 
<!-- Session From: Authenticate.cfm --> <!-- Convert Session to Local Variables -->
	<CFLOCK SCOPE="Session" TYPE="readonly" TIMEOUT=2>
		<CFSET FName = Session.FName>
		<CFSET LName = Session.LName>
		<CFSET BEMSID = Session.BEMSID>
	</CFLOCK>
 
<!-- Check for Form Variables from the previous page, and if they are missing it redirects the user to "Default.cfm"-->
	<CFIF not isDefined('FORM.SelectVolume')>
		<CFLOCATION URL="Default.cfm">
	<CFELSEIF not isDefined('FORM.SelectDate')>
		<CFLOCATION URL="Default.cfm">
	</CFIF>
 
 
<!-------------------------------------------------- End Session Variables -------------------->
 
 
<!-------------------------------------------------- Data Queries ------------------------------------------------>
<!-- Query: Get Employees List from the Volume DL -->
<CFQUERY NAME="getVolumeDL" DATASOURCE="EvtSpatial">
	SELECT	IPT, Commodity, BEMSID, Name, Program, Volume
	FROM		dbo.tblListDDR_DL
	WHERE	(Program = '#FORM.SelectProgram#') AND (Volume = '#FORM.SelectVolume#')
</CFQUERY>
 
<!--- <!-- Query: Insert Employees's Attendance into tblDDRAttendance -->
<CFQUERY NAME="postAttendance" DATASOURCE="EvtSpatial">
	INSERT INTO	dbo.tblDDRAttendance (Program, Volume, DDRDate, dbo.IPT, Commodity, BEMSID, Present)
	VALUES		('','','','','','','',)
</CFQUERY> --->
 
 
<!-------------------------------------------------- End Data Queries -------------------->
 
 
<!-------------------------------------------------- Begin Head -------------------------------------------------->
<HEAD>
	<TITLE>Everett Spatial Integration Action Items</TITLE>
 
	<META HTTP-EQUIV="Web-database app" CONTENT="text/html; charset=utf-8" />
	<META NAME="Boeing Brand Center" CONTENT="" />
	<META NAME="Action Item Database" CONTENT="" />
	<META NAME="Jon Bredensteiner" CONTENT="" />
	<META NAME="Description" CONTENT="Pilot Request Database for Everett Programs CPD Site Support" />
	<META NAME="03/11/2009" CONTENT="" />
	<META NAME="Bredensteiner, Jon W." CONTENT="" />
	<META NAME="robots" CONTENT="None" />
	<META HTTP-EQUIV="03/11/2011" CONTENT="-1"/>
 
 
<!-- Begin CSS -->
	<LINK REL="stylesheet" HREF="_css/styles.css" TYPE="text/css" />
	<LINK REL="stylesheet" HREF="_css/print.css" TYPE="text/css" MEDIA="print" />
	<LINK REL="stylesheet" HREF="_css/style.css" TYPE="text/css" />
<!-- End CSS -->
</HEAD>
<!-------------------------------------------------- End Headder -------------------->
 
 
<!-------------------------------------------------- Begin Body -------------------------------------------------->
<BODY>
	<CFINCLUDE TEMPLATE="_includes/header.cfm"><!-- Top Navigation -->
	
	<DIV ID="mainSection">
 
	<CFINCLUDE TEMPLATE="_includes/left_nav.cfm"><!-- Inserts the Left Navigation -->
 
	<CFINCLUDE TEMPLATE="_includes/top_nav.cfm"><!-- Inserts the Horizontal Navigation -->
 
	<DIV ID="personalityZone45"></DIV><!-- Inserts the Website Welcome Header -->
	
	<DIV ID="mainContent">
 
	<P ALIGN="left" STYLE="font-size:100%"><STRONG>DDR Meeting Attendance Homepage</STRONG></P> <!-- Page Title -->
 
 
<!-- ---------------------------------------- Main Table or Text Goes Here ---------------------------------------- -->
	<CFFORM METHOD="POST" NAME="ActionForm" ACTION="TestReceiveData.cfm">
	<TABLE BORDER="1" WIDTH="*" CELLPADDING=5 CELLSPACING=0 BORDERCOLOR="black" CLASS="left">
		<TR>
		<TH NOWRAP="nowrap" VALIGN="middle" ALIGN="center"CLASS="required" COLSPAN=4>Attendance</TH>
		</TR>
 
		<TR>
		<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Present</TD>
		<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Name</TD>
		<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>BEMSID</TD>
		<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Commodity</TD>
		</TR>
		
		<CFOUTPUT QUERY="getVolumeDL">
		<TR>
		<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="normal" COLSPAN="1" >
			<INPUT NAME="Present" ID="Present" TYPE="checkbox" VALUE=""/>
		</TD>
		<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="Name" ID="Name" TYPE="TEXT" READONLY VALUE="#Name#"></TD>
		<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="BEMSID" ID="BEMSID" TYPE="TEXT" READONLY VALUE="#BEMSID#"></TD>
		<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="Commodity" ID="Commodity" TYPE="TEXT" READONLY VALUE="#Commodity#"></TD>
		<INPUT NAME="Program" TYPE="hidden" VALUE="#FORM.SelectProgram#">
		<INPUT NAME="Volume" TYPE="hidden" VALUE="#FORM.SelectVolume#">
		<INPUT NAME="Date" TYPE="hidden" VALUE="#FORM.SelectDate#">
		<INPUT NAME="IPT" TYPE="hidden" VALUE="#IPT#">
		</TR>
		</CFOUTPUT>
		
		<TR>
		<TH CLASS="required" COLSPAN=4>
			<INPUT TYPE="submit" NAME="Submit" ID="Submit" VALUE="Submit Attendance">
		</TH>
		</TR>
	</TABLE>
	</CFFORM>
<!-- ---------------------------------------- End of Main Table or Text  ------------------ -->
 
 
	</DIV>
	</DIV>
 
	<CFINCLUDE TEMPLATE="_includes/footer.cfm"><!-- Page Footer --->
 
	</BODY>
<!-------------------------------------------------- End Body -------------------->
 
 
<CENTER><DIV ID="updated">Last updated 3/15/2009</DIV></CENTER>
 
</HTML>
<!-------------------------------------------------- End Page -------------------->
 
 
<!-------------------------------------------------- JavaScript -------------------------------------------------->
<!-- JavaScript: ??? -->	
	<SCRIPT LANGUAGE="javascript1.2" SRC="_scripts/javascript.js" TYPE="text/javascript"></SCRIPT>
 
<!-- JavaScript: this loops through the form, and inserts earh row into the database -->	
	<cfif isDefined('FORM.formSubmitted')>
	   <cfloop index="theID" list="#Present#">
		   <CFQUERY NAME="insertAttendance" DATASOURCE="#evtSpatial#">
				 INSERT INTO tblDDRAttendance (BEMSID, Program, Commodity, Volume, IPT, Date)
				 VALUES ('#theID#', #FORM.BEMSID#, #FORM.Program#, #FORM.Commodity#, #FORM.Volume#, #FORM.IPT#, #FORM.Date#)
		   </CFQUERY>
	   </cfloop>
	</cfif>
<!-------------------------------------------------- End JavaScript -------------------->

Open in new window

gdemaria

It seems we have gone from one field to many fields. In order to do multiple fields and keep them grouped together by their record, you need to do what some call "field arrays." Every field in your form needs to have a row number appended to it..

Name1, Name2, Name3
BemsID1, BemsID2, BemsID3

Where Name1 and BensID1 are part of the same record.

Here is some code that give you an example of how this would all work...

<cfset application.datasource = "YOUR DATASOURCE HERE">
 
<cfparam name="url.listingID" default="1">
<cfparam name="form.listingID" default="#url.listingID#">
 
<cfset variables.extraBlankRows = 3> <!---- number of extra blank records to add at the end for inserting ----->
<cfset variables.error = ""> <!---- will contain any error messages for display below later ----->
 
<!----- the action, will insert or update the record ----->
<cfif isDefined("form.btnHouseFeatures")>
  <cftry>
    <!---- loop through every record, add or update depending on whether they already had an ID ----->
    <cfloop index="kk" from="1" to="#val(form.totalRecords)#">
        <cfset variables.somethingEntered = true> <!---- assume some data was entered for each record ---->
    	<cfset form.photoname = form['photoname' & kk]>
    	<cfset form.description = form['description' & kk]>
    	<cfset form.photoID = form['photoID' & kk]>
        <cfif len(form.photoname) + len(form.description) eq 0>
          <cfset variables.somethingEntered= false> <!---- this record is blank ---->
        </cfif>
        <cfif val(form.photoID) eq 0> <!---- no primary key, so insert ---->
           <cfif variables.somethingEntered> <!--- don't save a blank record ---->
            <cfquery name="insertPhotos" datasource="#application.datasource#">
              insert into photos (photoname, description, listingID)
              values ('#form.photoname#','#form.description#',#form.ListingID#)
            </cfquery>
           </cfif>
        <cfelse> <!----- already exists, so update it ----->
           <cfif variables.somethingEntered> 
             <cfquery name="updatePhotos" datasource="#application.datasource#">
              update photos
                set photoname = '#form.photoname#'
                  , description = '#form.description#'
              where photoID = #val(form.photoID)#
            </cfquery>
           <cfelse> <!---- nothing entered, so delete the record ---->
             <cfquery name="deletePhotos" datasource="#application.datasource#">
              delete photos where photoID = #val(form.photoID)#
             </cfquery>
           </cfif>
        </cfif>
    </cfloop>
 
  <cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
  </cfcatch>
  </cftry>
</cfif>
 
 
<!---- FETCH DATA
       Only fetch data from the database is there were no errors 
       If there were errors, show the error and the same data as entered, but not saved yet ------>
<cfif len(variables.error) eq 0>
    <!---- fetch the data from the database and convert into an array ----->
    <cfquery name="getData" datasource="#application.datasource#">
      SELECT ListingID, photoID, photoname, description
      FROM photos
      where ListingID = #val(form.ListingID)#
      order by photoName
    </cfquery>
    <cfloop query="getData">  <!---- create a form variable for every record, every column in the format:  form.columName1 ------>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & getData.currentRow] = getData[aCol][getData.currentRow]>
      </cfloop>
    </cfloop>
    <cfset form.totalRecords = getData.recordCount>
    <!----- create extra form variables that are blank, same format form.columnName2 ---->
    <cfloop index="kk" from="1" to="#variables.extraBlankRows#"> 
      <cfset form.totalRecords = form.totalRecords + 1>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & form.totalRecords] = "">
      </cfloop>
    </cfloop>
</cfif>
 
<cfif len(variables.error)> <!----- show the error ----->
 <div style="background-color:pink;color:maroon;padding:10px;font-weight:bold;">
  <cfoutput>Error: #variables.error#</cfoutput>
 </div>
</cfif>
 
<cfform name="myform" id="myform">
    <h1>ENTER .JPG NAMES &amp; DESCRIPTIVE TEXT HERE</h1>
    <cfoutput>
    <table width="525" border="0" cellpadding="2" cellspacing="2">
      <tr>
        <th width="144" scope="col">Photo Name</th>
        <th width="412" scope="col">Description</th>
      </tr>
      <!---- us a simple counter, not the ID to tack onto the variable names ----->
      <cfloop index="ii" from="1" to="#form.totalRecords#">
      <tr>
        <td valign="top"><input type="text" name="photoname#ii#" value="#form['photoname'&ii]#"/></td>
        <td><textarea name="description#ii#" cols="70" rows="3">#form['description' & ii]#</textarea>
            <input type="hidden" name="photoID#ii#" value="#form['photoID' & ii]#"> <!---- the primary key of the table ----->
        </td>
      </tr>
      </cfloop>
      <tr>
        <td>
        <cfoutput>
        <input type="hidden" name="listingID" value="#URL.ListingID#">
        <input type="Hidden" name="totalRecords" value="#form.totalRecords#">
        </cfoutput>
        </td>
        <td align="left"><cfinput type="submit" name="btnHouseFeatures" value="ADD PHOTOS"/></td>
      </tr>
    </table>
    </cfoutput>
</cfform>

Open in new window

Jon Bredensteiner

ASKER

I am not doing updates to the database, only inserting new records. That said, it looks like I could leave out the below code from your example. Is this correct?

Thanks again, Jon

p.s. I always wanted to insert data from multiple fields, but I probably failed to explain myself correctly ;)

<!---- FETCH DATA
       Only fetch data from the database is there were no errors 
       If there were errors, show the error and the same data as entered, but not saved yet ------>
<cfif len(variables.error) eq 0>
    <!---- fetch the data from the database and convert into an array ----->
    <cfquery name="getData" datasource="#application.datasource#">
      SELECT ListingID, photoID, photoname, description
      FROM photos
      where ListingID = #val(form.ListingID)#
      order by photoName
    </cfquery>
    <cfloop query="getData">  <!---- create a form variable for every record, every column in the format:  form.columName1 ------>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & getData.currentRow] = getData[aCol][getData.currentRow]>
      </cfloop>
    </cfloop>
    <cfset form.totalRecords = getData.recordCount>
    <!----- create extra form variables that are blank, same format form.columnName2 ---->
    <cfloop index="kk" from="1" to="#variables.extraBlankRows#"> 
      <cfset form.totalRecords = form.totalRecords + 1>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & form.totalRecords] = "">
      </cfloop>
    </cfloop>
</cfif>
 
<cfif len(variables.error)> <!----- show the error ----->
 <div style="background-color:pink;color:maroon;padding:10px;font-weight:bold;">
  <cfoutput>Error: #variables.error#</cfoutput>
 </div>
</cfif>

Open in new window

gdemaria

Even if you're not fetching data, I think you would want form values in your input tags. That way, if when submitted, some values failed validation, the entered values would still appear in the form fields. If you have no value in the input tags, the fields would come back blank on an error.

To that end, you would want to create empty form fields for each column using something like this..

<cfloop index="kk" from="1" to="#variables.extraBlankRows#">
<cfset form.totalRecords = form.totalRecords + 1>
<cfloop index="aCol" list="#getData.columnList#">
<cfset form[aCol & form.totalRecords] = "">
</cfloop>
</cfloop>

But installed of looping through the getData columnList, you can loop through a list of your columns to create default values for your form fields.

This part just shows the error, if validation fails...

<cfif len(variables.error)> 
<div style="background-color:pink;color:maroon;padding:10px;font-weight:bold;">
<cfoutput>Error: #variables.error#</cfoutput>
</div>
</cfif>

Jon Bredensteiner

ASKER

Okay, I think I am pretty close, but I keep getting the same error on line 187 no matter what I try (see below error).

This is what is being passed from Default.cfm in the URL:
http://evtspatialpre.web.boeing.com/Attendance/DDRAttNewA1S1.cfm?Program=747-8I&Volume=Sec+44&Date=05%2F29%2F2009&SelectAction=DDRAttNewA1S1.cfm&Continue=Continue+%3E%3E%3E

Here is the error:
Element Date1 is undefined in a Java object of type class coldfusion.filter.FormScope referenced as
The error occurred in I:\Websites\evtspatialpre\Attendance\DDRAttNewA1S1.cfm: line 187
185 :                                     <INPUT NAME="Program#ii#" TYPE="hidden" VALUE="#FORM['Program'&ii]#">
186 :                                     <INPUT NAME="Volume#ii#" TYPE="hidden" VALUE="#FORM['Volume'&ii]#">
187 :                                     <INPUT NAME="Date#ii#" TYPE="hidden" VALUE="#FORM['Date'&ii]#">
188 :                                     <INPUT NAME="IPT#ii#" TYPE="hidden" VALUE="#FORM['IPT'&ii]#">
189 :                               </TR>

<!--- Put an ! before "DOCTYPE" below, and the page will look better in FireFox, but worse in IE --->
<DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<HTML XMLNS="http://www.w3.org/1999/xhtml" XML:LANG="en" LANG="en">
 
<!-------------------------------------------------- Session Variables ------------------------------------------->
<!-- Session From: Authenticate.cfm --> <!-- Verify that the user has permission to perform this task -->
	<CFLOCK SCOPE="Session" TYPE="readonly" TIMEOUT=2>
		<CFSET AccessLevel = Session.AccessLevel>
	</CFLOCK>
		
<!-- If they do not have access, send them to AccessDenied.cfm -->
		<CFIF AccessLevel LT 1>
			<CFLOCATION URL="AccessDenied.cfm">
		</CFIF>
 
<!-- Session From: Authenticate.cfm --> <!-- Convert Session to Local Variables -->
	<CFLOCK SCOPE="Session" TYPE="readonly" TIMEOUT=2>
		<CFSET FName = Session.FName>
		<CFSET LName = Session.LName>
		<CFSET BEMSID = Session.BEMSID>
	</CFLOCK>
 
<!-- Check for Form Variables from the previous page, and if they are missing it redirects the user to "Default.cfm"-->
<!------>
	<CFIF not isDefined('URL.Volume')>
		<CFLOCATION URL="Default.cfm">
	<CFELSEIF not isDefined('URL.Date')>
		<CFLOCATION URL="Default.cfm">
	</CFIF>
 
 
<!-- Variables for the Insert Query "postAttendance" -->
	<cfset application.datasource = "EvtSpatial">
 
	<cfparam name="url.Program" default="747-8I">
	<cfparam name="form.Program" default="#url.Program#">
 
	<cfparam name="url.Volume" default="Sec 44">
	<cfparam name="form.Volume" default="#url.Volume#">
 
	<cfparam name="url.Date" default="3/13/1978">
	<cfparam name="form.Date" default="#url.Date#">
 
	<cfset variables.extraBlankRows = 10> <!-- number of extra blank records to add at the end for inserting --->
	<cfset variables.error = ""> <!-- will contain any error messages for display below later -->
<!-------------------------------------------------- End Session Variables -------------------->
 
 
<!-------------------------------------------------- Data Queries ------------------------------------------------>
<!-- Query: Get Employees List from the Volume DL -->
	<cfif len(variables.error) eq 0>
		<!-- fetch the data from the database and convert into an array -->
		<CFQUERY NAME="getVolumeDL" DATASOURCE="#application.datasource#">
			SELECT	IPT, Commodity, BEMSID, Name, Program, Volume
			FROM		tblListDDR_DL
			WHERE	(Program = '#FORM.Program#') AND (Volume = '#FORM.Volume#')
			ORDER BY	Name
		</CFQUERY>
	
		<!-- create a form variable for every record, every column in the format:  form.columName1 -->
		<cfloop query="getVolumeDL">
			<cfloop index="aCol" list="#getVolumeDL.columnList#">
				<cfset form[aCol & getVolumeDL.currentRow] = getVolumeDL[aCol][getVolumeDL.currentRow]>
			</cfloop>
		</cfloop>
	
		<cfset form.totalRecords = getVolumeDL.recordCount>
		
		<!-- create extra form variables that are blank, same format form.columnName2 -->
		<cfloop index="kk" from="1" to="#variables.extraBlankRows#"> 
			<cfset form.totalRecords = form.totalRecords + 1>
			<cfloop index="aCol" list="#getVolumeDL.columnList#">
				<cfset form[aCol & form.totalRecords] = "">
			</cfloop>
		</cfloop>
	</cfif>
 
	<!-- show the error -->
	<cfif len(variables.error)>
		<div style="background-color:pink;color:maroon;padding:10px;font-weight:bold;">
			<cfoutput>Error: #variables.error#</cfoutput>
		</div>
	</cfif>
 
 
<!-- Insert Query: Loops through each record and inserts them into the database -->
<cfif isDefined("form.Submit")> <!-- Code to insert each record -->
	<cftry>
		<!-- loop through every record, add or update depending on whether they already had an ID -->
		<cfloop index="kk" from="1" to="#val(form.totalRecords)#">
			<cfset variables.somethingEntered = true> <!-- assume some data was entered for each record -->
			<cfset form.Present = form['Present' & kk]>
			<cfset form.Name = form['Name' & kk]>
			<cfset form.BEMSID = form['BEMSID' & kk]>
			<cfset form.Commodity = form['Commodity' & kk]>
			<cfset form.IPT = form['IPT' & kk]>
			<cfset FORM.Program = FORM['Program' & kk]>
			<cfset FORM.Volume = FORM['Volume' & kk]>
			<cfset FORM.Date = FORM['Date' & kk]>
			<cfif len(form.Name) + len(form.BEMSID) eq 0>
				<cfset variables.somethingEntered= false> <!-- this record is blank -->
			<cfelse>
				<cfif variables.somethingEntered> <!-- don't save a blank record -->
					<CFQUERY NAME="postAttendance" DATASOURCE="#application.datasource#">
					INSERT INTO	tblDDRAttendance (Present, Name, BEMSID, Commodity, IPT, Program, Volume, Date)
					VALUES		('#FORM.Present#', '#FORM.Name#', '#FORM.BEMSID#', '#FORM.Commodity#', '#FORM.IPT#', '#FORM.Program#', '#FORM.Volume#', '#FORM.Date#')
					</CFQUERY>
				</cfif>
			</cfif>
		</cfloop>
		
		<cfcatch type="Any">
			<cfset variables.error = cfcatch.message>
		</cfcatch>
	</cftry>
</cfif>
 
<!-------------------------------------------------- End Data Queries -------------------->
 
 
<!-------------------------------------------------- Begin Head -------------------------------------------------->
<HEAD>
	<TITLE>Everett Spatial Integration Action Items</TITLE>
 
	<META HTTP-EQUIV="Web-database app" CONTENT="text/html; charset=utf-8" />
	<META NAME="Boeing Brand Center" CONTENT="" />
	<META NAME="Action Item Database" CONTENT="" />
	<META NAME="Jon Bredensteiner" CONTENT="" />
	<META NAME="Description" CONTENT="Pilot Request Database for Everett Programs CPD Site Support" />
	<META NAME="03/11/2009" CONTENT="" />
	<META NAME="Bredensteiner, Jon W." CONTENT="" />
	<META NAME="robots" CONTENT="None" />
	<META HTTP-EQUIV="03/11/2011" CONTENT="-1"/>
 
 
<!-- Begin CSS -->
	<LINK REL="stylesheet" HREF="_css/styles.css" TYPE="text/css" />
	<LINK REL="stylesheet" HREF="_css/print.css" TYPE="text/css" MEDIA="print" />
	<LINK REL="stylesheet" HREF="_css/style.css" TYPE="text/css" />
<!-- End CSS -->
</HEAD>
<!-------------------------------------------------- End Headder -------------------->
 
 
<!-------------------------------------------------- Begin Body -------------------------------------------------->
<BODY>
	<CFINCLUDE TEMPLATE="_includes/header.cfm"><!-- Top Navigation -->
	
	<DIV ID="mainSection">
 
	<CFINCLUDE TEMPLATE="_includes/left_nav.cfm"><!-- Inserts the Left Navigation -->
 
	<CFINCLUDE TEMPLATE="_includes/top_nav.cfm"><!-- Inserts the Horizontal Navigation -->
 
	<DIV ID="personalityZone45"></DIV><!-- Inserts the Website Welcome Header -->
	
	<DIV ID="mainContent">
 
	<P ALIGN="left" STYLE="font-size:100%"><STRONG>DDR Meeting Attendance Homepage</STRONG></P> <!-- Page Title -->
 
 
<!-- ---------------------------------------- Main Table or Text Goes Here ---------------------------------------- -->
	<CFFORM METHOD="POST" NAME="ActionForm" ACTION="TestReceiveData.cfm" ONSUBMIT="return checkForm(this);">
		<CFOUTPUT>
			<TABLE BORDER="1" WIDTH="*" CELLPADDING=5 CELLSPACING=0 BORDERCOLOR="black" CLASS="left">
				<TR>
					<TH NOWRAP="nowrap" VALIGN="middle" ALIGN="center"CLASS="required" COLSPAN=4>Attendance</TH>
				</TR>
		
				<TR>
					<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Present</TD>
					<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Name</TD>
					<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>BEMSID</TD>
					<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="headerrow" COLSPAN=1>Commodity</TD>
				</TR>
				
				<CFLOOP INDEX="ii" FROM="1" TO="#FORM.totalRecords#">
					<TR>
						<TD NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="center" CLASS="normal" COLSPAN="1" >
							<CFINPUT NAME="Present#ii#" ID="Present" TYPE="CHECKBOX"/>
						</TD>
						<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="Name#ii#" ID="Name" TYPE="TEXT" READONLY VALUE="#FORM['Name'&ii]#"></TD>
						<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="BEMSID#ii#" ID="BEMSID" TYPE="TEXT" READONLY VALUE="#FORM['BEMSID'&ii]#"></TD>
						<TD  NOWRAP="nowrap" WIDTH="*" VALIGN="middle" ALIGN="left" CLASS="normal" COLSPAN="1" ><INPUT NAME="Commodity#ii#" ID="Commodity" TYPE="TEXT" READONLY VALUE="#FORM['Commodity'&ii]#"></TD>
						<INPUT NAME="Program#ii#" TYPE="hidden" VALUE="#FORM['Program'&ii]#">
						<INPUT NAME="Volume#ii#" TYPE="hidden" VALUE="#FORM['Volume'&ii]#">
						<INPUT NAME="Date#ii#" TYPE="hidden" VALUE="#FORM['Date'&ii]#">
						<INPUT NAME="IPT#ii#" TYPE="hidden" VALUE="#FORM['IPT'&ii]#">
					</TR>
				</CFLOOP>
			
				<TR>
					<INPUT NAME="totalRecords" TYPE="hidden" VALUE="#FORM.totalRecords#">
					<TH CLASS="required" COLSPAN=4>
						<CFINPUT TYPE="submit" NAME="SubmitAtt" ID="SubmitAtt" VALUE="Submited">
					</TH>
				</TR>
			</TABLE>
		</CFOUTPUT>
	</CFFORM>
<!-- ---------------------------------------- End of Main Table or Text  ------------------ -->
 
 
	</DIV>
	</DIV>
 
	<CFINCLUDE TEMPLATE="_includes/footer.cfm"><!-- Page Footer --->
 
	</BODY>
<!-------------------------------------------------- End Body -------------------->
 
 
<CENTER><DIV ID="updated">Last updated 3/15/2009</DIV></CENTER>
 
</HTML>
<!-------------------------------------------------- End Page -------------------->
 
 
<!-------------------------------------------------- JavaScript -------------------------------------------------->
<!-- JavaScript: ??? -->	
	<SCRIPT LANGUAGE="javascript1.2" SRC="_scripts/javascript.js" TYPE="text/javascript"></SCRIPT>
 
<!-- JavaScript: this loops through the form, and inserts earh row into the database -->	
	<cfif isDefined('FORM.formSubmitted')>
	   <cfloop index="theID" list="#Present#">
		   <CFQUERY NAME="insertAttendance" DATASOURCE="#evtSpatial#">
				 INSERT INTO tblDDRAttendance (BEMSID, Program, Commodity, Volume, IPT, Date)
				 VALUES ('#theID#', #FORM.BEMSID#, #FORM.Program#, #FORM.Commodity#, #FORM.Volume#, #FORM.IPT#, #FORM.Date#)
		   </CFQUERY>
	   </cfloop>
	</cfif>
<!-------------------------------------------------- End JavaScript -------------------->

Open in new window

SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jon Bredensteiner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jon Bredensteiner

ASKER

I'm not really closing the question, I'm simply giving the points to gdemaria, and excepting the code in my last post as the solution, which I couldn't have created without gdemaria's help. Thanks again gdemaria!

Jon Bredensteiner

ASKER

In case you are interested, here is a link to the follow-on question: https://www.experts-exchange.com/questions/24473227/How-can-I-change-the-below-code-to-only-return-the-rows-of-data-that-contain-a-value-in-a-specific-field.html