Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation help with cfquery for update!

    i couldn't update my database because everything is populated to an uncertain amount of rows of data with the same project name. why is it uncertain amount because this is depending on how many requested has being made to this project. let say i have a project named MTM and there are 6 requests been make to this project, therefore it will populate 6 times like this:
    -----------------------------------------------------------
    request| project name|Number|date|--time--|---update--
    -----------------------------------------------------------
    --1----|---- MTM--- |123456|4 Apr|2:15 PM|buttonUpdate
    --2----|---- MTM--- |457446|3 Apr|2:30 PM|buttonUpdate
    --3----|---- MTM--- |452741|2 Apr|2:20 PM|buttonUpdate
    --4----|---- MTM--- |234534|5 Apr|3:15 PM|buttonUpdate
    --5----|---- MTM--- |123414|4 Apr|2:45 PM|buttonUpdate
    --6----|---- MTM--- |124143|3 Apr|4:15 PM|buttonUpdate


    after user edit the txt field number user will click on update button.

    this is my SQL and the cause of the problem:
    <cfquery datasource="DBcadcam">
    UPDATE table1
    SET Number='#form.txtnumber#'
    WHERE ProjectName=#ProjectName#
    </cfquery>

    the WHERE clause will give me all the 6:
    ProjectName=MTM,MTM,MTM,MTM,MTM,MTM


    how can i make my submit button recognize which row it is submitting?
    just thought of this after typing so much ...

  • #2
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Ok, you must be having all of the form fields in one single form. You are getting that comma delimited list of field values because you have 6 form fields all named with the same name: "projectName". Basically, if there are multiple form fields with the same name, ColdFusion takes their values and makes a list out of them when they are submitted.

    Probably the easiest thing to do is to create a completely separate form for each row. Each form would only hold the fields of a particular row, and therefore the update button would only submit those particular form fields to your save page.

    Try that out and let me know how it goes. I'm just thinking tho, if you are using an html table, it might not result in valid markup with surrounding <tr> tags with <form> tags. So if that method doesn't work, we can either implement a solution by appending a row number to each form field, or using JavaScript to submit exactly what we want. Let me know though, and post your code too if you can btw.

    -Greg

  • #3
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Gjslick View Post
    form for each row
    yes i am using html table so thats not going to work for me.

    all this code is scanned, hope is not difficult to read..
    update.cfm
    Code:
    <!--- update.cfm --->
    <form name="formtab1e" action="update_dir.cfm" method="post">
    <table width="1000" a1ign="center" bgco1or="#FFFFCC" frame="box">
    <thead bgco1or="#66FFCC">
    <tr>
    <td width="50" align="center">Edit</td>
    <td width="50" align="center">Request</td>
    <td width="125" align="center">Project Name</td>
    <td width="110">Number of Drawing</td>
    <td width="150" align="center">Date requested</td>
    <td width="150" align="center">Time requested</td>
    <td width="130" align="center">Done By</td>
    <td width="130" align="center">Status</td>
    <td width="130" align="center">Comment</td>
    <td width="130" align="center">Update</td>
    </tr>
    </thead>
    <cfoutput query="DIRdata">
    <tr>
    <td a1ign="center"><input name="roption" type="radio" onClick="enab1etxt(this);"></td>
    <td align="center">#DIRdata.currentrow#</td>
    <td align="center">#ProjectName#</td>
    <td><input name="numdraw" type="text" disabled va1ue="#NumOfDrawing#" size="8"></td>
    <td a1ign="center"><input type="hidden" name="daterequested" va1ue="#DateformatIDateRequested,
    "m/d/yyyy")#">#DateformatIDateRequested,"dd/mmm/yyyy")#</td>
    <td a1ign="center">#TimeformatITimeRequested,"h:mm tt")#</td>
    <td a1ign="center">#DoneBy#</td>
    <td a1ign="center">
    <select name="status" disabled>
    <cfif #ProjectStatus# eq "Pending">
    <option>Pending</option>
    <option>Comp1eted</option>
    <cfe1seif #projectStatus# eq "Completed">
    <option>Completed</option>
    <option>Pending</option>
    </cfif>
    </select>
    </td>
    <td align="center">
    <input type="hidden" name="ProjectName" va1ue="#ProjectName#">
    <input type="hidden" name="comment" value="#ProjectComment#">
    <a href="viewcomment.cfm?ProjectName=#ProjectName#&comment=#ProjectComment#" target="_blank">View</a>
    </td>
    <td align="center"><input type="submit" name="update" disabled value="Update"></td>
    </tr>
    </cfoutput>
    </table>
    <table width="1000" align="center">
    <tr>
    <td width="832"><font color="#CCOOOO">Click on edit then change the details. </font></td>
    <td width="103" align="right"></td>
    <td width="49" align="right">
    <input type="button" name="print" value="Print" onClick="goto()">
    </td>
    </tr>
    </table>
    </form>
    updateAction.cfm
    Code:
    <!--- updateAction.cfm --->
    <cfquery name="DIRdata" datasource="DBcadcam">
    UPDATE DIR
    SET NumOfDrawing = '#Form.numdraw#',
            ProjectStatus = #Form.status#
    WHERE DateRequested='#form.daterequested#'
    </cfquery>
    
    <cflocation url="update.cfm">

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Dude, you're killing me here with the scans... Next time you scan something, please format it with the proper code-readable indentation, and correct any of the errors that might have come up from the OCR software.

    Here's an example of what the code would look like properly indented, in a readable form:
    Code:
    <!--- update.cfm --->
    <form name="formtab1e" action="update_dir.cfm" method="post">
        <table width="1000" a1ign="center" bgco1or="#FFFFCC" frame="box">
            <thead bgco1or="#66FFCC">
                <tr>
                    <td width="50" align="center">Edit</td>
                    <td width="50" align="center">Request</td>
                    <td width="125" align="center">Project Name</td>
                    <td width="110">Number of Drawing</td>
                    <td width="150" align="center">Date requested</td>
                    <td width="150" align="center">Time requested</td>
                    <td width="130" align="center">Done By</td>
                    <td width="130" align="center">Status</td>
                    <td width="130" align="center">Comment</td>
                    <td width="130" align="center">Update</td>
                </tr>
            </thead>
            
            <cfoutput query="DIRdata">
                <tr>
                    <td a1ign="center"><input name="roption" type="radio" onClick="enab1etxt( this );"></td>
                    <td align="center">#DIRdata.currentRow#</td>
                    <td align="center">#projectName#</td>
                    <td>
                        <input name="numdraw" type="text" disabled va1ue="#numOfDrawing#" size="8">
                    </td>
                    <td a1ign="center">
                        <input type="hidden" name="daterequested" va1ue="#dateformat( dateRequested, 'm/d/yyyy' )#">
                        #dateformat( dateRequested, 'dd/mmm/yyyy ')#
                    </td>
                    <td a1ign="center">#timeformat( timeRequested, 'h:mm tt' )#</td>
                    <td a1ign="center">#doneBy#</td>
                    <td a1ign="center">
                        <select name="status" disabled>
                            <cfif projectStatus eq 'Pending'>
                                <option>Pending</option>
                                <option>Comp1eted</option>
                            <cfelseif projectStatus eq 'Completed'>
                                <option>Completed</option>
                                <option>Pending</option>
                            </cfif>
                        </select>
                    </td>
                    <td align="center">
                        <input type="hidden" name="projectName" va1ue="#projectName#">
                        <input type="hidden" name="comment" value="#projectComment#">
                        <a href="viewcomment.cfm?projectName=#projectName#&comment=#projectComment#" target="_blank">View</a>
                    </td>
                    <td align="center"><input type="submit" name="update" value="Update" disabled></td>
                </tr>
            </cfoutput>
        </table>
        
        <table width="1000" align="center">
            <tr>
                <td width="832">
                    <font color="#CCOOOO">Click on edit then change the details.</font>
                </td>
                <td width="103" align="right"></td>
                <td width="49" align="right">
                    <input type="button" name="print" value="Print" onClick="goto();">
                </td>
            </tr>
        </table>
    </form>
    You might just want to just invest in a cheap USB key to take code back and forth.

    By the way, why don't you just download ColdFusion server onto your local computer, and use it as a development server? (Instead of relying on using a computer that doesn't have access to the internet.) That's how I do all of my development. The developer edition of CF server is free, and if you have Windows xp/vista/7 professional edition, you can use IIS as the local web server. (Otherwise you'll need to use Apache or something else as the web server.) Just remember to install a web server before installing ColdFusion (as you'll need to tell the ColdFusion installer which one you're using).

    Just a few notes on code style btw:
    1) Variable names should start with a lowercase letter. The only names that should really start with an uppercase letter is a class name (ColdFusion component).
    2) It can make your code much more readable to prefix outputted query columns with the name of the query. For example, use #DIRdata.projectName# instead of just #projectName#. This makes it clear that the variable is coming from a query, and isn't just a regular variable that was set somewhere earlier in the code.

    Here is a JavaScript solution to your problem. This grabs all of the input elements out of a table row (input's, select's, and textarea's - if you have any in the future), and populates a separate form with them. That form is then submitted, and you end up only submitting the data that was in your table row. Notice that there is no longer a form surrounding the table itself.
    Code:
    <!--- update.cfm --->
    
    <script>
        function createHiddenInputs( form, collection ) {
            for( var i = 0, len = collection.length; i < len; i++ ) {
                var hiddenInput = document.createElement( "INPUT" );
                hiddenInput.type = "hidden";
                hiddenInput.name = collection[ i ].name;
                hiddenInput.value = collection[ i ].value;
                form.appendChild( hiddenInput );
            }
        }
        
        
        // submitRow function: Takes all input elements in the table row, and creates
        // hidden inputs out of them in a separate form, which is then submitted.
        function submitRow( rowEl ) {
            var inputs = rowEl.getElementsByTagName( "INPUT" );
            var selects = rowEl.getElementsByTagName( "SELECT" );
            var textareas = rowEl.getElementsByTagName( "TEXTAREA" );
            
            
            var updaterForm = document.getElementById( 'updaterForm' );
            
            createHiddenInputs( updaterForm, inputs );
            createHiddenInputs( updaterForm, selects );
            createHiddenInputs( updaterForm, textareas );
            
            updaterForm.submit();
        }
    </script>
    
    
    <form id="updaterForm" action="update_dir.cfm" method="post">
        <!-- Fields will be populated via JavaScript submitRow() function -->
    </form>
    
    <table width="1000" a1ign="center" bgco1or="#FFFFCC" frame="box">
        <thead bgco1or="#66FFCC">
            <tr>
                <td width="50" align="center">Edit</td>
                <td width="50" align="center">Request</td>
                <td width="125" align="center">Project Name</td>
                <td width="110">Number of Drawing</td>
                <td width="150" align="center">Date requested</td>
                <td width="150" align="center">Time requested</td>
                <td width="130" align="center">Done By</td>
                <td width="130" align="center">Status</td>
                <td width="130" align="center">Comment</td>
                <td width="130" align="center">Update</td>
            </tr>
        </thead>
        
        <cfoutput query="DIRdata">
            <tr>
                <td a1ign="center"><input name="roption" type="radio" onClick="enab1etxt( this );"></td>
                <td align="center">#DIRdata.currentRow#</td>
                <td align="center">#DIRdata.projectName#</td>
                <td>
                    <input name="numdraw" type="text" va1ue="#DIRdata.numOfDrawing#" size="8" disabled>
                </td>
                <td a1ign="center">
                    <input type="hidden" name="dateRequested" va1ue="#dateformat( DIRdata.dateRequested, 'm/d/yyyy' )#">
                    #dateformat( DIRdata.dateRequested, 'dd/mmm/yyyy ')#
                </td>
                <td a1ign="center">#timeformat( DIRdata.timeRequested, 'h:mm tt' )#</td>
                <td a1ign="center">#DIRdata.doneBy#</td>
                <td a1ign="center">
                    <select name="status" disabled>
                        <cfif DIRdata.projectStatus eq 'Pending'>
                            <option>Pending</option>
                            <option>Comp1eted</option>
                        <cfelseif DIRdata.projectStatus eq 'Completed'>
                            <option>Completed</option>
                            <option>Pending</option>
                        </cfif>
                    </select>
                </td>
                <td align="center">
                    <input type="hidden" name="projectName" va1ue="#DIRdata.projectName#">
                    <input type="hidden" name="comment" value="#DIRdata.projectComment#">
                    <a href="viewcomment.cfm?projectName=#DIRdata.projectName#&comment=#DIRdata.projectComment#" target="_blank">View</a>
                </td>
                <td align="center"><input type="submit" name="update" value="Update" onclick="submitRow( this.parentNode.parentNode );" disabled></td>
            </tr>
        </cfoutput>
    </table>
    
    <table width="1000" align="center">
        <tr>
            <td width="832">
                <font color="#CCOOOO">Click on edit then change the details.</font>
            </td>
            <td width="103" align="right"></td>
            <td width="49" align="right">
                <input type="button" name="print" value="Print" onClick="goto();">
            </td>
        </tr>
    </table>
    That function submitRow() is pretty generalized btw, and you can use it with any HTML element -- not just a <tr> element. However, I left it named as submitRow() because that makes the most sense for your application here.

    Try that and let me know how it goes.

    -Greg

  • Users who have thanked Gjslick for this post:

    code L (04-14-2010)

  • #5
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the tips on how to name the variables and the solution! and sorry man next time i will edit it into a readable format =).

    Quote Originally Posted by Gjslick View Post
    You might just want to just invest in a cheap USB key to take code back and forth.

    By the way, why don't you just download ColdFusion server onto your local computer, and use it as a development server?
    I would really love to bring one too! however this company i am attached to, the security is really tight here, no storage devices..

  • #6
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Wow, weird company... lol. But alright, whatever works! Haha.

    -Greg

  • #7
    New Coder
    Join Date
    Mar 2010
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    thanks my program is updating now =)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •