[thelist] Question about Access database programming with ASP 3.0

Jonathan j at firebright.com
Wed Jun 9 18:24:58 CDT 2004


Howdy everyone...

Listen, I have a simple problem I'm trying to solve for a friend of mine,
but I'm not an ASP programmer.  I've been fighting with this one for
hours...

I've got this database in Access that looks like this:
http://www.firebright.com/staged/db.gif

What I'm looking to generate is something that looks about like this:

NAME		HOME					PHONE
--------------------------------------------------------------------
Jim		House	based on houseID		884-343-3333
							884-343-3332
							884-343-3335

James		House based on houseID		884-343-3311
							884-343-3331


I'm getting close to what I want, but it's not right...  This is the code I
have written...

<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("baza.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath &
";"
SQL_query = "SELECT DISTINCT Names.NameID, Names.HouseID, Names.Name,
Phones.PhoneID, Phones.Phone From Names, Phones WHERE Names.NameID =
Phones.NameID"
Set RS = MyConn.Execute(SQL_query)
%>

But it's outputting something more like:

Name House  Phone    
Jim 3 215-243-1240      
Jim 3 267-210-7237      
Jim 3 206-333-0575      
James 1 916-339-3163      
James 1 916-265-0320      
James 1 916-639-0325      
James 1 916-339-0161      

Now that's not right...

This is the full source code:

<HTML>
<BODY>
<%
Set MyConn = Server.CreateObject("ADODB.Connection")
MdbFilePath = Server.MapPath("baza.mdb")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath &
";"
SQL_query = "SELECT DISTINCT Names.NameID, Names.HouseID, Names.Name,
Phones.PhoneID, Phones.Phone From Names, Phones WHERE Names.NameID =
Phones.NameID"
'SQL_query = "SELECT DISTINCT Houses.House, Houses.HouseID, Names.NameID,
Names.Name From Names, Houses, Phones ORDER BY Names.NameID"
'SQL_query = "SELECT NameID,Name FROM Names, Houses WHERE NameID"
Set RS = MyConn.Execute(SQL_query)
%>

<table>
<tr>
 <th>Name</th>
 <th>House </th>
 <th>Phone</th>
 <th></th>
 <th></th>
 <th></th>
</tr>

<%
WHILE NOT RS.EOF
%>
<!--
SELECT Phones.PhoneID 
FROM Phones
INNER JOIN

SELECT Houses.HouseID, Phones.PhoneD FROM Names, Houses, Phones WHERE
Names.NameID=Phones.NameID AND Names.NameID=Houses.HouseID
 -->
<tr>
 <td><a href="?name=<%=RS("NameID")%>"><%=RS("Name")%></a></td>
 <td><a href="?house=<%=RS("HouseID")%>"><%=RS("HouseID")%></a></td>
 <td>
 <%
 Response.Write RS("Phone")
 %>
 
 </td>
 <td></td>
 <td></td>
 <td></td>
</tr>

<%
RS.MoveNext
WEND
 Response.Write "PageCount : " & RS.PageCount & "<br>" & vbcrlf
%>

</table> 
</BODY>
</HTML>

How do I solve this problem?  You'll note it's a file access Access
database, and unfortunately it has to stay that way.

Any help would be appreciated.

Jonathan



More information about the thelist mailing list