Multi Select (Solved) (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

hhammash -> 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



Spooky -> RE: Multi Select (11/24/2002 14:55:50)

The text format would need to look like :

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



hhammash -> 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



rdouglass -> 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???



hhammash -> 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



rdouglass -> 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" ) & " )"



hhammash -> 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



rdouglass -> 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.



hhammash -> RE: Multi Select (11/27/2002 0:16:22)

Hi rdouglas,

Thank you for the clarification.

Hisham



hhammash -> 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



hhammash -> 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



Spooky -> 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



hhammash -> 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



Spooky -> 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



hhammash -> 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



Spooky -> 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?



hhammash -> 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



Spooky -> 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



hhammash -> 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




Spooky -> 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 ;-)



rdouglass -> 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....[8|]



hhammash -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.109375