Efficient Paging Without The WebGrid is not working RSS

• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Efficient Paging Without The WebGrid is not working

  May 16, 2012 03:32 AM|LINK

  I followed Mike's Tutorial http://www.mikesdotnetting.com/Article/150/Web-Pages-Efficient-Paging-Without-The-WebGrid to create a my page but it will not work and I don't see anything wrong with the code. Maybe I am missing something.
  I am using SQL Express.

  Incorrect syntax near '.'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.           
  Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '.'.
  Source Error:

  Line 13: "Inner Join Productcategory on Products.Productcategoryid = Productcategory.Productcategoryid".ToList(); Line 14: Line 15: count = (int)db.QueryValue(sql); Line 16: totalPages = count/pageSize; Line 17: if(count % pageSize > 0){

   Here is my Code

  var pageSize = 3;
      var totalPages = 0;
      var count = 0;
      var page = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
      var offset = (page -1) * pageSize;
  
     var db = Database.Open("store");
     var sql = "Select Count(*) From Products "  +
  		"Inner Join Productcategory on Products.Productcategoryid = Productcategory.Productcategoryid".ToList();
  
      count = (int)db.QueryValue(sql);
      totalPages = count/pageSize;
      if(count % pageSize > 0){
          totalPages += 1;
      }
  
      sql = "Select Name, Price, Description, Categoryname From Products " +
                "Inner Join Productcategory on Products.Productcategoryid = Productcategory.Productcategoryid " +
                 "Order By Id OFFSET @0 ROWS FETCH NEXT @1 ROWS  ONLY";
  
     var result = db.Query(sql, offset, pageSize);
  }
  
  <p>Page @page of @totalPages</p>
      
      @foreach(var row in result){
         <h2>@row.name</h2>
         <p><strong>Author:</strong> @row.name @row.id<br />
         <strong>ISBN:</strong> @row.ISBN <br/>
         <strong>Description:</strong> @row.Description <br />
         <strong>Category: </strong> @row.Categoryname</p>
      }
      
      @{
          for (var i = 1; i < totalPages + 1; i++){
              <a href="/Paging/@i">@i</a>
          }
      }

• • Reply
  

  Mikesdotnett...

  All-Star

  154955 Points

  19872 Posts

  Moderator

  MVP

  Re: Efficient Paging Without The WebGrid is not working

  May 16, 2012 05:27 AM|LINK

  OFFSET and FETCH are ony supported by SQL CE. IF you are using SQL Express, you have to use ROW_NUMBER  in your SQL to manage paging of data: http://msdn.microsoft.com/en-us/library/ms186734.aspx

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Marked as answer by yousaid on May 17, 2012 08:25 PM