Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-17
2
Medium Priority
?
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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