SQL returning 1 ROW then all ROWS after refresh

[fxsol]

on my default page I have the ASP page call to the DB

[tt](SET ROWCOUNT 1 SELECT Name,Comm,Time FROM Comments Order By Time desc)[/tt]

and return only one row from that db. When the user clicks the link, which takes them to another page which calls to the same DB

[tt](SELECT * FROM Comments ORDER BY Time desc)[/tt]

and returns all the rows.


THe problem is that when the user clicks the link on the main page to go to the next page it only returns 1 row instead of all of them. if I wait like 3 minutes and hit refresh it will return all rows.


I hope I explained this correctly....


thanks in advance

barry

 

[tlbroadbent]

Add Set Rowcount 0 before the second query or after the first.

SET ROWCOUNT 1
SELECT Name,Comm,Time FROM Comments Order By Time desc
SET ROWCOUNT 0

ASP is reusing the connection so the ROWCOUNT option remains set at 1 for the 2nd query. After three minutes or so, the connection is closed and the ROWCOUNT value is reset. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fxsol]

thank you very much...

 

[Guest_imported]

First off, use the TOP N syntax.
The SET ROWCOUNT applies not just to the immediately following sql statement but to all subsequent statements until the next SET ROWCOUNT is encountered or the connection is released.
The latter is I think the reason all the rows appear again after while.
Since TOP N became available (v7) SET ROWCOUNT is now only rarely needed.

 

[fxsol]

I get an error

"Incorrect syntax near the keyword 'TOP'. "

when I try use TOP.....I have Ver7 and SP3....hmm

 

[tlbroadbent]

If you want to use TOP, make sure the compatibility level of the database is set to 70. Use sp_dbcmptlevel dbname to check the level. If you cannot change from compatibility level 6.5 then you'll not be able to use TOP. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fxsol]

and to bother you with another question....where can I find sp_dbcmptlevel dbname at??


thx

 

[tlbroadbent]

sp_dbcmptlevel is a system stored procedure found in the master database in SQL Server version 7 and 2000. dbname is the name of the datbase you want to check. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.