Populate a drop down from database

topshed · November 28th, 2004, 12:05 AM

I need to populate a drop down box with data from a field where there can be anything from 1 to 95 instances of each possibility
But I only want one of each instance in the drop down

EG:-
Database records
a
bbb
ccccccc
dd
e
ffffffff

I just want to end up with
a
b
c
d
e
ect...

I know it can be done but I cannot get my head around the code

Help anyone ??

jemacc · November 29th, 2004, 02:44 AM

Here is a sample using SUBSTRING

Code:

USE NORTHWIND
SELECT DISTINCT SUBSTRING(FirstName, 1, 1)AS NAME
FROM EMPLOYEES
ORDER BY NAME

Jaime E. Maccou
Applications Analyst

jemacc · November 29th, 2004, 03:11 AM

sorry in access you can use this

Code:

SELECT DISTINCT Left$([firstname],1) AS name
FROM EMPLOYEES
ORDER BY Left$([firstname],1);

Jaime E. Maccou
Applications Analyst

topshed · November 29th, 2004, 06:13 AM

Thanks for the replies but ..
I Used:---

myPath = Server.MapPath("../mydata.mdb")
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30
objDC.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "sheds", objDC, 0, 1

SELECT DISTINCT location FROM sheds

at this point i get

Microsoft VBScript compilation error '800a03fd'

Expected 'Case'

/up/pulldown.asp, line 15

SELECT DISTINCT location FROM sheds
-------^

I am missing something ??

topshed · November 29th, 2004, 10:55 AM

Please Ignore the last message..
Your 'DISTINCT' set me on the right path, and I just needed to get my head around the syntax and order of things to get it to work

Thanks for the Help

Topshed

topshed · November 29th, 2004, 09:38 PM

Sorry I am back again, I am using the following

myPath = Server.MapPath("../mydb.mdb")
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.ConnectionTimeout = 15
objDC.CommandTimeout = 30
objDC.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & myPath & ";"
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCT name FROM db4 ORDER BY name"
objRS.Open strSQL, objDC, 0, 1
If Not objRS.EOF Then
    objRS.MoveFirst %>
    <form method="get" action= "<%= strURL %>">
    <select name="search">
    <option>Select Class</option>
    <% Do While Not objRS.EOF %>
    <option value><%= objRS.Fields("name") %></option>
    <% objRS.MoveNext
    Loop %>
   </select>
  <input type="submit" value="Submit Here" />
</form>
<%
End If
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
strSearch = trim(Request.QueryString("search"))
response.write strSearch

Problem is although my pulldown box shows all the info that it should the debug lines at the end always report the result as NULL on Submit.

and further on response.write strSQL results in :-
"SQL statement: SELECT * FROM db4 WHERE name = 'NULL' ORDER BY name;"

What am i Missing _PLEASE HELP

Topshed