DB Record As Comma Delimited String

[rn5a]

A MS_Access DB table has 3 columns - ID, UID & Location. ID is an
AutoNumber column, UID is a foreign key (the primary key UID column
resides in another table). Each UID can have multiple entries under
the Location column something like this:

---------------------------
UID Location
---------------------------
2 China
2 Japan
2 Austria
2 India
2 Denmark
3 USA
3 UK
3 Korea
---------------------------

I want an ASP page to retrieve the above records based on the UID. For
e.g. if the UIO is 2, then China, Japan, Austria, India & Denmark
should be retrieved but I don't want the ASP page to display the
records in the same way as the DB table displays. Rather I want the
records to be displayed on the ASP page like this (assuming that
UID=2)

---------------------------
2 - China, Japan, Austria, India, Denmark
---------------------------

If UID=3, then the ASP page should display the records like this:

---------------------------
3 - USA, UK, Korea
---------------------------

How do I implement this?

[Bob Barrows [MVP]]

rn**@rediffmail.com wrote:

A MS_Access DB table has 3 columns - ID, UID & Location. ID is an
AutoNumber column, UID is a foreign key (the primary key UID column
resides in another table). Each UID can have multiple entries under
the Location column something like this:

---------------------------
UID Location
---------------------------
2 China
2 Japan
2 Austria
2 India
2 Denmark
3 USA
3 UK
3 Korea
---------------------------

I want an ASP page to retrieve the above records based on the UID. For
e.g. if the UIO is 2, then China, Japan, Austria, India & Denmark
should be retrieved but I don't want the ASP page to display the
records in the same way as the DB table displays. Rather I want the
records to be displayed on the ASP page like this (assuming that
UID=2)

---------------------------
2 - China, Japan, Austria, India, Denmark
---------------------------

If UID=3, then the ASP page should display the records like this:

---------------------------
3 - USA, UK, Korea
---------------------------

How do I implement this?

<%
dim cn, cmd, rs, sSQL,sHTML,uid
uid=2 'you will probably get this value from request
sHTML = uid & " - "
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=p:\ath\to\database.mdb"
sSQL="select Location From tablename " & _
"where UID=?"
set cmd=createobject("adodb.command")
with cmd
set .activeconnection=cn
.commandtext=sSQL
.commandtype=1 'adCmdText
end with
set rs=cmd.execute(,array(uid))
if not rs=nothing then
sHTML = sHTML & rs.getstring(2,,, ", ")
end if
rs.close:set rs= nothing
cn.close:set cn=nothing
response.write sHTML
%>
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.