Blog Setup - Showing count of related comments

[Darren]

Hi,

I am trying to set up my own basic blog using ASP.VBScript. The sql used to
retreive the entries is:

========================
SELECT * FROM entries
========================

The fields in tbl entries are:
-----------
EntryID (pk)
Date
Title
Subject
-----------

In access, the query used to retreive the count of comments for each entry
is:

=============================
SELECT entries.Title, Count(Comments.CommentID) AS CountOfCommentID
FROM entries INNER JOIN Comments ON entries.EntryID = Comments.EntryID
GROUP BY entries.Title, entries.EntryID;
=============================

The fields in tbl Comments are;
------------------
CommentID (pk)
Date
Name
Title
Comment
Website
EntryID (fk)
--------------------

On my .asp page I have a table where I will show the Title, Date and subject
of my blog entry. Below it I want to show how many comments there are for
each entry. The vbscript in my table looks like this:

============================
<%
While ((Repeat1__numRows <> 0) AND (NOT posts.EOF))
%>
<table width="450" border="1">
<tr>
<td colspan="2"><%=(posts.Fields.Item("Title").Value)% ></td>
<td><%=(posts.Fields.Item("Date").Value)%></td>
</tr>
<tr>
<td colspan="3"><%=(posts.Fields.Item("Subject").Value )%></td>
</tr>
<tr>
<td>Comments(<%=(comments.Fields.Item("CountOfComm entID").Value)%>)</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
<br>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
posts.MoveNext()
Wend
%>
===================================

The value - <%=(comments.Fields.Item("CountOfCommentID").Value )%> - is what
I need, but equal to the entryID of its associated entry from the Entries
table. Can anyone give me some ideas as to how to associated each comment
with its Blog entry.

Hope I have explained myself correctly.

Many thanks in advance for any and all help.

Best Regards
Darren

[url]www.yourdesignz.co.uk[/url]

[Bob Barrows]

Darren wrote:

> Hi,
>
> I am trying to set up my own basic blog using ASP.VBScript. The sql
> used to retreive the entries is:
>
> ========================
> SELECT * FROM entries
> ========================
>
> The fields in tbl entries are:
> -----------
> EntryID (pk)
> Date
> Title
> Subject
> -----------
>
> In access, the query used to retreive the count of comments for each
> entry is:
>
> =============================
> SELECT entries.Title, Count(Comments.CommentID) AS CountOfCommentID
> FROM entries INNER JOIN Comments ON entries.EntryID = Comments.EntryID
> GROUP BY entries.Title, entries.EntryID;
> =============================
>

<snip>

Don't use two recordsets. All the information you need can be returned in a
single recordset:

SELECT e.EntryID, e.[Date], e.Title, e.Subject
Count(c.CommentID) AS CountOfCommentID
FROM entries e INNER JOIN Comments c ON e.EntryID = c.EntryID
GROUP BY e.EntryID, e.[Date], e.Title, e.Subject

HTH,
Bob Barrows

PS. "Date" is a reserved keyword (it's the name of a VBA function!) and
should not be used for a field name. BlogDate would be a less ambiguous name
for this field, wouldn't it? As well as CommentDate in the Comments table
...?
See here for the list of keywords to be avoided when naming your database
objects:
[url]http://www.aspfaq.com/show.asp?id=2080[/url]
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Darren \(at work\)]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u1yf2X#0DHA.2000@TK2MSFTNGP11.phx.gbl...
|
| Don't use two recordsets. All the information you need can be returned in
a
| single recordset:
|
| SELECT e.EntryID, e.[Date], e.Title, e.Subject
| Count(c.CommentID) AS CountOfCommentID
| FROM entries e INNER JOIN Comments c ON e.EntryID = c.EntryID
| GROUP BY e.EntryID, e.[Date], e.Title, e.Subject

That does seem like a more common sense appoach Bob. Thank you for that.

| HTH,
| Bob Barrows
|
| PS. "Date" is a reserved keyword (it's the name of a VBA function!) and
| should not be used for a field name. BlogDate would be a less ambiguous
name
| for this field, wouldn't it? As well as CommentDate in the Comments table
| ..?
| See here for the list of keywords to be avoided when naming your database
| objects:
| [url]http://www.aspfaq.com/show.asp?id=2080[/url]

Thank you for the heads up on that also.
Much appreciated Bob.

Best Regards
Darren