Looping @#$%^

I am agravated that I have not yet come to grasp with the logic of looping(never sure of which one to use and when).

here is my situation.

have 2 tables
table 1
prodid (int)      prodno (key) Pc_description(varchar 50)
1                  2548             green eggs and ham
2                  2549             Cats, socks the

this table populates a dropdown list using:
<query>
select pc_description from products
</query>
Resulting
   <select name="productcodes" size="3" multiple>
          <cfoutput query="view_getproducts">
            <option value="#PC_description#"><font face="Geneva, Arial, Helvetica, san-serif" size="1">#PC_description#</font></option>
          </cfoutput>
        </select>

some of the items in this list have commas in them and must be kept together.

now on insert several things happen.

I must now get the prodno for this PC_description so I do this.
<cfif IsDefined("form.productcodes")>    
<cfloop list="#form.productcodes#" Index="productcodes">
 <!---convert the string to product numbers --->
 <cfquery name="convert_pcdescription_to_productcode" datasource="vendors">
SELECT     PRODNO
FROM         PRODUCTS
WHERE     (dbo.PRODUCTS.Pc_Description = '#form.productcodes#') </cfquery>
 
Now that that has been resolved i need to insert this record selection into another table that holds record specific information depending on the vendornumber.

I do this by :
<!--- insert them individualy --->
<cfoutput query="convert_pcdescription_to_productcode">
<cfquery name="CT_Prod_Codes" datasource="vendors">
Insert into ExhProd(vendorNumber, Prodno)
values (#variable.vendornumber#, '#prodno#')
</cfquery>
</cfoutput>
</cfloop>
</CFIF>

this works great if I only select 1 selection from my dropdown box.

how do I get it to

1.  get it to break up the multiple selections from the selection box  an list them individual records for insert as individual records.

2.  get em to not break up the Pc_description at the commas.

thanks ih advance...
jriver12Asked:
Who is Participating?
 
smiskCommented:
How about replacing the commas for the time being :

<select name="productcodes" size="3" multiple>
     <cfoutput query="view_getproducts">
          <option value="#replace(PC_description, ",", "^", "all")#">#PC_description#</option>
     </cfoutput>
</select>

Then on the receiving end, just replace the "^" with a "," after you extract the item :

<cfloop list="#form.productcodes#" Index="productcodes">
     <!--- you want to do your insert of individual records here --->
     <cfoutput>#replace(productcodes, "^", ",", "all")#</cfoutput>
</cfloop>

I think this solves both problems...?
0
 
jriver12Author Commented:
smisk,

I am still having trouble inserting multiple records from the drop down list.  

this is what I have.

take a look>
<query name="view_exsearch>
get something from somewhere where something = visitorNumber

</query>
 <cfset Variable.visitorNumber = view_exsearch.visitornumber>    
<cfif IsDefined("form.productcodes")>    
<cfloop list="#form.productcodes#" Index="productcodes">
 <!---convert the string to product numbers --->
 <cfquery name="convert_pcdescription_to_productcode" datasource="show">
SELECT     PRODNO
FROM         PRODUCTS
WHERE     (dbo.PRODUCTS.Pc_Description = '#replace(form.productcodes, "^", ",", "all")#') </cfquery>
<!--- <cfoutput>#replace(productcodes, "^", ",", "all")#</cfoutput> --->
<!--- insert them individualy --->
<cfoutput query="convert_pcdescription_to_productcode">
<cfquery name="CT_Prod_Codes" datasource="visitor">
Insert into ExhProd(visitorNumber, Prodno)
values (#variable.visitornumber#, '#prodno#')
</cfquery></cfoutput>
</cfloop>
</CFIF>
0
 
danrosenthalCommented:
When originally listing the drop-down why not set the values to be 'prodno' instead of converting them later?:

<option value="#prodno#"><font face="Geneva, Arial, Helvetica, san-serif" size="1">#PC_description#</font></option>

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
danrosenthalCommented:
The insert can be something like this:

<cfloop list="#form.productcodes#" Index="listitem">
  <cfquery name="CT_Prod_Codes" datasource="visitor">
    Insert into ExhProd(visitorNumber, Prodno)
    values (#variable.visitornumber#, '#listitem#')
  </cfquery>
</cfloop>
0
 
jriver12Author Commented:
danrosenthal
that method works on single selections of the drop down but once multiples are selected then the following error occurs.


[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '25,30' to a column of data type int.


SQL = "Insert into ExhProd(visitorNumber, Prodno) values (293, '25,30')"


what I need to figure out is how to get the comma delimetd list broken then enter as individuals ie (using the records above)

visitor number =293  prodnos selected were 25,30

there fore in the db they should be inserted as
2 rows

visitornubmer           Prod No
293                      25
293                      30
I know I am missing something..
0
 
jriver12Author Commented:
AH HA! got it.

I put it all in a loop.
<cfloop list="#form.productcodes#" Index="productcodes" delimiters=",">
 <cfquery name="CT_Prod_Codes" datasource="visitor">
Insert into ExhProd(exhibitorNumber, Prodno)
values (#variable.visitornumber#, '#Productcodes#')
</cfquery>
</cfloop>

I am going to split the points with all that have contributed.

any objections.?
0
 
smiskCommented:
Good to hear...
0
 
smiskCommented:
Good to hear...
0
 
jriver12Author Commented:
thanks bud.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.