Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I insert multiple values from a form into a database using ColdFusion?

Posted on 2013-11-17
2
Medium Priority
?
537 Views
Last Modified: 2013-11-17
My code isn't inserting the nd and 3 values of the form. It's duplicating the first. How do I fix this?

My form code is:

    <cfparam name="vcode" default="">
    <cfparam name="company_name" default="">
    <cfquery name="getProducts" datasource="mydatasource">
        select * from products where vendorcode = '#vcode#'
    </cfquery>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Add Products</title>
    <style type="text/css">
    <!--
    body,p,td,a {
        font-family: "Trebuchet MS", Tahoma, Verdana, sans-serif;
        font-size: 13px;
        margin: 0;
        padding: 0;
        color: #666;
        }
    -->
    </style>
    </head>
    <body>
    <p>&nbsp;</p>
    <table width="600" border="0" align="center" cellpadding="5" cellspacing="5">
      <tr>
        <td>
        <p>&nbsp;</p>
        <table border="0" cellspacing="4" cellpadding="4">
            <tr>
                <td valign="top">INCL.</td>
                <td valign="top"><strong>Item No.</strong></td>
                <td valign="top"><strong>Description</strong></td>
                <td valign="top"><strong>List Price</strong></td>
                <td valign="top"><strong>Stnd Disc</strong></td>
                <td valign="top"><strong>Stnd Net</strong></td>
                <td valign="top"><strong>Promo Net</strong></td>
                <td valign="top"><strong>Sell Unit</strong></td>
              </tr>
            <cfoutput>
            <cfform name="addProduct" action="addExistingProducts.cfm?fid=#fid#&vcode=#vcode#&pnew=no" method="post" target="_top">
            <cfloop query="getProducts">        
            <cfinput type="hidden" name="vcode" value="#vcode#" />
            <cfinput type="hidden" name="fid" value="#fid#" />  
            <cfinput type="hidden" name="pnew" value="no" />      
              <tr>
                <td valign="top"><input type="checkbox" size="10" name="pid" value="#productid#" /></td>
                <td valign="top"><input type="text" size="10" name="vendor_partno" value="#item_number#" readonly /></td>
                <td valign="top"><input type="text" size="65" name="short_description" value="#short_description#" readonly /></td>
                <td valign="top"><input type="text" size="6" name="listprice" value="#list_price#" readonly /></td>
                <td valign="top"><input type="text" size="6" name="discount" value="" /></td>
                <td valign="top"><input type="text" size="6" name="stndnet" value="" /></td>
                <td valign="top"><input type="text" size="6" name="promonet" value="#dealer_net_sale#" /></td>            
                <td valign="top"><input type="text" size="6" name="selling_unit" value="#selling_unit#" /></td>
              </tr>
            </cfloop>
            <tr>
                <td valign="top" colspan="8" align="center"><cfinput type="submit" name="submit" id="submit" value="add selected products"><br /><br /></td>
              </tr>
            </cfform>
        </cfoutput>
        </table>
        </td>
      </tr>
    </table>
    </body>
    </html>


The processing page looks like this:

    <cfparam name="fid" default="">
    <cfparam name="pid" default="">
    <cfparam name="vendor_partno" default="0">
    <cfparam name="short_description" default="0">
    <cfparam name="listprice" default="">
    <cfparam name="discount" default="">
    <cfparam name="stndnet" default="">
    <cfparam name="promonet" default="">
    <cfparam name="selling_unit" default="">
    <cfparam name="vcode" default="">
    <cfparam name="pnew" default="">
    <cfparam name="i" default="">
    <cfparam name="productList" default="">
    <cfparam name="productLength" default="">
   
   
    <cfset productList="#pid#">
    <cfset discountList="#discount#">
    <cfset stndnetList="#stndnet#">
    <cfset promonetList="#promonet#">
    <cfset selling_unitList="#selling_unit#">
   
    <cfset productLength = "#ListLen(productList)#">
   
    <cfloop list="#productList#" index="i">
   
        <cfquery name="getFlyerProducts" datasource="mydatasource">
            select * from products where productid = #i#
        </cfquery>
       
        <cfquery name="insertFlyerProducts" datasource="mydatasource">
                insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,vcode,pnew)
                values (#fid#,#i#,'#getFlyerProducts.item_number#','#getFlyerProducts.short_description#',#getFlyerProducts.list_price#,'#vcode#','#pnew#')
        </cfquery>
       
        <cfloop list="#discountList#" index="d">
            <cfquery name="insertFlyerDiscount" datasource="mydatasource">
                    update flyer_vendor_campaign_products
                        set discount = #d# where fid = #fid# and pid = #i#
            </cfquery>
        </cfloop>
       
        <cfloop list="#stndnetList#" index="s">
            <cfquery name="insertFlyerNet" datasource="mydatasource">
                    update flyer_vendor_campaign_products
                        set stndnet = #s# where fid = #fid# and pid = #i#
            </cfquery>
        </cfloop>
       
        <cfloop list="#promonetList#" index="p">
            <cfquery name="insertFlyerPromo" datasource="mydatasource">
                    update flyer_vendor_campaign_products
                        set promonet = #p# where fid = #fid# and pid = #i#
            </cfquery>
        </cfloop>
       
        <cfloop list="#selling_unitList#" index="u">
            <cfquery name="insertFlyerUnit" datasource="mydatasource">
                    update flyer_vendor_campaign_products
                        set selling_unit = '#u#' where fid = #fid# and pid = #i#
            </cfquery>
        </cfloop>
       
    </cfloop>
   
I am missing something, but I don't know what. Perhaps using ListGetAt? Or ListFind?

Any ideas Experts? I am totally stuck.
0
Comment
Question by:pulse239
  • 2
2 Comments
 

Author Comment

by:pulse239
ID: 39655491
It seems to be storing the last items in the list as the values.
0
 

Accepted Solution

by:
pulse239 earned 0 total points
ID: 39655524
I got it! :) Yay! :)

    <cfparam name="fid" default="">
    <cfparam name="pid" default="">
    <cfparam name="vendor_partno" default="0">
    <cfparam name="short_description" default="0">
    <cfparam name="listprice" default="">
    <cfparam name="discount" default="">
    <cfparam name="stndnet" default="">
    <cfparam name="promonet" default="">
    <cfparam name="selling_unit" default="">
    <cfparam name="vcode" default="">
    <cfparam name="pnew" default="">
    <cfparam name="i" default="">
    <cfparam name="productList" default="">
    <cfparam name="productLength" default="">
   
   
    <cfset productList="#pid#">
    <cfset discountList="#discount#">
    <cfset stndnetList="#stndnet#">
    <cfset promonetList="#promonet#">
    <cfset selling_unitList="#selling_unit#">
    <cfset productLength = "#ListLen(productList)#">
   
    <cfloop from="1" to="#ListLen(productList)#" index="i">
   
        <cfquery name="getFlyerProducts" datasource="mydatasource">
            select * from products where productid = #ListGetAt(productList,i)#
        </cfquery>
       
        <cfquery name="insertFlyerProducts" datasource="mydatasource">
                insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
                values (#fid#,#ListGetAt(productList,i)#,'#getFlyerProducts.item_number#','#getFlyerProducts.short_description#',#getFlyerProducts.list_price#,#ListGetAt(discountList,i)#,#ListGetAt(stndnetList,i)#,#ListGetAt(promonetList,i)#,'#ListGetAt(selling_unitList,i)#','#vcode#','#pnew#')
        </cfquery>
           
    </cfloop>
   
    <cflocation url="flyer_step3.cfm?fid=#fid#&vcode=#vcode#">
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question