ASP-Databases - A drop down menu returning query results

neillovell · Nov 8, 2002

Morning!

How can I create a drop down combo box that only holds the results of a query? I know how to make it static in HTML but...I suspect I need an array, but I'm a bit lost after that. Any help much appreciated!

macmonteiro · Nov 8, 2002

Does it fit?

-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_
<%
Set Conn=Server.CreateObject("ADODB.Connection&quot

Conn.Open("Logs&quot

Set LogRec=Server.CreateObject("ADODB.Recordset&quot

Sel="SELECT id, data, diasemana, titulo, corpo, logger FROM TB_Log ORDER BY id DESC"
Set LogRec=Conn.Execute(Sel)

response.write "<Select name='brazilian_soccer_team'>"
response.write "<option selected value=''>Choose a player</option>"
Do while not LoRec.EOF
response.write <option value='"&LogRec("player_id&quot

&"'>"&LogRec("player_name&quot

&"</option>"
LogRec.Movenext
Loop
response.write "</select>"
?>
-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_

macmonteiro · Nov 8, 2002

Excuse me, I didn't fix my 'zombie' properly.
It should be like this:
-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

<%
Set Conn=Server.CreateObject("ADODB.Connection&quot

Conn.Open("Logs&quot

Set LogRec=Server.CreateObject("ADODB.Recordset&quot

Sel="SELECT player_id, player_name FROM TB_Players ORDER BY player_id DESC"
Set LogRec=Conn.Execute(Sel)

response.write "<Select name='brazilian_soccer_team'>"
response.write "<option selected value=''>Choose a player</option>"
Do while not LoRec.EOF
response.write <option value='"&LogRec("player_id&quot

&"'>"&LogRec("player_name&quot

&"</option>"
LogRec.Movenext
Loop
response.write "</select>"
?>
--------------------------->>>>--------------------
Something like this......?

macmonteiro · Nov 8, 2002

and at the end, not ?> but %>, of course you know....;-)

neillovell · Nov 8, 2002

I don't have any luck with that. This is my code, there is a page before that lets the user submit two names to go in to the database.

<HTML>

<HEAD>
<TITLE>Text to Database</TITLE>

</HEAD>

<BODY>

<%
' Name the database we will use
accessdb="UnsecuredTimesheet.mdb"

' Create the connection object
cn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &

Server.MapPath("UnsecuredTimesheet.mdb&quot

& ";"

set rs = Server.CreateObject("ADODB.Recordset&quot

' Put the SQL statement in to the variable sql. This string will be read and thus the

command given.

sql = "INSERT INTO testtable (fname, lname) "
sql = sql & "values ('" & firstname
sql = sql & "','" & lastname & "')"

' test response.write sql This will come out //**********************************
'response.write sql

' Open in read and write mode
rs.Open sql, cn,3,3
%>

<h2>Drop Down Form</h2><br />
Drop Down Should Go Here

</BODY>
</HTML>

macmonteiro · Nov 8, 2002

You've got me confused now...

ok, lets assume that you've inserted these two values into your DB and then you want to show a combo with data from the same DB. alright?
what do you want to fetch from your DB? The same values?
I didn't understand what's the purpose of your page.

You'll do it, just explain things calmly and some one here will help you.

neillovell · Nov 8, 2002

All I want is to generate a drop down menu with values retrieved from a query.

Ignore the first and last name entry bits, I just posted the code to show what my connection was looking like.

welshone · Nov 8, 2002

just do it like this :

<%
Dim SearchTextcompany, MyConncompany, SQLcompany, RScompany

Set MyConncompany=Server.CreateObject("ADODB.Connection&quot

MyConncompany.Open "dsnname","susername","password"

SQLcompany = "SELECT * FROM table"

Set RScompany = MyConncompany.Execute(SQLcompany)
%>

<Select name = "company" >
<option selected>--Select company--

<% While Not RScompany.EOF
%>
<option style="color:black" value="<%=RScompany("ID&quot

%>">

<%=RScompany.Fields("Name&quot

%>

<%
RScompany.MoveNext
Wend
%>

<%
RScompany.Close
MyConncompany.Close
Set RScompany = Nothing
Set MyConncompany = Nothing
%>

</select>

macmonteiro · Nov 8, 2002

ok. so, pay attention:
_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-

'create a form
response.write "<form name='any_name' action='u_know' method='get'>"

'create a combo
response.write "<select name='any_name'>"

'Create a Loop...
Do while not cn.EOF

'...that will repeat this instruction:
response.write "<option value='"& rs("field_name&quot

&"'>"& rs("field_name&quot

&"</option>"
rs.Movenext
' until cn is at the End Of File
Loop

'Close the combo
response.write "</select>"
response.write "</select>"

//// then, your ASP code should look like this :::::

response.write "<form name='any_name' action='u_know' method='get'>"
response.write "<select name='any_name'>"
Do while not cn.EOF
response.write "<option value='"& rs("field_name&quot

&"'>"& rs("field_name&quot

&"</option>"
rs.Movenext
Loop
response.write "</select>"
response.write "</form>"

??????? The user's browser would receive:

<form name="any_name" action="u_know" method="get">
<select name="any_name">
<option value="value1">Value1</option>
<option value="value2">Value2</option>
<option value="value3">Value3</option>
<option value="value4">Value4</option>
</select>
</form>
-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_-_

I hope it fits....

macmonteiro · Nov 8, 2002

welshone, your example is nice, too.
but did you know that the processment of your code by the server will be faster if you dont close/open your ASP?
instead of it, I'd rather use a lot of 'response.write'

but everyone must find his own way to do things. and that's the fun!

neillovell · Nov 8, 2002

I have tried both but I must be missing something obvious. Could I bother you once more, if I supply the information?

*The query is called QueryFavProjects. The field to be referenced is ProjID

*The database is called UnsecuredTimesheet.mdb

*I access the database by doing the following
<%
' Name the database we will use
accessdb="UnsecuredTimesheet.mdb"

' Create the connection object
cn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("UnsecuredTimesheet.mdb&quot

& ";"

set rs = Server.CreateObject("ADODB.Recordset&quot

' This is an example of inputting in to the database, just
' to show that it can be done.
sql = "INSERT INTO testtable (fname, lname) "
sql = sql & "values ('" & firstname
sql = sql & "','" & lastname & "')"

' Open in read and write mode
rs.Open sql, cn,3,3
%>

sorry I'm having such a problem inplementing this, I've not used ASP and databases before.

macmonteiro · Nov 8, 2002

Can you tell us what is the error msg you received when you ran the asp?

neillovell · Nov 8, 2002

Page cannot be displayed

macmonteiro · Nov 8, 2002

Just it?
It may seem a little "duh", you know, but if you are running ASP in a localhost, have you properly configured your Personal Web Server?

Another thing, try chnge your line:
cn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("UnsecuredTimesheet.mdb&quot

& ";"

By this:
cn.Open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
Server.MapPath("\UnsecuredTimesheet.mdb&quot

)

Else consider using the ADODB.Connection. Whatever you choose tell us what happened.
so long

welshone · Nov 8, 2002

thanks for the info. macmonteiro , I didn't know opening / closing the asp would effect the speed.
I'll try your way.

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!

ASP-Databases - A drop down menu returning query results

neillovell

Programmer

macmonteiro

Programmer

macmonteiro

Programmer

macmonteiro

Programmer

neillovell

Programmer

macmonteiro

Programmer

neillovell

Programmer

welshone

Programmer

macmonteiro

Programmer

macmonteiro

Programmer

neillovell

Programmer

macmonteiro

Programmer

neillovell

Programmer

macmonteiro

Programmer

welshone

Programmer

Similar threads

Part and Inventory Search

Sponsor