Passing an input from form into Coldfusion Query

Question

Hello I am trying to pass a variable who's value would come from a form input to a Query to retrieve a value and pass back to the form as a hidden value.Very confusing, and I hope I am overthinking this. I am getting a Passed_Lot_Number is undefined error.

Here is the code I have so far:

<CFOUTPUT>
    <cfquery name = "OutputDetails" datasource = "#Application.PrimaryDataSource#">
         SELECT ShippingAdviceID
         FROM ShippingAdvice
         WHERE CustomerID =  #Passed_CustomerID#
         AND LotNumber = #Passed_Lot_Number#
    </cfquery>
        <td align="left" colspan="1">
        <input class="frm3" type="text" id="Outstanding_Passed_LotNumber" size="3" maxlength="6" tabindex="25">
          <form name="Show_SampleLogSheet" class="frm" action="/Buying/Shipping_Advice/Index.cfm" method="post">
            <input type="hidden" name="Passed_CustomerID" value="#Passed_CustomerID#">
            <input class="frm3" type="text" name="Passed_Lot_Number" size="3" maxlength="6" tabindex="25">
          </form>
        </td>
</CFOUTPUT>

Forgive me, this code is really old and I have been tasked to adding some more functionality to it. I really appreciate any help.

Thank you

Edit:

Here is some updated code:

<CFOUTPUT>
   <td align="left" colspan="1">
     <input class="frm3" type="text" id="Outstanding_Passed_LotNumber" size="3" maxlength="6" tabindex="25" style="background-color: ##838383;border:1px solid ##000000; color:white">
        <form name="Show_SampleLogSheet" class="frm" action="/Buying/Shipping_Advice/Index.cfm" method="post" style="display: inline">
          <input type="hidden" name="Passed_CustomerID" value="#Passed_CustomerID#">
          <input class="frm3" type="text" name="Passed_Lot_Number" size="3" maxlength="6" tabindex="25">
             <cfif structKeyExists(form, "Passed_Lot_Number ")>
                <cfquery name = "OutputDetails" datasource = "#Application.PrimaryDataSource#">
                    SELECT ShippingAdviceID
                    FROM tblShippingAdvice
                    WHERE CustomerID =  #Passed_CustomerID#
                    AND LotNumber = #Passed_Lot_Number#
                 <cfreturn Passed_ShippingAdviceID />
                </cfquery>
            </cfif>
        <input type="hidden" name="Passed_ShippingAdviceID" value="#Passed_ShippingAdviceID#">
      </form>
   </td>
</CFOUTPUT>

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/180980/discussion-on-question-by-g-rose-passing-an-input-from-form-into-coldfusion-quer). — Samuel Liew, Sep 29 '18 at 04:50

score 1 · Answer 1 · answered Oct 01 '18 at 17:42

1

So I finally solved the issue. Turns out I was thinking about this wrong. Another perfect example of understanding data flow before working on something. Turns out there was a total of 3 pages that the data was passed through. It goes from Client -> Interface Page -> Display Results. The form submitted to the Interface Page and from there I just added logic that defined the Passed_ShippingAdviceID variable. Here is the updated form code:

<td align="left" colspan="1">
  <input class="frm3" type="text" id="Outstanding_Passed_LotNumber" size="3" maxlength="6" tabindex="25" style="background-color: ##838383;border:1px solid ##000000; color:white">
    <form name="Show_SampleLogSheet" class="frm" action="/Interface Page" method="post" style="display: inline">
      <input type="hidden" name="Passed_CustomerID" value="#Passed_CustomerID#">
      <input class="frm3" type="text" name="Passed_Lot_Number" size="3" maxlength="6" tabindex="25">
      <input type="hidden" value="1" name="Passed_Activate">
      <input type="hidden" value ="" name = "Passed_ShippingAdviceID">
    </form>
</td>

Here is the Query on the interface page that defined Passed_ShippingAdviceID:

<cfif Passed_ShippingAdviceID IS "">
  <cfquery name = "OutputDetails" datasource = "#Application.PrimaryDataSource#">
        SELECT ShippingAdviceID
        FROM tblShippingAdvice
        WHERE CustomerID =  '#Passed_CustomerID#'
        AND LotNumber = '#Passed_Lot_Number#'
  </cfquery>
    <cfset Passed_ShippingAdviceID = OutputDetails.ShippingAdviceID>
</cfif>

answered Oct 01 '18 at 17:42

G.Rose

644
7
29

@Steve How would you suggest I use it in this instance? – G.Rose Oct 02 '18 at 12:09
What does "broke everything" mean? If you are passing `Passed_ShippingAdviceID` through the form, rather than `cfif Passed_ShippingAdviceID is ""`, you should be checking the `form` structure for it. I think you may be picking up defaulted variable values that have the same names as some of the variables you seem to want to set. – Shawn Oct 02 '18 at 14:32
Also, why is your `input` for `Outstanding_Passed_LotNumber` outside of your `form` tag? – Shawn Oct 02 '18 at 14:33
And for `cfqueryparam` you would use it something like `CustomerID = `. And if your database column `CustomerID` isn't an integer data type, you'll need to change the `cfsqltype` attribute. See Steve's link. – Shawn Oct 02 '18 at 14:36
@Shawn `Outstanding_Passed_LotNumber` is used for a jquery function. Thank you. It created some issues because the system uses that same function for like 30 different things so it created some problems for users. Now I am working on a way to alert the user if the entered `Outstanding_Passed_LotNumber` doesn't match a `LotNumber` in the database – G.Rose Oct 02 '18 at 15:22
@G.Rose I believe forms can get wonky when elements aren't associated with a form on the page, but it's still in the DOM and jQuery can still access it. I just think it doesn't get passed to the next page. Not sure. Were you able to get your other values passing through the forms? – Shawn Oct 02 '18 at 16:00
@Shawn Unfortunately I am not able to share much detail which is frustrating to myself and of course people like you who are trying to help. I was able to get all of the fields I needed thankfully, I just need to figure out how to alert the user if they enter a lot number that's not already registered. Using AJAX for this now – G.Rose Oct 02 '18 at 16:23
1

Create a cfm page that just contains a query to check if the lot number exists. Then use a jquery `post()` to determine what to do. Maybe something like `$.post('checkLotNo.cfm',{lotNumber:$('#Passed_Lot_Number').val()}, function(data){ if(!data.exists){ //codeToAlertUser } }, 'JSON' ) ;`. You'll have to determine when to fire that call from your form. And you'll have to handle injection issues. – Shawn Oct 02 '18 at 17:15

score 0 · Accepted Answer · answered Sep 29 '18 at 08:37

First of all, you have checked this condition structKeyExists(form, "Passed_Lot_Number "). This means, after submitting the form, the inside of the condition code will be executed.

But, You have given code doesn't have to submit button. Please add the submit button.

After submitting the form, we can able to get the form fields value like as below,

form.Passed_CustomerID and form.Passed_Lot_Number

And you must to put the <cfreturn Passed_ShippingAdviceID /> code after the <cfquery> tag.

I have added a code. Hope, this will help.

<cfoutput>
    <cfparam name="Passed_ShippingAdviceID" default="0">
    <cfif structKeyExists(form, "submit")>
        <cfquery name = "OutputDetails" datasource = "#Application.PrimaryDataSource#">
            SELECT ShippingAdviceID
            FROM tblShippingAdvice
            WHERE CustomerID =  "#form.Passed_CustomerID#"
            AND LotNumber = "#form.Passed_Lot_Number#"
        </cfquery>
        <cfset Passed_ShippingAdviceID = OutputDetails.ShippingAdviceID>
    </cfif>
    <form name="Show_SampleLogSheet" class="frm" action="/Buying/Shipping_Advice/Index.cfm" method="post" style="display: inline">
        <input class="frm3" type="text" id="Outstanding_Passed_LotNumber" size="3" maxlength="6" tabindex="25" style="background-color: ##838383;border:1px solid ##000000; color:white">
        <input type="hidden" name="Passed_CustomerID" value="#Passed_CustomerID#">
        <input class="frm3" type="text" name="Passed_Lot_Number" size="3" maxlength="6" tabindex="25">
        <input type="hidden" name="Passed_ShippingAdviceID" value="#Passed_ShippingAdviceID#">
        <input type="submit" value="submit" name="submit">
    </form>
</cfoutput>

Thanks,

Don't forget to include the expected variable scope in the `cfparam` @G.Rose - To be clear, the code shouldn't use [`cfreturn`](https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-r-s/cfreturn.html) at all. A `cfreturn` is only used when the code is inside a `cffunction`. — SOS, Oct 01 '18 at 03:08
Is there anyway to do this without the submit? The form should submit upon enter keypress — G.Rose, Oct 01 '18 at 11:59
Yeah we can do it. On the key press we need to call the JS function and in that function need to submit the form. — jawahar N, Oct 01 '18 at 12:49
But, I m not sure we need this on key press. Because, for every key press the JS function will be called. — jawahar N, Oct 01 '18 at 12:50
Please please please don't advocate putting straight `form` variables into a query. Even if just copying original code. At the very least, put those variables inside a `cfqueryparam`. — Shawn, Oct 02 '18 at 14:25
Yeah I know.Here I just give the sample code. That's it. Any way I will follow this. — jawahar N, Oct 03 '18 at 02:35

Passing an input from form into Coldfusion Query

2 Answers2