Multi-selection box

mrong
mrong used Ask the Experts™
on
Greeting,
I need a multi-selection text box in asp which allow users to select data and then run the search thru the table in access and display the data.
Please provide sample code.
Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
what exactly do you mean by multi-selection text box?

Author

Commented:
It is like a combo box and user can select multiple items from it. thanks.
Top Expert 2011

Commented:
Did you mean something like this?

     <%
		SQLstmt = "Select fieldname from yourTable"
	        set ResultsSet = conn.Execute(SQLstmt)
            %>
            <select name="Result" size="4" multiple>
            <option value="">&nbsp;</option>
            <%
             Do Until ResultsSet.EOF
              ResultID = ResultsSet("fieldname")
             If ResultID = Result Then sel = "SELECTED" Else sel = ""
            %>
            <OPTION Value="<%=ResultID%>" <%=sel%> > <%=ResultsSet("fieldname")%>
           <%
            ResultsSet.MoveNext
            Loop
            %>

          </select>

Open in new window

Author

Commented:
sammySeltzer:
I tried your code and it gave me the combo box I need.
There is a empty cell in the first row of the combo box, is there anyway to get rid of it?
Top Expert 2011

Commented:
Just get rid of this line:
            <option value="">&nbsp;</option>

Author

Commented:
what will be in request("Result") when the user submit this form?
say the user select item1,item2, item4. will request("Result") ='item1,item2,item4"?

thanks.

Author

Commented:
How to add a SELECT ALL in the box? thanks
Top Expert 2011

Commented:
I think you can do this:

result = request("Result")

strResult = Replace( result , ", ", "','" )

That will create comma-separated list of results from the combo selection
Top Expert 2011

Commented:
Also, if you want to add the SELECT ALL feature, then it may be necessary to change from dropdown combo box to checkbox combo box.

Author

Commented:
I printed the request("Result") on the screen once submitted and the output is the following:
item1,item2,item3
How to change it to 'item1',item2','item3'?
Access requires single quote.

Also do you have sample code for checkbox combo box?

thanks.
Top Expert 2011

Commented:
You will see the ' (single quotes) if you are performing an insert or delete.

As far as displaying records, it won't show the single quotes and it shouldn't give you an error.

Are you getting one?

I will have to put something together for the checkbox combo

Author

Commented:
I am performing select for searching.
If I don't add single quote, it works fine if user select 1 item. For more than 1 item, it won't find any match.
My sql is the following:
  strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty in ('"& request("Result")&"');"

Thanks.

Author

Commented:
ok below takes care the single quote problem
strResult = "'"&Replace( request("Result") , ", ", "','" )& "'"
Top Expert 2011

Commented:
You are using the wrong search param.

First, don't forget this:

strResult = Replace( result , ", ", "','" )

Then it should be more like this:

 strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty IN ('" & strResult & "')"

Open in new window


Then for checkbox combox with SELECT ALL feature, something like this:

<head>
<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
var checkflag = "false";
function check(field) {
if (checkflag == "false") {
for (i = 0; i < field.length; i++) {
field[i].checked = true;}
checkflag = "true";
return "Uncheck All"; }
else {
for (i = 0; i < field.length; i++) {
field[i].checked = false; }
checkflag = "false";
return "Check All"; }
}
//  End -->
</script>
</head>
<body>
<%

	'Open a connection to the database
	projGroup= Request.QueryString("projGroup")
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
    	"DATA SOURCE=" & server.mappath("db\YourDB.mdb")
	objConn.Open

   'Query the table

    SQL = "SELECT fieldname FROM YourTable ORDER BY SomeFieldName asc"
    'response.write sql
    'response.end
    Set objRS = objConn.execute(SQL)

	'Display the FORM and the top of the TABLE
	Response.Write "<FORM METHOD=POST ACTION=""doYourThing.asp"" target=""_new"">"
	Response.Write "<input type='checkbox' name='checkall'  onClick='this.value=check(this.form.Delete)'>check all/uncheck all"
	Response.Write "<TABLE border='1' cellpadding='0' cellspacing='4' style='border-collapse: collapse' bordercolor='#111111' width='100%'>"
	Dim iLoop
	Response.Write "<TR>"
	Response.Write "<TH>Some column Header</TH>"
	Response.Write "</TR>"

	'Display each element in the table..
	Do While Not objRS.EOF

		Response.Write "<TR>" & vbCrLf

		'Create a checkbox to check for searching records, setting the checkboxes
		'Value equal to the current items
		Response.Write "<TD><INPUT TYPE=CHECKBOX NAME='result' "
		Response.Write "VALUE=" & objRS("someIDValue") & "></TD>"

		'Display the number and name of the record
		Response.Write vbCrLf & "<TD>" & objRS("somevalue") & "</TD>"

		Response.Write "</TR>" & vbCrLf & vbCrLf

		'Move to the next record...
		objRS.MoveNext
	Loop

	'Print the end of the table, the submit button, and the
	'the end of the form.
	Response.Write "</TABLE>"
	Response.Write "<P><INPUT TYPE=SUBMIT VALUE=""Search Selected records"">"
	Response.Write "</FORM>"


	'Clean up our ADO objects
	objRS.Close
	Set objRS = Nothing

	objConn.Close
	Set objConn = Nothing
%>
</body>
</html>

Open in new window

Top Expert 2011

Commented:
If you put single quotes here:
ok below takes care the single quote problem
strResult = "'"&Replace( request("Result") , ", ", "','" )& "'"

Then you would have problem putting them here again:

 strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty IN ('" & strResult & "')"

Author

Commented:
there is an error in script function. say "Length is null or not an boject"
Top Expert 2011

Commented:
Sorry, change this:
Response.Write "<input type='checkbox' name='checkall'  onClick='this.value=check(this.form.Delete)'>check all/uncheck all"

To this:

Response.Write "<input type='checkbox' name='checkall'  onClick='this.value=check(this.form.result)'>check all/uncheck all"


Author

Commented:
the checkall function is working.
when I submitted this checkbox form, it truncated the request("result') like the following:
if I chose 'Parking Lot' then request("result') ='Parking'  other than the whole string.
Top Expert 2011

Commented:
Again, you cannot use request("result")

You need to use:

strResult = "'"&Replace( request("Result") , ", ", "','" )& "'"
 strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty IN ('" & strResult & "')"

Author

Commented:
Yes, I am using strResult when I got the error. thanks
Top Expert 2011

Commented:
Ok, let's make one change.

Replace these 2 lines:

            'Create a checkbox to check for searching records, setting the checkboxes
            'Value equal to the current items
            Response.Write "<TD><INPUT TYPE=CHECKBOX NAME='result' "
            Response.Write "VALUE=" & objRS("someIDValue") & "></TD>"

with these:

            'Create a checkbox to check for searching records, setting the checkboxes
            'Value equal to the current items
            %>
            <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=objRS("someIDValue")%>"></TD>
            <%
Top Expert 2011

Commented:
Oh, by the way, this:

strResult = Replace( result , ", ", "','" )
 strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty IN ('" & strResult & "')"

is still wrong.

It should be this:

Author

Commented:
I have  Response.Write " <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=rsItems1("Specialty_Details")%>"></TD>"
Error:
Expected end of statement
Response.Write " <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=rsItems1("Specialty_Details")
----------------------------------^



Top Expert 2011

Commented:
The code I posted does not have Response.write.

Replace these 2 lines:

            'Create a checkbox to check for searching records, setting the checkboxes
            'Value equal to the current items
            Response.Write "<TD><INPUT TYPE=CHECKBOX NAME='result' "
            Response.Write "VALUE=" & objRS("someIDValue") & "></TD>"

with these:

            'Create a checkbox to check for searching records, setting the checkboxes
            'Value equal to the current items
            %>
            <TD><INPUT TYPE="CHECKBOX" NAME="result" VALUE="<%=objRS("someIDValue")%>"></TD>
            <%

Author

Commented:
sammySeltzer:
I replaced with your code and it works.
One last question. When I clicked on the 'Search selected record' , the new page with searching result poped up. But on the main page, it showed message 'Error on Page' in the page footer bar.
Do you know how to fix it?
thanks.
Top Expert 2011
Commented:
Ok, quick question for you.

When you click 'Search selected record' , what page would you like to see pop up?

In the code I posted, I asked that when you click 'Search selected record', for the a page called doYourThing.asp to pop up and process the search data. See this line:      
Response.Write "<FORM METHOD=POST ACTION=""doYourThing.asp"" target=""_new"">"

That's just a page I stuck in there. Replace it with your own page and then continue with the setup we established earlier:

result = request("result")
strResult = Replace( result , ", ", "','" )
strSQL1 ="SELECT DISTINCT tbl1.* FROM tbl1 left JOIN tbl2 ON tbl1.ID = tbl2.ID where tbl2.Specialty IN ('" & strResult & "')"

etc

etc



Author

Commented:
Yes, I have replaced the page name I want popup as the following ,but still having "error on page" message.

Response.Write "<FORM METHOD=POST ACTION=""MasterSpecialty.asp"" target=""_new"">"

Author

Commented:
sammySeltzer:
Sorry, my bad. There was an error in my script.
Thank you very much for your help.
Top Expert 2011

Commented:
How is the new search page different from the main page?

I am trying to understand why the main page is giving an error when you are processing data in the new search page.

Can you show relevant data from the page that is error'ing out?
Top Expert 2011

Commented:
Thanks a lot for the points and grade.

Author

Commented:
sammySeltzer:
I will post another questinon soon for merge two search pages together(you saw both questions).
Please see if you can help.
Top Expert 2011

Commented:
sure, just come back here and let me know once the question is posted.

Author

Commented:
sammySeltzer:
I just posted another question at the link below. thanks.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/ASP/Q_27244125.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial