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

Update query not working from edit page

  • July 21, 2010
  • 1 reply
  • 14451 views

Next incident with the guestbook saga:

Successful execution of gb_entry_mstr_det_e.cfm with URL:
http://www.benoitsystems.com/ww/bd/gb/gb_entry_mstr_det_e.cfm?call_number=14 all have database current info...this is good, but:

textboxes

Problem 1:
the SELECT field does not does not reflect/display the correct option I know exists in the database
the database field whence this SELECT object is supposed to display is a number, not text.
now,...

Problem 2:
Clicked on "Update Your Entry" button at bottom of edit page (to update by going to gb_confirm_update.cfm)
<INPUT
TYPE="submit"
NAME="submit"
VALUE="Update Your Entry">

then, arriving at the gb_confirm_update.cfm page, ...

Got an error (below) executing the page: gb_confirm_update.cfm with resulting URL:

http://www.benoitsystems.com/ww/bd/gb/gb_confirm_update.cfm?call_number=#gb_entryID#

--- snippet from template gb_confirm_update.cfm
<CFQUERY DATASOURCE="9130.ww" NAME="ww_gb_ud">
UPDATE gb_entries
SET
gb_entry_stts_='form.gb_entry_stts_',
gb_entry_nm_f='form.gb_entry_nm_f',
gb_entry_nm_l='form.gb_entry_nm_l',
gb_entry_nm_l_dspl_x=form.gb_entry_nm_l_dspl_x,
gb_entry_tce='form.gb_entry_tce',
gb_entry_tce_dspl_x=form.gb_entry_tce_dspl_x,
gb_entry_cy='form.gb_entry_cy',
gb_entry_stt='form.gb_entry_stt',
gb_entry_instr='form.gb_entry_instr',
gb_entry_m='form.gb_entry_m',
gb_entry_del_x=form.gb_entry_del_x
WHERE gb_entryID=form.gb_entryID
</CFQUERY>
--- end snippet ---

=================================================
Error Executing Database Query. 
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4. 
 
The error occurred in E:\benoitsystems.com\wwwroot\ww\bd\gb\gb_confirm_update.cfm: line 2

1 : <!--- <CFUPDATE DATASOURCE="9130.ww" TABLENAME="gb_entries"> --->
2 : <CFQUERY DATASOURCE="9130.ww" NAME="ww_gb_ud">
3 : UPDATE gb_entries
4 : SET
--------------------------------------------------------------------------------
SQL    UPDATE gb_entries SET gb_entry_stts_='form.gb_entry_stts_', gb_entry_nm_f='form.gb_entry_nm_f', gb_entry_nm_l='form.gb_entry_nm_l', gb_entry_nm_l_dspl_x=form.gb_entry_nm_l_dspl_x, gb_entry_tce='form.gb_entry_tce', gb_entry_tce_dspl_x=form.gb_entry_tce_dspl_x, gb_entry_cy='form.gb_entry_cy', gb_entry_stt='form.gb_entry_stt', gb_entry_instr='form.gb_entry_instr', gb_entry_m='form.gb_entry_m', gb_entry_del_x=form.gb_entry_del_x WHERE gb_entryID=form.gb_entryID 
DATASOURCE   9130.ww
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://www.benoitsystems.com/ww/bd/gb/gb_entry_mstr_det_e.cfm?call_number=14
Date/Time   21-Jul-10 03:11 PM
=================================================
I have every NAME of each object matching, verbatum, in the UPDATE query, and every database field name correct in teh query also.

    This topic has been closed for replies.

    1 reply

    J
    JR__Bob__Dobbs
    Inspiring
    July 21, 2010

    I suspect that you want to update the columns to the value of the form fields, not a literal value of 'form.gb_entry_stts_' for example.

    Try using the CFQUERYPARAM tag in your SQL statements.

    Here is a snippet that might help.  Note that variable names are surrounded by pound signs.


    <CFQUERY DATASOURCE="9130.ww" NAME="ww_gb_ud">
    UPDATE gb_entries
    SET
    gb_entry_stts_= <cfqueryparam value="#form.gb_entry_stts_#" cfsqltype="CF_SQL_VARCHAR" />,
    gb_entry_nm_f= <cfqueryparam value="#form.gb_entry_nm_f#" cfsqltype="CF_SQL_VARCHAR" />    
    WHERE gb_entryID= <cfqueryparam value="#form.gb_entryID#" cfsqltytpe="CF_SQL_INTEGER" />
    </CFQUERY>


    CFQUERYPARAM
    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

    Message was edited by: JR "Bob" Dobbs  Corrected spelling error in code sample.

    E
    EwokStudAuthor
    Known Participant
    July 25, 2010

    I learned that the CFQUERYPARAM is for security and may make the query run faster and/or more efficiently, but the query should be able to operate with or without the CFQUERYPARAM, right?

    Well, let's see...the guestbook is not a high priority target, and the database is all full of bogus info - to test the guestbook - and will be deleted and replaced.  In other words, the database is not the one I'll be using after I get everything working.  I see what you are saying, but I'd like to get the "skeletal code" of the Update Query working before I add the Security.  However, I added the CFQUERYPARAM anyway, but the field which contains the status -  gb_entry_stts_  - is a number field and so I changed the cfsqltype="CF_SQL_VARCHAR" /> to cfsqltype="CF_SQL_INTEGER" /> but did I do it right?  Should I use BIGINT if the field is a Long Integer in the database?  Do I use SMALLINT or TINYINT if the database field is a byte?

    There is a hard space before your slash; is this required?

    What if there is no need for a where clause?  If I wish to have all the records in the query display, how is the CFQUERYPARAM used then?

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

    My second issue - yet to be addressed - is why the edit webpage (of a single database record) which has the current details (db field values) ready for edit, does not display in the SELECT object, as I know the database contains:

       <B>Entry Status:</B>
       <SELECT
        NAME="gb_entry_stts_"
        VALUE="#gb_entry_stts_#">
        <CFOUTPUT QUERY="q_stts">
         <OPTION>#gb_stts#</OPTION>
        </CFOUTPUT>
       </SELECT>

    Given, that the database field in the guestbook  -  gb_entry_stts_  -  is a number, the ID# of the "feeding" status table: gb_status_rf has 3 records whose ID and text are: ID#1 Publish, ID#2 Hold, ID#3 Suspend  and it is that ID which goes into the gb_entry_stts_ field of the guestbook.  The query which feeds the cfoutput for the select is:

    <CFQUERY NAME="q_stts" DATASOURCE="9130.ww">
    SELECT *
    FROM gb_stts_rf

    ORDER BY gb_stts;
    </CFQUERY>

    The "feeding" table called gb_stts_rf has these fields: gb_stts_rfID and gb_stts

    Why is the SELECT box not displaying the current value?  Guestbook entry #22 should display "Publish" (whose ID#=1), but the SELECT box displays "Hold" (ID#=2) instead, and coincidentally, is first in the alphabetized list of options.  It's not a coincidence, is it?

    I tried this in a CFFORM, using CFSELECT, and the same thing happened; I just got the alphabetized list from gb_stts_rf.

    J
    JR__Bob__Dobbs
    Inspiring
    July 26, 2010
    I learned that the CFQUERYPARAM is for security and may make the query run faster and/or more efficiently, but the query should be able to operate with or without the CFQUERYPARAM, right?

    Queries can be run without CFQUERYPARAM, but use of CFQUERYPARAM is recommended for all queries that you are passing variables to.  Your original query was not working because your variable names were not surrounded by pound signs.  form.gb_entry_stts_ should be #form.gb_entry_stts_#.

    However, I added the CFQUERYPARAM anyway, but the field which contains the status -  gb_entry_stts_  - is a number field and so I changed the cfsqltype="CF_SQL_VARCHAR" /> to cfsqltype="CF_SQL_INTEGER" /> but did I do it right?

    The snippet looks good.  I can't say if it is right without seeing the entire code block.  Does the query execute successfully?

    Should I use BIGINT if the field is a Long Integer in the database?

    I assume would assume yes.

    Do I use SMALLINT or TINYINT if the database field is a byte?

    Do you mean BIT?

    There is a hard space before your slash; is this required?

    The closing slash is optional.  <cfqueryparam value="#form.someField#" cfsqltype="CF_SQL_INTEGER"> would work just as well.

    What if there is no need for a where clause?  If I wish to have all the records in the query display, how is the CFQUERYPARAM used then?

    CFQUERYPARAM is needed when passing variables to queries.  If you are running a query like: "SELECT ColumnA, ColumnB FROM MyTable" then you aren't using any parameters.

    Why is the SELECT box not displaying the current value?

    The SELECT element does not have a value attribute.  Try using the selected attribute of the OPTION element.

    If I understand your question the value of "gb_entry_stts_ eq" would be "1" and you would like "1" to be the selected item in the select list when the values of "gb_stts" is "1" for one of the available options.

    http://www.w3.org/TR/REC-html40/interact/forms.html#h-17.6

    <SELECT
        NAME="gb_entry_stts_">
        <CFOUTPUT QUERY="q_stts">
         <OPTION #Iif(gb_entry_stts_ eq  gb_stts, DE(' selected="selected"'), DE(''))#>#gb_stts#</OPTION>
        </CFOUTPUT>
       </SELECT>

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