Problem Quering Multiple selections in a select box

If multiple messages are selected , for eample 3 are selected , after the form submission they will appear in a comma seperated value
example

#form.mylistbox# will be hello1,hello2,hello3

so when u do futher queries (selects or inserts based on that

if its a select then instead of "=
' you can use "IN"

forexample - usual
select * from table1
where productlist = #form.mylistbox#
now you do
select * from table1
where productlist IN #form.mylistbox#
OR Loop oi

<cfloop from="1" to="#listlen(form.mylistbox)#" index"iIndex">

Now whatever you do, but instead or form.mylistbox, use the index #iIndex#

</cfloop>

Only pasting a snapshot of your code might help..

cheers.
yog

here are the codes for the selection box:

<cfoutput query="ProductSearch">
<option value="#productSearch.DESCRIPTION#">
#productsearch.DESCRIPTION#</option>
</cfoutput>

here is the code that pulls the values from the select.

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE '#form.results#')
</CFQUERY>


this is what I get when the code above is ran in CF :

SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE 'Bearings, Sanitary,Tables, Debeaking & Inoculation')
Bearings, Sanitary is one topic and
Tables, Debeaking & Inoculation is the second

it runs the query but returns no record sets.

Solution 1.

<cfloop list="#form.results#" index="i_result" delimiter=",">

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,
dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,
dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
 dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
 (dbo.PRODUCTS.DESCRIPTION LIKE '#i_result#')
</CFQUERY>

</cfloop>

<!---Make ready for using IN--->
<cfset i = 1>
<cfloop list="#form.results#" index="i_result" delimiter=",">
   <cfif i eq 1>
       <cfset mylist = "'" & "#i_result#" & "',">
   <cfelseif i neq listlen(form.results)>
       <cfset mylist = mylist & "'" & "#i_result#" & "',">
   <cfelse>
       <cfset mylist = mylist & "'" & "#i_result#" & "'">
   </cfif>
<cfset i = i + 1>
</cfloop>

Now need not loop the query - use IN

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,

dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,

dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
dbo.PRODUCTS.DESCRIPTION IN (#mylist#)
</cfquery>

cheers..

sorry second comment is solution 2.

for solution one, you have too put th output also inside loop accordingly..

cheers.

here are the codes for the selection box:

<cfoutput query="ProductSearch">
<option value="#productSearch.DESCRIPTION#">
#productsearch.DESCRIPTION#</option>
</cfoutput>

here is the code that pulls the values from the select.

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE '#form.results#')
</CFQUERY>


this is what I get when the code above is ran in CF :

SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity, dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail, dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
                   dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
                   (dbo.PRODUCTS.DESCRIPTION LIKE 'Bearings, Sanitary,Tables, Debeaking & Inoculation')
Bearings, Sanitary is one topic and
Tables, Debeaking & Inoculation is the second

it runs the query but returns no record sets.

Ah did you try the above one i said

---------------
<cfset i = 1>
<cfloop list="#form.results#" index="i_result" delimiter=",">
  <cfif i eq 1>
      <cfset mylist = "'" & "#i_result#" & "',">
  <cfelseif i neq listlen(form.results)>
      <cfset mylist = mylist & "'" & "#i_result#" & "',">
  <cfelse>
      <cfset mylist = mylist & "'" & "#i_result#" & "'">
  </cfif>
<cfset i = i + 1>
</cfloop>
<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,
dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,
dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
dbo.PRODUCTS.DESCRIPTION IN (#mylist#)
</cfquery>

Looks like you didn't

Thanks

Sorry about the last comment added I made the mistake of refreshing my pc and it resent the same comment.

here are the results of codes that you sent.

Trying solution # 1 I got:

Error Diagnostic Information
Just in time compilation error

An unknown attribute 'delimiter' has been encountered at document position (9:48) to (9:56) while processing tag CFLOOP. This tag can only take the following attributes:

COLLECTION
CONDITION
DELIMITERS
ENDROW
FROM
INDEX

Using Solution 2:

Error Diagnostic Information
Just in time compilation error

An unknown attribute 'delimiter' has been encountered at document position (11:48) to (11:56) while processing tag CFLOOP. This tag can only take the following attributes:

COLLECTION
CONDITION
DELIMITERS
ENDROW
FROM
INDEX
ITEM
LIST
QUERY
STARTROW
STEP
TO
The last successfully parsed CFML construct was a CFLOOP tag occupying document position (11:1) to (11:7).

The specific sequence of files included or processed is:
C:\Inetpub\wwwroot\Products\matchco.cfm      


Date/Time: 06/06/01 15:11:11
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)
Remote Address: 127.0.0.1
HTTP Referer: http://127.0.0.1/Products/exhibitProdList.cfm

 




--------------------------------------------------------------------------------



--------------------------------------------------------------------------------
Execution Time

4 milliseconds
4 ms STARTUP, PARSING, & SHUTDOWN



--------------------------------------------------------------------------------
Parameters
Form Fields:

FIELDNAMES=RESULTS,RESULTS,RESULTS



ITEM
LIST
QUERY
STARTROW
STEP
TO
The last successfully parsed CFML construct was a CFLOOP tag occupying document position (9:1) to (9:7).

The specific sequence of files included or processed is:
C:\Inetpub\wwwroot\Products\matchco.cfm      


Date/Time: 06/06/01 15:07:42
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows 98)
Remote Address: 127.0.0.1

sorry that is a spelling mistake

Instead of
<cfloop list="#form.results#" index="i_result" delimiter=",">
USE
<cfloop list="#form.results#" index="i_result" delimiters=",">

cheers

that works,  it is now finding the records but it still is not showing all of them:

here is the code for the page can you tell me why they are not comming up.  :




<cfloop list="#form.results#" index="i_result" delimiters=",">

<CFQUERY NAME="searchit2" DATASOURCE="ProductCodes">
SELECT      dbo.Exhibitr.COMPANYNAME, dbo.Exhibitr.CompanyMailingAddress, dbo.Exhibitr.CompanyCity,

dbo.Exhibitr.CompanyState, dbo.Exhibitr.ZIP1, dbo.Exhibitr.CompanyPhone, dbo.Exhibitr.CompanyFax, dbo.Exhibitr.CompanyEmail,

dbo.PRODUCTS.DESCRIPTION
FROM         dbo.Exhibitr, dbo.EXHPROD_, dbo.PRODUCTS
WHERE       dbo.Exhibitr.ExhNum = dbo.EXHPROD_.ExhNUM AND
dbo.PRODUCTS.PRODNum = dbo.EXHPROD_.ProdNUM AND
(dbo.PRODUCTS.DESCRIPTION LIKE '#i_result#')
</CFQUERY>

</cfloop>

<body  >


<cfoutput query="searchit2">

   Company: #COMPANYNAME# <br>
Address:#CompanyMailingAddress#<br>
CITY, STATE ZIP #CompanyCity#, #CompanyState# #ZIP1#<br>
PHONE:#CompanyPhone#<br>
FAX:#CompanyFax#<br>
EMAIL: <a href=mailto:"#CompanyEmail#"> #CompanyEmail#</a><p>
</font>
</cfoutput>  

If you use solution 2, you need not loop the result

Thank you very much it worked like a charm and unlike anything I was trying to write It has no errors.

here is an lil extra for the final twist on the results.

again thanks

like always you guys are flawless..

Glad that worked :)

cheers..