Skip to main content
R
rambo
Known Participant
March 23, 2011
Question

Populating a web form from a lookup table

  • March 23, 2011
  • 2 replies
  • 838 views

Hi

I am looking for some expert help please

I have a lookup query

<cfquery name="emp">

select * from employee

where empid = '#form.empid#'

</cfquery>

and what I would like to do is when a user enters their employeeid in a text input field and clicks "find" it triggers the query and brings back that employees details and populates the other form fields on the pages (name, address etc) without a page refresh.

What I can't understand is when they click the "find" button after they have entered an employee ID how does it trigger the query (and where would the query sit within the page) and how would the results get returned to populate the form without a page refresh?

If anybody has some code examples this would be great, I am using CF7

TIA

    This topic has been closed for replies.

    2 replies

    WolfShade
    WolfShade
    Legend
    March 23, 2011

    You shouldn't use innerHTML to write to form elements.  You can use it to create the form elements, although there are better ways.

    AJaX would be a good way to do it.  Have it access a .cfm file that contains the query and outputs the data and nothing else, preferably in a delimited list format, then you can use JavaScript to parse the string and populate the elements.

    ^_^

    R
    ramboAuthor
    Known Participant
    March 23, 2011

    Thanks but do you have any code examples / walkthroughs that will help me get started,?

    I have come across the following example, but how would I adapt this to enable it so when an employeeid is entered and the lookup button is pressed then the rest of the form fields are completed with the data gathered from the record in the db?

    <script language="JavaScript" src="http://www.365labs.net/cf_jquery/jquery.js" type="text/javascript"></script>
    <script type="text/javascript">
    $(document).ready(function(){
      $('#contentdiv').html('&& nbsp;');
    $("#idm").change( function() {
    var formval = { idm:$(this) .val()};
    $.ajax({
       type: "POST",
       url: "request_processor.cfm",
       dataType: "json",
       data: formval,
       success: function(response){
       $('#contentdiv').fadeIn(2000).append(response.MAIN_DISH);
       }
      }); 
    });  
    });
    </script>
    <style type="text/css">
      body {font-family: Arial, Helvetica, sans-serif;}
      #databox { border: 1px solid Gray;
              margin:30px auto 0px auto;
              padding: 10px;
              width: 200px;}
    #contentdiv { color:red; font-size: 30px;  margin-top: 10px;}
    </style>

      
      
    </head>

    <body>

    <div align="center" id="databox">
    <select id="idm">
      <option value="0" SELECTED>What's for Dinner?...</option>
      <option value="1">Monday</option>
      <option value="2">Tuesday</option>
      <option value="3">Wednesday</option>
      <option value="4">Thursday</option>
      <option value="5">Friday</option>
      <option value="6">Saturday</option>
    </select>
    <p id="contentdiv"> </p>
    </div>

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

    request_processor.cfm page

    <cfset odata = createObject("component","menudata")>
    <cfset thedata = odata.getMenuData(FORM.idm)>
    <cfset ojson = createObject("component","cfjson")>
    <cfset theresults = ojson.encode(thedata)>
    <cfoutput>#theresults#</cfoutput>
      

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

    menudata.cfc page

    <cfcomponent displayname="menudata">
    <cffunction name="getMenuData">
      <cfargument name="menu_id" required="yes">
      <cfquery name="get_menu" datasource="menudb">
       SELECT menu.day,
         menu.main_dish
       FROM menu
       WHERE menu.qsid = #ARGUMENTS.menu_id#
      </cfquery>
      <cfset pstruct = structNew()>
      <cfset pstruct.day = get_menu.day>
      <cfset pstruct.main_dish = get_menu.main_dish>
      <cfreturn pstruct>
    </cffunction>
    </cfcomponent>
      

    D
    Dan_Bracuk
    Inspiring
    March 23, 2011

    Put the query in a separate page, and include that page in a very small iframe on the first page.  Use javascript to call the query page and use innerHTML to write back to the form fields.

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