I am creating a simple message board using ASP for a client and I would like to add in a few features. My main issue is this:

<b>I would like to be able to list the total number of replies for a topic</b>

Here is my database - table layout

<b>TLBTopics</b>

TopicID
TopicTitle
TopicDetails

<b>TLBReply</b>

ReplyID
TopicID
ReplyDetails

I have a page that lists all of the topics from TBLTopics and , using ultradev, I have inserted a repeat region to display all the topic titles. I would like to be able to have some sort of a function that will count all the replies in TBLReply that have the same TopicID as the topic title from TBLTopics. It should work the same as this message board, showing the number of replies for each topic.

I hope I was clear enough... any help is very much appreciated.

Thanks in advance.

when you create a recordset in DW you will see [total records] in the bindings panel...this will provide you with the total number of records that match the criteria in your SQL statement...just drag this onto the work area and it will be displayed when you run the page.

alternatively, doing it by hand you go something like this...

&lt;%Response.Write rsMyRecodset.RecordCount%&gt;

DW is great, believe me...I've tried just about every app on the market...but one drawback of all WYSIWYG HTML editors (no matter how good they are) is that they sometimes write way too much code to accomplish simple tasks...as is the case here. Writing by hand will save you more than 50 lines of code.

hope this helps

Doc
Rangewalk Digital Studios

Edited by - on 16 Apr 2003 02:38:10

Dave, thanks for your response. My question was probably not very clear. I am aware of the feature to display the total records of a recordset, but I don't think that will help me... My recordset will select all the topicTitles from TBLtopics and will display the titles in a repeat region. However, for EACH of those topics, there will be several replies stored in in TBLReply linked to TBLTopics with TopicID. I need to Show how many replies (from TBLReply) there are for each topic (from TBLTopics). If I were to simply drag the totalRecords from the databindings to the page, I would have the total number of topics and not the total number of replies for each of the topics.

Any other ideas? Thanks again.

OK, now I see...

I wrote this page that should suit your needs. You will obviously have to tweak a thing or two, but the concept should get you where you need to be.

&lt;%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%&gt;
&lt;!--#include file="Connections/messageboard.asp" --&gt;
&lt;%
Dim rsTopic
Dim rsReplies

Set rsTopic = Server.CreateObject("ADODB.Recordset"
rsTopic.ActiveConnection = MM_messageboard_STRING
rsTopic.Source = "SELECT * FROM dbo.TBLTopics ORDER BY ID DESC"
rsTopic.CursorType = 0
rsTopic.CursorLocation = 2
rsTopic.LockType = 1
rsTopic.Open()
%&gt;
&lt;html&gt;
&lt;head&gt;
&lt;title&gt;Topics&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
&lt;table width="98%" border="0" cellspacing="0" cellpadding="0"&gt;
&lt;tr&gt;
&lt;td&gt;Topic&lt;/td&gt;
&lt;td&gt;Replies&lt;/td&gt;
&lt;/tr&gt;
&lt;% While Not rsTopic.EOF
Set rsReplies = Server.CreateObject("ADODB.Recordset"
rsReplies.ActiveConnection = MM_messageboard_STRING
rsReplies.Source = "SELECT * FROM dbo.TBLReply WHERE TopicID = '" & rsTopic.Fields.Item("TopicID".Value & "'"
rsReplies.CursorType = 0
rsReplies.CursorLocation = 2
rsReplies.Open()
%&gt;
&lt;tr&gt;
&lt;td&gt;&lt;% Response.Write rsTopic.Fields.Item("TopicTitle".Value%&gt;&lt;/td&gt;
&lt;td&gt;&lt;% Response.Write rsReplies.RecordCount %&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;%rsReplies.Close()
Set rsReplies = Nothing

rsTopic.MoveNext
Wend
%&gt;
&lt;/body&gt;
&lt;/html&gt;
&lt;%
rsTopic.Close()
Set rsTopic = Nothing
%&gt;



Hope this helps...
Let me know if I can help you out in any way.

Doc
Rangewalk Digital Studios

Edited by - on 17 Apr 2003 02:28:14

If I use your code to display the total number of records: <b> Response.Write rsReplies.RecordCount </b>, I always come up with a -1... weird???

Other than that, I think this will work. Thanks for your help!!!

Oops, my bad...forgot to change the cursor type

by default, DW sets cursor type to Forward Only. you need to chnage it to static...you can do this one of two ways

1) select the recordset in code view and change the value in the Cursor Type drop-down to Static

2) in code view change the following line:

rsReplies.CursorLocation = 2

to

rsReplies.CursorLocation = 3

That will get rid of that pesky negative one...

Hope that helps...

Doc
Rangewalk Digital Studios

That's OK. I did get the repeat region working using the dreaweaver method (Your right, it does take 50 lines of code). But I will try changing the cursor type to save some coding.

Thanks again for your help!