How should I create this Recordset? - URGENT!

[Guest_imported]

Hi All,

I am working on an ASP auction site. I would like to create a page that pulls all the current information into a page as follows:

My database contains 3 tables:

Items - Item ID#, Item Name, Description, etc.
Bids - Item ID#, CurBid, BidTime, etc.
Users - userName, fName, lName, eMail, telephone, etc.

The page I want to create would display all the auction items, the current highest bid, bidtime, and the User who made the bid (along with any pertinent information pulled from the fields of the database tables.

What would be the best way to go about doing this?

I tried creating a recordset (rsItems) to list all items, and then another recordset inside of that to pull the info from the bids table and the users table (with a WHERE clause to make the connection between the bids table and the items table, and an INNER JOIN to connect the buyer and the userName between the other two tables).

If I'm not getting an EOF error, the page is displaying one record over and over and over until the script times out.

Help please!
Thanks!

 

[JuanitaC]

In the loop where you are writing out the contents of your recordset, do you have this:

rs.movenext

where rs is the name of your recordset? I have forgotten this many times myself

If this doesn't fix the problem, post the code (both that creates the recordset and that prints it out).

 

[Guest_imported]

Thanks for replying JuanitaC. Here is my code. I'm an ASP newbie, so please forgive any glaring errors! Here is a link to the resulting page and error :

http://www.lighthousedesign.net/siteproof/playersam/auction/summary.asp

Set Conn = Server.CreateObject("ADODB.Connection&quot
Conn.Open strConnString
SQL = "SELECT * FROM auctionItems ORDER BY auctionID"
set rsItem = Server.CreateObject("ADODB.RecordSet&quot
Set rsItem = Conn.Execute(SQL)
while not rsItem.EOF
rsItem.MoveFirst

strItem = rsItem("itemName&quot
strauctionid = rsItem("auctionID&quot

SQL2 = "SELECT * FROM users INNER JOIN auctionBids ON auctionBids.buyer = users.userName WHERE auctionID="&strauctionid&""
set rsUsers = Server.CreateObject("ADODB.RecordSet&quot
set rsUsers = Conn.Execute (SQL2)

if not rsUsers.eof then

rsUsers.MoveFirst
%>
<tr>
<td><%=rsItem(&quot;itemName&quot%></td>
<td><%=rsItem(&quot;auctionID&quot%></td>
<td>$<%=rsUsers(&quot;maxPrice&quot%></td>
<td><%= rsUsers(&quot;buyer&quot %></td>
<td><%= rsUsers(&quot;eMail&quot %></td>
<td><%= rsUsers(&quot;bidTime&quot %></td>
</tr>
<%
else

response.write &quot;<tr><td>&quot;
response.write strItem & &quot;</td><tr><td>&quot;
response.write strauctionid & &quot;</td>&quot;
response.write &quot;<td colspan=&quot;&quot;4&quot;&quot;>There are no bidders on this item.</td></tr>&quot;

end if
rsUsers.MoveNext

rsItem.moveNext
wend
%>
</table>
<%
rsUsers.Close
set rsUsers = Nothing
rsItem.Close
set rsItem = Nothing
Conn.Close
set Conn = Nothing

 

[Chopstik]

At the beginning of your code, just after the While Not rsItem.EOF, you have then told it to move to the first item. Each time you loop, it is going back to the first item in your code. I would re-arrange your code as follows and see if this helps:

Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open strConnString
SQL = &quot;SELECT * FROM auctionItems ORDER BY auctionID&quot;
set rsItem = Server.CreateObject(&quot;ADODB.RecordSet&quot
Set rsItem = Conn.Execute(SQL)
rsItem.MoveFirst
while not rsItem.EOF

 

[KellyHero]

Thanks for the help Chopstick! That was ONE of the problems! I also created a sub routine for the second SQL statement and called it where needed. Works like a charm!

Well, almost...now I realize that when someone bids on an item, a new row is added to the bids table instead of updating the current rows, so while the summary page is displaying the user information correctly, it's not displaying the correct (most recent) information.

Oh well, more learning for me, I suppose! (-: