use IN instead of =
<CFQUERY NAME="fullcheckquery" DATASOURCE="brinventory">
SELECT Steel.width,
Steel.weight,
Steel.length,
Steel.quantity,
Steel.price,
location.location,
lookup.type,
lookup2.type as smalltype,
method.method,
size_lookup.size_type
FROM Steel,
location,
lookup,
lookup2,
method,
size_lookup
WHERE steel.type_id = lookup.type_id
AND steel.typeid = lookup2.typeid
AND steel.size_id = size_lookup.size_id
AND steel.location_id = location.location_id
AND steel.method_id = method.method_id
AND lookup.type_id IN (Select type_id from lookup where
lookup.type =
'#form.steeltype#')
</CFQUERY>
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-----Original Message-----
From: Webmaster [mailto:[EMAIL PROTECTED]]
Sent: Friday, October 04, 2002 11:35 AM
To: CF-Talk
Subject: loop problems
This is the query I am running but because I have multiple entries going in
to this form that gets submitted. The bottom part of query does not work
because it is a list how do I get it to look at each individual record and
loop through for each one?
<CFQUERY NAME="fullcheckquery" DATASOURCE="brinventory">
SELECT Steel.width,
Steel.weight,
Steel.length,
Steel.quantity,
Steel.price,
location.location,
lookup.type,
lookup2.type as smalltype,
method.method,
size_lookup.size_type
FROM Steel,
location,
lookup,
lookup2,
method,
size_lookup
WHERE steel.type_id = lookup.type_id
AND steel.typeid = lookup2.typeid
AND steel.size_id = size_lookup.size_id
AND steel.location_id = location.location_id
AND steel.method_id = method.method_id
AND lookup.type_id = (Select type_id from lookup where
lookup.type =
'#form.steeltype#')
</CFQUERY>
<CFLOOP QUERY="fullcheckquery">
<CFIF (form.steeltype EQ fullcheckquery.type) AND (form.type EQ
fullcheckquery.smalltype) AND (form.width EQ fullcheckquery.width) AND
(form.weight EQ fullcheckquery.weight) AND (form.length EQ
fullcheckquery.length) AND (form.price EQ fullcheckquery.price) AND
(form.location EQ fullcheckquery.location)>
<CFQUERY DATASOURCE="brinventory" NAME="update">
SELECT steel.id,
steel.quantity
FROM Steel,
lookup,
lookup2,
location
WHERE steel.type_id = lookup.type_id
AND steel.typeid = lookup2.typeid
AND steel.location_id = location.location_id
AND lookup.type_id = (Select type_id from lookup where
lookup.type =
'#form.steeltype#')
AND lookup2.typeid = (Select typeid from lookup2 where
lookup2.type =
'#form.type#')
AND Steel.width = '#form.width#'
AND Steel.weight = '#form.weight#'
AND Steel.length = '#form.length#'
AND steel.price = '#form.price#'
AND location.location_id = (Select location_id from location
where
location.location = '#form.location#')
</CFQUERY>
<CFSET setquantity =update.quantity + form.quantity>
<CFQUERY DATASOURCE="brinventory" NAME="update">
UPDATE Steel
SET quantity = #setquantity#
WHERE steel.id = #update.id#
</CFQUERY>
<CFELSE>
<CFOUTPUT>This is a new entrie!</CFOUTPUT>
</CFIF>
</CFLOOP>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
