limiting and using the TOP operator

Hi,

please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!

Anyhow,

I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)

I want to return the top 8 rows, the next 8 top rows and so on.

This is the sql string I have:
'retrieve pagination in order to construct the rest of the sql string
Dim startrec As Integer
If pageno = 1 Then
startrec = 0
Else
startrec = (pageno - 1) * pagesize
End If
' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page
' startrec is the record where I want to start in the db.
strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc"
noresults.text = strsql & " <br> " & searchwhat & searchfor

strsqlbasic is constructed elsewhere and is just the 'from X where y = var

Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?

thanks for any help.Assuming you've got a contigous list of ItemIDs then BETWEEN or just a simple (> x and < x+5) would work.|||the item id's are far from contiguous.
Actually, I found an elegant solution in the end, if not the best from a performance viewpoint.

Overload the dataadaptor fill with start record and number of records
myDA.Fill(myDS, startrec, pagesize, "Item")

If anyone has a nice solution that reduces the number of DB hits without cacheing the whole DB hit, I'd love to see it.

cheers,|||As it happens I do...

Always work solution, but not most perf pseudo code...

1. Create your own contigous row id and page that

delcare @.pageResults (rowid int identity (1,1), ... other colums you want to return)
insert into @.pageResults
<your query
Select * from @.pageResults where RowId between @.Start and @.End

Or a more fancy one that is great perf but more complicated to apply...

SELECT TOP 10 * FROM
(
SELECT TOP 10 MyPKID, <other cols>
FROM <table in question>
WHERE MyPKID IN (
SELECT TOP 11 MyPKID FROM <table in question> ORDER BY MyPKID)
ORDER BY MyPKID DESC) AS tmp
ORDER BY MyPKID

Takes a bit of looking at but it works really well. You need to play around with the TOP N numbers to set the start Id and the page size