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

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.

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.

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, ...?)

i is a number or INT value in the database.

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?

I am trying to insert number 123455 as a test. Could this be the issue?

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

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

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>

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

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

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

just remove the <cfoutput>#productListArray#</cfoutput> and only dump and abort, what you se and can you post the image of that

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>

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

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>

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.

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>

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?

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

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>

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! :)

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