 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
November 28th, 2004, 12:05 AM
|
|
Authorized User
|
|
Join Date: Sep 2003
Location: Melbourne, Victoria, Australia.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Populate a drop down from database
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 ??
|
November 29th, 2004, 02:44 AM
|
|
Friend of Wrox
|
|
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
November 29th, 2004, 03:11 AM
|
|
Friend of Wrox
|
|
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
November 29th, 2004, 06:13 AM
|
|
Authorized User
|
|
Join Date: Sep 2003
Location: Melbourne, Victoria, Australia.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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 ??
|
November 29th, 2004, 10:55 AM
|
|
Authorized User
|
|
Join Date: Sep 2003
Location: Melbourne, Victoria, Australia.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
November 29th, 2004, 09:38 PM
|
|
Authorized User
|
|
Join Date: Sep 2003
Location: Melbourne, Victoria, Australia.
Posts: 81
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
Similar Threads
