Skip to main content
N
New_York_Guy
Known Participant
December 12, 2008
Question

How to insert a value into a table

  • December 12, 2008
  • 1 reply
  • 539 views
I have an ordering system. It displays all the orders by customer ID. If an order is complete, a check number is entered for the corresponding line item, otherwise it is left blank.

My code uses the following code to display the form. All are display fields only, except for the last one, where the check number can be entered.

<cfoutput query="qryDetail" group="partNumber">
<tr>
<td valign="top" class="TitleText" align="center">#lineItem#</td>
<td valign="top" class="TitleText" align="center">#OrderNumber#</td>
<td valign="top" class="TitleText" align="center">#partNumber#</td>
<td valign="top" class="TitleText" align="center">#dollarformat(qryDetail.unitValue)#</td>
<td valign="top" class="TitleText" align="center">
<cfinput type="text" name="checkNumber#keyID#">
<input type="hidden" name="keyID" value="#qryDetail.keyID#">
</td>


I use something like the following to update the table with the check numbers entered.

<cfloop index="KeyID" list="#form.KeyID#" delimiters=",">
<cfquery name="qryUpdate" datasource="dbName">
update tblChecks
set checkNumber = '#Evaluate("form.checkNumber#KeyID#")#'
where custID = '#form.custID#'
and KeyID = '#KeyID#'
</cfquery>
</cfloop>

Now we have another table that will contain the customer order history. If the check number was entered for a particular line item, I need to insert that line item nubmer into this table, along with some other columns. I tried to use the same loop, then other loops, but cannot seem to get this to work. Seems simple enough, but I obvisouly am missing something.

How would I insert the line item number (only if a check number was entered for that line item) into the table ?
    This topic has been closed for replies.

    1 reply

    N
    Newsgroup_User
    Inspiring
    December 12, 2008
    modifying your approach a bit you can do all rather easily:

    1) rename your fields for entering check numbers as
    name="checkNumber_#lineItem#_#keyID#" to store the lineitem number in
    the filedname

    2) get rid of the hidden keyID field - you will not need it

    3) change your cfloop to:
    <cfloop list="#form.fieldnames#" index="field">
    <cfif left(field, 11) is 'checkNumber' AND len(trim(form[field]))>
    <cfset keyid = listlast(field, '_')>
    <cfset lineitem = listgetat(field, 2, '_')>
    <cfset checknum = form[field]>
    <!--- update check number --->
    <cfquery name="qryUpdate" datasource="dbName">
    UPDATE tblChecks
    SET checkNumber = '#checknum#'
    WHERE custID = '#form.custID#'
    AND KeyID = '#keyid#'
    </cfquery>
    <!--- insert record in customer history table --->
    <cfquery name="insertCustHist" datasource="dbName">
    INSERT INTO yourcustomerhistorytable
    (custID, lineItem, ...)
    VALUES
    ( '#form.custID#'
    , '#lineitem#'
    , ...
    )
    </cfquery>
    </cfif>
    </cfloop>

    the loop iterates over all fields in the form scope, is the field's name
    begins with "checkNumber" and has a value, it extracts the lineItem and
    keyID from the field's name using some cf list functions, then runs 2
    queries updating/inserting data.

    [NB: form[field] used above is a substitute for your Evaluate(....)]

    of course this will only work if your lineItem and keyID values DO NOT
    contain _ (underscore)....

    also, using <cfqueryparam> may be a good idea and a good habit to acquire...

    hth

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    N
    New_York_GuyAuthor
    Known Participant
    December 13, 2008
    Hello Azadi, thanks for your response.

    I used your sample and it did not seem to work. The tables were not updated, so I put in a <cfelse> to display values, and sure enought, the <cfif left(field, 11)> part failed and the output displayed.

    <cfinput type="text" name="checkNumber_#lineItem#_#keyID#">


    <cfloop list="#form.fieldnames#" index="field">
    <cfif left(field, 11) is 'checkNumber' AND len(trim(form[field]))>
    <cfset keyID = listlast(field, '_')>
    <cfset lineItem = listgetat(field, 2, '_')>
    <cfset checknum = form[field]>

    <cfelse>
    <cfoutput>form.fieldnames is #form.fieldnames#" and field is #field#
    and left is #left(field, 11)# AND len is #len(trim(form[field]))#
    </cfoutput>
    <cfabort>

    If both check numbers are enetered, the <cfelse> is :
    form.fieldnames is CHECKNUMBER_1_216,CHECKNUMBER_2_217,GFMRDN,SITEID,BTNSUBMIT" and field is GFMRDN and left is GFMRDN AND len is 4

    If the first check number is eneterd only, the <cfelse> is :
    form.fieldnames is CHECKNUMBER_1_216,CHECKNUMBER_2_217,GFMRDN,SITEID,BTNSUBMIT" and field is CHECKNUMBER_2_217 and left is CHECKNUMBER AND len is 0

    If the second check number is entered only, the <cfelse> is :
    form.fieldnames is CHECKNUMBER_1_216,CHECKNUMBER_2_217,GFMRDN,SITEID,BTNSUBMIT" and field is CHECKNUMBER_1_216 and left is CHECKNUMBER AND len is 0

    GFMRDN and SITEID are hidden fields that I need to use and BTNSUBMIT is the submit button and the check numbers are empty, even though I just added them.

    Can you tell me what I am doing wrong ?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices