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
