• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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...
0
jriver12
Asked:
jriver12
  • 4
  • 3
  • 2
1 Solution
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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.

Join & Write a Comment

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.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now