6 Replies Latest reply on Jul 19, 2010 7:49 AM by charlie@carehart.org

    CFUPDATE versus SQL UPDATE

    EwokStud

      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

        • 1. Re: CFUPDATE versus SQL UPDATE
          Jochem van Dieten Level 4

          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

          • 2. CFSELECT choice from database not reflected in edit page for field
            EwokStud Level 1

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

            • 3. Re: CFUPDATE versus SQL UPDATE
              Jochem van Dieten Level 4

              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.

              • 4. Re: CFUPDATE versus SQL UPDATE
                EwokStud Level 1

                Checkbox was eliminated (commented out) as per your recommendation.  The topic for Spices and Seasonings Lesson is still coming as Fruits....in the topic dropdown (CFSELECT) on the detail page.  The checkbox was also eliminated in the list of fields to update (on the update page vwp_swdc_lsn_det_e_ud.cfm).

                 

                Food Lesson Listing:

                http://65.61.35.197/family/doug/bd/hby/cook/vwp_swdc_lsn_mstr_lst.cfm

                 

                Edit a particular line item (record/lesson) by clicking on the pencil graphic.  Let's say The Balance of Spices Lesson Lesson (call_number=4)

                http://65.61.35.197/family/doug/bd/hby/cook/vwp_swdc_lsn_det_e.cfm?call_number=4

                 

                I then successfully arrive at the editing page for the record "The Balance of Spices", change the topic from Fruits to Spices and Seasonings (with the hidden ID field in the CFSELECT).  Click submit....

                Error:

                Error Occurred While Processing Request 
                Error Executing Database Query. 
                [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. 
                 
                The error occurred in E:\benoitsystems.com\wwwroot\family\doug\bd\hby\cook\vwp_swdc_lsn_det_e_ud.cfm: line 11

                9 :  swdc_lsn_tpc_='#form.obj_swdc_lsn_tpc_#',
                10 :  swdc_lsn_dur_='#form.obj_swdc_lsn_dur_#'
                11 : WHERE call_number=#form.obj_swdc_lsnID#;
                12 : </CFQUERY>
                13 :

                 

                --------------------------------------------------------------------------------

                SQL    UPDATE vwp_swdc_lsn SET swdc_lsn_pre_='1', swdc_lsn='The Balance of Spices', swdc_lsn_n_='3', swdc_lsn_tpc_='1', swdc_lsn_dur_='45' WHERE call_number=4; 
                DATASOURCE   9130.vwp
                VENDORERRORCODE   -3010
                SQLSTATE   07002

                Please try the following:
                Check the ColdFusion documentation to verify that you are using the correct syntax.
                Search the Knowledge Base to find a solution to your problem.


                Browser   Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)
                Remote Address   71.233.234.226
                Referrer   http://65.61.35.197/family/doug/bd/hby/cook/vwp_swdc_lsn_det_e.cfm?call_number=4
                Date/Time   17-Jul-10 05:22 PM

                Stack Trace (click to expand)

                 

                Though,...I changed some of teh database field names (removing the _rf extension) I also changed these in the CF code,...that's not a problem.

                 

                Doug

                • 5. Re: CFUPDATE versus SQL UPDATE
                  charlie@carehart.org Adobe Community Professional & MVP

                  I'll just throw in that this seems a discussion better suited to one of the CF forums (rather than CFBuilder). I suppose this will happen from time to time as people new to CF get introduced to CFBuilder as "the editor" and therefore they assume this forum is a place to ask getting started questions.

                   

                  EwokStud, you may want to check out:

                   

                  http://forums.adobe.com/community/coldfusion/coldfusion_getting_started

                  http://forums.adobe.com/community/coldfusion/coldfusion_general

                   

                  Others at http://forums.adobe.com/community/coldfusion.

                   

                  /charlie

                  • 6. Re: CFUPDATE versus SQL UPDATE
                    charlie@carehart.org Adobe Community Professional & MVP

                    EwokStud, as you've changed this discussion to a new thread title, I'll repeat my comment I made just at the end of your last one. (I can't tell if you read it before you chose to create this new topic).

                     

                    Your question is not about CFBuilder (the IDE). As such, you really should take it to the correct CF forum. I'd recommend either:

                     

                     

                    http://forums.adobe.com/community/coldfusion/coldfusion_getting_started

                    http://forums.adobe.com/community/coldfusion/coldfusion_general

                     

                    Others at http://forums.adobe.com/community/coldfusion.

                     

                    Since people reading this may not see my note in that other topic thread (http://forums.adobe.com/message/2981760#2983674), I also noted that "I suppose this will happen from time to time as people new to CF get introduced to CFBuilder as "the editor" and therefore they assume this forum is a place to ask getting started questions."

                     

                    It's not to discourage such questions, but rather simply to direct people to the proper forums for different topics. Hope that's helpful.

                     

                    /charlie