Solved

Looping @#$%^

Posted on 2002-05-16
9
247 Views
Last Modified: 2013-12-24
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
Comment
Question by:jriver12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Accepted Solution

by:
smisk earned 100 total points
ID: 7014187
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
 

Author Comment

by:jriver12
ID: 7014457
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
 
LVL 15

Expert Comment

by:danrosenthal
ID: 7014504
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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 15

Expert Comment

by:danrosenthal
ID: 7014517
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
 

Author Comment

by:jriver12
ID: 7014542
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
 

Author Comment

by:jriver12
ID: 7014659
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
 
LVL 1

Expert Comment

by:smisk
ID: 7014709
Good to hear...
0
 
LVL 1

Expert Comment

by:smisk
ID: 7014741
Good to hear...
0
 

Author Comment

by:jriver12
ID: 7014815
thanks bud.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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