Looped Recordset Problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Looped Recordset Problem...

Author

Topic

kwilliams

194 Posts

Posted - 2004-09-17 : 16:20:46

I have this Select statement that basically looks for users that have replied to a message board thread on our "Bulletin Board":


//**********BEGIN SELECT STATEMENT FOR NEW POSTS**********
var rsSubscribers_RE = Server.CreateObject("ADODB.Recordset");
rsSubscribers_RE.ActiveConnection = MM_strConn_Bulletin_STRING;
rsSubscribers_RE.Source = "SELECT * FROM dbo.Replies AS RE JOIN dbo.Members AS ME ON RE.Member_ID = ME.Member_ID JOIN dbo.Categories AS CA ON RE.Cat_ID = CA.Cat_ID WHERE RE.Subscription = 'Y' AND RE.Post_ID = '" + Post_ID + "' ORDER BY RE.DateTime DESC";
rsSubscribers_RE.CursorType = 0;
rsSubscribers_RE.CursorLocation = 2;
rsSubscribers_RE.LockType = 3;
rsSubscribers_RE.Open();
var rsSubscribers_RE_numRows = 0;
var Member_ID_RE = rsSubscribers_RE.Fields.Item("Member_ID").Value;
Response.Write(Member_ID_RE + "<br>");
//**********END SELECT STATEMENT FOR NEW POSTS**********
%>

It works great, but the results display duplicates if the user's entered more than one reply to the same thread. How can I add a filter that will only display one record for each user? I think that this is going to be a tricky one, but hopefully someone knows a simple solution already (fingers crossed). Hope to hear from someone soon...

KWilliams

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-17 : 16:38:02

Do you have any sort of timestamp column in the Replies table?

kwilliams

194 Posts

Posted - 2004-09-17 : 16:39:57

Yes I do; it's called "DateTime". How original huh. Why do you ask? How can I use DateTime to remove duplicate results?

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-17 : 16:48:35

I think this would work:

SELECT MIN(DateTime) FROM (SELECT * FROM dbo.Replies AS RE JOIN dbo.Members AS ME ON RE.Member_ID = ME.Member_ID JOIN dbo.Categories AS CA ON RE.Cat_ID = CA.Cat_ID WHERE RE.Subscription = 'Y' AND RE.Post_ID = '" + Post_ID + "' ORDER BY RE.DateTime DESC)

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-17 : 16:51:11

Actually that would only return the datetime value, sorry about that, but that should set you on the right track.

kwilliams

194 Posts

Posted - 2004-09-17 : 16:54:35

I gave it a try, but I got this error message:

Microsoft OLE DB Provider for SQL Server error '80040e21'

The requested properties cannot be supported.

/BulletinBoard/bgscripts/subscribers.asp, line 52

Line 52 refers to the Select statement. I was trying something else, but I'm not sure why it's not working. SQL Server automatically creates an alternate column name for the 2nd Member_ID entry like this:

SELECT RE.Member_ID, ME.Member_ID AS Expr1, FROM...

So I tried it in the ASP front-end page, but it didn't work. Any other suggestions?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-17 : 17:07:21

write a stored procedure, do not try to write/edit/troubleshoot an ad-hoc SQL statment like this. practice your SELECT in QA until you get it right, and then save it as a stored procedure.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-17 : 17:16:28

If you are just looking for a list of users, would DISTINCT not work for you??


rsSubscribers_RE.Source = "dbo.mySP '" & postId & "'"


/****************
Create Procedure dbo.mySP
@postId nvarchar(10)
As

SELECT Distcint RE.member_Id 
FROM dbo.Replies AS RE 
JOIN dbo.Members AS ME 
ON RE.Member_ID ME.Member_ID 
JOIN dbo.Categories AS CA 
ON RE.Cat_ID = CA.Cat_ID 
WHERE RE.Subscription = 'Y' AND RE.Post_ID = @postId
ORDER BY RE.DateTime DESC
go
***********/

Corey

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-17 : 17:18:06

I think this will work, but there's probably a better way than I'm coming up with. Jeff has a very good point though. Even if you don't create a stored procedure, you should test it in Query Analyzer first.

SELECT * FROM
dbo.Replies AS RE JOIN dbo.Members AS ME ON RE.Member_ID = ME.Member_ID
JOIN dbo.Categories AS CA ON RE.Cat_ID = CA.Cat_ID
WHERE RE.Subscription = 'Y' AND RE.Post_ID = '" + Post_ID + "'
AND RE.DateTime = SELECT MIN(DateTime) FROM
(SELECT * FROM
dbo.Replies AS RE JOIN dbo.Members AS ME ON RE.Member_ID = ME.Member_ID
JOIN dbo.Categories AS CA ON RE.Cat_ID = CA.Cat_ID
WHERE RE.Subscription = 'Y' AND RE.Post_ID = '" + Post_ID + "')

kwilliams

194 Posts

Posted - 2004-09-17 : 17:28:40

I agree about testing it in Query Analyzer, and that's what I've been doing so far. But when I take a working script, and put it into an ASP front-end, I get a "The requested properties cannot be supported" error message.

About using "DISTINCT", I tried that earlier today after reading an online article about it, but it didn't work for some reason. I tried it like this:
SELECT DISTINCT RE.Member_ID FROM dbo.Replies
..and like this:
SELECT DISTINCT * FROM dbo.Replies
...but neither of them worked. How should the suntax for DISTINCT be used in my Select statement? Am I doing something wrong in the way I'm trying to use it? Thanks again for all of your help.

kwilliams

194 Posts

Posted - 2004-09-17 : 17:47:48

Ok, I have a working Select statement that assigns a separate name for the 2nd Member_ID:

<%
rsSubscribers_RE.Source = SELECT RE.Member_ID, RE.Cat_ID, RE.Post_ID, RE.DateTime, RE.Topic, RE.Subscription, ME.Member_ID AS Expr1, ME.LName, ME.FName, ME.Email, CA.Category, CA.Cat_ID AS Expr2 FROM dbo.Replies RE INNER JOIN dbo.Members ME ON RE.Member_ID = ME.Member_ID INNER JOIN dbo.Categories CA ON RE.Cat_ID = CA.Cat_ID WHERE (RE.Subscription = 'Y') AND (RE.Post_ID = '" + Post_ID + "') AND (RE.Member_ID <> 'Expr1') ORDER BY RE.DateTime DESC";
var Expr1 = rsSubscribers_RE.Fields.Item("Expr1").Value;Response.Write("<strong>Expr1: </strong>" + Expr1 + "<br>");
%>

..so now if I can only loop non-duplicate results, I should be set. This is the code for my current loop that displays all of the duplicate records:

<%
//Repeat settings for rsSubscribers_RE
var Repeat2__numRows = -1;
var Repeat2__index = 0;
rsSubscribers_RE_numRows += Repeat2__numRows;
%>
<%
while ((Repeat2__numRows-- != 0) && (!rsSubscribers_RE.EOF)) {//Then
%>
<%
var Expr1 = rsSubscribers_RE.Fields.Item("Expr1").Value;
Response.Write("<strong>Expr1: </strong>" + Expr1 + "<br>");
<%
Repeat2__index++;
rsSubscribers_RE.MoveNext();
}//End if
}//End if
//*******End loop email to RE subscribers*******
%>

This results in duplicate results. So then I tried...

<%
while ((Repeat2__numRows-- != 0 || Expr1 <= 1) && (!rsSubscribers_RE.EOF)) {//Then
%>

...but that didn't work either. Do you think that I'm headed in the right direction? If so, is there a way to revise the loop above so that it filters duplicate results? Thanks again.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-21 : 00:36:53

have you tried using the having clause and group by?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 04:31:57

try this:


'SELECT RE.*, ME.*, CA.* 
FROM 	dbo.Replies AS RE 
	JOIN dbo.Members AS ME ON RE.Member_ID = ME.Member_ID 
	JOIN dbo.Categories AS CA ON RE.Cat_ID = CA.Cat_ID 
	INNER join (select max(DateTime) as DateTime, RepliesId 
		from dbo.Replies 
		WHERE 	RE.Subscription = 'Y' AND 
		RE.Post_ID = '" +Post_ID + "' 
		group by RepliesId) RE1 on RE.DateTime = RE1.DateTime and RE.RepliesId = RE1.RepliesId
ORDER BY RE.DateTime DESC'

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:18:39

Distinct would not be like (as you have not defined RE):
SELECT DISTINCT RE.Member_ID FROM dbo.Replies

Try:
SELECT DISTINCT Member_ID FROM dbo.Replies

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources