record count

[chrismartz]

I was wondering how I can show the ammount of a certain item in my database.....in my comments, i would like to show how many comments there are for a certain blog number.

[javaNoobie]

use rs.recordCount.
Note: rs.cursorType must be 3.

[buntine]

You have already used this in your code. I saw it in the code you sent me a few days ago. Mayby you should go over it again. :p

[chrismartz]

when i put rs.recordcount, it counts every entry within the comments section of my database....i need it to pull from a certain number entered into the database based on what the link goes to......like index.asp?id=2 i need it to find all those marked 2 and tally up the total and post it....i have Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open "SELECT * FROM comments",objDC1, 3 to open the db where the comments are and number is the column i need to get the information from.....what do i do? I have the following on the page where its suposed to show the number of comments:<% rs.moveFirst
for i = 0 to rs.RecordCount
if not rs.EOF Then
%><tr>
<td align="left" class="B3"><center><font class="title"><%= rs.Fields("blogtitle") %></font><b><font class="links"><br><%= rs.Fields("udate") %> - <a href="perm.asp?id=<%= rs1.Fields("id") %>">Permalink</a> - <a href="comments.asp?id=<%= rs.Fields("id") %>"><%= rs1.RecordCount %>Comments</a></b></font></center><font class="blog">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<%= rs.Fields("blogmessage") %></font><br>
<center><img alt="" src="images/bar.gif"> <img alt="" src="images/bar.gif"> <img alt="" src="images/bar.gif"><br><br></center></td>
</tr>

<%
rs.MoveNext
end if
next
%></

[chrismartz]

any ideas?

[javaNoobie]

i think your sql statement is wrong. isnt is suppose to be:

"SELECT * FROM comments WHERE id=" & request.querystring("id")

[chrismartz]

on this page....i don't have querystrings in the url because i am pulling the records from teh database and posting them in numerical order.

[javaNoobie]

How are you getting the index to know which comments to get?

Also you can use 'while not rs.eof.. wend' instead of 'for i=0 to rs.recordCount.. next' and there wun be a need to check if rs.eof

edit: Ok.. i think i know what you are getting at. Correct me if i am wrong. You wish to select all blog entries. And for each of these blog entries you wish to get the number of comments for that particular entry?
If so, you are gonna need 2 recordsets. One to get all blog entries. The 2nd to get the number of comments. The sql statement for the 2nd recordset would probably be:

"SELECT * FROM comments where ID=" & rs("blogId")

You would have to change rs.recordCount to probably rs2.recordCount.

[chrismartz]

thanks...i'll try that

[chrismartz]

thanks....i'm glad you knew what I was doing that all seems to work, KIND OF....I have put:Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT * FROM blog ORDER BY ID DESC", objDC, 3

Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open "SELECT * FROM comments where num=" & rs("ID"),objDC1, 3 but in all the comment numbers there is a 0.....I don't know why! If you need a visual of what i mean...go to http://www.martzblog.tk and look at each entry where it says comments (0)....that zero should be replaced with the ammount of actual comments there are for that certain part....i have attached a .zip file with my database in it for another visual!

[chrismartz]

here is where i have attached that zip that i forgot to in the last post!

[buntine]

You could probably use a join, which would give you the same result in one query rather than two.

SELECT b.*, c.* FROM blog b, comments c WHERE c.num = b.id;

Or, you could even go a bit further, though, im not sure if Access even allows the following type of semi-advanced SQL.

SELECT b.*, c.*, COUNT(c.num) FROM blog b, comments c WHERE c.num = b.id GROUP BY c.num;

Regards.

[chrismartz]

shouldn't I still be able to have to select statements and shouldn't it work...which it doesn't

[buntine]

The problem is not in your code, its in your database design.

Inspect your DB, and then use the join SQL statement. There is no need to use two SQL queries.

Regards.

[javaNoobie]

You would probably have to do this..

<%
'this is the 1st recordset to retrieve all the blogs
Set rs1 = Server.CreateObject("ADODB.RecordSet")
rs1.Open "SELECT * FROM blog",objDC1, 0

'this is the 2nd recordset to retrieve the comments according to current blog entry that is being rendered
dim rs2
set rs2 = Server.CreateObject("ADODB.RecordSet")

while not rs1.eof
rs2.open "SELECT * FROM comments WHERE num=" & rs1("ID"), objDC1, 3
%>
'html and asp codes here.
<%
rs2.close
rs1.movenext
wend
%>

[buntine]

Like I said before, there is no need to use two SQL queries. Your method will require the server to execute a new query for every record returned!

What if 350 records are returned? Thats 351 SQL queries instead of 1! What would you rather?

This is part of the reason the relational database methodology was created.

Regards.

[chrismartz]

okay....I have changed my connection to Set rs = Server.CreateObject("ADODB.RecordSet")
rs.Open "SELECT b.*, c.* FROM blog b, comments c WHERE c.num = b.id;", objDC, 3 is this what you mean buntine? I then call all my information through rs.Fields and the name....but now it is repeating my last blog entry twice.....am i doing it wrong? checkout http://www.martzblog.tk to see what i mean

[javaNoobie]

buntine, that sql statement will only return blog entries which have comments. If certain blog entries do not have comments, they will not be shown. This, i believe is not what sirhcchris3 wants.
I think what u meant was a LEFT JOIN sql statement.
SELECT b.ID, b.blogmessage, b.blogtitle, b.udate, COUNT(c.num) As Total FROM blog b LEFT JOIN comments c ON c.num=b.id GROUP BY b.ID, b.blogmessage, b.blogtitle, b.udate

[buntine]

Ok, I see what your saying. But if thats the case, why are we selecting all fields from the comments table?

There must be a more efficient way than executing a new SQL query for each record. I will have a think about it.

Regards.

[javaNoobie]

I agree, there isnt a need to select everything from the comments table. The above sql statement should work fine since you only want the number of comments for that particular blog entry.

[chrismartz]

does everyone understand what i'm trying to do or should I explain?

[chrismartz]

Originally posted by javaNoobie
SELECT b.ID, b.blogmessage, b.blogtitle, b.udate, COUNT(c.num) As Total FROM blog b LEFT JOIN comments c ON c.num=b.id GROUP BY b.ID, b.blogmessage, b.blogtitle, b.udate I have put this in my connection and it outputs all the blogs but it doesn't show the whole thing....it cuts it off....checkout http://www.martzblog.tk ...also, the comments arent listed as the right amount of comments per entry!

[buntine]

The problem may lie in the design of your database. Have you set up primary and foreign keys? The design of your database is very importany to ensure entity and referential integrity.

You may want to send it to us. Just attach the atabase to a post if its not too large.

Regards.

[chrismartz]

here is the attached access file zipped!

[javaNoobie]

I've tested the sql statement. It works fine for the number of comments. However it only retrieves the 1st 255 characters from each blog entry..

[chrismartz]

any way to change to all the text showing and also...i need to chage the order of which they show!

[chrismartz]

Also, it is counting the amount of blog entries not the ammount of comments

[javaNoobie]

Well.. the sql statement works fine for me. As to why it is only retrieving the first 255 characters, I have no idea. So i guess we will have to use 2 sql statements. One to retrieve everything in blog table. The other will retrieve the blogID and the number of comments for that blog entry.

Dim rs1
set rs1 = Server.createObject("ADODB.Recordset")

rs1.open "SELECT * FROM blog", objDC, 0 'retrieve everything

Dim rs2
set rs2 = Server.createObject("ADODB.Recordset")

rs2.open "Select b.ID, count(c.num) As NumOfComments FROM blog b LEFT JOIN comments c ON c.num = b.id Group by b.ID", objDC, 0 'retrieve only the blog id and number of comments

[chrismartz]

I need to order the blog posts by ID from 1 to more than that...how would i do that?

[javaNoobie]

use ORDER BY


SELECT * FROM blog ORDER BY ID

SELECT b.ID, COUNT(c.num) As numOfComments FROM blog b LEFT JOIN comments c ON c.num = b.id GROUP BY b.ID ORDER BY b.ID

[chrismartz]

now i can't get anything to show with that connection...what to do? heres how i'm calling it:
<tr><td><table height="500" width="775"><br><tr><td><table align="left" class="B4"><% rs.moveFirst
for i = 0 to rs1.RecordCount
if not rs1.EOF Then
%><tr>
<td align="left" class="B3"><font class="title"><%= rs.Fields("blogtitle") %></font><b><font class="links"><br><%= rs.Fields("udate") %> - <a href="perm.asp?id=<%= rs.Fields("id") %>">Permalink</a> - <a href="comments.asp?id=<%= rs.Fields("id") %>">Comments (<%= rs1.RecordCount %>)</a></b><br><font class="blog">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<%= rs.Fields("blogmessage") %></font><br>
<center><br><br></center></td>
</tr>

<%
rs1.MoveNext
end if
next
%></table><center><td width="120" align="right" valign="top"><table align="center" cellspacing="0" cellpadding="0" class="b6"><tr><td><img alt="" src="images/links.gif"></td></tr><tr><td><img src="images/bkg_top.gif" alt=""></td></tr><tr><td background="images/bkg.gif"><center><a href="http://www.ihsaa.org/main.shtm" target="_new" style="color:#ffffff;">IHSAA</a><br><a href="http://www.warsaw.k12.in.us/wchs" target="_new" style="color:#ffffff;">High School</a><br></center></td></tr><tr><td><img src="images/bkg_bottom.gif" alt=""></td></tr></table><br><center><img alt="" src="images/bar2.gif"><br><br><a href="http://jigsaw.w3.org/css-validator/check/referer"><img border="0" alt="Valid CSS" src="images/css_valid.gif"></a><br><img alt="Powered by ASP" src="images/asp_powered.gif"><br><img alt="MDB Driven" src="images/mdb_driven.gif"><br>
</td></tr></table></center>

[javaNoobie]

<%
Dim rs1
set rs1 = Server.createObject("ADODB.Recordset")
rs1.open "SELECT * FROM blog ORDER BY ID", objDC, 0 'retrieve everything

Dim rs2
set rs2 = Server.createObject("ADODB.Recordset")
rs2.open "Select b.ID, count(c.num) As NumOfComments FROM blog b LEFT JOIN comments c ON c.num = b.id Group by b.ID ORDER BY b.ID", objDC, 0 'retrieve only the blog id and number of comments

If not rs1.eof Then
%>
<table>
<%
while not rs1.eof
%>
<tr>
<td align="left" class="B3"><font class="title"><%=rs1.Fields("blogtitle")%></font><br>
<b><font class="links"><br><%=rs1.Fields("udate")%> - <a href="perm.asp?id=<%=rs1.Fields("id")%>">Permalink</a> - <a href="comments.asp?id=<%=rs1.Fields("id")%>">Comments (<%=rs2.Fields("numOfComments")%> )</a></font></b><br>
<font class="blog"><%=rs1.Fields("blogmessage")%></font>
</td>
</tr>
<%
rs1.movenext
rs2.movenext
wend
%>
</table>
<%
End If
%>

[chrismartz]

great...that worked...thanks a bunch