Solved

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

Posted on 2013-11-17
2
467 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now