Dropdowns with info from DB

[The Wizzard]

Hey Everyone...

This is for my car site, (www.ocmotorcars.com/motorcars)

I want to have a dropdown menu of all the car MAKES but there are a lot of BMW's, Mercedes, etc... so I only want to take 1 from each car Make...

Anyone know how to get it done?

Thanks!

[raf]

If you want an imedeate answer to your question, run a search.

You need to use a DISTINCT in the sql to have just 1 value for each make.
Then built the dropdown using a loop.

<%
dim rsMake
set rsMake = ("adodb.recordset")

dim sql
sql="SELECT DINSTINCT make, id FROM table ORDER BY make asc"

rsMake.Open sql, conGranIT
%>
your html cofe for form etc

<select name="make" size="1" onclick="submit();">
<option value="0" selected>Choose ...

<%
do while rsMake.EOF=false
Response.Write("<option value=" & rsMake.Fields("id")& ">")
Response.Write(server.HTMLEncode(rsMake.Fields("make")) & "</option>")
rsMake.MoveNext
loop

rsMake.Close
set rsMake=nothing
%>

[The Wizzard]

Im getting this error:


Microsoft VBScript runtime error '800a01a8'

Object required: '[string: "adodb.recordset"]'

/motorcars/index.asp, line 334

[whammy]

Set rsMake = Server.CreateObject("ADODB.Recordset")

;)

[The Wizzard]

hmm, i cant get that to work, but i did get this to work, the only problem with it is that it displays repeats of the makes...

Lists BMW like 7 times, Mercedes like 5 times, etc...

Can you modify this script to limit all Makes to only 1?

Thanks!!!!

--------------------------------------------------------------------------------

<%
Dim conn, ConnectString

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("DATABASE.mdb") & ";Persist Security Info=False"
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnectString
%>
<%
Dim rsMake
Dim sql

sql = "SELECT DISTINCT make, ID FROM Cars ORDER BY make"
Set rsMake = Server.CreateObject("ADODB.Recordset")
rsMake.Open sql, conn, 3, 3
%>
<select name="Make" class="input">
<%if not rsMake.EOF then
rsMake.Movefirst
do until rsMake.EOF
%> <option value="inventory.asp?psearch=<%=rsMake("make")%>"><%=rsMake("make")%></option>
<% rsMake.Movenext
loop
rsMake.Movefirst
end if
%></select>

[whammy]

What are you using for a database? DISTINCT takes some special handing if you're using Access...

[raf]

Sorry Wizard. I must be loosing it. Two error in one reply.
Realy sorry.

(error because i eddited existing code and deleted to much)

Whammy solved the first, the second will probably be that you need to group the records from the view. So the sql should be

sql = "SELECT DISTINCT make, ID FROM Cars GROUP BY make, ID ORDER BY make"

This is when you use Jet SQL (Micro$oft db's)

[The Wizzard]

Its not working... It still repeats...

http://www.ocmotorcars.com/motorcars/index.asp

Hmm...

[The Wizzard]

Ok, cool, nevermind, got it working...

Great, Thanks A LOT!!!

Here is the final code incase anyone needs it...

-----------------------------------------------------------------------------------

<%
Dim conn, ConnectString

ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("Database.mdb") & ";Persist Security Info=False"
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnectString
%>
<%
dim rsMake

dim sql
sql = "SELECT DISTINCT make FROM Cars GROUP BY make ORDER BY make"
Set rsMake = Server.CreateObject("ADODB.Recordset")

rsMake.Open sql, conn, conGranIT
%>

<select name="psearch" class="input">
<option selected>Choose a Make...</option>

<%
do while rsMake.EOF=false
Response.Write("<option value=" & rsMake.Fields("make") & ">")
Response.Write(server.HTMLEncode(rsMake.Fields("make")) & "</option>")
rsMake.MoveNext
loop

rsMake.Close
set rsMake=nothing
%>
</select>