sqlvb.netselectpagination

Sql SELECT statement with paging vb.net

I have looked at many other similar questions and cant get this to work for my statement. This is the SELECT statement that currently works, and I need to add paging.

      "SELECT TOP 15 * FROM  tblEvents WHERE (dbo.fnEventSearchDistance(@CityLat, " & _
      "@CityLong, latitude, longitude) <= @Radius AND (StartDate >= GETDATE())) "

This is the closest I have been able to get.

      "SELECT ROW_NUMBER() OVER(ORDER BY StartDate) AS RowNum, * FROM tblEvents " & _
      "WHERE RowNum BETWEEN ((@PageNum - 1) * @PageSize + 1) " & _
      "AND (@PageNum * @PageSize) " & _
      "ORDER BY StartDate"

      comm2.Parameters.AddWithValue("@PageSize", 25)
      comm2.Parameters.AddWithValue("@PageNum", 2)

I need a SELECT Statement that rewrites the first SELECT statement to incorporate paging, where I can add pageSize and pageNum parameters

Solution

  • Assuming SQL Server 2008 and previous, you should try this:

    "SELECT col1, col2 FROM (SELECT ROW_NUMBER() OVER(ORDER BY StartDate) AS RowNum, * FROM tblEvents) AS E " & _
"WHERE RowNum BETWEEN ((@PageNum - 1) * @PageSize + 1) " & _
"AND (@PageNum * @PageSize) " & _
"ORDER BY StartDate"

    Note that I put col1, col2 on the select, you should put the columns you need there. For SQL Server 2012, this is quite simpler:

    "SELECT * FROM tblEvents ORDER BY StartDate " & _ 
"OFFSET (@PageNum - 1) * @PageSize ROWS FETCH NEXT @PageNum ROWS ONLY"