...

View Full Version : populating drop down menu from the database



pinkcat_02
02-25-2003, 02:14 AM
hiya

I want to populate 2 menu from the database but once a value is chosen from the first menu the values in the second menu will be arranged according to the chosen value in the first menu.

Something like different events held at different branches at different dates. So if the user chooses one branch then the date menu has to be arranged with the dates that are valid for that branch. I have branches and dates coloumns in my table in the database but I couldn't figure out how to start doing it.

Does anyone have any ideas?

Thanks

raf
02-25-2003, 11:25 AM
Seems like two questions to me:
- populating dropdows
- linkink dropdows clientsided

this is a tread that covers both (at first look). Similar questions are posted here frequently so a search will turn up some usefull threads.

http://www.codingforums.com/showthread.php?s=&threadid=10860&highlight=drop+down

Personally, i would split it up and create a framepage with the first dropdown in the top frame. put an onclick='submit();' on it and then load the second dropdown in another frame. The value that was selected in the first dropdown will be used in the sql-satement to select the recordset for the second dropdown.

I've you would like to give this a try, let us know.

Spudhead
02-25-2003, 11:39 AM
If you were doin this in .NET it'd be easy ;)

Anyway - depending on how much data I was looking at - how many branches, etc. - I'd go about this one of two ways. Load everything, or load the first thing and make the page repopulate itself.

You could load everything (ie: the lists of dates at different branches) into javascript arrays, and keep your <select> boxes in <div>'s that you can rewrite as necessary. If you need more detail on populating javascript variables from server-side data, or on rewriting HTML content dynamically, just shout.

Alternatively, you could just load the list of branches, and the first list of dates, and make the <select> onChange submit a branch id in the querystring which grabs the dates for that branch and rewrites the page with the correct dates.

Any help?

pinkcat_02
02-25-2003, 12:54 PM
oh I am completely lost guys :) Thanks for suggestions but I can't get my head of those. Populating one dropdown from a database is not a problem, what confuses me is how to make it work in a way that whatever value is in the first drop down will affect the dates on the other one.

And once the user submit this query it will then select* from the table where Branch_Name=<% %> and date=<% %>

Populating the menus is one vb scripy and using it on sql query is another script where confuses me.

About javascript arrays I couldn't really understand what you mean. My head goes blank..sorry can you tell me bit more?

Thanks

raf
02-25-2003, 01:15 PM
pinkcat_02,

well. quick recap. 2 options : load in everything into the browser and use javascript topopulate the second dropdownbox.
or
load a page withthe first dropdown (name='branche').
(no problem). Submit this form to an asp page. In this page, you'll build the sql statement to select the possible dates. Something like


sql="select id,dates from table where branche_Name='aname'"
sql=replace(sql,"aname",request.form("branche")

so the branche-name that was selected it put into the condition of the sql-string.

now, the resulting recordset goes into the second dropdown + you include a hidden field in the form with the brachename in it.

when you post that second form, you select the record(s) with the right branchename and date.


Does this make any sense to you?

christrinder
02-25-2003, 03:58 PM
OK, I'm not web expert, but I can do these db populated DD menus. This is how I do it, and I think it is fairly straight-forward to follow. I think you'll be able to work it around your db.

<select name="group">

<%
strSQLfran = "SELECT id, franchise from groups"
Set objRSfran = oConnection.Execute(strSQLfran)
Do While Not objRSfran.EOF
%>

<option value="<% = objRSfran("id") %>"><% = objRSfran("franchise") %></option>

<%
objRSfran.MoveNext
Loop
objRSfran.Close
Set objRSfran = Nothing
%>

</select>

raf
02-25-2003, 04:11 PM
Populating one dropdown from a database is not a problem, what confuses me is how to make it work in a way that whatever value is in the first drop down will affect the dates on the other one. ;)

Moeis
02-25-2003, 06:12 PM
Yup client side is the way, I reckon.

-use asp to populate the first select menu (OK)
-use asp to populate a set of javascript arrays (eg.)
-use the onChange event on the first select menu to trigger a client-side function which populates the second select menu accordingly with the data from one of the arrays.
-don't forget to set the length of the select menu first!

That's how I always do it, anyway.


[m03;5]

pinkcat_02
02-25-2003, 09:50 PM
I couldn't do it, does anyone has an example of this javascript arrays with the required ASP which can be an example that I can have a look at?

Thanks

pinkcat_02
02-26-2003, 12:53 AM
raf

I have done it the way u have told with populating the first drop down menu and then poulating the second with the information it got from the first one...Cheers...:thumbsup:

Now I wonder if there is any way of submitting the first menu and keeping it on the same page with the second one, something like it will submit itself without an action so that two menus will stay in the same page?

Thanks

Roelf
02-26-2003, 07:20 AM
Originally posted by pinkcat_02
Now I wonder if there is any way of submitting the first menu and keeping it on the same page with the second one, something like it will submit itself without an action so that two menus will stay in the same page?

remote scripting, it is much easier in .net as earlier mentioned, but can also work in classic asp. Here is a tutorial:
http://www.devguru.com/features/tutorials/RemoteScripting/RemoteScripting1.html

raf
02-26-2003, 08:35 AM
pinkcat_02,

look at my first post.
just use a frampage. In the top frame (or left frame) you display the page with the first dropdown. In the form tag of this page, you include a target that points to the second frame (below or right). If you submit the first page, the second dropdown will appear in the other page. You can then set the target in this second page to "parent" or even to a third frame
If you wan't you can submit the form using a bit of (on-click) javascript (but i woudn't do that. not everyone is that heppy about javascript)

When you initially load the frameset. The second (third ... frame's can be an empty page, or a page saying 'The results will be shown her, thanks to Codingforums.com'


Now. You can of coarse doall this clientsided (using javascript or a DSO with XML) but this means youre only coding for js-complient browsers (or with the DSO even for IE5+) + that you need to load in a lott of data (depending on how many options you've got in the dropdowns).

pinkcat_02
02-26-2003, 10:16 PM
i have tried using iframes as my template is table based. i tried to put two of the asp in the cell of the table but I ended up getting ! populated menu and one empty one:

Once I select a value from the first menu it then repopulates the same menu with the second menus options leaving the second menu still empty where I can say there is no function of the second menu.

I think I am doing something wrong but I couldn't figure it out as it is the first time that I am using iframes. I have used it like the code below:

<td height="21"><IFRAME
src="dropdown.asp" target="_top" marginwidth="0" marginheight="0" hspace="0" vspace="0" frameborder="0" scrolling="no" bordercolor="#000000"></iframe>

&nbsp;
<IFRAME
src="events.asp" target="_top" marginwidth="0" marginheight="0" hspace="0" vspace="0" frameborder="0" scrolling="no" bordercolor="#000000"></iframe>

</td>

dropdown.asp is the one that populates the first menu
events.asp is the one that gets the values from the first menu and populates it according to it.

Thanks

pinkcat_02
02-26-2003, 11:07 PM
as another option I put two of the menus in the same ASP which is called dropdown.asp and called it in iframe in my page .

With this codebelow it populates the first table but it doesn't show the second one it gives HTTP/1.1 404 Object Not Found error.

<form>
<select name="Branch" onChange="if (this.value.length > 0) {window.location.href=this.value;}">


<%

Dim DataConn
Dim CmdPopulateEvents
Dim SQL
Dim CURRENT_STATE_NAME
%>

<%
CURRENT_Branch_Code = ""
%>



<%
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulateEvents = Server.CreateObject("ADODB.Recordset")
%>


<%

DataConn.Open "DBQ=" & Server.Mappath("\ty\x.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

SQL = "SELECT DISTINCT Branch_Code FROM Event"
CmdPopulateEvents.Open SQL, DataConn
%>



<%While Not CmdPopulateEvents.EOF%>

<option <% If CURRENT_Branch_Code = CmdPopulateEvents("Branch_Code") Then %>
<% Response.Write(" selected ") %><% End If %>value="<%= CmdPopulateEvents("Branch_Code") %>"><%= CmdPopulateEvents("Branch_Code") %></option>

<%
CmdPopulateEvents.MoveNext
Wend
%>

<%

CmdPopulateStates.Close
Set CmdPopulateEvents = Nothing
DataConn.Close
Set DataConn = Nothing
%>


</Select>

<%
Set DataConn = Server.CreateObject("ADODB.Connection")
Set CmdPopulateEvents = Server.CreateObject("ADODB.Recordset")
%>


<%
DataConn.Open "DBQ=" & Server.Mappath("\ty\x.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"

sql="select Event_Name from Event where Branch_Code='aname'"
sql=replace(sql,"aname",request.form("Branch"))

CmdPopulateEvents.Open SQL, DataConn
%>

<Select Name="Event_Name" size="1">
<%While Not CmdPopulateEvents.EOF%>

<option value="<%= CmdPopulateEvents("Event_Name") %>"><%= CmdPopulateEvents("Event_Name") %></option>

<%
CmdPopulateEvents.MoveNext
Wend
%>

<%

CmdPopulateEvents.Close
Set CmdPopulateEvents = Nothing
DataConn.Close
Set DataConn = Nothing
%>
</Select>

</form>

whammy
02-27-2003, 01:32 AM
IMHO I just think that multiple select combos (not a multiple select, but combinations of selects, i.e. "combo boxes") are too much work client-side... probably not too bad if I had time to really mess with it, but I don't!

I would just post the page to itself when you selected something onchange, and then add the other combo box... yeah, it refreshes the page. But it also saves you hour(s) of headaches. So what? ;)

spudhead is right about .NET, it does this easily... but using remote scripting to accomplish this in classic ASP is just another headache when all you have to do is just post to the page again, and it's simple that way...

pinkcat_02
02-27-2003, 01:38 AM
Well I just wanna know where I had done a mistake because to me it seems like it should be working, it's always diffucult to see the error on your own code so I thought someone else may notice where I have done wrong.

I tried using iframes as well but they didn't work in terms of having this empty drop down menu which has no action.

:confused:

pinkcat_02
02-27-2003, 03:27 PM
raf,

Any idea where I have done wrong as I was trying your idea?

raf
02-27-2003, 03:47 PM
pinkcat_02,

i'll send you some pages where i do something like that. (this evening or tomorrow) to show you how i do it.

the code you posted can not work (if its on one page) since the form with the first dropdown in needs to be posted to the page that will create the second dropdown.

Once there was a value selected in the first dropdown, both dropdowns will remain on screen and the values in the second one will be altered as soon as you pick another value in the first one.

you can see an example here
http://www.delijn.be/frame_reisinformatie.html
click on resiwegplanner and then just select some value (+click the arrow).
if you add an on-click submit javascriptfunction to speed it up a little bit ...

i think this is the best option if you have a lot of options in your checkbox. otherwise it will take to long to load in the data.

pinkcat_02
02-27-2003, 04:00 PM
raf,

yeah that was something I was trying to do.

I managed to do it in2 different pages but when I tried to connect those with iframes in a table cell, it's where I started to have problems.

I am waiting for your reply.

Thanks

raf
02-27-2003, 07:19 PM
after reading your last post, i don't know if this will be a great help, but here it is.

the info.txt gives some more info about the pageflow.
if you can do it in two pages, what's the problem then ? The user wount see the difference between a frampage (if you don't use a border between frames) and a regular page ?
Why do you wan't is in a table-cell ?

What's an iframe, by the way?

Anyway let me know if you still have problems.

(Oh yeah. Browsing these page wount work for obvious reasons.)

pinkcat_02
02-27-2003, 07:35 PM
Well I have already designed my template before doing this thing and it is table based so it will take extra time now if i do the same template with frames so I thought there might be a way of using something like frames in table and I learned that is called iframe.

I behaves like a frame and u can use within table so I thought this would solve the problem but it didn't...haven't checked yours yet, will do later .

Thanks

pinkcat_02
03-01-2003, 10:56 PM
I have checked the code u have used Target which takes you to other page where u using the second half as a frame too. But I am using iframe which I think causes the problem.

It seems like it should do the same job though cos the only difference is using <iframe> tags instead of <frame>. If i had been using the frames it would have been solved but because of using iframes it seems like I'll carry on having the problems.

There must be a way of doing that I have exactly the same code my main page is consists of 2 iframes :

one is the drop down menu which gets the values from db and gets populated

second one is the one which replaces the sql and shows the results of select statement

now when i put a target on my first asp it opens a new page where i want it to open my second asp + nothing empty is loaded when i first do my drop down asp instead the second page's html is showing without the expected values.

I am not sure if iframes does the exact job of frames in tables as i am not that experienced with iframes but this situation is really annoying :confused:

raf
03-02-2003, 09:39 PM
euh. i couldn't quite follow that. sory.

well. i'll write the page for you. it 'll come inhandy sooner or later.
this is what i'll do:
- write a sort a multi-purpose page
- on first load, only the first dropdown wille be generated.
- a flag will be set (say: load = "yes")
- the page will post to itself (but will be processed and parsed serversided)
- the first and second dropdown will be generated
- on submit, a third page will be called that does some kind a action

so check this thread.think i'll post it tomorrow or so

pinkcat_02
03-03-2003, 01:14 PM
thanx raf, i'll be waiting...

raf
03-03-2003, 10:20 PM
pinkcat_02,

see attach for the page.
i've put it in a table (didn't care to much about the layout though)

the first time you request the page, only the category dropdown is build and loaded.
The page is posted to itself when you hit the button.
The second time, the productsdropdown is also loaded(with the products from that catagory) + in the catagorydropdownbox, the catagory is selected.

when you hit the button again, you're redirected to another page (full the adress in) with the values from the dropdowns in the querystring.

(of course, you'll need to adjust the code and change the sql statements and stuff)

You can change the category without be´ng redirected. (you're redirected after you select a product)

Now, it's all serversided. No Javascript (which could be easiers ometimes, but not if you have large option-lists)
Because i didn't want to use Javascript, i didn' include an onclick-submit function for the dropdown (so you need to hit the button)

Hope this helps you along.

raf
03-03-2003, 10:25 PM
pinkcat_02,

see attach for the page.
i've put it in a table (didn't care to much about the layout though)

the first time you request the page, only the category dropdown is build and loaded.
The page is posted to itself when you hit the button.
The second time, the productsdropdown is also loaded(with the products from that catagory) + in the catagorydropdownbox, the catagory is selected.

when you hit the button again, you're redirected to another page (full the adress in) with the values from the dropdowns in the querystring.

(of course, you'll need to adjust the code and change the sql statements and stuff)

You can change the category without be´ng redirected. (you're redirected after you select a product)

Now, it's all serversided. No Javascript (which could be easiers ometimes, but not if you have large option-lists)
Because i didn't want to use Javascript, i didn' include an onclick-submit function for the dropdown (so you need to hit the button)

Hope this helps you along.

pinkcat_02
03-05-2003, 05:06 PM
raf,

Thanks a lot this example was the code I have written myself and posted in this thread to ask what is wrong with it where now I can understand that i had to out if statement that checks if the user chooses a value from the first drop down menu...now i can understand it more clearly as it is the way how i was thinking of...

Though i have a small problem that the drop downs refresh itself when the user clicks on the button, it gives the data that the user wants but let's say it doesn't stay as the value the user selected but instead it goes at the beginning of the values.

How can I make the drop downs to refresh themselves when a value is selected?

Thanks

pinkcat_02
03-05-2003, 06:45 PM
one more problem :(

when i am working with drop downs i don't want to redirect the user to another page but instead i want to display the selected data in the same page but because of using
<form method="POST">
the page goes back to its first situation.

if i don't use <form method="POST"> the user can submit the value from the second drop down menu which was populated considering the first drop down.

this should work if each drop down posts the page to different pages but then i couldn't make it work in terms of uploading an empty iframes when the page is called for the first time with the first drop down.

:confused: :rolleyes:

Vladdy
03-05-2003, 07:01 PM
It's not that hard to implement the "remote scripting" yourself:

client:


<script>
serverPoll = document.createElement('script');
serverPoll.type = 'text/javascript';
serverPoll.src = 'processPoll.asp?FirstMenu=Selection';
document.getElementsByTagName('head')[0].appendChild = serverPoll;


server:
- Process the querystring
- If needed return javascript for page changes

No frames needed - JS takes care "behind the scenes"

pinkcat_02
03-05-2003, 07:11 PM
thanks for the suggestion but I didn't really understand what this script does. Can you explain it pls?


Thanks

Vladdy
03-06-2003, 02:00 AM
My understanding that you need to interact with the server side script without reloading the page. The script accomplishes just that. By creating the script element dynamically, the request is sent to the server-side script and you pass information in a querry string.
This server-side script processes this information and can write back javascript that updates the page as needed.

raf
03-06-2003, 08:29 AM
pinkcat_02,

i'm not sure i understand your questions/problems.
can you clarify what you mean with

it gives the data that the user wants but let's say it doesn't stay as the value the user selected but instead it goes at the beginning of the values.

and with

when i am working with drop downs i don't want to redirect the user to another page but instead i want to display the selected data in the same page but because of using
(and some more that can't be included in quote)


vladdy's sollution is one of many, but my aim was to do it without javascript (for large option-lists)

pinkcat_02
03-06-2003, 08:05 PM
raf,

posting a page to itself makes it appear like the first time u uploading it. In my case the first time the page is loaded it is loaded with 1 drop down only then for the second drop down i post the page to itself and what i get is the first drop down + the second drop down


now if i wanna display data from the database by what i have selected from the second drop down, i can't really again post the page to itself, cos in this case it just gioes back to page with one drop down. Hope this is more clear now?

The second thing you didn't understand is something i meant like:

First drop down -----> Select
Second drop down----->Select


Information..............


I want to see the drop downs at the same page with the information i have got as a result of those two drop downs rather than displaying the information at another page with redirecting....Is it clear now?

Hope it is :(

Thanks

whammy
03-07-2003, 12:57 AM
You simply need to use subroutines or something, when you're posting to the page... this is pseudo code:

<%
' MAIN PROGRAM

Call WriteHTMLHeader()

If TheySelectedMyFirstDropdown AND _
TheySelectedMySecondDropdown Then
Call DisplayThirdDropdown()
ElseIf TheySelectedMyFirstDropdown Then
Call DisplaySecondDropdown()
Else
Call DisplayFirstDropdown()
End If

Call WriteHTMLFooter()

' END MAIN PROGRAM
%>

<% Sub WriteHTMLHeader() %>
<html>
<!-- stuff here -->
<% End Sub %>


<% Sub WriteHTMLFooter() %>
<!-- stuff here -->
</html>
<% End Sub %>

Then of course you could have subroutines to display your different dropdowns or whatnot. I post pages to themselves almost exclusively after using ASP for nearly 2 years now, but each person has their own methods...

If you REALLY want to do this without refreshing the page, you'll have to populate all of your database information for each dropdown possibility into a multidimensional array, or use some method of remote scripting... if you are willing to work out the intricacies, then go for it!

But you might want to start out simply if you're not familiar with it, assuming you chose the first option (such as populating one array first, and perhaps practicing populating 2-d or 3-d arrays from a database), and work your way up.

I would probably have to mess around with it a bit to get it to work, myself - although it shouldn't be too hard with a bit of planning and experimentation. ;)

pinkcat_02
03-07-2003, 01:48 AM
I have sorted out the problem thanks for the suggestions guys and sorry for the headache...

I haven't tried preventing the page to refresh, I'll give it a go...

Thanks again :thumbsup:

pinkcat_02
03-09-2003, 11:19 PM
i again have a problem about displaying things which decides what to decide by getting the values from the first and the second drop down menu.

For example: When I have 2 drop down menus and a result depending on the values of the first and the second drop down menu it fails to get the data that the user has choosen as the pages refeshes itself and the value of the first drop down menu goes back to the it's own value rather than the value that user has selected.

This refreshing gave me enough headaches it works fine when the drop downs just get the values of the one above it but fails when the third sql needs the value from the first and the second drop downs.

Any suggestions?

Thanks

whammy
03-10-2003, 03:52 AM
I only have one... and that is we need to start fresh, since this post is getting too long.

Sorry pinkcat, but please post your new problems in a new thread. ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum