Solved

Looping @#$%^

Posted on 2002-05-16
9
229 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Script 12 152
Internal DNS Alias (CNAME) - two domain names to one IP address 4 76
IIS Site Configuration Copy 2 74
Soundcloud.com 4 21
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 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

9 Experts available now in Live!

Get 1:1 Help Now