so, I updated a lot in with this code from the casing, the hidden input field. I have not been able to change the validation or work on SQL Injections, but I am getting there. I am having 2 main problems and they both deal with elements not beinga able to be foind in the form.
<cfquery name="pri" datasource="#TEST#">
select priority
from tbl_erd_priority
where unit_supported = '#orgs.unit_supported#'
order by priority asc
</cfquery>
<!---get engineer data--->
<cfquery name="geteng" datasource="#TEST#">
select *
from tbl_erd_eng
where unit_supported = '#orgs.unit_supported#'
order by engineer_name asc
</cfquery>
<!---get all data--->
<cfquery name="eng_work" datasource="#TEST#">
select *
from tbl_erd
where
<cfif isdefined("form.btn_id2")>id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.id#">
<cfelseif isdefined("url.id")>id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#"></cfif>
</cfquery>
<!---get the weapon system for below--->
<cfquery name="ws" datasource="#TEST#">
select *
from tbl_erd_weapsys
where unit_supported = '#orgs.unit_supported#'
</cfquery>
<!---get area data for management listings--->
<cfquery name="getarea" datasource="#TEST#">
select area
from tbl_erd_area
where unit_supported = '#orgs.unit_supported#'
</cfquery>
<!---get eng data--->
<cfquery name="prev" datasource="#TEST#">
select engineer1
from tbl_erd
where
<cfif isdefined("form.btn_id2")>id = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.id#">
<cfelseif isdefined("url.id")>id = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.id#"></cfif>
</cfquery>
<!---get information for cmxg auto comments--->
<cfif #orgs.unit_supported# eq 'TEST'>
<cfquery name="getrecta" datasource="#TEST#">
select *
from tbl_erd
where id = '#form.id#'
</cfquery>
<cfif '#form.engineer1#' is not '#getrecta.engineer1#'>
<cfquery name="eecdinsert" datasource="#TEST#">
insert into erd_robins.dbo.tbl_erd_eng_notes (unit_supported, erd_id, eng_notes)
values (<cfqueryparam cfsqltype="cf_sql_varchar" value="#orgs.unit_supported#">,<cfqueryparam cfsqltype="cf_sql_integer" value="#form.id#">,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.auto_engineer#">);
</cfquery>
</cfif>
</cfif>
<cfoutput>
<cfset prev_eng = #prev.engineer1#>
</cfoutput>
<form action="" method="post" name="erd">
<input type="hidden" name="proj_title_required" value="proj title is required">
<input type="hidden" name="proj_user_notes_required" value="proj user notes are required">
<input type="hidden" name="priority_required" value="priority is required">
<input type="hidden" name="weapsystem_required" value="please provide a weapon system">
<input type="hidden" name="engineer1_required" value="primary engineer is required">
<input type="hidden" name="proj_user_type_required" value="please provide a proj type">
<cfoutput>
<input type="hidden" value="#eng_work.id#" name="track_num">
<input type="hidden" name="prev_eng" value="#prev.engineer1#">
<input type="hidden" value="#eng_work.proj_number#" name="proj_number">
<input type="hidden" value="#eng_work.poc_name#" name="poc_name">
<input type="hidden" value="#eng_work.poc_phone#" name="poc_phone">
<input type="hidden" value="#eng_work.username#" name="username">
<input type="hidden" value="#eng_work.firstname#" name="firstname">
<input type="hidden" value="#eng_work.lastname#" name="lastname">
<input type="hidden" value="#eng_work.phone#" name="phone">
<input type="hidden" value="#eng_work.email#" name="email">
<input type="hidden" value="#eng_work.unit_supported#" name="unit_supported">
<input type="hidden" value="#dateformat(now())#" name="startdate">
<input type="hidden" value="#eng_work.bldg_num#" name="bldg_num">
<input type="hidden" value="#eng_work.proj_type#" name="proj_type">
<input type="hidden" value="#eng_work.proj_status#" name="proj_status">
<input type="hidden" value="#eng_work.bnumber#" name="bnumber" />
<input type="hidden" value="The Project Primary Engineer has been updated-(#dateformat(now(), 'mmm-dd-yyyy')# #timeformat(now(), 'hh:mm:ss tt')#)-#session.lastname#" name="auto_engineer">
</cfoutput>
<tr>
<td bgcolor="ffffff">
<cfoutput>
<a href="erd_adm_manage.cfm?area=#eng_work.proj_user_type#&username=#session.username#&unit_supported=#orgs.unit_supported#" style="font: 8pt verdana; color: ff0000" target="userform"><u><b>back</b></u></a><br><br>
</cfoutput>
</td>
</tr>
<tr>
<td align="center">
<table cellspacing="5" cellpadding="3" bgcolor="efefef" align="center" border="1" width="750" bordercolor="e5e5e5">
<tr bgcolor="f5ead8">
<td colspan="6" style="font: 9pt verdana; color: 000000" align="left" valign="top">
<b>"<cfoutput>#eng_work.proj_user_type#</cfoutput> Work Request" - <font color="0000ff">RE-ASSIGN</font> Form</b>
</td>
</tr>
<tr bgcolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" colspan="6" valign="top">
<u>Use the following form to make your consideration:</u><br>
<br>
<font color="993399" size="3">*</font> <font color="006666" size="3">*</font> Are done prior to assignment - if they are to be updated
<!---show message coming into this page--->
<cfif isdefined("url.message")>
<b style="font: 10pt verdana; color: ffff00">◊</b><b style="font: 10pt verdana; color: 0033ff"><cfoutput>
#iif(isvalid("string", url.message), de("#htmleditformat(url.message)#"), de("this is not valid text"))#
</cfoutput></b>
</cfif>
</td>
</tr>
<cfoutput query="eng_work">
<tr bgcolor="f5ead8">
<td colspan="3">
<table border="2" bordercolor="cccccc" cellpadding="1" cellspacing="1" width="350">
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<font color="993399" size="3">*</font> Project Title:
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<input type="text" label="proj_title" value="#proj_title#" name="proj_title" size="30">
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<font color="993399" size="3">*</font> Project Desc.:
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<textarea style="font: 8pt verdana; color: 000000" name="proj_user_notes" cols=33 rows=5 wrap="virtual">#proj_user_notes#</textarea>
</td>
</tr>
</table>
</td>
<td colspan="3" valign="top">
<table border="2" bordercolor="cccccc" cellpadding="1" cellspacing="1" width="400">
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Requestor:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#firstname# #lastname#</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Email:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#email#</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Phone:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#phone#</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Track Num.:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#id#</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Building:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#bnumber#</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Org.:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#unit_supported#</td>
</tr>
</table>
</td>
</tr>
<tr bgcolor="f5ead8">
<td valign="top" colspan="3">
<table border="2" bordercolor="cccccc" cellpadding="1" cellspacing="1" width="350">
<cfif #orgs.unit_supported# eq '402d emxg'>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_name#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC Phone:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_phone#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Assigned:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat(now())#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Submitted:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat('#eng_work.dateofentry#', 'dd-mmm-yy')#
</td>
</tr>
<cfelseif #orgs.unit_supported# eq '402d cmxg'>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_name#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC phone:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_phone#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Assigned:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat(now())#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Submitted:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat('#eng_work.dateofentry#', 'dd-mmm-yy')#
</td>
</tr>
<cfelse>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_name#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Alternate POC Phone:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#poc_phone#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Assigned:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat(now())#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Date Submitted:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">
#dateformat('#eng_work.dateofentry#', 'dd-mmm-yy')#
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">Proj Num:</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bgcolor="f4f7f7" bordercolor="cccccc">#proj_number#</td>
</tr>
</cfif>
</cfoutput>
</table>
</td>
<td valign="top" colspan="3">
<table border="2" bordercolor="cccccc" cellpadding="1" cellspacing="1" width="400">
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<font color="0000cc" size="3">*</font> Primary Eng:
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bordercolor="cccccc">
<select name="engineer1" style="font: 7pt verdana; color: 000033">
<!---'reset' is the value needed to stop the clock for the current engineer assigned to this record--->
<option value="reset">---Select----------</option>
<option value=""></option>
<cfoutput query="geteng">
<option value="#htmleditformat(engineer_name)#" <cfif #engineer_name# is #eng_work.engineer1#>selected</cfif>>#htmleditformat(engineer_name)#</option>
</cfoutput>
</select>
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
Alternate Eng:
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bordercolor="cccccc">
<select name="engineer2" style="font: 7pt verdana; color: 000033">
<option value="">---select----------</option>
<option value=""></option>
<cfoutput query="geteng">
<option value="#htmleditformat(engineer_name)#" <cfif #engineer_name# is #eng_work.engineer2#>selected</cfif>>#htmleditformat(engineer_name)#</option>
</cfoutput>
</select>
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" >
<font color="993399" size="3">*</font> <cfif #orgs.unit_supported# eq '402d emxg' or #orgs.unit_supported# eq '402d cmxg'>Org:<cfelseif #orgs.unit_supported# eq '402d smxg'>Squadron<cfelse>Weapon System:</cfif>
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bordercolor="cccccc">
<select name="weapsystem" style="font: 7pt verdana; color: 000033">
<option value="">---select----------</option>
<option value=""></option>
<cfoutput query="ws">
<option value="#htmleditformat(weapsys)#" <cfif #weapsys# is #eng_work.weapsystem#>selected</cfif>>#htmleditformat(weapsys)#</option>
</cfoutput>
</select>
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<font color="006666" size="3">*</font> Project Area:
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top" bordercolor="cccccc">
<select name="proj_user_type" style="font: 7pt verdana; color: 000033">
<option value="">---select----------</option>
<option value=""></option>
<cfoutput query="getarea">
<option value="#htmleditformat(area)#" <cfif #area# is #eng_work.proj_user_type#>selected</cfif>>#htmleditformat(area)#</option>
</cfoutput>
</select>
</td>
</tr>
<tr bordercolor="f5ead8">
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<font color="993399" size="3">*</font> Priority
</td>
<td style="font: 8pt verdana; color: 000000" align="left" valign="top">
<select name="priority" style="font: 7pt verdana; color: 000033">
<option value="">---select----------</option>
<option value=""></option>
<cfoutput query="pri">
<option value="#htmleditformat(priority)#" <cfif #priority# is #eng_work.priority#>selected</cfif>>#htmleditformat(priority)#</option>
</cfoutput>
</select>
</td>
</tr>
</table>
</td>
</tr>
<tr bgcolor="f5ead8">
<td colspan="6" align="center">
<table border="2" bordercolor="cccccc" cellpadding="1" cellspacing="1" width="500">
<tr bordercolor="fef1de">
<td width="20%" style="font: 9pt verdana; color: 000000" align="center" valign="top">
<input type="submit" name="btn_delete1" value=" Delete Request" style="font: 9pt arial, helvetica, sans-serif ; color: #cc0033; font-weight:bold;" onclick="return confirm('on the next page - please provide a reason for deleting this request')">
</td>
<td width="20%" style="font: 9pt verdana; color: 000000" align="center" valign="top">
<input type="submit" name="btn_update2" value="Change Proj Type" style="font: 9pt arial, helvetica, sans-serif ; color: #006666; font-weight:bold;">
</td>
<td width="20%" style="font: 9pt verdana; color: 000000" align="center" valign="top">
<input type="submit" name="btn_updaterequest2" value=" Update Request " style="font: 9pt arial, helvetica, sans-serif ; color: #993399; font-weight:bold;" onclick="return confirm('you are about to update this work request - are you sure?')">
</td>
<td width="40%" style="font: 9pt verdana; color: 000000" align="center" valign="top">
<input type="submit" name="btn_assign2" value=" Assign Request " style="font: 9pt arial, helvetica, sans-serif ; color: #0000cc; font-weight:bold;" onclick="return confirm('you are about to assign this work request - are you sure?')">
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</form>
Ok so tthe two elements I am having problems with are form.engineer1 and form.auto_engineer. The auto_engineer field is located in the output section of the area where the hidden input fields are. And the engineer1 field is located towards the bottom of the form. I am using the naming conventions right and everythign. I do not uncewrstand what the problem could be.
I hope thi is easier to read now, and any help would be appreciated.
Ok here are some improvements and notes for you.
First this select query. You are doing a select * query. Are you wanting to to pull this to the page every time? Or only when you want to do an insert (that doesn't seem right).
On the next if statement and the insert query I have made changes too.
In your insert query you are seeking some form vars and that orgs.unit_supported looks like a struct.
Anyways when we have an insert query from a form we usually look for a variable that will be present. There are lots to choose from below.
To do this you would wrap your insert query with something like
<cfif isdefined(form.auto_engineer)></cfif>The next concern is that you are wide open for SQL injection. Consider using
<cfqueryparam>See your code above for an example.Removed the output tags and the hash tags. You don't need to wrap cfoutput around cfset and you don't need hash tags for already set variables.
<cfset prev_eng = prev.engineer1>If you are posting this form to 'this' page the you can make the action blank and like magic the page will self post...(Unless you have a worker page that does stuff).
But we will assume that you are not.
Question are all your inputs for this form really supposed be 'hidden' or are you wanting to show stuff because you are informing them of stuff that they might have done wrong which would be confusing because that would be form handling logic not inputs).
But I have seen this when people aggregate form stuff like in form wizards.
This stuff here...Don't do this:
I understand what you want to do above but it is unorthodox and pretty wierd.
Consider form checks like this (after you click submit):
...or something like that.
Now for the last part...the textarea:
You are hiding this? Ok. but you don't need to hide this in a text area. I put it in a hidden input. Note as long as your double quotes surrounding the value and single quotes inside your text strings. You should be all good.
And that solves that problem.
Consider refactoring all this...and next time...hit up the stack exchange code review website for this type of thing.
Good luck!