Solved

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

Posted on 2013-11-05
24
318 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_
ID: 39624792
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
ID: 39624849
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_
ID: 39624954
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

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

Expert Comment

by:_agx_
ID: 39624994
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
ID: 39625174
I am trying to insert number 123455 as a test. Could this be the issue?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39625323
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 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39626699
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
ID: 39627793
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_
ID: 39628263
<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
ID: 39630433
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
ID: 39630462
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
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39630497
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
ID: 39630562
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
ID: 39630740
>  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
ID: 39637736
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
ID: 39637802
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
ID: 39637821
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_
ID: 39639662
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
ID: 39646818
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_
ID: 39648161
(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
ID: 39649812
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
ID: 39650225
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
ID: 39651580
Thank you for your patience and assistance. I wouldn't have been able to do this without you. You rock! :)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 …
In this article I will describe the Detach & Attach 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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

806 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