Populate a Multicolumn ASP Listbox from Access data

**simora** · 07-26-2010 02:34 PM

How can I create a Listbox or Multicolumn listbox using ASP and fill in 2 textboxes when I select an item from the ListBox.

I want to use that information on a form, then submit the form after.

If I have to use a PopUp as the form, with elements of the ASP page, that's OK.

Thanks

**nursa** · 07-27-2010 03:32 AM

can you have multicolumn listboxes?

You have to use html code to print your page, so if you're using asp you'll have to 'response.write ("<html>blah blah</html>")'

In which case you can use onchange event handlers to populate other elements

i fill a drop-down using asp like this, but the data is coming from SQL Server:

Code:

<select name="test" id="test">
<option value="">Select Something</option>
<%sSql="SELECT col1, col2 FROM table"         ---- or exec SP
	set rsmTmp= Server.CreateObject("ADODB.Recordset")
    rsmTmp.Open sSql, GetMyConnection         ---- this is a connection routine from an include page.
	if not rsmTmp.EOF then
	do while not rsmTmp.EOF
		response.write "<option value='"&rsmTmp("col1")
		response.write "'>"&rsmTmp("col2")
                response.write "</option>"
		rsmTmp.movenext
	loop
	else
		response.write "Nothing found"
	end if%>
</select>

I'm not sure where you're getting your data from tho, so i hope this helps

**simora** · 07-27-2010 04:25 AM

Thanks nursa:

You pointed me in the right direction and more questions.

My data is in an MsAccess database , so I'm thinking that after I get the selection, I'll pass the responses to a Pop Up window where a form can be filled out.
I'm trying to modify this sample here
http://www.webconcerns.co.uk/asp/combo/combo.asp
without much luck.

Can you suggest a better alternative.

Thanks

**nursa** · 07-27-2010 05:04 AM

first of all, are you retireving the data from the db ok? test this by looping through all the fields in your recordset. e.g. 'for each x in myrecordset.fields response.write x.name next'

secondly i dont understand your concept of a popup? do you mean a new window with html input elements based on the users selection choice? if so - this involves a lot of coding - something which i would do in an sql SP first then dynamically print the page.

thirdly, where are you falling down? you're not giving much away

**simora** · 07-27-2010 06:29 AM

RE: first of all, are you retireving the data from the db ok? NO !

I'm trying to get the data into a Listbox so that it is formatted like 2 columns. If you look at the example I was referencing, it has both a Course Name & Course ID. I want to display both in my Listbox, ( Not ComboBox)
I will then take the variable of the selected item and pass it to a new PopUp window which has a form. I cant get the data into the listbox in 2 columns.

**nursa** · 07-27-2010 07:11 AM

ok, i think you're reffering to this, which is more or less what i posted earlier:

Code:

Do while not oRs.EOF
   if Request.Form("courses") = oRs("CourseName") then
      Response.Write "<OPTION VALUE = '" & oRS ("CourseName") & "' SELECTED>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   else
      Response.Write "<OPTION VALUE = '" & oRs ("CourseName") & "'>"
      Response.Write oRs("CourseName") & "</Option>"
      oRs.MoveNext
   end if
loop

I cant see "both a Course Name & Course ID" anywhere in that tutorial? infact, you can see they've only select the name in the sql string:

strSQL = "SELECT DISTINCT CourseName FROM tblCourses "

i think you need to make sure you understand html elements properly. select boxes need values and descriptions:

Response.Write "<OPTION VALUE = '" & oRs ("CourseName") & "'>" ------the value
Response.Write oRs("CourseName") & "</Option>" ------the description for the user

the box doesn't display both

http://www.w3schools.com/tags/tag_select.asp

**simora** · 07-27-2010 01:11 PM

Nursa:

What I was referring to is the Access courses database. The tblCourses table does have the 2 columns COurseID as well as CourseName.

CourseID CourseName
C0378 History
C0450 Maths
C0573 English
C0587 French
C0749 Physics
C0987 Geography

What I was trying to do is show these 2 columns as above in the ListBox. ( A Multicolumn Listbox ) Then put the CourseID in one textbox and the CourseName in another textbox probably using OnClick.

After the selection is made, since I want to use that info in filling out a form, unless I can use Ajax, ( Which I dont know at this point ) I was planning on passing the values of those 2 textboxes to a form which exists in a PopUp window.

Unless there is some other way to get both values into my form. I'm open to other suggestions.

**nursa** · 07-27-2010 01:47 PM

Ok, maybe something like this?

Code:

      Response.Write "<OPTION VALUE = '"&oRs('CourseName')&"|"&oRs('CourseID')&"'>"
      Response.Write "description"</Option>"

then if it's selected use js to split the the values out again and pass them to form elements. (if you need to know how to do this, try searching for js help). there may be a better solution... because this is obviously more than just an asp question.

**simora** · 07-27-2010 02:03 PM

Great nursa:

Looks workable. Thanks.

I'm looking to create a Select that replaces this
strSQL = "SELECT DISTINCT CourseName FROM tblCourses ORDER BY CourseName"
with something that is comprable to this;
<%sSql="SELECT col1, col2 FROM table" which you used. You seem to be able to show 2 columns.

Having done that, what am I going to get from this ?
The following was selected : <%=Request.Form ("courses")%>

Would I be getting 2 variables or one here ?

**nursa** · 07-27-2010 02:45 PM

Yea like I said simora, I thought you only wanted to know how to put 2 values into 1 selection box (hence the title) request.form gives you all the values from your elements. But you're not asking asp questions anymore and I think I've answered you're asp question.

Now you need to start ferreting around for the info you need on other threads to do with the other languages you're asking about - html, javascript, sql

i dont have time to explain every detail of your page i'm afraid - just the odd question. i strongly suggest you take some time to go through this site, it will help you a lot.

http://w3schools.com/asp/asp_ref_request.asp

**simora** · 07-27-2010 03:06 PM

Hi:

Thanks for your help. Not sure this is an asp issue but the example that we were looking at is a drop down list which shows 1 value to select from. Is there a way to convert it to a listbox. Same info etc.. just a listbox rather than a ComboBox.

I was using that example page as part of a learning experience.

**nursa** · 07-27-2010 03:19 PM

i dont really know what you mean anymore, get to grips with html first. its the basis for all web pages

**simora** · 07-29-2010 02:31 AM

Thanks anyway for your help:

Figured it out;

Code:

<%		
	Set oRs=Server.CreateObject("adodb.recordset")

	strSQL = "SELECT CourseName,CourseID FROM tblCourses ORDER BY CourseID"
	oRs.Open strSQL, conn		
		
%>

<select size= 5 NANE= " MyListIs" > 
<%

	Do while not oRs.EOF

	Response.Write "<OPTION>"& oRS ("CourseID") &"" &" " &"-" &" " &""& oRS ("CourseName")&"</Option>"
	oRs.MoveNext 

	loop		
	%>	
	
</SELECT>

**nursa** · 07-29-2010 04:25 AM

no worries, but this:

Code:

&"" &" " &"-" &" " &""&

could be shortened to this

Code:

&" - "&

not sure why you have &"" ? (its adding nothing but still using memory to compile) - each ampersand causes an explosion in memory useage so try to avoid using them over & over

Thread: Populate a Multicolumn ASP Listbox from Access data

Thread Tools

Display