Access Database from pull down menu

[Andy_1877]

I am currently in the process of building a search facility on a website which will utilise dependent drop down menus...i.e there are five menus and each child menu is dependent on what the user has slected in the previous parent menu.

Is it possible to take the poduct which is in the final menu and query my MS Access database to return details of the product on the website? If so how?

Regards,
Andy

[sdonato]

to do this with asp you would need to refresh the page each time one of the dropdowns are choosen.

your best bet to do this is with javascript b/c it is client side script asp is server side.

-sdonato

[Andy_1877]

Thanks for the post bit I fear I didnt fully explain myself, sorry.

I am developing the menus in javascript, due to the constant refreshing of the page in ASP..

What I would like to know is .... Can I take the information from the final menu and use it with ASP to search my Access Database?
i.e. I worked through the five menus, narrowing the search for a product down until i had one final product. I then use this information to search on the database, returning the details stored there to the screen usng ASP.

Is this plausible? If so how could it be done?

[sdonato]

oh....

let me just make sure i got this right.
you want to use the your dropdown list to make search results from the access db?

now if that is the case yeah no prob.

you want to make a final dropdown with the db results?

also a can do.

either one will require you to refresh the page or to go to another page to display your results.

have you done connections to the db and sql queries before??????

-sdonato

[Andy_1877]

have you done connections to the db and sql queries before??????

I have basic knwoledge of this.

When I get to the last of the menus, there should only be amaximum of 2/3 products due to the previous menus having narrowd the search down, using javascript.

When I make the final selection from the last menu i want to use asp to access my database, get the details on the selected product and return it to the screen.

[sdonato]

i copied some code below i hope that some of this looks familier.

if you need me to explain anything let me know.

-sdonato


<%
set Conn = server.createobject("adodb.connection")
Conn.open "DSN=DBSDSN;UID=USERID;PWD=PASSWORD"

sql = "select * from tableinaccess where option1 = '"&request("dropdown1")&"' and option2 = '"&request("dropdown2")&"' and option3 = '"&request("dropdown3")&"' order by somefield"

'response.write sql
set RS15 = Conn.Execute(sql)
l=RS15.RecordCount

while not RS15.EOF
%>

<%= rs15("option1") %>

<%
RS15.movenext
wend
RS15.close
set RS15 = nothing

Conn.close
set Conn = nothing
%>

[Andy_1877]

<%
sql = "select * from tableinaccess where option1 = '"&request("dropdown1")&"' and option2 = '"&request("dropdown2")&"' and option3 = '"&request("dropdown3")&"' order by somefield"

This all seems quite clear thanks.

set RS15 = Conn.Execute(sql)
l=RS15.RecordCount

while not RS15.EOF
%>

<%= rs15("option1") %>

what does this part do?

[sdonato]

it connects to the db and runs the sql query and then counts the records so that you can loop through the results you could put in there a if then statement that would show something like
<%
if RS15.EOF then
%>
no results please try again
<%
else
while not RS15.EOF
%>
<%= rs15("option1") %>
<%
RS15.movenext
wend
RS15.close
set RS15 = nothing
end if
Conn.close
set Conn = nothing
%>

the <%= rs15("option1") %> is where you would subsitute the information you would like to display

there are a bunch of different ways to do this, but that is the one i had in one of my files.

hope this helps,
-sdonato

[Andy_1877]

Just so I have got his right (sorry to be a pest)

set RS15 = Conn.Execute(sql)
l=RS15.RecordCount

while not RS15.EOF
%>

<%= rs15("option1") %>

This part part connects to the db and runs the sql query. The sql query takes the selections from each of the menus and queries the database for them.

The

<%= rs15("option1") %>

then displays the information.

Thanks for your help.

[sdonato]

<%= rs15("option1") %> will not display all the results just the option1 field from each record. you can add as many fields to display in the records area like:

<%= rs15("option1") %> <br>
<%= rs15("option2") %> <br>
<%= rs15("option3") %>

you can do a link also if you need to like:

<a href="page.asp?page=<%= rs15("link") %>">

you will need to make a table or something to design the layout of the records being displayed

all the fields i used are just ones i made up. if you would like to email me the page i could show you with the field names in your db already.... might be helpful

just let me know if you need more help

-sdonato