Add/Delete/Modify Database Record form

I have a form set up in which I want the viewer to be able to select a job title from a list menu. From that selection, I want them to have the option to modify or delete that job selection or add a new job. WIth that I have a "Edit Posting" button, "Delete Posting" or "Add New Posting" button in the form for them to submit the form with. But how do I set it up so each button will submit to the appropriate page, (i.e the modify the selected job page, the delete the selected job page, or the create new job page"

Here is the code I have set up. I am using Coldfusion:

<cfquery name="JobPost"
datasource="kpstool_accesscf_jobs">
SELECT * FROM jobs
</cfquery>
<p class="Photos">Please select an option below:</p>
<form action="UticaUpdateJob.cfm" method="post">
<table width="310" border="1">
<tr>
<td width="120" class="Copy">Job Position: </td>
<td colspan="2"><select name="Title" id="Position" class="formcopy">
<option value="Jobs">Choose One</option>
<cfoutput query="JobPost">
<option value="#JobID#">#Title#</option>
</cfoutput>
</select></td>
</tr>
<tr>
<td><input name="submit" type="submit" id="submit" value="Edit Posting"></td>
<td width="62"><input type="submit" id="Delete Posting" value="Modify Posting"></td>
<td width="106"><input name="submit2" type="submit" id="submit2" value="Insert New Posting"></td>
</tr>
</table>
</form>

Thanks,
Dave

One option would be to place a hidden field in your form.

Attach an event handler to your submit buttons so that JavaScript places a value in the hidden field coresponding to the button pressed.

Your action page can now identify which button was pressed by looking at the value of that hidden and redirect accordingly.

This is the code I have now set up which functions except for one thing. When the user selects the "New Posting" option, I want it to get the insert template. However, currently I have to have a value selected for "Model" in order for it to go to the insert page which obviously doesn't make sense as the button should redirect to the insert template whether a value was selected or not. Basically, if the insert option is selected, I want the page to stop processing and redirect to the insert template. Any ideas as to how I need to revise my code?

<cfoutput>
<cfif isdefined("form.Insert")>
<cfinclude template="../Admincopy/UticaInsertPosting.cfm">
</cfif>
<cfif form.Model is "Model">
<cflocation url="UticaJobPostings.cfm?Message=Please select a Model to edit">
<cfexit>
</cfif>
<cfif IsDefined("form.edit")>
<cfinclude template="../Admincopy/UticaEditJob.cfm">
<cfelseif isdefined("form.Delete")>
<cfinclude template="../Admincopy/UticaDeleteJob.cfm">
</cfif>
</cfoutput>

Never mind- I got it- just threw a cfexit after the include template for the insert posting!

Dave

I seem to have a new problem with my update form page. It seems I am getting a syntax error in my UPDATE statement.

Here is the error message:

Error Executing Database Query.

Syntax error in UPDATE statement.

The error occurred in D:\Hosting\uticarenew\Admincopy\UticaUpdatedJobInf o.cfm: line 92
90 : Date = '#Form.Date#',
91 : Active = '#Form.Active#'
92 : WHERE AutoID = #Form.AutoID#
93 : </cfquery>
94 : <cfoutput>
SQL UPDATE Gas SET Model = 'Chevy', MPG = 20, Date = '1-10-01', Active = 'Yes' WHERE AutoID = 2
DATASOURCE UticaRenew_accesscf_Mileage
VENDORERRORCODE 3092
SQLSTATE &nbsp;

As you can see, it is pulling in the AutoID as being "2". I don't have quotes around the #form.AutoID# so I can't see what the syntax error is. Any ideas??

Dave

Well I figured "Date" was a reserved word and changed it, but still get the same issue:

Syntax error in UPDATE statement.

The error occurred in D:\Hosting\uticarenew\Admincopy\UticaUpdatedJobInf o.cfm: line 92
90 : Date = '#Form.Calendar#',
91 : Active = '#Form.Active#'
92 : WHERE AutoID = #Form.AutoID#
93 : </cfquery>
94 : <cfoutput>
SQL UPDATE Gas SET Model = 'Blake', MPG = 20, Date = '1/11/01', Active = 'Yes' WHERE AutoID = 6
DATASOURCE UticaRenew_accesscf_Mileage
VENDORERRORCODE 3092
SQLSTATE &nbsp;

Also, the Date or "Calendar" field as it is labeled in the database, is a text field, hense the quotes. Active is a checkbox with a "Yes" value

Dave

I figured it out! Forgot to change Date to Calendar in my code. But here's a new problem.

In my submission form, I have a checkbox that I want the user to check if that posting should be active or, come up as a listing when it does a query to display the current listings. (hope that makes sense). I set the checkbox value to be "Yes"
I noticed however that my form will come back with an undefined error for that checkbox if it is not checked. How would I need to revise my code for either the form page or the cfm action page for that form for it to simply not insert a yes value in the database field "Active" for that listing?

Basically, on the display page for the listings, I will have it query only the listings with a "yes" value in the Active field.

Here is a snipit of the code for the checkbox form itself. It is the code for my update listing.

<td><input type="text" name="Calendar" value="#Calendar#"></td>
<tr>
<td><input name="Active" type="checkbox" id="Active" value="Yes"></td>

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

Here is the cfm insert page code for this:

<cfquery name="JobPost"
datasource= "UticaRenew_accesscf_Mileage">
UPDATE Gas
SET Model = '#Form.Model#',
MPG = #Form.MPG#,
Calendar = '#Form.Calendar#',
Active = '#Form.Active#'
WHERE AutoID = #Form.AutoID#
</cfquery>

Dave