Add/Delete/Modify form

I'm relatively new at this but anyway, I am setting up a form for a user to add/modify/delete records in a database. I have it somewhat functioning in that if the user does not select a value from the drop-down menu, it returns an error to please select a listing. My problem however is that I want the user to be able to select the add posting button option without having a value selected in the drop down menu. Here is what I have so far that will only function if a value is selected:

<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>

Any help would be great!

Dave

 

The CFSELECT tag has a 'required' attribute.

Can you post the code for your form? (Where is the select list?)

 

Well I actually got my initial problem worked out. However, 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\UticaUpdatedJobInfo.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

 

DATE is a reserved word in MS Access (I'm assuming from your datasource name that this is an access db). Try changing it to something else. Also, if it is a date/time field, you may have to remove the quotes.

Access reserved words:
http://support.microsoft.com/kb/286335

 

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\UticaUpdatedJobInfo.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 are or "Calendar" as it is labeled in the database, is text.

 

You changed the wrong 'DATE'

You need to change the field name in the database.

eg.:
SET last_updated = '#form.calendar#'

 

Geez! I am SUCH an idiot. Thanks.

Okay one more question. 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

 

Okay, I set it up as follows ( I changed it to default to yes instead of the user having to check it if they want it active):

<cfparam name="form.active" default="Yes" />
<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>

But what seems to be happening is that I modify the record but when I go to the list and grab that updated record, it still displays it as being checked even if I uncheck the box in my update. What isn't happening is when I select a record to update and go to the update form with that record info, the active box is checked no matter what, it doesn't seem to be checking against the database value for that record to see if a "yes" is inserted there.

Dave

 

I think my problem resides in the fact in that when I am pulling a record to update, I need to populate the active check box correctly (i.e, it should be checked if the Active field reads "Yes" or unchecked if there is no value there. So I am trying to figure out how to get the active checkbox to be checked or not. Here is the error I am getting with what I have:

Invalid CFML construct found on line 28 at column 88.

ColdFusion was looking at the following text:
checked

The CFML compiler was processing:

a cfif tag beginning on line 28, column 61.

The error occurred in D:\Hosting\uticarenew\Admincopy\UticaEditJob.cfm: line 28
26 : <td><input type="text" name="Calendar" value="#Calendar#"></td>
27 : <tr>
28 : <td><input name="Active" type="checkbox" id="Active" <cfif #Gas.active# EQ "Yes" checked</cfif>></td>
29 : <input type="hidden" name="AutoID" value="#AutoID#">
30 : <td colspan="2"><input name="submit" type="submit" value="Update Information"></td>

So any ideas of what I need to fix??

Dave

 

Well I figured out that issue but have one more problem. When I test my form, I notice that with each submit, the value of the active box is changed whether I do anything to it or not. (I.E. for one record when I edit it and the box is unchecked at submission, when I go back and pull that record again to the edit form, it is now checked. And if I pull it in a again, it is unchecked, etc.)

Here's the code for the checkbox in the form:
<td>Active:</td>
<input name="Active" type="checkbox" id="Active" <cfif #UpdateModel.Active# EQ "Yes"> checked</cfif>></td>
<tr>

Here's the code for the cfm update page:

<cfparam name="form.active" default="Yes" />
<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

 

Sorry I left you hanging yesterday Dave - I had to run a live web cast yesterday afternoon of a high profile event with lots of press...

A checkbox data only gets posted if the checkbox is checked.

In other words, if the box is unchecked, form.active DOES NOT EXIST in your update page. You have defaulted form.active to YES, so if you do not check the box when you submit the form, form.active will always be YES.

Changing your cfparam to <cfparam name="form.active" default="No" /> should solve the problem.

 

Well I tried it:

<cfparam name="form.active" default="No" />

but now the box remains unchecked no matter what when I query the record into the update form. So it seems it is now ignoring the checkbox value completely when it updates? I also did an output query on the cfm page and it does not show a value for Active while it does for the others. It also didn't show a value when I had the default at Yes.

Any ideas on that??

Thanks,
Dave

 

Hey, I got it figured out- I needed to give a value to the checkbox- Yes.

With that solved, one last issue. If the user does not select insert new item or does not select a listing in the initial menu, I want an error message to come back for them to select an option.

This is how I thought I should set it up, but it doesn't output the message (the message does show in the url though)

<cfif form.Model is "Model">
<cflocation url="UticaJobPostings.cfm?Message=Please select a Model to edit">
<cfif isdefined("url.message")>
<cfoutput>#url.message#</cfoutput>
</cfif>

 

If you localize your variables your code would be much cleaner.