...

View Full Version : populate dropdown box from DB using ASP



jerusking
01-04-2007, 03:01 PM
Hi guys, I'am a newbee in ASP i have a problem selecting a value from the dropdown box which i papulated from database.
I have a select form name style then this is the select statement Select distinct proptype from developerproperties order by proptype and the result is
Garden Home
Golden Mile
Shoreline
Signature Villa
Town Home

Now if i just select the Shoreline from the dropdown box there is another dropdown which only relevant to the shoreline for example
if shoreline selected the result of the next dropdown should be only TypeA,TypeB,TypeC,TypeD,TypeE,TypeF

how can i do this on asp or is there any code like jsp on these?please help.

Many Thanks

Jerus

tcadieux
01-05-2007, 04:33 PM
http://javascript.internet.com/navigation/connected-dropdown-box.html

dev
01-06-2007, 10:53 AM
u can write a javascript for the event onchange in the first combo.pass a action variable like "action=loadcombo2" also pass the value of the first combo.

within the page get the querystring of action variable and check for the condition
if action = "loadcombo2" then
perform the loading of combo2.
end if

jerusking
01-06-2007, 11:43 AM
Thanks Dev and tcadieux, but my first dropdown box is database driven, if i will select Shoreline it will only show the relevant data for the second dropdown.

According to tcadieux the script is good but the data is hardcoded, how can i create these javascript if the data are from the database, izt possible?

Many THanks

Jerus

dev
01-06-2007, 12:00 PM
its somethng like this.please check the below code

<html>
<head>
<script language="javascript">
function fngetvalue()
{
var strvalue;
strvalue=<get the value of the combo>; window.document.<formname>.action="<pagename>.asp?value="+strvalue+"&action=loadcombo2"
}
</script>
</head>
<body>
combo1
======
<select id="cb1" onchange="fngetvalue()">
<option value="1">Garden Home</option>
<option value="2">Golden Mile</option>
<option value="3">Shoreline</option>
<option value="4">Signature Villa</option>
<select>

<%
'loading second combo
dim action,Value
action=request.querystring("action")
if action ="loadcombo2" then

Value=request.querystring("value")//value of the first combo

'write the select query
strselqry = "select * from tblname where Proptype="& Value &""
'Execute the query
End if
%>



</body>
</html>

jerusking
01-07-2007, 01:46 PM
Thanks Dev, these is gonna be a big help, i will try to integrate the codes you've given. Thanks alot. Jerus

jerusking
01-09-2007, 12:10 PM
Hi Dev,

Im still not getting the result. I will show you the entire part of my code, please bear with me. I dont get the second dropdown condition and also the javascript.

this is the code.

<%@ Language = VBscript %>
<html>
<head>
<title>Database Search</title>
<script language="javascript">
function fngetvalue()
{
var strvalue;
strvalue=<get the value of the combo>;
window.document.propsearch.action="dropdowndb.asp?value="+strvalue+"&action=loadcombo2"
}
</script>
</head><body>

<%
Dim objconn,objRS,strSQL,style
style=Request.QueryString("style")

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=sqloledb;"//i just rid the whole connectionstring
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "Select distinct proptype from developerproperties order by proptype"

objRS.Open strSQL, objconn

Response.Write "<form method=post name='propsearch' action='jrs_results.asp'><select name=style onchange='fngetvalue()'>"
Response.Write "<option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "<br><br>"

''' Second drop down list starts here ''''
dim action,Value
action=request.querystring("action")
if action ="loadcombo2" then
Value=request.querystring("value")'value of the first combo
strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & style &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn

Response.Write "<select name=ptype><option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "</form>"
objconn.Close
%>
</body>
</html>

dev
01-09-2007, 01:50 PM
did u try putting alert messages inside the javascript (just for checking purpose).so that u can get to know the value of first combo is getting passed.

one more thing is the second combo in the same page or the different page.

Spudhead
01-09-2007, 01:57 PM
strvalue=<get the value of the combo>;

I think that dev intended you to replace this with some javascript that gets the value of the combo? This leads us to a problem:


<select name=style onchange='fngetvalue()'>

"style" is a HTML attribute. I'd be very careful about calling HTML elements "style". Especially if it doesn't have quotes around it. Lets's call it "selStyle" instead, and give it an ID.


<select name='selStyle' id='selStyle' onchange='fngetvalue()'>

Then you can update your line of javascript so that it works:


strvalue = document.getElementById('selStyle').options[document.getElementById('selStyle').selectedIndex].value;

So: when your first combo box is changed (ie: something is selected), a javascript function is fired. This gets the value of the selected option, and submits the form to dropdowndb.asp (ie: itself), sending the value of whatever was chosen in a variable called "value", and the string "loadcombo2" in a variable called "action". With it up to now?

Now... the ASP is set up so that it looks to see if those variables are present:


action=request.querystring("action")
if action ="loadcombo2" then
value=request.querystring("value")

And if they are, it fires a second request to the database, to populate the second dropdown. And here we find another problem:


where proptype='" & style &"'

You populate this variable, "style", right near the top of the page:


style=Request.QueryString("style")

But style isn't passed in the QueryString. The only variables that might be in the QueryString are "action" and "value". If you've changed the name of your combo box like I said, you'll be able to get it by going:


style = Request.Form("selStyle")

but if you've been paying attention you'll know that this value will be exactly the same as the value you got in the now-confusingly-named QueryString variable "Value". So you may as well stop mucking about with the form and use the value that you've already got:


where proptype='" & value &"'

Hope that helps.... :)

dev
01-09-2007, 02:10 PM
Hi spudhead ,
i have not asked the user to replace ths wth javascript .
in the first sample what i have provided i have clearly given the ID to the 1st combo.
but i have not shown the way to retrieve the combo's value.
instead i gave strvalue=<get the value of the combo>;
regards
Dev

jerusking
01-09-2007, 06:43 PM
Hi dev and Spudhead,

These form is only in one page, its a search page.so when i select the first dropdown it should be remain and the second dropdown will be the relevant to each value selected.

These is my first dropdown:

strSQL = "Select distinct proptype from developerproperties order by proptype"

objRS.Open strSQL, objconn

Response.Write "<form method=post name='propsearch' action='jrs_results.asp'><select name=propstyle onchange='fngetvalue()'>"
Response.Write "<option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"

Results:Golden Mile, Garden Home, Shoreline, Town Home


And the second dropdown:

Dim style
style = request.form(propstyle)

if style = "Shoreline" then //lets say i selected these value from the first dropdown
strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & style &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn

Response.Write "<select name=ptype><option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"

dev: from the javascript i dont get the loadcombo2 value, is that the id or name of the second dropdown?

Spudhead: Thanks for the tips and clarifications.It is truely greatfull.

Guys please help me on these.I cant get the problem.

Regards,

Jerus

dev
01-10-2007, 01:49 PM
u have to use the id of combo1 inorder to get the value.

jerusking
01-11-2007, 03:14 PM
Iam sorry Dev, im failed to implement the codes.:(

Spudhead
01-11-2007, 05:24 PM
You failed to implement the codes?

:mad: :mad: :mad:

This will not do, jerusking. You have failed in your task. You've let yourself down, you've let the forum down, you've let the whole internet down.

;)

Dude, post your whole page. You're so close it's almost painful.

jerusking
01-12-2007, 12:40 PM
Hi Spudhead, these is the whole part of my code, bear with me trying to work these out but im failed.:confused:

<%@ Language = VBscript %>
<html>
<head>
<title>Database Search</title>
<script language="javascript">
function fngetvalue()
{
var strvalue;
strvalue = <get the value of the combo>; // i have no idea about these line
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2" // and also loadcombo2 value
}
</script>
</head><body>

<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=sqloledb;"
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "Select distinct proptype from developerproperties order by proptype"
objRS.Open strSQL, objconn
Response.Write "<form method=post nam='propsearch' action='jrs_results.asp'><select name='propstyle' id='propstyle' onchange=""fngetvalue()"">"
Response.Write "<option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "<br><br>"

''' Second drop down list starts here ''''
Dim action, Value
action=request.querystring("action")

if action ="loadcombo2" then
Value=request.querystring("value")
strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & Value &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn

Response.Write "<select name='ptype' id='ptype'><option value='' SELECTED>No preferences</option>"
Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close
Response.Write "</select>"
Response.Write "</form>"
objconn.Close
%>
</body>
</html>

dev
01-13-2007, 08:59 AM
1.) strvalue = window.document.propsearch.propstyle.item(document.propsearch.propstyle.selectedIndex).value
//ths is how u get the value of combo1. Here propsearch is the id of the <form > and propstyle is the id of combo(u hv not given id for the form tag gv it)

2.) loadcombo2 is a action that u pass to the same page where u can load the second combo.

i.e
u r selecting some value from first combo .
At that moment the page is again getting loaded .
U have to load the second combo which is present in the same page.
So u r using the condition if Action=loadcombo2 then
//loading the combo2 values
End if

Hope atleast now u shd have gt.

jerusking
01-14-2007, 01:00 PM
Hi Dev, Still not working the results...i will show u your codes how i implemented it. Please bear with me...

<%@ Language = VBscript %>
<html>
<head>
<title>Database Search</title>

<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = strvalue = window.document.propsearch.propstyle.item[document.propsearch.propstyle.selectedIndex].value;
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>
</head>

<body>
<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=sqloledb;"
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "Select distinct proptype from developerproperties order by proptype"
objRS.Open strSQL, objconn

Response.Write "<form method=post name='propsearch' id='propsearch' action='jrs_results.asp'>"
Response.Write "<select name='propstyle' id='propstyle' onchange='fngetvalue()'>"
Response.Write "<option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop

objRs.Close
Response.Write "</select>"

Response.Write "<br><br>"

''' Second drop down list starts here ''''

Dim action, propval
action=request.querystring("action")

if action ="loadcombo2" then
propval=request.querystring("value")

strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & propval &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn
Response.Write "<select name='ptype' id='ptype'><option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close

Response.Write "</select>"
Response.Write "</form>"
objconn.Close
%>
</body>
</html>

dev
01-15-2007, 06:37 AM
mistakes done
1)in the javascript
<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = strvalue = window.document.propsearch.propstyle.item//this[document.propsearch.propstyle.selectedIndex].value;// lines have problem


window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>

actually the code is
-----------------
the braces u hv used is wrong .
strvalue = window.document.propsearch.propstyle.item(document.propsearch.
propstyle.selectedIndex).value;

2.) u have given some action in the Form tag (i.e jrs_results.asp)is it the same page or diff.
what abt (window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2")
in above stmnt u hv given dropdownbox01.asp-what is this page ?

jerusking
01-15-2007, 11:59 AM
Thanks Dev, i amend the javascript, so it should be like these?
<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = window.document.propsearch.propstyle.item(document.propsearch.propstyle.selectedIndex).value;
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>


The action from form tag jrs_results.asp is a different page, it is the result page of dropdownbox01.asp, where dropdownbox01.asp is the search page and the jrs_results.asp is the result page.

Many thanks,
Jerusking

dev
01-15-2007, 01:54 PM
yes now ur script is correct.

now my question is Hope u bear with my question!

in which form both the combo's are present.
(gv me the page name)

why u hv to give form action as jrs_results.asp .because everytime the form action page only will be opened.

jerusking
01-15-2007, 02:15 PM
the two combo box is in the dropdownbox01.asp page, and if i submit search then it will be going to jrs_results.asp for the result page.

Many thanks

jerusking

dev
01-15-2007, 02:25 PM
now it shd work for you .

jerusking
01-15-2007, 02:59 PM
Hi dev, so i have a dropdownbox01.asp page with the two combo. and the form action is jrs_results.asp. If that so i will amend

<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = window.document.propsearch.propstyle.item(document.propsearch.propstyle.selectedIndex).value;
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>

instead of dropdownbox01.asp change it to jrs_results.asp

Many thanks

jerusking

dev
01-16-2007, 01:02 PM
1.no u hv said to combo's are in same page (dropdownbox01.asp)that is.
2.on select of a value from first combo u have to populate second combo values.
3. no need of changing to jrs_results.asp instead of dropdownbox01.asp because it will (jrs_results.asp)called only when u submit the page.

jerusking
01-16-2007, 02:53 PM
Im sorry dev, nothings happen...shamed on me...please take a look my codes

<%@ Language = VBscript %>
<html>
<head>
<title>Database Search</title>

<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = window.document.propsearch.propstyle.item(document.propsearch.propstyle.selectedIndex).value;
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>

</head>

<body>
<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=sqloledb;"
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "Select distinct proptype from developerproperties order by proptype"
objRS.Open strSQL, objconn

Response.Write "<form method=post name='propsearch' id='propsearch' action='jrs_results.asp'>"
Response.Write "<select name='propstyle' id='propstyle' onchange='fngetvalue()'>"
Response.Write "<option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop

objRs.Close
Response.Write "</select>"

Response.Write "<br><br>"

''' Second drop down list starts here ''''

Dim action, propval
action=request.querystring("action")

if action ="loadcombo2" then
propval=request.querystring("value")

strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & propval &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn
Response.Write "<select name='ptype' id='ptype'><option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close

Response.Write "</select>"
Response.Write "</form>"
objconn.Close
%>
</body>
</html>

here is the link http://www.edwardsandtowers.com/dropdownbox01.asp

jerusking
01-17-2007, 02:08 PM
Please give me one more patience about these problem dev. :-(

Spudhead
01-17-2007, 04:35 PM
Enough already :D



<%@ Language = VBscript %>
<html>
<head>
<title>Database Search</title>

<script type="text/javascript">
function populateTheSecondDropdown(valueToUse)
{
window.location.href="dropdownbox01.asp?value=" + valueToUse + "&action=loadcombo2";
}
</script>

</head>

<body>
<%
Dim objconn,objRS,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "Provider=sqloledb;"
objconn.Open
Set objRs = Server.CreateObject("ADODB.Recordset")

'''''First drop down list starts here'''''

strSQL = "Select distinct proptype from developerproperties order by proptype"
objRS.Open strSQL, objconn

Response.Write "<form method=post name='propsearch' id='propsearch' action='jrs_results.asp'>"
Response.Write "<select name='propstyle' id='propstyle' onchange='populateTheSecondDropdown(this.options[this.selectedIndex].value)'>"
Response.Write "<option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("proptype") & "'>" & objRS("proptype") & "</option>"
objRS.MoveNext
Loop

objRs.Close
Response.Write "</select>"

Response.Write "<br><br>"

''' Second drop down list starts here ''''

Dim action, propval
action=request.querystring("action")

if action ="loadcombo2" then
propval=request.querystring("value")

strSQL = "Select distinct propertyplantype from developerproperties where proptype='" & propval &"' order by propertyplantype"
else
strSQL = "Select distinct propertyplantype from developerproperties order by propertyplantype"
end if

objRS.Open strSQL, objconn
Response.Write "<select name='ptype' id='ptype'><option value='' SELECTED>No preferences</option>"

Do While Not objRS.EOF
Response.Write "<option value='" & objRS("propertyplantype") & "'>" & objRS("propertyplantype") & "</option>"
objRS.MoveNext
Loop
objRs.Close

Response.Write "</select>"
Response.Write "</form>"
objconn.Close
%>
</body>
</html>



Right. Copy and paste all that lot, save it as dropdownbox01.asp, and check that it works.

As you can see, it's a bit different to what you've been trying. This is your old code:


<script type="text/javascript">
function fngetvalue()
{
var strvalue
strvalue = window.document.propsearch.propstyle.item(document.propsearch.propstyle.selectedIndex).value;
window.document.propsearch.action="dropdownbox01.asp?value="+strvalue+"&action=loadcombo2"
}
</script>

Couple of points:

1. I've never seen anyone get the value of the selected option in a select box like that. I have no idea whether it works or not - I doubt it, personally - but to rewrite it using the more traditional syntax is:
document.propsearch.propstyle.options[document.propsearch.propstyle.selectedIndex].value;

2. All your script was doing was setting the action of the form. It wasn't actually telling the form to submit. You can do that with the line window.document.propsearch.submit();. Which leads me to....

3. Why do you need to go submitting forms all over the place? You're already using javascript to get the value you need, and you're passing it via the URL. So all you need to do is build a new URL with the value you've just got, and tell the window to go there. So:

<script type="text/javascript">
function fngetvalue()
{
var valueToUse = document.propsearch.propstyle.options[document.propsearch.propstyle.selectedIndex].value;
window.location.href="dropdownbox01.asp?value="+valueToUse+"&action=loadcombo2";
}
</script>

And that's all you need.

4. You COULD leave it at that, but why write two lines of javascript when you can write one? Why not get the select box to send its selected value to the javascript, instead of making the javascript go and look it up? You're already calling the javascript function from the select box, in the onchange bit. That means that you can replace all that document.propsearch.propstyle gubbins with the simple "this" keyword, and you get this.options[this.selectedIndex].value - which then gets automagically passed into the valueToUse variable in the javascript.

Easy, huh?

:thumbsup:

jerusking
01-18-2007, 12:36 PM
This one is working now...it is true geratfull Spudhead thanks to you and to you dev...you guys gave me more ideas about programming. I will still keep you in touch if i got some problems... :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum