Combining pagination and refinement in webmatrix/razor

I am trying to combine two of the tutorials provide by Mike Brind. Seperately they work, but combined they don't, and I'm sure it's just a simple change I need to make.

I want to display a query over several pages, but if I change a search parameter, I want it to update. Here is my code (using Mike's books example):

@{
    Page.Title = "Paging Books";
    Page.Header = "View Books";

    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("Books");
    var sql = "Select Count(*) From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
           "Inner Join Categories on Books.CategoryId = Categories.CategoryId";
    count = (int)db.QueryValue(sql);
    totalPages = count/pageSize;        
    if(count % pageSize > 0){
        totalPages += 1;
    }
    sql = "Select Title, ISBN, Description, FirstName, LastName, Category From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
           "Inner Join Categories on Books.CategoryId = Categories.CategoryId " +
           "Order By BookId OFFSET @0 ROWS FETCH NEXT @1 ROWS  ONLY;";

    var result = db.Query(sql, offset, pageSize);

    var categories = db.Query("Select CategoryID, Category FROM Categories");
}

<form method="post" action="">
    Select Category:
    <select name="CategoryID">
        <option value="">-- View All Books --</option>
        @foreach(var c in categories){
            <option value="@c.CategoryId">@c.Category</option>
        }
    </select> <br />
    <input type="submit" name="Action" value="Select" />
</form>           

<p>Page @page of @totalPages</p>
@if(IsPost){
    foreach(var row in db.Query(sql, offset, pageSize, Request["CategoryID"])){
        <h2>@row.Title</h2>
        <p><strong>Author:</strong> @row.FirstName @row.LastName<br />
            <strong>ISBN:</strong> @row.ISBN <br/>
            <strong>Description:</strong> @row.Description <br />
            <strong>Category: </strong> @row.Category</p>
    }
}


@{
    for (var i = 1; i < totalPages + 1; i++){
        <a href="/Paging/@i">@i</a>
    }
}

This code currently shows all info in the correct pages, but when I change the Category, it doesn't just show results that match, it still shows all categories.

Your asp code is incomplete. You only have SQL code to return the full list of books. Nowhere does it include a statement to filter according to the category selected by the user.

Before you do:

var sql = "Select Count(*) From Books " + 
       "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
       "Inner Join Categories on Books.CategoryId = Categories.CategoryId";

you need to test if the request contains the attribute CategoryID with a non-empty value. If not empty define a different sql to filter on the category. Like so:

if(!Request.QueryString["CategoryID"].IsEmpty() ) {
    var sql = "Select Count(*) From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId " + 
           "Where Books.CategoryID=@0";
    count = (int)db.QueryValue(sql, Request.QueryString["CategoryID"])
}
else {
    var sql = "Select Count(*) From Books " + 
           "Inner Join Authors on Books.AuthorId = Authors.AuthorId "; // no need for 2nd inner join
    count = (int)db.QueryValue(sql);
}

The same issue arises in the second sql statement. Do similar to above adjusting for offset.