recordset paging frustration

[Microbe]

Hey folks, I am trying to do some recordset paging and am at my wits end. I have done heaps of research looking for a solution myself but no joy, I am sure someone here will be know the problem.

I have the following ASP code

rsMail.source = "SELECT count(*)  FROM email  WHERE recipientID = '" & session("ID") &  "' AND delete_flag <> '" & session("ID") & "' AND folder='inbox';"
rsMail.CursorLocation = adUseClient
rsMail.open()

response.write("total records = " & rsMail.fields(0) & "<br><br>")
rsMail.close()

rsMail.source = "SELECT email.*, members.* FROM email INNER JOIN members ON email.senderID = members.id WHERE recipientID = '" & session("ID") &  "' AND delete_flag <> '" & session("ID") & "' AND folder='inbox' order by emailID;"

response.write rsMail.source
'response.end

	'Set properties for recordset paging	
	rsMail.CursorLocation = adUseClient
	rsMail.PageSize = 2  	
rsMail.Open()

response.write ("<br><br> page count = " & rsMail.pagecount)

This produces the following output

total records = 6

SELECT email.*, members.* FROM email INNER JOIN members ON email.senderID = members.id WHERE recipientID = '12439' AND delete_flag <> '12439' AND folder='inbox' order by emailID;

page count = -1

Can anyone explain why pagecount = -1? It should be 3, right? 6 records with a page size of 2.

Nothing I have tried gets it right and if I can't get the pagecount right paging won't work. I have tried different cursors, and so many different things I am now starting to get muddled.

Suggestions please and heaps of thanks in advance.

Steve Davis

http://www.HaHaHa.com.au/weblog

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.

 

[johpje]

Replace the first query with

"SELECT count(*) FROM email INNER JOIN members ON email.senderID = members.id WHERE recipientID = '" & session("ID") &  "' AND delete_flag <> '" & session("ID") & "' AND folder='inbox' order by emailID;"

and see if you still get a rowcount of 6

cheers
Johpje

 

[Microbe]

That won't work anyway because you can't have an ORDER BY with the count(*)

However if I remove the ORDER BY, yes, I do get a count of 6

As an aside, if I take the SQL statement (that displays on the page and returns a pagecount of -1) and paste it straight into enterprise manager it works perfectly.


Steve Davis

http://www.HaHaHa.com.au/weblog

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.

 

[Microbe]

Well, I wish I could give myself a star

The problem seems to be something in the SQL statement that was only found through a bunch of trouble-shooting.

This did not work (section that broke it is bold)

SELECT [b]email.*, members.*[/b] FROM email INNER JOIN members ON email.senderID = members.id WHERE recipientID = '" & session("ID") &  "' AND delete_flag <> '" & session("ID") & "'"

however, this did work

select * from email INNER JOIN members ON email.senderID = members.id WHERE recipientID = '" & session("ID") &  "' AND delete_flag <> '" & session("ID") & "'"

With the first example email.PageCount always returned -1, but with the second example it returns the right value.

After tearing my hair out I am delighted, but not satisfied...yet. Can anyone explain why the first example returned the (apparently) identical recordset but pagecount value broke?

Steve Davis

http://www.HaHaHa.com.au/weblog

NOTE: This sig does not include any reference to voting, stars, or marking posts as helpful as doing so is cause for membership termination.