Solved

My item number is storing a zero when using an array. How do I fix this?

Posted on 2013-11-05
24
313 Views
Last Modified: 2013-11-15
<cfset productList="#pid#">

<cfset productListArray = listToArray(productList,",",false,true)>


<cfloop array="#productListArray#" index="i">
    <cfquery name="insertFlyerProducts" datasource="datasource">
        insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
        values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
0
Comment
Question by:pulse239
  • 14
  • 8
  • 2
24 Comments
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
May be a data conversion problem, but hard to say without more info:

1. Which value is the item number : #i#?
2. What are the actual values in the array?
3. What's the data type of the "PID" column?

Side note, you should always use cfqueryparam. Especially when looping, because it can improve performance when running the same query over and over.  Plus it solves a lot of data conversion issues, and more important protects against sql injection.
0
 

Author Comment

by:pulse239
Comment Utility
The values in the array are the product ID which is supposed to be "i". When I output the array, the values display correctly, but don't store correctly.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Thanks, but that doesn't answer all of the questions. Most likely you just need to use cfqueryparam, but .. that's just a guess without more info.

Without knowing what the #i# value contains, or what type of column its stored in (varchar, etc..) I can't tell you how to fix the problem ;-)  So

1) what's an example of the #i# value ?
2) what's the data type of the PID column (varchar, datetime, integer, ...?)
0
 

Author Comment

by:pulse239
Comment Utility
i is a number or INT value in the database.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
There's no obvious reason a "number" would not insert correctly into an INT column - even without cfqueryparam.  But like I said, we can't tell you why it's 0 if we don't even know what value you're trying to insert! ;-)

I've asked twice, but you haven't posted it. Is there a reason you can't provide an example of the value you're inserting?
0
 

Author Comment

by:pulse239
Comment Utility
I am trying to insert number 123455 as a test. Could this be the issue?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
No, that number is fine for an INT column.  To confirm, I tested it w/SQL 2005 and it ran w/o issue. So there's something else going on (different values, different data type,e tc...)

(Retyped from another screen, so ignore any typos ...)

<cfquery ....>
        CREATE TABLE TestTable ( PID INT )
</cfquery>

<cfset productListArray = listToArray("123455,99999", ",", false, true)>
<cfloop array="#productListArray#" index="i">
    <cfquery ...>
          INSERT INTO TestTable ( PID )
          VALUES ( #i# )
    </cfquery>
</cfloop>

<!--- dump results (showed 2 records as expected) --->
<cfquery name="getResults" ....>
        SELECT * FROM TestTable
</cfquery>
<cfdump var="#getResults#">
0
 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
try using the val around the #i#, and see which values are going as 0 in the database, might help in generating your issues and checking where exactly the problem lies

like this

#val(i)#
0
 

Author Comment

by:pulse239
Comment Utility
It is defiantly not reading the number. It passes as a NULL value. Any idea why this would be occurring?

<cfset productList="#pid#">

<cfset productListArray = listToArray("productList", ",", false, true)>


<cfloop array="#productListArray#" index="i">
    <cfquery name="insertFlyerProducts" datasource="datasource">
        insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
        values (#fid#,#val(i)#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
<cfset productListArray = listToArray( "productList", ",", false, true)>
Oh wait... that's different than your original code. By putting quotes around the variable name you're inserting the literal string "productList" - NOT the contents of a variable named productList.

Remove the quotes, and dump the array before your insert to make sure it actually contains the numbers you said it does, not the string "productList":


<cfset productListArray = listToArray( productList , ",", false, true)>
<cfdump var="#productListArray#" label="See what you're inserting....">
<cfloop array="#productListArray#" index="i">
    <cfoutput>
         preparing to insert PID = #i#<br>
    </cfoutput>
    ..... query ....
</cfloop>
0
 

Author Comment

by:pulse239
Comment Utility
I have inserted this:

<cfset productListArray = listToArray( productList , ",", false, true)>
<cfdump var="#productListArray#" label="i">
<cfoutput>#i#</cfoutput><cfabort>

and when I try to display what I am inserting it shows:

 Variable I is undefined.
 
The error occurred in D:/Inetpub/nisscorest/admin/vendors/addNewProduct.cfm: line 17

15 : <cfset productListArray = listToArray( productList , ",", false, true)>
16 : <cfdump var="#productListArray#" label="i">
17 : <cfoutput>#i#</cfoutput><cfabort>
18 : <cfif pnew is "no">
19 : <cfloop array="#productListArray#" index="i">
0
 

Author Comment

by:pulse239
Comment Utility
When I remove 'label' and just have this code:

<cfset productList="#pid#">

<cfset productListArray = listToArray( productList , ",", false, true)>
<cfdump var="#productListArray#">
<cfoutput>#productListArray#</cfoutput><cfabort>

I get this:

 Complex object types cannot be converted to simple values.
The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.

The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.
 
The error occurred in D:/Inetpub/nisscorest/admin/vendors/addNewProduct.cfm: line 20

18 : <cfset productListArray = listToArray( productList , ",", false, true)>
19 : <cfdump var="#productListArray#">
20 : <cfoutput>#productListArray#</cfoutput><cfabort>
21 : <cfif pnew is "no">
22 : <cfloop array="#productListArray#" index="i">
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:myselfrandhawa
Comment Utility
just remove the <cfoutput>#productListArray#</cfoutput> and only dump and abort, what you se and can you post the image of that
0
 

Author Comment

by:pulse239
Comment Utility
If I remove the array, and just try to use the productList, I get this error, but it looks like I'm almost there. I double checked the variable types with the code and the numeric and int fields have no quotes and the others do have single quotes. Am I doing something wrong on the insert statement?

MY CODE:

<cfset productList="#pid#">

<cfloop list="#productList#" index="i">
    <cfquery name="insertFlyerProducts" datasource="datasource">
        insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
        values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
</cfloop>

ERROR:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.
 
The error occurred in D:/Inetpub/URL.com/admin/vendors/addNewProduct.cfm: line 22

20 :     <cfquery name="insertFlyerProducts" datasource="datasource">
21 :         insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
22 :         values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
23 :     </cfquery>
24 : </cfloop>
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
>  Variable I is undefined.

Right because #i# doesn't exist yet. It's created by the cfloop. Take another look at the example, you can only dump #i# inside the cfloop like this:

<cfloop array="#productListArray#" index="i">      <=== variable #i# is created here
    <cfoutput>
         preparing to insert PID = #i#<br>
    </cfoutput>

    ..... cfquery here ....
</cfloop>

> When I remove 'label' and just have this code:
> ..
> <cfdump var="#productListArray#" label="See what you're inserting....">
> <cfoutput>#productListArray#</cfoutput><cfabort>

You can't use <cfoutput> with complex objects like arrays. That's why the example uses cfdump ;-) Get rid of the extra line you added and it'll work fine.  I'm not sure why you removed the label (unless your version doesn't support it?)

Am I doing something wrong on the insert statement?

Sounds like one of your numeric values is empty.  That would cause the error you're seeing.  (Again, you should be using cfqueryparam on all values. Among other things it would help avoid errors like this. Plus you'd protect your db. Currently someone could drop your entire database with a simple form field entry.)

Does the error message include the generated SQL? If not, add some debugging. This code just prints out the SQL being sent to the db:

<cfloop list="#productList#" index="i">
    <!--- NO query tags here --->
    <cfoutput>
        insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
        values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfoutput>
    <br>
</cfloop>
0
 

Author Comment

by:pulse239
Comment Utility
Now I get the following error when trying to add an existing product and no error on a new product. It just doesn't add the item to the database,

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.
 
The error occurred in D:/Inetpub/url/admin/vendors/addNewProduct.cfm: line 22

20 :     <cfquery name="insertFlyerProducts" datasource="mydatasource">
21 :         insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
22 :         values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
23 :     </cfquery>
24 : </cfloop>

MY CODE:

 <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="productListArray" default="">

<cfif pnew is "no">
<cfset productList="#pid#">
    <cfloop list="#productList#" index="i" delimiters = ",">
        <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#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
        </cfquery>
    </cfloop>
<cfelse>
    <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#,#pid#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
</cfif>
<cflocation url="flyer_step3.cfm?fid=#fid#&vcode=#vcode#">
0
 

Author Comment

by:pulse239
Comment Utility
Weird. I completely changed the code and it is still happening.

    <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="productListArray" default="">
      
      <cfset productList="#pid#">
      
      <cfif pnew is "no">
      
      <cfloop list="#productList#" index="i">
      
      <cfquery name="getProducts" datasource="mydatasource">
            select * fromproducts where pid = #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#,#getProducts.pid#,'#getProducts.vendor_partno#','#getProducts.short_description#',#getProducts.listprice#,#getProducts.discount#,#getProducts.stndnet#,#getProducts.promonet#,'#getProducts.selling_unit#','#vcode#','#pnew#')
            </cfquery>
      </cfloop>
      
      <cfelseif pnew is "yes">
      
      <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#,#pid#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
      </cfquery>
      </cfif>
      <cflocation url="flyer_step3.cfm?fid=#fid#&vcode=#vcode#">

This is what the second query is trying to insert. It is a preexisting item.

VENDORERRORCODE         102
SQLSTATE         HY000
SQL          insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew) values (104,,'13','test',100,20,95,80,'ea','acs','yes')

The code for a new item is:

    <cfquery name="getLastVendorFlyer" datasource="mydatasource">
        select * from flyer_vendor_campaigns where fid = #fid#
    </cfquery>
   
    <cfquery name="getLastPid" datasource="mydatasource" maxrows=1"">
        select * from flyer_vendor_campaign_products where vcode = #vcode# order by pid desc
    </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><img src="../../images/NISSCO.jpg" alt="Nissco RDG" /></p>
        <p>&nbsp;</p>
        <table border="0" cellspacing="4" cellpadding="4">
            <tr>
                <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>
                <td valign="top"><strong>ADD</strong></td>
              </tr>
            <cfoutput>
            <cfform name="addProduct" action="addNewProduct.cfm?fid=#fid#&vcode=#vcode#" method="post" target="_top">    
            <cfinput type="hidden" name="vcode" value="#vcode#" />
            <cfinput type="hidden" name="fid" value="#fid#" />
            <cfinput type="hidden" name="pid" value="#evaluate(getLastPid.pid + 1)#" />
            <cfinput type="hidden" name="pnew" value="yes" />      
              <tr>
                <td valign="top"><input type="text" size="10" name="vendor_partno" value="" /></td>
                <td valign="top"><input type="text" size="65" name="short_description" value="" /></td>
                <td valign="top"><input type="text" size="6" name="listprice" value="" /></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="" /></td>            
                <td valign="top"><input type="text" size="6" name="selling_unit" value="" /></td>
                <td valign="top" colspan="8" align="center"><cfinput type="submit" name="submit" id="submit" value="add new"><br /><br /></td>
              </tr>
            </cfform>
        </cfoutput>
        </table>
        </td>
      </tr>
    </table>
    </body>
    </html>
0
 

Author Comment

by:pulse239
Comment Utility
I did get the new products working, but my loop is not looping using the array or the list option. It is very strange. It tries to store all 3 products as one.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
That looks like the exact same error from before ...  what were the results of the debugging code I suggested??  


This code just prints out the SQL being sent to the db: It's not supposed to insert anything.

Show ProductList First <cfdump var="#productList#">

<cfloop list="#productList#" index="i">
    <!--- NO query tags here --->
    Show SQL:
    <cfoutput>
        insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
        values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfoutput>
    <br>
</cfloop>
0
 

Author Comment

by:pulse239
Comment Utility
It's inserting this:

See what you're inserting.... - array
1       2
2       2
3       3
4       3
5       4
6       4

When it should only be 3 values. The 2,3,4. So it is doubling the code.

<cfset productList="#pid#">
<cfset productListArray = listToArray(productList,",",false,true)>

<cfdump var="#productListArray#" label="See what you're inserting....">
<cfoutput>#productListArray#</cfoutput><cfabort>

<cfloop list="#productList#" index="i">
    <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#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
</cfloop>

...and it returns an error.

 Complex object types cannot be converted to simple values.
The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.

The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.
 
The error occurred in D:/Inetpub/URL/admin/vendors/addNewProduct.cfm: line 21

19 :
20 : <cfdump var="#productListArray#" label="See what you're inserting....">
21 : <cfoutput>#productListArray#</cfoutput><cfabort>
22 :
23 : <cfloop list="#productList#" index="i">

Why would it be duplicating? Could that be causing the error?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(Edit)

You're not running the exact code posted. Can you run it and post the results?  Otherwise, if you're running completely different code we're going to keep going around in circles .. ;-)

>  Complex object types cannot be converted to simple values.

That's an earlier version of some debugging code I posted - but you changed it and  accidentally introduced an error. (I explained the reason for "complex object..." error here)
0
 

Author Comment

by:pulse239
Comment Utility
Well, I had to change the code to even get the numbers to work correctly and it appears to be, but instead of looping the way it is supposed to, it duplicates it. What would be the best way to do this? Perhaps I am just doing it completely incorrectly.

It actually is the same code, the only change was I wasn't displaying the params and the else statement. Below is the original code that is producing the duplication and the complex value error. The only addition from my original post is the cfdump. Maybe I've been looking at the same piece of code too long.

<cfset productList="#pid#">
<cfset productListArray = listToArray(productList,",",false,true)>

<cfdump var="#productListArray#" label="See what you're inserting....">
<cfoutput>#productListArray#</cfoutput><cfabort>

<cfloop list="#productList#" index="i">
    <cfquery name="insertFlyerProducts" datasource="nisscorest">
            insert into flyer_vendor_campaign_products (fid,pid,vendor_partno,short_description,listprice,discount,stndnet,promonet,selling_unit,vcode,pnew)
            values (#fid#,#i#,'#vendor_partno#','#short_description#',#listprice#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#vcode#','#pnew#')
    </cfquery>
0
 

Author Comment

by:pulse239
Comment Utility
I figured out the issue with the inserting of one value, but I have 5 values from one form that need to be inserted using check boxes for the identity. So the form looks like this:

    <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>

Processing Page:

    <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,discount,stndnet,promonet,selling_unit,vcode,pnew)
                values (#fid#,#i#,'#getFlyerProducts.item_number#','#getFlyerProducts.short_description#',#getFlyerProducts.list_price#,#discount#,#stndnet#,#promonet#,'#selling_unit#','#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>

It works! Eureka! Thank you experts! :)
0
 

Author Closing Comment

by:pulse239
Comment Utility
Thank you for your patience and assistance. I wouldn't have been able to do this without you. You rock! :)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

14 Experts available now in Live!

Get 1:1 Help Now