We help IT Professionals succeed at work.

jquery AJAX and ColdFusion database update

Medium Priority
1,417 Views
Last Modified: 2012-03-26
I'm just starting out with jquery and CF. I want to be able to update a MySQL database using a form, and have the updated result appear in the form field.

I have this form in a table:

<table border="1" cellpadding="5" cellspacing ="4">
      <tr><td colspan="2"><cfoutput>#getGallery.gallery_name#</cfoutput></td></tr>
        <cfoutput query="getGallery">
        <form id="captionForm" name="captionForm_#ID#">
        <tr><td>#ID#<img src="../../assets/gallery/#GID#/icons/#image_name#" width="90" height="67" alt="image" /></td>
        <td><input type="text" id ="caption_#ID#" name ="caption_#ID#" value="" onChange="doSetCaption(#ID#)" /></td></tr>
        </form>
        </cfoutput>

</table>



I have this script:

<script>
var doSetCaption = function(ID){
      
      $.ajax({
      type: "post",
      url:"doUpdateGalleryCaption.cfc",
      data: { method: "doSetCaption" },
      cache:false,
        imageID: ID
  });
 
}
</script>



And I have this cfc:

<cfcomponent output="false">
   
        <cfset datasource ="dsn">

    <cffunction name="doSetCaption" access="remote" output="false" returntype="Any">
    <cfargument name="imageID" type="numeric" required="yes">            
    <cfquery datasource="dsn" name="doUpdateCaption">
                UPDATE gallery_images
                SET caption  = (<cfqueryparam cfsqltype="cf_sql_varchar" value="FORM_VALUE _HERE">)
                WHERE ID = (<cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.imageID#">)
                </cfquery>
     <cfreturn>
     </cffunction>
</cfcomponent>

The script runs, and fireBug reports an error:

POST: http://thedomain.org/doUpdateGalleryCaption.cfc

But there's no content in the FB response body.

I need some help please.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
what is your issue, i am unable to understood from ur post

1. do u want to submit the code to the database and repopulate the response to the page and refresh the gallery

2. or something else?

Author

Commented:
Sorry, I should have been clearer. It's your item #1.


When a user types text  into the the form field input (i="caption_#ID#) and presses the "Enter" key, the function "doSetCaption" should run. (onChange="doSetCaption(#ID#).

The function should call the cfc, which will update the DB.

The text that the user has typed into the form field should remain in the form field.

The background color of the text input box will change to #f0f0f0 to show that it's been edited.

Thanks.
CERTIFIED EXPERT

Commented:
try this:

<script>
var doSetCaption = function(ID){
      $.ajaxSetup({cache:false});
      $.ajax({
            type: "post",
            url:"doUpdateGalleryCaption.cfc?method=dpSetCaption&imageID="+ID,
            data: $("#captionForm").serialize(),
            success: function(data) {
                  $("#caption_"+ID).attr("style","background-color:#f0f0f0");
            }
      });
}
</script>

note that in your code you have the text field set to trigger a function onChange - different browsers interpret the change event differently - so you should take that into account

Author

Commented:
I still can't get this to go. I ran the script as you suggested, then changed it as follows. No joy.
The first alert comes up with the ID, so the function is being called.



I changed data to: $("#captionForm_" + ID).serialize() since the captionForm ID's are being dynamically generated.


var doSetCaption = function(ID){
      alert('running' + ID);
      $.ajaxSetup({cache:false});
      $.ajax({
            type: "post",
            url:"doUpdateGalleryCaption.cfc?method=doSetCaption&imageID="+ID,
            data: $("#captionForm_" + ID).serialize(),
            success: function(data) {
                  //$("#caption_"+ID).attr("style","background-color:#f0f0f0");
                  alert('OK');
            }
      });
}
</script>
CERTIFIED EXPERT

Commented:
In your code you have your form id="captionForm" NOT "captionForm_#ID#" - that is your form name!

      Fix this and it should work.
CERTIFIED EXPERT
Commented:
do it like this

 <td><input type="text" id ="caption_#ID#" name ="caption_#ID#" value="" onChange="doSetCaption(#ID#)" class="colorMe" /></td></tr>

$(document).ready(function() {
      $(".colorMe").live("change",function() {
            var id = this.id.replace("caption_","","");
            alert(ID);
            var data = 'captionID='+id;
             $.ajax({
            type: "post",
            url:"doUpdateGalleryCaption.cfc?method=doSetCaption,
            data: data,
            success: function(data) {
                  $("#caption_"+ID).attr("style","background-color:#f0f0f0");
                  alert('OK');
            }
      });
     });
});

also if i can see a live code, that could be cool
CERTIFIED EXPERT

Commented:
just remove function ffrom ur input field

Author

Commented:
Ok, I've made the changes but still no success. I'm not seeing any js errors.

link here:

http://www.boxorox.net/eex/edit_gallery.cfm

This is the cfc:

<cfcomponent output="false">
   
        <cfset datasource ="dsn">

    <cffunction name="doSetCaption" access="remote" output="false" returntype="Any">
    <cfargument name="id" required="yes">            
    <cfquery datasource="dsn" name="doUpdateCaption">
                UPDATE boxo_gallery_images
                SET caption  = (<cfqueryparam cfsqltype="cf_sql_varchar" value="boxo">)
                WHERE ID = (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#">)
                </cfquery>
     <cfreturn false>
     </cffunction>
</cfcomponent>
CERTIFIED EXPERT

Commented:
whats with the parenthesis around the cfqueryparam tags?

Also - post your current form code - that was where the problem was ...

Author

Commented:
The parentheses are a non-issue.


        <cfoutput query="getGallery">
        <form id="captionForm" name="captionForm_#ID#">
        #ID#<img src="../../assets/gallery/#GID#/icons/#image_name#" width="90" height="67" alt="image" />
        <input type="text" id ="caption_#ID#" name ="caption_#ID#" value="#caption#"  class="colorMe" />
        </form>
        </cfoutput>
CERTIFIED EXPERT

Commented:
see - you did NOT make the change that i said.
so WHAT did you change when you said you made the changes then tried it???

what i said earlier was:
In your code you have your form id="captionForm" NOT "captionForm_#ID#" - that is your form name!
      Fix this and it should work.

You do realize that name and id are two different things  - right?
It will not work unless you do that ...

Author

Commented:
OK. It took awhile, but I have it figured out.

I've changed the captionForm id, corrected your typos, and removed the superfluous "type: post" setting. The "type" setting is not necessary, since the default jquery .ajax type is "GET", and, in this case, the cfc will run with or without it.

I've also changed the data setting so that it includes the cfc method as well as the missing form caption value as requested in my original post (cfc - "FORM_VALUE_HERE"). The template and cfc are obviously useless otherwise.

It turns out that the line "var data = 'captionID='+id;" is also inappropriate without a corresponding change for the arguments value in the cfc, so I didn't use it.

The .colorMe class with the change event handler strategy works very well. Thanks.

Here's the form -

<table....
        <form id="captionForm" name="captionForm">
        <cfoutput query="getGallery">
        <tr>
        <td><img src="../../assets/gallery/#GID#/icons/#image_name#" width="90" height="67" alt="image" /></td>
        <td><input type="text" id ="caption_#ID#" name ="caption_#ID#" value="#caption#"  class="colorMe" /></td>
        </tr>
        </cfoutput>
        </form>
</table>



This works:

$(document).ready(function() {
      $(".colorMe").live("change",function() {
            var id = this.id.replace("caption_","","");
          var caption = $("#caption_" + id).val();
            var data = {ID: id, method: "doSetCaption", caption: caption};
            $.ajax({
            url:"doUpdateGalleryCaption.cfc",
            data: data,
            success: function() {
                  $("#caption_"+id).attr("style","background-color:#f0f0f0");
            }
      });
     });
});

I also figured out how to get the cfc to work properly, a first for me.

<cfcomponent output="false">

    <cffunction name="doSetCaption" access="remote" output="false" returntype="Any">
    <cfargument name="id" required="yes" type="numeric">  
    <cfargument name="caption" required="yes" type="string">            
    <cfquery datasource="dsn" name="doUpdateCaption">
                UPDATE gallery_images
                SET caption  = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.caption#">
                WHERE ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.id#">
    </cfquery>
    <cfreturn false>
    </cffunction>
</cfcomponent>

Author

Commented:
This answer was enough to point me in the right direction, so that I could eventually figure out the answer on my own. "myselfrandhawa" knows jquery, so I learned a great deal.

 I have to say that I could do without the ALL CAPS AND MULTIPLE ??? AND !! in the other comments though. It comes off as a little hysterical, and it's not really helpful, but all-in-all, it was a good experience.