Paging | Foreach | : The Official Microsoft ASP.NET Forums

Reply

Akhtar.Z

None

0 Points

10 Posts

Paging | Foreach |

Feb 12, 2013 12:05 PM|LINK

Hi

I am following Mike's tutorial on Efficient Paging Without The WebGrid and I am having diffuclties in finishing off using this method in my application as in trying to place the result variable (see below in bold) in the right place.

Any idead, many thanks!

Here is the code and error message:

@{

    var pageSize = 1;
    var totalPages = 0;
    var count = 0;
    var page = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
    var offset = (page -1) * pageSize;


    var db = Database.Open("AssetSystem");

    var sql = "Select Count(*) From AssetList ";

    count = (int)db.QueryValue(sql);
    totalPages = count/pageSize;
    if(count % pageSize > 1){
        totalPages += 0;
    }

      sql = "SELECT * FROM AssetList Order By AssetId desc OFFSET @0 ROWS FETCH NEXT @1 ROWS  ONLY";
    
    var result = db.Query(sql, offset, pageSize);

    var selectCommand = "SELECT * FROM AssetList Order By AssetId desc";
var searchTerm = "";

 if(!Request.QueryString["searchAssetNumber"].IsEmpty() ) {
        selectCommand = "SELECT * FROM AssetList WHERE AssetNumber = @0";
        searchTerm = Request.QueryString["searchAssetNumber"];
    }
 
    if(!Request.QueryString["searchLocation"].IsEmpty() ) {
        selectCommand = "SELECT * FROM AssetList WHERE Location LIKE @0";
        searchTerm = "%" + Request["searchLocation"] + "%";
    }
 
    var selectedData = db.Query(selectCommand, searchTerm);
    var table = new WebGrid(source: selectedData, rowsPerPage:2);


 
}
<!DOCTYPE html>
<html>
<head>
    
    <style type="text/css">
        table, th, td {
          border: solid 2px #bbbbbb;
          border-collapse: collapse;
          padding:5px;
         font-size: small;
        }
    </style>
</head>
<body>
    
    <br><br><a href="@Href("~/Admin")">Asset Management</a> >> Current Asset Register
    
    <br><h3>Edit Asset:</h2>
    
     <form method="get">
        <div>
        <label for="searchAssetNumber">Enter Asset Number:</label>
        <input type="text" size="10" name="searchAssetNumber" value="@Request.QueryString["searchAssetNumber"]" />
        <input type="Submit" value="Search Asset Number" /><br/>
       
        </div>
        <div>
          <br><label for="searchLocation">Enter Location:</label>
          <input type="text" size="10" name="searchLocation" value="@Request.QueryString["searchLocation"]" />
          <input type="Submit" value="Search Location" /><br/> <br>(Leave blank to list all items)<br>
            </div>
    
    
      </form>

    <p>Page @page of @totalPages</p>
    
    
    <br><table>
      <thead>
        <tr>
          <th>&nbsp;</th>
          <th>AssetNumber</th>
                   <th>ProductType</th>
           <th>Manufacturer</th>
            <th>Model</th>
            <th>SerialNumber</th>
            <th>DateRegistered</th>
            <th>Department</th>
            <th>Location</th>
         <th>Usage</th>
             <th>CurrentState</th>
             <th>Notes</th>
                </tr>
      </thead>
        
      <tbody>
        @foreach (var row in db.Query(result, selectCommand, searchTerm)) { 
          <tr>
            <td><a href="@Href("~/Asset/UpdateAsset.cshtml", row.AssetId)">Edit</a></td>
            <td>@row.AssetNumber</td>
            <td>@row.ProductType</td>
            <td>@row.Manufacturer</td>
            <td>@row.Model</td>
            <td>@row.SerialNumber</td>
                        <td>@row.DateRegistered</td>
            <td>@row.Department</td>
            <td>@row.Location</td>
            <td>@row.Usage</td>
            <td>@row.CurrentState</td>
            <td>@row.Notes</td>
          </tr>
        
        }
      </tbody>
    </table>
        
        @{
        for (var i = 1; i < totalPages + 1; i++){
            <a href="/Paging/@i">@i</a>
        }
    }
        
</body>
</html>

Error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

Compilation Error 
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. 

Compiler Error Message: CS1502: The best overloaded method match for 'WebMatrix.Data.Database.Query(string, params object[])' has some invalid arguments

Source Error:

 
 Line 110: Line 111: <tbody> Line 112: @foreach (var row in db.Query(result, selectCommand, searchTerm)) { Line 113: Line 114: <tr> 
Source File: c:\inetpub\wwwroot\ICTHelpdesk\Asset\ListAsset - Copy.cshtml    Line: 112

Reply

bmains

All-Star

29116 Points

5886 Posts

MVP

Re: Paging | Foreach |

Feb 12, 2013 01:05 PM|LINK

Hi,

As the error says, with this:

db.Query(result, selectCommand, searchTerm)

Should only use a signature of:

Database.Query(string, params object[])

This method only takes two parameters, the first being the string. Remove or move the result parameter, and it should be fine.

Brian

"Trust in the Lord and do what is good; dwell in the land and live securely. Take delight in the Lord, and He will give you your heart's desires" (Psalm 37: 3-4).

Edited by bmains on Feb 12, 2013 01:05 PM

Reply

Akhtar.Z

None

0 Points

10 Posts

Re: Paging | Foreach |

Feb 12, 2013 01:25 PM|LINK

Thank you for the response. I have removed the result parameter, it removes the error but the paging links doesn't work. If I move the result parameter, where would I move it to and how?

Reply

bmains

All-Star

29116 Points

5886 Posts

MVP

Re: Paging | Foreach |

Feb 12, 2013 05:11 PM|LINK

What are you using it for? Without that, I can't answer the question.

Brian

"Trust in the Lord and do what is good; dwell in the land and live securely. Take delight in the Lord, and He will give you your heart's desires" (Psalm 37: 3-4).

Reply

Akhtar.Z

None

0 Points

10 Posts

Re: Paging | Foreach |

Feb 14, 2013 12:22 PM|LINK

Sorry I am a complete novice. I am trying to add paging without a grid (using Mike's tutorial).

My application includes:

Searching through the records of the database
Editing a record (a link to different page)

And I want to include paging instead getting all the records in one page.

@foreach (var row in db.Query(selectCommand, searchTerm)) {

The queries selectCommand and searchTerms is used to do a search of the database. I am having diffculty inputting the result parameter to include paging. The paging works if I remove the above queries:

   @foreach (var row in result) {

But this would remove the search control. How would I combine the two controls/parameters?

Reply

GmGregori

Contributor

5438 Points

730 Posts

Re: Paging | Foreach |

Feb 16, 2013 06:15 PM|LINK

Akhtar.Z

Sorry I am a complete novice. I am trying to add paging without a grid (using Mike's tutorial).

My application includes:

Searching through the records of the database
Editing a record (a link to different page)

I have modified Mike's tutorial to support searching.

If you want to try the following code, you must download the Northwind.sdf database from one of the Mike's articles that use it:

@{
    var pageSize = 10;      // number of rows per page
    var totalPages = 0;
    var count = 0;
    var page = Request["page"].IsInt() ? Request["page"].AsInt() : 1;
    var company = Request["company"];
    var country = Request["country"];

    if (IsPost)
    {
        page = 1;
        company = Request.Form["comField"];
        country = Request.Form["couField"];
        Response.Redirect("Default?page=" + page + "&company=" + company + "&country=" + country);
    }

    var comParam = "%" + company + "%";
    var couParam = "%" + country + "%";
    var offset = (page -1) * pageSize;
    
    var db = Database.Open("Northwind");
    var sql = "Select Count(*) From Customers " +
                "WHERE CompanyName LIKE @0 AND Country LIKE @1 ";
    count = (int)db.QueryValue(sql, comParam, couParam);
    totalPages = count/pageSize;
    if(count % pageSize > 0){
        totalPages += 1;
    }
    sql = "Select CompanyName, ContactName, Address, City, Country From Customers " +
                "WHERE CompanyName LIKE @0 AND Country LIKE @1 " +
                "Order By CompanyName OFFSET @2 ROWS FETCH NEXT @3 ROWS ONLY;";
    
    var result = db.Query(sql, comParam, couParam, offset, pageSize);
}

<!DOCTYPE html>
<html>
    <head>
        <title>Select Customers</title>
        <style>
            body { font-family: verdana; font-size: 76%; }
            h2{ color: blue; }
            thead th{ color: blue; text-align: left; font-weight: bold; }
            th { text-align: left; font-weight: normal; }
            hr { margin: 10px 0 10px 0; }
        </style>
    </head>
    <body>
        <h2>View Customers</h2>
        <form method="post">
            <div id="selform">
                Company Name: <input type="text" name="comField" value="@company" />
                Country: <input type="text" name="couField" value="@country" />
                <input type="submit" />
            </div>
        </form>

        <hr>

        <p>Page @page of @totalPages</p>
        <table>
            <thead>
                <tr>
                    <th>Company Name</th>
                    <th>Contact Name</th>
                    <th>Address</th>
                    <th>City</th>
                    <th>Country</th>
                </tr>
            </thead>
            <tbody>
                @foreach(var row in result) {
                    <tr>
                        <th>@row.CompanyName</th>
                        <th>@row.ContactName</th>
                        <th>@row.Address</th>
                        <th>@row.City</th>
                        <th>@row.Country</th>
                    </tr>
                }
            </tbody>
        </table>

        <hr>
    
        @{
            for (var i = 1; i < totalPages + 1; i++){
                <a href="/Default?page=@i&company=@company&country=@country">@i</a>
            }
        }
    </body>
</html>

You could accomodate the code to your needs.

A suggestion: before to add new functions to your site, be sure that it works as you expect.

Edited by GmGregori on Feb 16, 2013 06:17 PM
Marked as answer by Angie xu - MSFT on Feb 19, 2013 01:32 AM

Paging | Foreach | RSS

5 replies

Akhtar.Z

Paging | Foreach |

bmains

Re: Paging | Foreach |

Akhtar.Z

Re: Paging | Foreach |

bmains

Re: Paging | Foreach |

Akhtar.Z

Re: Paging | Foreach |

GmGregori

Re: Paging | Foreach |

Akhtar.Z

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft