Problems with looping

rockstar09 · December 7th, 2009, 01:03 PM

Hi I need some help on a tutorial I am doing. Its just a library database. I need to display on screen in a table a list of all addresses of customers that have hired out a book and a list against that address on screen of which books in a table.

Example:

Address Book

Address 1 Book1
Book2

Address 2 Book 1
Book 2
Book 3

I am partially managing to get it to display correctly except an address is still displaying twice but one occurrence of the address lists a set of books correctly against that address and the other occurrence of the address displays the rest of the books.

So I am trying to display a column of addresses. With each address only displaying once and then each book associated with that address is then display in the Books column or Title column as I have called it.

Can you help or point me to a good tutorial on the right way to loop through this. I am pulling my data from a view I created in MS Access, the view pulls in the list of addresses and their books from the relational database modelling a Library.

Hope this makes sense.

Many thanks in advance

Please see code below

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim conn, connString, sqlGetAddressOfBooks, rsGetAddressOfBooks
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("rltestingDB.mdb")
sqlGetAddressOfBooks = "SELECT * FROM viewWhatBooksAreAtWhatAddress"
Set conn = Server.CreateObject("ADODB.Connection")
Set rsGetAddressOfBooks = Server.CreateObject("ADODB.Recordset")
conn.Open(connString)
rsGetAddressOfBooks.Open sqlGetAddressOfBooks, conn
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Books</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="global.css" rel="stylesheet" type="text/css">
</head>
<body>
<%'response.Write("Hello world")%>
<table width="800" border="0">
<tr>
<th>Address</th>
<th>Books</th>
</tr>
<%
Dim address, nextAddress
address=""
while(rsGetAddressOfBooks.eof = false)%>
<tr>
<%If rsGetAddressOfBooks("ADDRESS") <> address then%>
<td><%=rsGetAddressOfBooks("ADDRESS")%></td>
<td><%=rsGetAddressOfBooks("TITLE")%></td>
<%else%>
<td><% nextAddress=rsGetAddressOfBooks("ADDRESS")%></td>
<td><%=rsGetAddressOfBooks("TITLE")%></td>
<%end if
address = rsGetAddressOfBooks("ADDRESS")%>
</tr>
<%
rsGetAddressOfBooks.movenext
wend
%>
</table>
</body>
</html>

<%
rsGetAddressOfBooks.Close
Set rsGetAddressOfBooks = Nothing
conn.Close
Set conn = Nothing

%>

rstelma · December 7th, 2009, 02:32 PM

I think the problem might be in your ELSE part of your IF. If you've already written the address once in the first part of the IF then in the else you just want to write nothing in that <td> tag.

It's really hard to tell though if you don't have some kind of data problem with some character difference in the addresses. Maybe there is a leading or ending space that's throwing you into the ELSE when you don't want it to.

Can you post your table relationship and what datatypes you have for the columns?

Richard

dparsons · December 7th, 2009, 02:36 PM

I have cleaned up your relevant code below. I think this is more an issue of TR's being out of place then your actual logic.

asp Code:

<%
Dim address
 
while(Not rsGetAddressOfBooks.eof)
     if rsGetAddressOfBooks("ADDRESS") <> address then
          response.write("<tr>")
          response.write("<td>" & rsGetAddressOfBooks("ADDRESS") & "</td>")
          response.write("<td>" & rsGetAddressOfBooks("TITLE") & "</td>")
          response.write("</tr>")
     else
          response.write("<tr>")
          response.write("<td>&nbsp;</td>")
          response.write("<td>" & rsGetAddressOfBooks("TITLE") & "</td>")
          response.write("</tr>")
     end if
 
     address = rsGetAddressOfBooks("ADDRESS")
rsGetAddressOfBooks.movenext
wend
%>

hth
-Doug

rockstar09 · December 7th, 2009, 03:29 PM

Hi dParsons and rstelma

Thanks for getting back to me, I have have tried what you suggested but still have the same results, can I attach the database file to a post?

Thanks

rockstar09 · December 7th, 2009, 03:54 PM

I see from FAQ that I am not allowed to post attachements. Whats the best way for me to show you the database?

rstelma · December 7th, 2009, 04:33 PM

Doug supplied the code changes that I was suggesting. Very nice job Doug.

It seems there might be a data problem. All of the other addresses are working correctly right?

The reason I was interested in your database was to see how you were setting up your relationships between your address table and your title table. Can you send the query that creates your view?

rockstar09 · December 7th, 2009, 04:44 PM

Hi rstelma

Thanks for getting back to me, and thanks to Doug for the code.

Here is the query on the relational database that creates the view for me:

SELECT HOUSE_NUMBER & HOUSE_NAME & ADDRESSES.HOUSE_NAME & ADDRESSES.HOUSE_NUMBER_PART & ADDRESSES.STREET & ADDRESSES.TOWN & ADDRESSES.POSTCODE AS ADDRESS, BOOKS.TITLE
FROM (ADDRESSES INNER JOIN customers ON ADDRESSES.ADDRESS_ID=customers.ADDRESS_ID) INNER JOIN (BOOKS INNER JOIN CUSTOMER_BOOKS ON BOOKS.BOOK_ID=CUSTOMER_BOOKS.BOOK_ID) ON customers.CUSTOMER_ID=CUSTOMER_BOOKS.CUSTOMER_ID;

Hope this helps

Cheers

dparsons · December 8th, 2009, 04:32 PM

Can you post me some actual sample data. Maybe the results for the first and second address retrieved by the view?

-Doug

rockstar09 · December 16th, 2009, 05:12 PM

Thanks for your help on this one, but although this is a tutorial I am working on the data is actually peoples real addresses so I don't want to post this on the forum. I think I have just about figure the issue out anyway. But thanks again for all your help.