DB Record As Comma Delimited String

R

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)
 
B

Bob Barrows [MVP]

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)

<%
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
%>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top