asked on

Drop Down Box from table and display results

Table : UnitHours
Search Field : PartNumber
Fields : PartNumber, Description, Price

I need an asp page that will create a drop down box with all part numbers found in the above table. Once user select a part number from drop down box and click on submit button, all data belongs to selected part numbers from the above table should displayed in table format (same page recommended). Please help. I am new to asp therefore leave some comments.

Wakie

<%
'Query the database
SQL = "SELECT * FROM UnitHours"

'Then execute it similar to the following syntax:
Set RS = DB.Execute(SQL)

'Print description and drop down box
Response.Write "Part number : "
Response.Write "<br>"
Response.Write "<select name=""dropdown"">"

'Loop through records
Do Until RS.EOF
Response.Write "<option value=""" & RS("PartNumber") & """>"
RS.MoveNext
Loop
%>

If that doesn't work, please post your source code here with any errors you are getting.

Thanks,
Wakie.

gladxml

Nantha,

Just cahnge the the db path... and test the code...

<%response.buffer=true%>
<%

Dim oConn, dbPath
dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & dbPath & ";"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM UnitHours"
rs.Open sql, oConn

%>
<html>
<head>
<title>Virtual Hosting | Members Area</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
</head>
<script language="Javascript">
function doSel(obj)
{
for (i = 0; i < obj.length; i++)
if (obj[i].selected == true)
eval(obj[i].value);
}

</script>
<body>
<select name="Ref_ID" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs.eof)%>
<option value="location.href='dropdown.asp?id=<%=rs("PartNumber")%>'" <%if request.querystring("id")=rs("PartNumber") then response.write "selected" end if%>><%=rs("PartNumber")%></option>
<%
rs.movenext
loop
%>
</select>

<%if request.querystring("id") <> "" then

set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM UnitHours WHERE ID = '"& request.querystring("id") &"'"
rs1.Open sql, oConn%>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Part NUmber</td>
<td><%=rs1("PartNumber")%></td>
</tr>
<tr>
<td>Description</td>
<td><%=rs1("Description")%></td>
</tr>
<tr>
<td>Price</td>
<td><%=rs1("Price")%></td>
</tr>
</table>

<%rs1.Close()
Set rs1 = Nothing
end if%>

</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>

HTH...

HAppy programming...

gladxml

Nantha,

BTW save my first posting as dropdown.asp

Also this line

WHERE ID = '"& request.form("Ref_ID") &"'"

replace it with this

WHERE PartNumber = '"& request.form("Ref_ID") &"'"

assuming that partNumber field is data type text if not just take out the single quote on this part...

'"& request.form("Ref_ID") &"'

for the second code below again just change the databasename...

========save this as combobox.asp

<%response.buffer=true%>
<%

Dim oConn, dbPath
dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & dbPath & ";"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM UnitHours"
rs.Open sql, oConn

%>
<html>
<head>
<title>Virtual Hosting | Members Area</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
</head>
<script language="Javascript">
function doSel(obj)
{
for (i = 0; i < obj.length; i++)
if (obj[i].selected == true)
eval(obj[i].value);
}

</script>
<body>
<form name="form1" method="post" action="combobox.asp">
<select name="Ref_ID" onchange="doSel(this)">
<option value="">Please select</option>
<%Do while not(rs.eof)%>
<option value="<%=rs("PartNumber")%>'" <%if request.form("Ref_ID")=rs("PartNumber") then response.write "selected" end if%>><%=rs("PartNumber")%></option>
<%
rs.movenext
loop
%>
</select>
<input type="submit" name="Submit" value="Submit">
</form>
<%if request.querystring("id") <> "" then

set rs1 = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM UnitHours WHERE PartNumber = '"& request.form("Ref_ID") &"'"
rs1.Open sql, oConn%>

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>Part NUmber</td>
<td><%=rs1("PartNumber")%></td>
</tr>
<tr>
<td>Description</td>
<td><%=rs1("Description")%></td>
</tr>
<tr>
<td>Price</td>
<td><%=rs1("Price")%></td>
</tr>
</table>

<%rs1.Close()
Set rs1 = Nothing
end if%>

</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>

HTH...

Happy programming...

jimboVB

a little change from gladxml's comment

<%response.buffer=true%>
<%

Dim oConn, dbPath
Dim partNo
partNo = Request.form("partNumber")

dbPath = Server.MapPath("db3.mdb")
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & dbPath & ";"

set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM UnitHours where PartNumber = " & partNo
Set rs = oConn.Execute(sql)

Do while not rs.Eof

Response.write rs("PartNumber")
Response.write "<br><br>"
Response.write rs("Description")
Response.write "<br><br>"
Response.write rs("Price")

rs.Movenext

WEND

rs.close()
oConn.close()
set rs = nothing
set oConn = nothing

%>

ASKER CERTIFIED SOLUTION

gladxml

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nantha

ASKER

Thanks folks. I kind of got injured while walking. I'll be returning to my office next week. I'll test it and let you guys know.
Nantha

Nantha

ASKER

gladxml,

I was able to run at home. I works fine. Thanks a lot. Could you please exlain me the followings:
1. <%response.buffer=true%>
why do have to do this ?

2. <option value="<%=rs("PartNumber")%>" <%if request.form("Ref_ID")=rs("PartNumber") then response.write "selected" end if%>><%=rs("PartNumber")%></option>

why there is an If statement ?

Since you solved the full question I'll grand you all points.
Nantha

Nantha

ASKER

I was able to understand the basics of dropdown box. Thanks a lot. I'll keep posting some more in future where needs arrive.
Nantha