Skip to main content
A
Anonymous
October 29, 2010
Question

How do you get a cfwindow to update a query on the origin page

  • October 29, 2010
  • 2 replies
  • 913 views

I can't seem to get my head around this problem. I've got an accounting app where I want to add a new bill and if I type in a vendor who is not yet in the database I want to pop up a window to record the new vendor information, then return to the add bill page and continue with the information i've entered so far. I've tried cfwindows, but they don't seem to actually submit to the database, and even so, when I return to the origin page all typed data is cleared. Even a pointer towards a solution would be great. Sorry if the problem is spelled out well.

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    October 31, 2010

    Why do it with a pop-up, when you can do it without? You could do it as follows.

    Make, for example, Vendor name an autosuggest input field. When the user types, Coldfusion fetches the matching name from the database, if any exists. The bind attributes ensure that Coldfusion will automatically fill in the ID that corresponds to a matching name, and eventually the product that corresponds to the vendor name and ID.

    If no such match exists, then you know the vendor is new. In that case, the application will add the new vendor. You may also choose to update, for example, the name or product of a vendor.

    addNewVendor.cfm

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

    <cfif isDefined("form.vendor_name")>

    <!--- Assmumes Vendor.cfc is in the current directory --->

    <cfset vendorObject = createobject("component","Vendor")>

    <cfset vendorObject.updateVendor(form.vendor_name, form.vendor_id, form.vendor_prod)>

    </cfif>

    <cfform>


    Vendor name: <cfinput type="text" name="vendor_name" autosuggest="cfc:Vendor.getName({cfautosuggestvalue})"><br><br>
    Vendor ID: <cfinput type="text" name="vendor_id" bind="cfc:Vendor.getId({vendor_name})"><br><br>
    Vendor product: <cfinput type="text" name="vendor_prod" bind="cfc:Vendor.getProduct({vendor_name},{vendor_id})"><br><br>
    <cfinput name="sbmt" type="submit" value="Add or update vendor">
    </cfform>


    Vendor.cfc
    ==========
    <cfcomponent output="false">
        <cffunction name="getName" access="remote" returntype="array" output="false">
            <cfargument name="suggestvalue" required="true">
            <cfset var local = structNew()>
            <!--- The function returns suggestions as an array. --->
            <cfset local.vendorArray = ArrayNew(1)>
           
            <!--- Get all unique last names that match the characters the user types. --->
            <cfquery name="local.getVendorName" datasource="myDSN">
            SELECT DISTINCT vendorName FROM Vendor
            WHERE vendorName LIKE <cfqueryparam value="#suggestvalue#%"
                cfsqltype="cf_sql_varchar">
            </cfquery>
           
            <!--- Convert the query to an array. --->
            <cfloop query="local.getVendorName">
                <cfset arrayAppend(local.vendorArray, vendorName)>
            </cfloop>
            <cfreturn local.vendorArray>
        </cffunction>

        <cffunction name="getId" access="remote" returntype="array" output="false">
            <cfargument name="vendorName" required="true">
            <cfset var local = structNew()> 
            <cfset local.idArray = ArrayNew(1)>
           
            <cfquery name="local.getVendorId" datasource="myDSN">
            <!--- Get ID that matches vendor name --->
            SELECT id FROM Vendor
            WHERE vendorName = <cfqueryparam value="#arguments.vendorName#"
                cfsqltype="cf_sql_varchar">
            </cfquery>
           
            <cfloop query="local.getVendorId">
                <cfset arrayAppend(local.idArray, id)>
            </cfloop>
            <cfreturn local.idArray>
        </cffunction>
       
        <cffunction name="getProduct" access="remote" returntype="array" output="false">
            <cfargument name="name" required="true">
            <cfargument name="id" required="true">
            <cfset var local = structNew()>
            <cfset local.prodArray = ArrayNew(1)>
           
            <cfquery name="local.getProd" datasource="myDSN">
            <!--- Get product that matched vendor name and id --->
            SELECT product FROM Vendor
            WHERE vendorName = <cfqueryparam value="#arguments.name#"
                cfsqltype="cf_sql_varchar">
            AND id = <cfqueryparam value="#arguments.id#"
                cfsqltype="cf_sql_varchar">
            </cfquery>
           
            <cfloop query="local.getProd">
                <cfset arrayAppend(local.prodArray, product)>
            </cfloop>
           
            <cfreturn local.prodArray>
        </cffunction> 
       
        <cffunction name="updateVendor" access="public" returntype="void" output="false">
            <cfargument name="name" required="true">
            <cfargument name="id" required="true">
            <cfargument name="product" required="true">
            <cfset var local = structNew()>
            <!--- I have assumed vendor ID is unique --->
            <cfquery name="local.verifyVendor" datasource="myDSN">
            SELECT count(*) as noOfVendors
            FROM Vendor
            WHERE id = <cfqueryparam value="#arguments.id#"
                cfsqltype="cf_sql_varchar">
            </cfquery>
            <!--- If vendor exists in table, update; else insert--->
            <cfif local.verifyVendor.recordCount GT 0>       
                <cfquery name="local.updateVendor" datasource="myDSN">
                UPDATE Vendor
                SET product =  <cfqueryparam value="#arguments.product#" cfsqltype="cf_sql_varchar">,
                vendorName = <cfqueryparam value="#arguments.name#" cfsqltype="cf_sql_varchar">
                WHERE id =    <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_varchar">             
                </cfquery>
            <cfelse>
                <cfquery name="local.saveVendor" datasource="myDSN">
                INSERT INTO Vendor(vendorName,id,product)
                VALUES(<cfqueryparam value="#arguments.name#" cfsqltype="cf_sql_varchar">,
                       <cfqueryparam value="#arguments.id#" cfsqltype="cf_sql_varchar">,
                       <cfqueryparam value="#arguments.product#" cfsqltype="cf_sql_varchar">)
                </cfquery>
            </cfif>
        </cffunction>
    </cfcomponent>
    A
    Anonymous
    November 1, 2010

    Thanks, BKBK, I think this is the correct approach for my specific issue. Thanks so much for your help.

    ilssac
    ilssac
    Inspiring
    October 29, 2010

    For a more sophisticated application like you describe, you have got to have a clear idea of what happens on the Client, what happens on the server and how information gets from one to the other.

    The <cfwindow...> is just a handy wizard to make DHTML and|or AJAX based <div> on the client.  These submit data to a server just as any other action on the page would.  With a request.  That request could affect the entire page with links or forms, or they could be contained inside of frames that only affect that part of the User Interface, or the requests could be made behind the scenes with JavaScirpt XMLHttpRequest() funciton calls.

    No matter how the request is made, the server is going to return a response.  That response would then be directed to the root browser, frame or response handler defined in the JavaScript function call.  That response can then be used to update the HTML.  How much the browser will do this for you and how much you will have to program yourself depends on what type of response you are working with.

    From here it is up to you on how you want to put all these various pieces together.  You can have the 'window' do all the work, you can pass the data from the 'window' to the parent to do the work.  You can have the window submit the data AND update the parent without bothering with a response from the server.  There are practically infinite possibilities here.

    A
    Anonymous
    November 1, 2010

    Thanks ilssac, for the very thoughtful reply. I'm working my way through it, slowly but surely. I really appreciate your help.

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