ASP. NET C# GridView Paging

Question

Hi good day to all. I have this web application for a certain company. In this web application there is a part that uses GridView to display records from the database and the way it is being displayed it is hard coded. I'll display my codes bellow.

          string SQLCommand = "SELECT LastName +', ' +FirstName + ' '+MiddleInitial AS  'Name', UserName + ' 'As 'User Name', StreetAddress FROM  CustomersMaster Where LastName   Like '%"+  SearchText.Text.Trim() + "%'";

            SqlCommand com = new SqlCommand(SQLCommand, con);

            SqlDataAdapter adp = new SqlDataAdapter(com);

            DataTable tbl = new DataTable();
            adp.Fill(tbl);

            AdminViewBuyersGV.DataSource = tbl;
            AdminViewBuyersGV.DataBind();

My problem is I want to use the "Paging" property of the GridView but when I activated the "Paging" property and then when I run it there's an error that says that "The data source does not support server-side data paging". I just want to know how to use paging when I already hard-coded it.

Is there a way on how to solve my problem. Thank You in Advance and God Bless! :)

Answer 1

did you write code for the AdminViewBuyersGV_PageIndexChanging routine?

Answer 2

Unless you have a good reason for binding your datasource in code, you might want to look at using a SqlDataSource control in the .aspx page. You can give it a parameter (which is safer than building a sql string the way you are) and it should support paging out of the box...

Answer 3

Make sure you are acting on data...

if (tbl.Rows.Count > 0)
{
      AdminViewBuyersGV.DataSource = tbl;
      AdminViewBuyersGV.DataBind();
}
else
 {
  // no records
 }

Answer 4

Ok, so full example using SqlDataSource (as Kendrick mentioned):

First you need to collect the parameters from your query, which it looks like you are already doing in a textbox somewhere. Something like this:

<asp:Label ID="lblLastName" runat="server" AssociatedControlID="tbLastName" Text="Last Name" ClientIDMode="Static" />
<asp:TextBox ID="tbLastName" runat="server" ClientIDMode="Static" />
<asp:Button ID="btnSearch" runat="server" ClientIDMode="Static" Text="Search" />

Now we need to take your Raw Inline SQL out of the code behind and move it into a SqlDataSource. We also want to get rid of the potential SQL Injection you have in your query by using a parameterized query instead :) In order to do that we need to hook up our TextBox to the parameter, but luckily DataSource controls allow us to do this without any code by using SelectParameters.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ContactManagerConnectionString %>"    
    SelectCommand="SELECT LastName + ', ' + FirstName + ' ' + MiddleInitial AS 'Name', UserName AS 'User Name', StreetAddress FROM CustomersMaster WHERE (LastName LIKE '%' + @SearchText + '%')">
<SelectParameters>
    <asp:ControlParameter ControlID="tbLastName" ConvertEmptyStringToNull="true" 
        Name="SearchText" PropertyName="Text" DbType="String" />
</SelectParameters>
</asp:SqlDataSource>

Once those two pieces are in place it is just a matter of setting up your grid the way you want, and setting the AllowPaging property to true.

<asp:GridView runat="server" AllowPaging="True" DataSourceID="SqlDataSource1" 
AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="Name" HeaderText="Name" ReadOnly="True" 
            SortExpression="Name" />
        <asp:BoundField DataField="User Name" HeaderText="User Name" 
            SortExpression="User Name" />
        <asp:BoundField DataField="StreetAddress" HeaderText="StreetAddress" 
            SortExpression="StreetAddress" />
    </Columns>
</asp:GridView>

VOILA!

Codeless paging*

* It is worth noting that this is pretty poor in terms of optimization as all the paging work is done on the Web Server. To really make this efficient you would want to employ some paging at the database level. Otherwise you will bring back every record every single time.