title: Infinite scroll Sql statement to get next records
body: <p>I am trying to do infinite scroll in my .NET website, using javascript and the following sql statement (sql server 2008). This sql gets the first 10 rows, but my javascript causes the sql to execute each time the user scrolls to the bottom of the page, and each time, it pulls the same (first 10) records, but I want it to pull the NEXT 10 records, each time user scrolls to the bottom. How do I use this sql and row_number to get the NEXT 10 rows, each time user scrolls to bottom of page?</p>
  
  <pre><code>SELECT * FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY DateTime) As RowNum, 
    * From Topic) As a 
  WHERE RowNum 
  BETWEEN 1 AND 10
  </code></pre>
  
  <p>Here is the javascript:</p>
  
  <pre><code>&lt;script type="text/javascript"&gt;
          $(document).ready(function () {
  
         function lastPostFunc() {
             $('#divPostsLoader').html('&lt;img src="images/bigLoader.gif"&gt;');
  
             //send a query to server side to present new content
             $.ajax({
                 type: "POST",
                 url: "Default.aspx/Foo",
                 data: "{}",
                 contentType: "application/json; charset=utf-8",
                 dataType: "json",
                 success: function (data) {
  
                     if (data != "") {
                         $('.divLoadData:last').after(data.d);
                     }
                     $('#divPostsLoader').empty();
                 }
  
             })
         };
  
         //When scroll down, the scroller is at the bottom with the function below and fire the lastPostFunc function
         $(window).scroll(function () {
             if ($(window).scrollTop() == $(document).height() - $(window).height()) {
                 lastPostFunc();
             }
         });
  
      });
  &lt;/script&gt;
  </code></pre>
  
  <p>I now have the above sql in stored procedure:</p>
  
  <pre><code>&lt;System.Web.Services.WebMethod()&gt;
  Public Shared Function Foo() As String
      Dim strConn As String = "Data Source="
      Dim conn As New SqlConnection(strConn)
      Dim Cmd As New SqlCommand("InfiniteScroll", conn)
      Cmd.CommandType = CommandType.StoredProcedure
      Dim DA As New SqlDataAdapter(Cmd)
      Dim DS As New DataSet()
      DA.Fill(DS, "RefologyForumTopic")
      Dim getPostsText As New StringBuilder()
      Dim dv As DataView = DS.Tables(0).DefaultView
  
      For Each myDataRow As DataRowView In dv
          getPostsText.AppendFormat("price: {0}&lt;/br&gt;", myDataRow("Topic"))
          getPostsText.AppendFormat("description: {0}&lt;/br&gt;&lt;/p&gt;", myDataRow("UserID"))
      Next
  
      getPostsText.AppendFormat("&lt;div style='height:15px;'&gt;&lt;/div&gt;")
      Return getPostsText.ToString()
  End Function
  </code></pre>