[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