Drop-Down box - curiosity 3

TonyU · Jun 4, 2003

[tt]Let me start by saying "I've never seen it done" with ASP

Here's the question:
Is is possible to have a drop-down box populate from a SQL table AND make it so that the user (if choice needed is not found) can insert a new record into the drop-down.?

I'd liked to hear some ideas if such thing is even possible or other methods I could use....

^{Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver}

palbano · Jun 4, 2003

Yeah it’s been done. The HTML <select> element does not support the edit mode that a combo box can in a Windows application. People have done that in DHTML and using a combination of the <select> and <input> elements. I think you can find examples at places like codeproject, 4guysfromrolla, dynamicdrive etc.

-pete

TonyU · Jun 4, 2003

[tt]Thanks for you quick response palbano, I was not sure how to search this curiosity.

I was searching for things like "edit drop-down" etc...

Thanks again.

^{Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver}

Mr3Putt · Jun 4, 2003

Not directly into the drop-down. the HTML <SELECT> element doesn't work that way.

You'd need an additional field, perhaps displayed when "..OTHER" is selected from the drop-down?

Here's a (really, really) rough-cut example...

Code:

<html>
<head>
<script type=&quot;text/JavaScript&quot; language=&quot;JavaScript&quot;>
function showHide(what, val){
  theObj = (what == 'doodle') ? document.getElementById('hopper'):document.getElementById('doodle');
  if ((what == 'doodle')&&(val == 'x')){
    theObj.value = '';
    theObj.style.display = '';
    return true;
  }
  if (what == 'hopper') {
    if (val == '') {
      theObj.selectedIndex = 0;
      theObj.style.display = '';
      document.getElementById('hopper').style.display = 'none';
    }else{
      theObj.selectedIndex = 0;
      theObj.style.display = 'none';
    }
  }
}
</script>
<title>Untitled Document</title>
</head>
<body>
<form id=&quot;f1&quot; name=&quot;f1&quot;>
  <select id=&quot;doodle&quot; style=&quot;display:inline;&quot; onchange=&quot;showHide(this.id, this.options[this.selectedIndex].value);&quot;>
    <option value=&quot;0&quot; selected>--Select Something--</option>
    <option value=&quot;1&quot;>1</option>
    <option value=&quot;2&quot;>2</option>
    <option value=&quot;3&quot;>3</option>
    <option value=&quot;x&quot;>..other</option>
  </select>
  <input type=&quot;text&quot; id=&quot;hopper&quot; style=&quot;display:none;&quot; value=&quot;CRAP&quot; onchange=&quot;showHide(this.id, this.value)&quot;>
</form>
</body>
</html>

TonyU · Jun 4, 2003

[tt]Thanks Mr3Putt

^{Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver}

sergeiY · Jun 4, 2003

If you would like to populate SELECT box from DB it's easy

I developed this little sub a year ago and happiely used it ever after

Sub ASPSelect(rs, value, display, selected)
' __________________________________________________________________________________
' *START* Dynamically build a select menu based on named members of a recordset
' The 'selected' parameter takes a string value equal to the desired selected option
' __________________________________________________________________________________
Dim isSelected
Dim options

options = ""

do until rs.EOF

if(rs(value) = selected) Then isSelected = " selected" else isSelected = ""
options = options & "<option value='" rs(value) & "'" & isSelected & ">" & rs(display) & "</option>"

rs.MoveNext
loop
'clean up
set rs = nothing

Response.Write(options)
' __________________________________________________________________________________
' END Dynamically build a select menu based on named members of a recordset
' The 'selected' parameter takes a string value equal to the desired selected option
' __________________________________________________________________________________
End Sub

and this is how to use it:

<select name="group_id" size="1" class="ctrlText">
<% Call ASPSelect (conn.Execute("SELECT group_id, group_name FROM Groups WHERE status_id = 1 ORDER BY group_name&quot

, "group_id", "group_name", intGroupID)%>
</select>

and if you would like to select a specific item set intGroupID to that ID otherwise set it to ""

I came from Cold Fusion world and this functionality was there since version 3.0 (?) ... 1997 (?) I think ... the tag called CFSELECT

anyway I hope this helps to build your SELECT from DB the other part of your question how to be able to add items probably feasable using DHTML but you need to be aware that it will not work in older browsers

Sergei

Tarwn · Jun 4, 2003

Ok, yes it is possible to populate from the db and add allow the user to add an item. This can be done in exactly the same manner as if it were a static select box, because the action of adding an element to it is a client-side event.

Code:

<%
Option Explicit

Dim conn
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)

conn.Open &quot;My connection string&quot;


'--------------- Processing the form if it was submitted
Dim rs_color, sql_color
Dim rs_user, sql_user

If Request.Form(&quot;selColor&quot;) <> &quot;&quot; Then
	'add any new colors first
	If Request.Form(&quot;newColorList&quot;) <> &quot;&quot; Then
		Dim arrColor, i
		'split the list
		arrColor = Split(Request.Form(&quot;newColorList&quot;),&quot;,&quot;)

		'loop through the list adding each entry
		For i = 1 to UBound(arrColor)
			sql_color = &quot;INSERT INTO Color(colorname) VALUES('&quot; & arrColor(i) & &quot;')&quot;
			conn.Execute sql_color
		Next

	'update the user entry
	sql_user = &quot;UPDATE User SET FavoriteColor = &quot; & Request.Form(&quot;selColor&quot;) & &quot; WHERE user_id = &quot; & whatever
	conn.Execute sql_user

End If

'--------------- Displaying the Form

'get the colors from the database
sql_color = &quot;SELECT colorname FROM Color&quot;
Set rs_color = conn.Execute(sql_color)

'get the user information from the database
rs_user = &quot;SELECT FavoriteColor FROM User WHERE user_id = &quot; & whatever
Set rs_user = conn.Execute(sql_user)

%>
<html>
<head>
<script language=&quot;javascript&quot;>
	function addColor(){
		//get the color
		var col = frmColors.newColor.value;
		
		//add it to the drop down
		frmColors.selColor.options[selColors.options.length] = New Option(col,col);

		//add it to our list to add to the database
		frmColors.newColorList.value = frmColors.newColorList + &quot;,&quot; + col

		//clean up form by hiding the boxes
		frmColors.newColor.style.display = &quot;none&quot;;
		frmColors.btnAdd.style.display = &quot;none&quot;;

		//select the newly added color
		frmColors.newColor.selectedIndex = selColors.options.length - 1;
	}
	function entry(){
		//if the first item is selected
		if(frmColors.selColor.selectedIndex == 0){
			//show the box and button
			frmColors.newColor.style.display = &quot;none&quot;;
			frmColors.btnAdd.style.display=&quot;none&quot;;
		}
	}
</script>
</head>
<body>
<form method=&quot;POST&quot; action=&quot;dropdown.asp&quot; name=&quot;frmColors&quot;>
<select name=&quot;selColor&quot; onChange=&quot;entry();&quot;>
	<option value=&quot;&quot;> Add New Entry </option>
	<%
	Do Until rs_color.EOF
		'start the option
		Response.Write &quot;<option value=&quot;&quot;&quot; & rs_color(&quot;colorname&quot;) & &quot;&quot;&quot;

		'Check each color against the users favorite, select the one that matches
		If rs_color(&quot;colorname&quot;) = rs_user(&quot;FavoriteColor&quot;) Then Response.Write &quot; selected&quot;

		'finish the option
		Response.Write &quot;>&quot; & rs_color(&quot;colorname&quot;) & &quot;&quot;&quot;
		rs_color.MoveNext
	Loop
	%>
</select>
<input type=&quot;text&quot; name=&quot;newColor&quot; value=&quot;Enter Your Color&quot; style=&quot;display:none;&quot;>
<input type=&quot;button&quot; value=&quot;Add Your Color&quot; onClick=&quot;addColor();&quot; name=&quot;btnAdd&quot; style=&quot;display:none;&quot;>
<input type=&quot;hidden&quot; name=&quot;newColorList&quot;>
<input type=&quot;submit&quot; value=&quot;Submit&quot;>
</form>
</body>
</html>
<%

'Clean up ADO objects
conn.Close
Set rs_colors = Nothing
Set rs_user = Nothing
Set conn = Nothing
%>code]

Now this example has a User table and we assume thatthe whatever variable is gotten from somewhere and is the id of an existing user. I could have supplied additional code in case it was a new user, but didn't think it ws necessary.

It then allows the user to select an existing color or add new colors to the dropdown. These additions are then not only added to the dropdown but also added to the Colors table in the database once the form is submitted.

So, as I said before, yes, it is possible and not really that difficult either.

SideNote: this code was written on the fly so there may be some minor syntax errors and such...

[u][sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub][/u]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Get better results for your questions: faq333-2924
Frequently Asked ASP Questions: faq333-3048

onpnt · Jun 4, 2003

I love it when you refer to those quality of posts as "on the fly"

____________________________________________________
_{$str = "sleep is good for you. sleep gives you the energy you need to function";

$Nstr = ereg_replace("sleep","coffee",$str); echo $Nstr;}

_{http://www.onpntwebdesigns.com}

sergeiY · Jun 4, 2003

Tarwin you're right it will work no problem

TonyU · Jun 4, 2003

[tt]The feeling is mutual onpnt about Tarwn's "on the fly" remark.

Thanks Tarwn, I will give it a go.

P.S. I did not know you had changed your name to Tarwin as sergeiY said

[yoda]

^{Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver}

TonyU · Jun 5, 2003

[tt]It worked great fellas. Thanks again.

I more question tho....
If I click the letter "T" so the cursor locates the first T encountered in the dropdown, how can I make it so when I type "TO" it's focus finds all begining with TO etc...

^{Delete * from brain Where MaxLevel = "Full" and reaction = "Slow" order by StartOver}

Mr3Putt · Jun 5, 2003

The "type-ahead" feature is do-able... but it's a major pain in the butt. Lots and lots of script, with timeouts, etc... many loops through your options listing every time a keypress event fires.

Or, you could force your users to use the Mac version of IE5.x... the only browser I know of where type-ahead works on an HTML SELECT element.

TonyU · Jun 5, 2003

[tt]Ok thanks Mr3Putt, I'll leave how it is for now.

Consider this post Done

Thanks to all that responsed and those that had the intention...

^{There's never time to do it right, but there's always time to do it over!}

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Drop-Down box - curiosity 3

TonyU

Technical User

palbano

Programmer

TonyU

Technical User

Mr3Putt

Programmer

TonyU

Technical User

sergeiY

Programmer

Tarwn

Programmer

onpnt

Programmer

sergeiY

Programmer

TonyU

Technical User

TonyU

Technical User

Mr3Putt

Programmer

TonyU

Technical User

Similar threads

Part and Inventory Search

Sponsor