Skip to main content
E
EwokStud
Known Participant
July 17, 2010
Question

CFUPDATE versus SQL UPDATE

  • July 17, 2010
  • 1 reply
  • 1739 views

I have followed the manuals'

code for updating a record in the database via a webpage, and the update page produces an error.  I have received two or three errors, but the update is never successful.  Below, I have the templates - with their code.  Please help.

====================================================================

vwp_swdc_lsn_mstr_lst.cfm

This is the master list of lesson plans - it works great. - (a candiddate for CFCACHE)

<CFQUERY NAME="ViewLessonsMstr" DATASOURCE="vwp">
SELECT swdc_lsn.*, IIf([swdc_lsn_del_x]=Yes,'Yes','') AS swdc_lsn_del_z, swdc_crs_rf.*, swdc_tpc_rf.*, swdc_lvl_rf.swdc_lvl_clr
FROM ((swdc_lsn LEFT JOIN swdc_tpc_rf ON swdc_lsn.swdc_lsn_tpc_ = swdc_tpc_rf.swdc_tpc_rfID) LEFT JOIN swdc_crs_rf ON swdc_tpc_rf.swdc_tpc_crs_ = swdc_crs_rf.swdc_crs_rfID) LEFT JOIN swdc_lvl_rf ON swdc_crs_rf.swdc_crs_lvl_ = swdc_lvl_rf.swdc_lvl_rfID
ORDER BY swdc_lsn.swdc_lsn;
</CFQUERY>

<CFQUERY NAME="_css" DATASOURCE="vwp">
SELECT site_txt_misc.*
FROM site_txt_misc
WHERE ((vwps_incl_ttl)='CSS');
</CFQUERY>


<!--
// © 2010
// The Vivace Web Project
// All Rights Reserved
// -->
<HTML>
<HEAD>
<TITLE>Lesson Master List</TITLE>

<CFOUTPUT QUERY="_css">#site_txt_misc#</CFOUTPUT>

</HEAD>
<BODY>
<H1 CLASS="H1_c17">School of Web Development</H1>
<H2 CLASS="H2_c16">Curriculum</H2>
<H3 CLASS="H3_c15">Lesson Master List</H3>
<BR>
<TABLE
BORDER="0"
CELLPADDING="5"
CELLSPACING="0"
BORDERCOLOR="white"
BGCOLOR="#FFFFFF">

<!-- Headings -->

<TR CLASS="td_hd">
<TD>
  
</TD>
<TD>
  Lesson Name
</TD>
<TD>
  Course Name
</TD>
<TD ALIGN="center">
  Level
</TD>
<TD WIDTH=15>
  Minutes
</TD>
<TD WIDTH=15>
  Del
</TD>
</TR>
<CFOUTPUT QUERY="ViewLessonsMstr">


<!-- data -->
<TR BGCOLOR=#swdc_lvl_clr#>
<TD>
<A HREF="vwp_swdc_lsn_det_e.cfm?call_number=#swdc_lsnID#">
<IMG
  SRC="C:/acb/img/util/bullets/a_note.gif"
  WIDTH="24"
  HEIGHT="24"
  BORDER="0">
</A>
</TD>
<TD>
<A HREF="vwp_swdc_lsn_det_e.cfm?call_number=#swdc_lsnID#">#swdc_lsn#</A>
</TD>
<TD>
#swdc_crs_rf#
</TD>
<TD ALIGN="center">
#swdc_crs_lvl_#
</TD>
<TD WIDTH=15>
#swdc_lsn_dur_#
</TD>
<TD WIDTH=15>
#swdc_lsn_del_z#
</TD>
</TR>
</CFOUTPUT>
</TABLE>
</BODY>
</HTML>

====================================================================

vwp_swdc_lsn_det_e.cfm

- Having trouble with a select box (tried CFSELECT with CFFORM and had trouble, but I'd prefer to use CFSELECT)

- The selectbox already has a choice in the database, and the record being edited is not reflecting the current value for the field: swdc_lsn_tpc_ during editing

<CFQUERY NAME="q_swdc_lsn_det_e" DATASOURCE="vwp">
SELECT swdc_lsn.*
FROM swdc_lsn
WHERE #call_number#=swdc_lsnID;
</CFQUERY>

<CFQUERY NAME="_css" DATASOURCE="vwp">
SELECT site_txt_misc.*
FROM site_txt_misc
WHERE ((vwps_incl_ttl)='CSS');
</CFQUERY>

<!--- query used for field dropdown in table.field: swdc_lsn.swdc_lsn_tpc_ --->
<CFQUERY NAME="q_swdc_lsn_tpc" DATASOURCE="vwp">
SELECT swdc_tpc_rfID, swdc_tpc_r
FROM swdc_tpc_rf
ORDER BY swdc_tpc_r;
</CFQUERY>
<!--
// © 2010
// The Vivace Web Project
// All Rights Reserved
// -->
<HTML>
<HEAD>
<TITLE>Edit This Lesson</TITLE>
<CFOUTPUT QUERY="_css">#site_txt_misc#</CFOUTPUT>
</HEAD>
<BODY>
<H1 CLASS="H1_c17">School of Web Development</H1>
<H2 CLASS="H2_c16">Curriculum</H2>
<H3 CLASS="H3_c15">Lesson Detail Edit Page</H3>
<DIV ALIGN="left">
<!-- f_vwp_swdc_lsn_det_e -->
<FORM
ACTION="vwp_swdc_lsn_det_e_ud.cfm"
NAME="form">

<CFOUTPUT QUERY="q_swdc_lsn_det_e">
<INPUT
TYPE="hidden"
VALUE="#swdc_lsnID#"
NAME="swdc_lsnID">
<P>
Lesson Pre Title (The, A, or An):
<INPUT
TYPE="textbox"
VALUE="#swdc_lsn_pre_#"
NAME="swdc_lsn_pre_"
MAXLENGTH="1"
SIZE="3">
<P>
Lesson Title or Name:
<INPUT
TYPE="textbox"
VALUE="#swdc_lsn#"
NAME="swdc_lsn"
MAXLENGTH="100"
SIZE="100"> <!-- database character max for this field -->
<P>
Lesson Topic:
</CFOUTPUT>


<SELECT

<CFOUTPUT QUERY="q_swdc_lsn_tpc">
 
  NAME="swdc_lsn_tpc_">

  <OPTION VALUE="swdc_tpc_rfID">#swdc_tpc_r#</OPTION>
</CFOUTPUT>
</SELECT>
<CFOUTPUT QUERY="q_swdc_lsn_det_e">

<P>
Lesson Sequence Number:
<INPUT
TYPE="textbox"
VALUE="#swdc_lsn_n_#"
NAME="swdc_lsn_n_"
MAXLENGTH="3"
SIZE="5">
<P>
Lesson Expected Duration:
<INPUT
TYPE="textbox"
VALUE="#swdc_lsn_dur_#"
NAME="swdc_lsn_dur_"
MAXLENGTH="3"
SIZE="5">
<P>
Select here to delete this lesson:
<INPUT
TYPE="checkbox"
VALUE="#swdc_lsn_del_x#"
NAME="swdc_lsn_del_x">
<P>
<DIV ALIGN="center">
<INPUT
TYPE="Submit"
VALUE="submit"
NAME="submit">
</DIV>
</CFOUTPUT>
</FORM>
</DIV>
</BODY>
</HTML>

====================================================================

vwp_swdc_lsn_det_e_ud.cfm

- The next problem is that certain fields are not recognized, but I tripled checked field spellings

- unsuccessful update (I tried CFUPDATE also)

<!--- <CFUPDATE DATASOURCE="vwp" TABLENAME="swdc_lsn"> --->

<CFQUERY DATASOURCE="vwp" NAME="q_swdc_lsn_det_e_ud">
UPDATE swdc_lsn
SET
swdc_lsn_pre_='#form.swdc_lsn_pre_#',
swdc_lsn='#form.swdc_lsn#',
swdc_lsn_n_='#form.swdc_lsn_n_#',
swdc_lsn_tpc_='#form.swdc_lsn_tpc_#',
swdc_lsn_dur_='#form.swdc_lsn_dur_#',
swdc_lsn_del_x='#form.swdc_lsn_del_x#'
WHERE call_number=#form.swdc_lsnID#;
</CFQUERY>

<HTML>
<HEAD>
<TITLE>Update Verification</TITLE>
<!--- <CFOUTPUT QUERY="incl_css_bd">#incl_code#</CFOUTPUT> --->
</HEAD>
<BODY>
<DIV ALIGN="center">
<TABLE
  BORDER="0"
  WIDTH="100%"
  HEIGHT="100%"
  CELLPADDING="0"
  CELLSPACING="0">
<TR>
  <TD HEIGHT="100%" ALIGN="center" VALIGN="middle">
Your edit has been saved.
  </TD>
</TR>
</TABLE>
</DIV>
</BODY>
</HTML>

====================================================================

(My server I rent space from doesn't yet use Access 2007)

The database file: vwp.mdb

The table: swdc_lsn (the table of lesson plans)

     Fields:

          swdc_lsnID (Autonumber)

          swdc_lsn_pre_ (choice of "The", "An", "A" Byte)

          swdc_lsn (Title of lesson plan: Text-100)

          swdc_lsn_n_ (lesson plan sequence number: Byte)

          swdc_lsn_tpc_ (Topic of this lesson plan - dropdown fed by table: swdc_tpc_rf: LongInteger)

          swdc_lsn_del_x (deletion of this lesson plan is emminent: checkbox)

The table: swdc_tpc_rf (the table of topics)

     Fields:

          swdc_tpc_rfID (Autonumber)

          swdc_tpc_r (Title of Topic: Text-100) {I originally spelled this field as swdc_tpc_rf, but changed it to experiment}

          swdc_tpc_crs_ (Course - dropdown fed by table: swdc_crs_rf: LongInteger)

          swdc_tpc_del_x (deletion of this lesson plan is emminent: checkbox)

====================================================================

In summary, I get a little confused with when to use the number signs and when not to, becuase I have noticed that sometimes, the CF code doesn't use them!  Also, the VALUE attribute in the INPUT objects - I beleive - is the database field name, but I noticed that the update page refers to the NAME attribute, ie. form.objectname instead of the value (which is the database field name).

Message was edited by: EwokStud

This topic has been closed for replies.

1 reply

J
Jochem van Dieten
Inspiring
July 17, 2010

Please show the error message and only the part of the code that is relevant for your problem. We are all busy people and you are more likely to get help if we don't have to search through your code to find the relevant part. See http://forums.adobe.com/thread/607238?tstart=0

E
EwokStudAuthor
Known Participant
July 17, 2010

This is a new abbreviated version of my first post (which was originally titled poorly as "CFUPDATE versus SQL UPDATE").  The 3 problems remain the same.
======================================
vwp_swdc_lsn_det_e.cfm
- edit page

<CFQUERY NAME="q_swdc_lsn_det_e" DATASOURCE="vwp">
SELECT swdc_lsn.*
FROM swdc_lsn
WHERE #call_number#=swdc_lsnID;
</CFQUERY>

<CFQUERY NAME="q_swdc_lsn_tpc" DATASOURCE="vwp">
SELECT swdc_tpc_rfID, swdc_tpc_r
FROM swdc_tpc_rf
ORDER BY swdc_tpc_r;
</CFQUERY>
---snippet from page---
<CFFORM
ACTION="vwp_swdc_lsn_det_e_ud.cfm"
NAME="f_vwp_swdc_lsn_det_e">

<CFOUTPUT QUERY="q_swdc_lsn_det_e">
---form snippet---
<INPUT
TYPE="checkbox"
VALUE="#swdc_lsn_del_x#"
NAME="obj_swdc_lsn_del_x">

Lesson Topic:
<CFSELECT
  QUERY="q_swdc_lsn_tpc"
  NAME="swdc_lsn_tpc_"
  SIZE="1"
  REQUIRED="No"
  VALUE="swdc_lsn_tpc_"
  MULTIPLE="no"
  DISPLAY="swdc_tpc_r">
</CFSELECT>
---end form snippet---
</CFFORM>
---end page snippet---
======================================
vwp_swdc_lsn_det_e_ud.cfm
- update page
<CFQUERY DATASOURCE="vwp" NAME="q_swdc_lsn_det_e_ud">
UPDATE swdc_lsn
SET
swdc_lsn_pre_='#form.obj_swdc_lsn_pre_#',
swdc_lsn='#form.obj_swdc_lsn#',
swdc_lsn_n_='#form.obj_swdc_lsn_n_#',
swdc_lsn_tpc_='#form.obj_swdc_lsn_tpc_#',
swdc_lsn_dur_='#form.obj_swdc_lsn_dur_#',
swdc_lsn_del_x='#form.obj_swdc_lsn_del_x#'
WHERE call_number=#form.obj_swdc_lsnID#;
</CFQUERY>
======================================
Using vwp.mdb with two tables: swdc_lsn, and swdc_tpc_rf

swdc_lsn: (fields in the update SQL above)
swdc_tpc_rf: (fields: swdc_rfID (Autonumber), swdc_tpc_r (text), and non-pertinent fields)

Given: tpc=topic, lsn=lesson

Problem 1: The edit page for the lesson displays well (no error), but the dropdown
topic field in the lesson table (swdc_lsn_tpc_), which is a Long Integer field, is not displaying the current choice.  In other words, the the lesson on apples should display the topic called fruits (using its ID); it is not.  Instead, the edit page displays all the fields correctly, except in the CFSELECT dropdown list (nbr field for topic).  I know that in the database, the topic field is correct (fruits).  However, the first choice in CFSELECT is cookbooks (Given: cookbooks, fruit, spices, and vegetables.)
-------------------------
Problem 2: The submit does take me to the correct template using the correct linkage of id's, but the error is:

Error Occurred While Processing Request
Error Diagnostic Information
Error resolving parameter FORM.OBJ_SWDC_LSN_DEL_X

The specified form field cannot be found. This problem is very likely due to the fact that you have misspelled the form field name.

The error occurred while evaluating the expression:

#form.obj_swdc_lsn_del_x#

The error occurred while processing an element with a general identifier of (#form.obj_swdc_lsn_del_x#), occupying document position (11:18) to (11:42).

Remote Address: 127.0.0.1
HTTP Referer: http://127.0.0.1/vwp/School/Curric/vwp_swdc_lsn_det_e.cfm?call_number=32
Template: C:\vwp\School\Curric\vwp_swdc_lsn_det_e_ud.cfm

-------------------------
Problem 3: is CFINSERT and CFUPDATE still used?   my environment is CF 4.5

end post======================================

J
Jochem van Dieten
Inspiring
July 17, 2010

EwokStud wrote on 7/17/2010 7:37 PM:

Problem 1: The edit page for the lesson displays well (no error), but the dropdown

topic field in the lesson table (swdc_lsn_tpc_), which is a Long Integer field, is not displaying the current choice.

Use # around the selected value in the code.

Problem 2: The submit does take me to the correct template using the correct linkage of id's, but the error is:

Error resolving parameter FORM.OBJ_SWDC_LSN_DEL_X

The specified form field cannot be found. This problem is very likely due to the fact that you have misspelled the form field name.

This problem occurs because you use a checkbox in the form. A checkbox

only exists in the action page if it is selected. Use a radio button in

your form or isDefined() in your action page to make sure the variable

exists. If you need more info, read the chapter on forms in the HTML

4.01 standard at w3c.org.

Problem 3: is CFINSERT and CFUPDATE still used? my environment is CF 4.5

I believe cfinsert and cfupdate run the risk of SQL injection attacks in

CF 4.5. Not many people use them.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices