navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

Microsoft MVP

 

Multi Select (Solved)

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Multi Select (Solved) 		Page: [1]
 
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		Multi Select (Solved) - 11/24/2002 14:43:25   
Hi all,

I have a multiselect drop down. When more than one option is selected the SQL sould change into " Where ...IN..." .

How to wirte that for Text and Numbers?

Suppose I selected three numbers and want the DRW to search IN(1990,1998,1999)

Or I selected text IN(UK,US,France)

I think number is easier in this case, text needs some trimming.

Thanks a lot
Hisham

< Message edited by hhammash -- 12/30/2002 3:21:25 PM >
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select - 11/24/2002 14:55:50   
The text format would need to look like :

IN(' UK' ,' US' ,' France' )

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select - 11/25/2002 12:14:24   
Hi Spooky,

Thank you for your reply. I know how to do that directly. What I meant is that I have a multi select drop down, it' s name is " City" for example, I select 4 cities then click submit.

What I was is how to write the sql: example

IN(City)
City here is the name of the drop down which is multi select.

The question is how to save all the selections into a variable and look in the contents of that variable?

Thanks
Hisham

(in reply to hhammash)
rdouglass

 

Posts: 9187
From: Biddeford, ME USA
Status: offline

  		RE: Multi Select - 11/25/2002 13:02:59   
How about something like this:

<% DIM cityArray, myString
cityArray = split(Request.Form(" City" )," ," )
myString = " "

FOR x = 0 to UBound(cityArray)
' (Do your stuff here - example below)
myString = myString & " ,' " & cityArray(x) & " ' "
NEXT%>

This example would take your multi-select dropdown data and convert it to a string like Spooky suggested (' value1' ,' value2' ,...). Then you (in theory..:)) could do something like:

" SELECT * FROM myTable WHERE city IN (" & myString & " )"

Is that what you' re looking for???

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select - 11/25/2002 14:55:50   
Hi rdouglas,

It is what I am looking for, but:

1- Where to put it on a dieted DRW that has the Drop down box?

2- Do you have a link to a tutorial how to deal also with numbers?

Thanks a lot
Hisham

(in reply to hhammash)
rdouglass

 

Posts: 9187
From: Biddeford, ME USA
Status: offline

  		RE: Multi Select - 11/25/2002 15:30:39   
I' m not really sure what your end result is supposed to be, so...

1. The code I provided needs to be anywhere on a page the dropdown is posting to (remember this has to be ' posted to' because of the ' Request.Form()" item). However, you' ll need it somewhere before you use the myString values.

2. Dealing with numbers, IIRC you don' t need to do any array stuff; just something like:

" SELECT * FROM myTable WHERE city IN (" & Request.Form(" myDropdownName" ) & " )"

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select - 11/25/2002 16:45:06   
Thank you roger,

I am posting to the same page. A multiselect populated from the database using a DRW and underneath is the main DRW which lists the results.

Is it better to put the search form on a separate page?

Thanks
Hisham

< Message edited by hhammash -- 11/25/2002 4:49:56 PM >

(in reply to hhammash)
rdouglass

 

Posts: 9187
From: Biddeford, ME USA
Status: offline

  		RE: Multi Select - 11/26/2002 9:32:04   
I don' t think its any better one way or another - let the site (or application) dictate that. The only thing is, if you put it on the same page, you' ll have to deal with the initial page entry. What I sometimes do is put a record in the DB with an explination (or a ' souped up' no-record-found message) and use that as the default.

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select - 11/27/2002 0:16:22   
Hi rdouglas,

Thank you for the clarification.

Hisham

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select - Rdouglas - 12/28/2002 13:40:39   
Hi rgdouglas,

I tried the code, it is almost working, but I am gettin this error:

quote:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' Country IN ( ,' UK' ,' Sweden' ,' Germany' )' .
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



Here is the full page: 
<html>

<head>
<meta name=" GENERATOR"  content=" Microsoft FrontPage 5.0" >
<meta name=" ProgId"  content=" FrontPage.Editor.Document" >
<meta http-equiv=" Content-Type"  content=" text/html; charset=windows-1252" >
<title>Country</title>
</head>

<body>

<form BOTID=" 0"  METHOD=" POST"  ACTION=" Search3.asp" >
  <table BORDER=" 0" >
    <tr>
      <td><b>Country</b></td>
      <td><nobr>
<!--#include file=" ../_fpclass/fpdblib.inc" -->
<% if 0 then %>
<SCRIPT Language=" JavaScript" >
document.write(" <div style=' background: yellow; color: black;' >The Database Results component on this page is unable to display database content. The page must have a filename ending in ' .asp' , and the web must be hosted on a server that supports Active Server Pages.</div>" );
      </SCRIPT>
<% end if %>
<%
fp_sQry=" SELECT * FROM Customers" 
fp_sDefault=" " 
fp_sNoRecords=" No records returned." 
fp_sDataConn=" Customers" 
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice=" Country" 
fp_sMenuValue=" Country" 
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
<select NAME=" Country"  SIZE=" 6"  multiple>
      <!--#include file=" ../_fpclass/fpdbrgn1.inc" -->
      <option><%=FP_FieldHTML(fp_rs," Country" )%></option>
      <!--#include file=" ../_fpclass/fpdbrgn2.inc" -->
       </select>
    </nobr></td>
    </tr>
  </table>
  <p><br>
  <input TYPE=" Submit" ><input TYPE=" Reset" >
</p>
  <p> </p>
</form>
<table width=" 100%"  border=" 1" >
  <thead>
    <tr>
      <td><b>CustomerID</b></td>
      <td><b>CompanyName</b></td>
      <td><b>ContactTitle</b></td>
      <td><b>Country</b></td>
    </tr>
  </thead>
  <tbody>
<!--#include file=" ../_fpclass/fpdblib.inc" -->
<% if 0 then %>
<SCRIPT Language=" JavaScript" >
document.write(" <div style=' background: yellow; color: black;' >The Database Results component on this page is unable to display database content. The page must have a filename ending in ' .asp' , and the web must be hosted on a server that supports Active Server Pages.</div>" );
</SCRIPT>
<% end if %>
<% DIM CountryArray, myString 
CountryArray = split(Request.Form(" Country" )," ," ) 
myString = " "  

FOR x = 0 to UBound(CountryArray) 
myString = myString & "  ,' "  & CountryArray(x) & " ' "  
NEXT
%> 

<%
fp_sQry=" SELECT * FROM Customers WHERE Country IN ("  & myString & " )"  
fp_sDefault=" Country=" 
fp_sNoRecords=" <tr><td colspan=4 align=left width=" " 100%" " >No records returned.</td></tr>" 
fp_sDataConn=" Customers" 
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=" " 
fp_sMenuValue=" " 
fp_iDisplayCols=4
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file=" ../_fpclass/fpdbrgn1.inc" -->
<!--webbot bot=" DatabaseRegionStart"  endspan i-checksum=" 49162"  --><tr>
      <td>
      <%=FP_FieldVal(fp_rs," CustomerID" )%>  </td>
      <td>
      <%=FP_FieldVal(fp_rs," CompanyName" )%>  </td>
      <td>
       <%=FP_FieldVal(fp_rs," ContactTitle" )%>  </td>
      <td>
      <%=FP_FieldVal(fp_rs," Country" )%>  </td>
    </tr>
    <!--#include file=" ../_fpclass/fpdbrgn2.inc" -->
</tbody>
</table>

</body>

</html>


Where did I do wrong.

Thanks a lot
Hisham

< Message edited by hhammash -- 12/28/2002 1:43:35 PM >

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas) - 12/29/2002 3:45:42   
Hi Roger,

I am sure if I can get rid of the comma before the first country it will work.

Thanks
Hisham

(in reply to hhammash)
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 6:16:08    
FOR x = 0 to UBound(CountryArray)
myString = myString & " ' "  & CountryArray(x) & " ' " 
If x < UBound(CountryArray) then response.write " ," 
NEXT


_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 10:28:27   
Hi Spooky,

Thanks a lot for your reply.

I tried the code but:

1- When I run the page I get the following message:
quote:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' Country IN ()' .
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers


2- When I select 1 country it works fines

3- When I select more than one country it give no error and no results and puts a comma on top of the results table.

Thanks a lot
Hisham


< Message edited by hhammash -- 12/29/2002 10:29:21 AM >

(in reply to hhammash)
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 11:17:33   
Youll need to modify your SQL string if there is no input for country so it selects all records.
If you want to return no records, use a term that doesnt exist in the country list as the default string


_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 11:50:18   
Hi Spooky,

Thanks for your reply.

I changed the SQL, now it lists no records, when I search one country it is OK, when I search more than one it gives no records. I am sure that the countries I select should return records.

I think the SQL is not taking it as In(' USA' ,' France' ,' Brazil' ).

I think I still have a problem with the position of the comma. It should start only after the first country and stop before the last country.

Any more ideas?

Thanks a lot
Hisham

(in reply to hhammash)
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 13:43:06   
Im not entirely sure of the logic you need, but this is waht I was thinking :

If trim(myString) = " " then myString = " ' none' "

fp_sQry=" SELECT * FROM Customers WHERE Country IN (" & myString & " )"

That should return the " no records" error
Is that what you want? or what supposed to happen when no records exist?


_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/29/2002 14:36:38   
Hi Spooky,

I will try this, but I will answer your question before.

What I have is a multiselect list box which displays countries. I want to select more than one country and search.

Example: I might select 3 countries form the list USA,France and Brazil then press submit.

I want to save whatever I select in an array then split it into (' USA' ,' France' ,' Brazil' ) in order to be able to search it by SQL as:
Select * from customers where country in(' USA' ,' France' ,' Brazil' ). What I am getting from this code is where country in (,' USA' ,' France' ,' Brazil' ), the first comma is the problem.

Thank you for your patience.

Thanks Spooky
Hisham

(in reply to hhammash)
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/30/2002 1:29:11   
We sorted the first comma right?
I wasnt sure what you needed if no countries were choosen.
It sounds like all records should be returned if none are selected.

If so : 

If len(myString) > 0 then
fp_sQry=" SELECT * FROM Customers WHERE Country IN ("  & myString & " )"   
Else
fp_sQry=" SELECT * FROM Customers
End if



_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/30/2002 3:24:27   
Hi,

Thank you Spooky.

I will try this and let you know. But the good news is that I' ve got a very simple solution for the whole matter from afriend no_mac_jack.

The solution needs no code and no diet.

It is:
The Option tags were like this
<option><%=FP_FieldHTML(fp_rs," Country" )%></option>

Convert the tag to read like this:
<option VALUE=" ' <%=FP_FieldHTML(fp_rs," Country" )%>' " ><%=FP_FieldHTML(fp_rs," Country" )%></option>

Then in the SQL statement, put:

Select * from customers where country in(::country::)

no need for and quotation marks. The ::country:: here is the name of the list box.

Try it here:
http://www.fmhs.uaeu.ac.ae/immconf/country2.asp

Thanks a lot for your help Spooky, I will try your last code and let you know. Because I will need to solve it with the code.

Thanks
Hisham


(in reply to hhammash)
Spooky

 

Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/30/2002 12:26:13   
Yep, that would be my solution too, but I was just following the code above ;-)

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to hhammash)
rdouglass

 

Posts: 9187
From: Biddeford, ME USA
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/30/2002 13:13:42   
Ya mean, I' ve been doin' it by ' brute force' and nobody told me there was a quicker way??? :)

Ah well, good practice....:)

(in reply to hhammash)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Multi Select (rdouglas, Spooky) - 12/30/2002 15:19:07   
Hi rdouglas,

Thank you for your reply.

In fact I was surprised to find this easy quick way, but it was also useful to know how to be done by coding.

Thanks
Hisham

(in reply to hhammash)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Multi Select (Solved) 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.