Testing for individual empty Recordsets

[Dug]

Hi there,I've done a simple JOIN and was wondering if there is a way to test for individual empty recordsets?When I do

 If objRS("Comment") = "" Then ......

I get an exception occurred error. My code is below:

objRS.Open "SELECT * FROM blog.tblcomments, blog.tblblog", objConn, 1If objRS.EOF Then		Response.Write "Sorry, no data found."Else	Do Until objRS.EOF		Response.Write "<h3><p>" & objRS("Title") & "</h3></p>"		Response.Write "<p>" & objRS("Blog") & "</p>"			If objRS("CommentTitle") = objRS("Title") Then				Response.Write "<p>" & objRS("Comment") & "</p>"			End If		objRS.MoveNext	Loop	End If

Is there a better way to do this? For just now, I will put this live, but it assumes there is data in both tables. CheersDug

[Dug]

Actually ignore that, I've found a solution:

Set objRS = Server.CreateObject("ADODB.Recordset")Set rsComment = Server.CreateObject("ADODB.Recordset")Set rsBlog = Server.CreateObject("ADODB.Recordset")rsComment.Open "SELECT * FROM tblcomments", objConn, 1rsBlog.Open "SELECT * FROM tblblog", objConn, 1objRS.Open "SELECT * FROM tblblog, tblcomments", objConn, 1If rsComment.EOF Then	Do While Not rsBlog.EOF		Response.Write "<p><h3>" & rsBlog("Title") & "</h3></p>"		Response.Write "<p>" & rsBlog("Blog") & "</p>"				%>				<p><a href="blog/AddComment.asp?Title=<%= rsBlog("Title") %>">Add Comment</a></p>				<%		rsBlog.MoveNext	Loop	Else	Do While Not objRS.EOF		Response.Write "<p><h3>" & objRS("Title") & "</h3></p>"		Response.Write "<p>" & objRS("Blog") & "</p>"			If objRS("Title") = objRS("CommentTitle") Then				Response.Write "<p>" & objRS("Comment") & "</p>"			End If					%>				<p><a href="blog/AddComment.asp?Title=<%= objRS("Title") %>">Add Comment</a></p>				<%						objRS.MoveNext	LoopEnd If%>

One more question.... when I add a second comment to the same post, it duplicates the post with the comment instead of displaying the comments under the one post.... would there be any solution to this?CheersDug

[justsomeguy]

You'll need to check which is the last post that was printed and don't print it again if necessary, or you can also just get the posts, print each post, and when you print the post get the comments for it and print those.

[Dug]

Cheers man. Would you be able to provide an example of what you've described? I know a lot of the ASP syntax off by heart, and can usually visualize what I want to do, but in this case I'm struggling.CheersDug

[justsomeguy]

Just keep a variable that contains an ID number or something for the last post that you printed. If the post you're looking at now has the same ID as the variable then don't print it.

[Dug]

Hiya,Ok I've worked out that it was a problem with my SQL statement "SELECT * FROM tblblog, tblcomments".To that effect I've stuck to the 2 separate recordsets rsBlog and rsComment. I've thought it would be logical to do this:

Do While Not rsBlog.EOF 		Response.Write "<p><h3>" & rsBlog("Title") & "</h3></p>"		Response.Write "<p>" & rsBlog("Blog") & "</p>"			Do While Not rsComment.EOF				If rsBlog("Title") = rsComment("CommentTitle") Then					Response.Write "<p>" & rsComment("Comment") & "</p>"					rsComment.MoveNext				End If						Loop									%>				<p><a href="blog/AddComment.asp?Title=<%= rsBlog("Title") %>">Add Comment</a></p>				<%					Response.Write "<p> </p><hr /><p> </p>"			rsBlog.MoveNext	Loop'etc......

but IIS gives a script timeout error. Is there a better way to do this?CheersDug

[justsomeguy]

It doesn't look like you're getting new comments for each blog.It doesn't look like there's one in the code you posted, but are you sure there's not an infinite loop that is causing the script to timeout?

[Dug]

Sorry I've taken ages to reply.............Ok, progress. I've decided to do my site in ASP.NET now. I've got the blog working - it's displaying the correct comments with the correct post, however it keeps on posting the blog again with the next comment, instead of having the comments all appearing under one blog. Here is my code:

<script runat="server">	Sub Page_Load(obj as object, e as eventargs)			dim objConn as new OdbcConnection _			(ConfigurationSettings.AppSettings("RemoteBlog"))			dim objCmd as OdbcCommand = new OdbcCommand _			("SELECT * FROM tblblog LEFT JOIN tblcomments ON tblblog.Title=tblcomments.CommentTitle", objConn)		dim objReader as OdbcDataReader			try			objCmd.Connection.Open()			objReader = objCmd.ExecuteReader()		catch ex as OdbcException			lblMessage.Text = ex.Message		end try			DataGrid1.DataSource = objReader		DataGrid1.DataBind()			objCmd.Connection.Close()	 				End Sub</script>

I'm very nearly there, so just got to get this sorted. Oh, the link to the page I'm doing this on is:BlogCheersDug

[justsomeguy]

It's the same issue - a SQL join creates every possible combination, so it matches up all blog posts with all comments, it's a multiplication operation, you end up with one row for each matching comment/blog combination. The answer is the same as before. You need to use a variable to keep track of the last blog that you printed. You compare that variable with the current blog each time through the loop. If the variable is the same as the current blog then you've already printed this blog, so just print the comment. If the variable is not the same as the current blog then you're looking at a new blog, so print the blog first, then the comment.