Solved

Looping @#$%^

Posted on 2002-05-16
9
219 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
  • 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now