If I bind it to a SqlDataSource in Markup, it pages perfectly.
If I bind ot to a DataAdapter in CodeBehind, the pager doesn't work.
Why not?
Before I post too much code, is there any issue in principal which would stop the pager working if the GV was filled via a DataAdapter?
Here's the Adapter:
SqlDataAdapter sda1 = new SqlDataAdapter("SiteSearch", conn);
sda1.SelectCommand.Parameters.Add("@textbox", SqlDbType.Text).Value = strSearch;
sda1.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt1 = new DataTable();
sda1.Fill(dt1);
if (dt1.Rows.Count > 0)
{
resultsGridView.DataSource = dt1;
resultsGridView.DataBind();
dt1.Dispose();
}
It is in the Page_Load handler. It fills the GV with exactly the same data as a SqlDataSource MarkUp approach (the SQL from the DataSource has - in the Adapter method - been moved to a Stored Procedure), so BOTH methods are finding the right data and filling the GV perfectly. But, while paging works perfectly in theSqlDataSource method, in the C# DataAdapter method, nothing happens at all when a new page number is clicked.
Thank you for sticking with me on this: banging my head against a brick wall, here.
I've changed the handler to yours, as per your advice, but still nothing. The Pager reports 4 pages but clicking 2, 3 or 4 does nothing: no page change.
banksidepoet
Participant
774 Points
862 Posts
Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 29, 2013 12:29 PM|LINK
Hi.
I have a GridView.
If I bind it to a SqlDataSource in Markup, it pages perfectly.
If I bind ot to a DataAdapter in CodeBehind, the pager doesn't work.
Why not?
Before I post too much code, is there any issue in principal which would stop the pager working if the GV was filled via a DataAdapter?
Here's the Adapter:
SqlDataAdapter sda1 = new SqlDataAdapter("SiteSearch", conn); sda1.SelectCommand.Parameters.Add("@textbox", SqlDbType.Text).Value = strSearch; sda1.SelectCommand.CommandType = CommandType.StoredProcedure; DataTable dt1 = new DataTable(); sda1.Fill(dt1); if (dt1.Rows.Count > 0) { resultsGridView.DataSource = dt1; resultsGridView.DataBind(); dt1.Dispose(); }It is in the Page_Load handler. It fills the GV with exactly the same data as a SqlDataSource MarkUp approach (the SQL from the DataSource has - in the Adapter method - been moved to a Stored Procedure), so BOTH methods are finding the right data and filling the GV perfectly. But, while paging works perfectly in theSqlDataSource method, in the C# DataAdapter method, nothing happens at all when a new page number is clicked.
Here's the Pager MarkUp from the GV:
Any ideas would be greatly appreciated. I'll post full code if "in principal" there are no obvious issues.
Thanks.
oned_gk
All-Star
31737 Points
6481 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 29, 2013 02:37 PM|LINK
vinz
All-Star
127026 Points
17936 Posts
MVP
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 29, 2013 02:47 PM|LINK
Since you manually bind your gridview with data from DataTable then you also need manually handle the paging functionality like what isdemonstrated here: http://deepak-sharma.net/2012/10/25/gridview-paging-and-sorting-in-asp-net-without-using-a-datasource/
Also, you might want to consider using custom paging: http://geekswithblogs.net/dotNETvinz/archive/2012/08/10/gridview-custom-paging-with-linq.aspx
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
banksidepoet
Participant
774 Points
862 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 29, 2013 02:49 PM|LINK
oned_gk,
Thanks for your reply but there is no such variable as "PageIndex" allowed withing this handler.
banksidepoet
Participant
774 Points
862 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 30, 2013 08:54 AM|LINK
vinz
Thank you for your response.
This still doesn't work for me. I've followed the code in the first link.
Here's the complete code. Can you see any obvious errors, here?
MarkUp
<%@ Page Title="" Language="C#" MasterPageFile="~/master-pages/home.Master" AutoEventWireup="true" CodeBehind="search_results_experimental.aspx.cs" Inherits="Website_1.search_results" %> <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server"></asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="Main" runat="server"> <asp:Image ID="HeaderImage" runat="server" CssClass="HeadImage" ImageUrl="~/assets/images/headers/pages/search_page.jpg" /> <p class="sectionHead">Search Results</p> <div id="centreTextArea"> <asp:GridView ID="resultsGridView" runat="server" AutoGenerateColumns="False" Width="500px" CssClass="productGV" BorderStyle="Solid" BorderWidth="1px" BorderColor="#CCCCCC" AlternatingRowStyle-BackColor="#FAFAFA" AlternatingRowStyle-CssClass="ARStyle" ForeColor="#505050" HeaderStyle-BackColor="#EEEEEE" HeaderStyle-ForeColor="#1E5E9F" HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" HeaderStyle-VerticalAlign="Middle" HeaderStyle-Height="30px" HeaderStyle-CssClass="prodGVHeader" CellPadding="4" RowStyle-BorderStyle="Solid" RowStyle-BorderWidth="1px" RowStyle-BorderColor="#CCCCCC" RowStyle-Height="50px" DataKeyNames="product_id" OnRowCommand="resultsGridView_RowCommand" OnRowCreated="resultsGridView_RowCreated" OnRowDataBound="resultsGridView_RowDataBound" AllowSorting="True" AllowPaging="True" onpageindexchanging="resultsGridView_PageIndexChanging" onsorting="resultsGridView_Sorting"> <AlternatingRowStyle BackColor="#FAFAFA" CssClass="ARStyle"></AlternatingRowStyle> <Columns> <asp:BoundField DataField="product_id" Visible="true" /> <asp:BoundField DataField="reference" HeaderText="Code" SortExpression="reference" ItemStyle-HorizontalAlign="Center" ItemStyle-VerticalAlign="Middle" ItemStyle-Height="25px" ItemStyle-Width="70px"> <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Height="25px" Width="120px" CssClass="prodGVItemStyle"></ItemStyle> </asp:BoundField> <asp:TemplateField HeaderText="Product" SortExpression="title"> <ItemTemplate> <asp:HyperLink ID="productTitleHyperLink" runat="server" Text='<%# Eval("title") %>' NavigateUrl='<%# string.Format("/cognex-vision-systems/product-detail.aspx?product_id={0}", Eval("product_id")) %>'></asp:HyperLink> </ItemTemplate> <ItemStyle CssClass="prodGVProd prodGVItemStyle" ForeColor="#1E5E9F" Height="25px" HorizontalAlign="Center" VerticalAlign="Middle" Width="160px" /> </asp:TemplateField> <asp:TemplateField HeaderText="Price"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text="P.O.A." Width="60px"></asp:Label> </ItemTemplate> <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" CssClass="prodGVItemStyle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Quantity"> <ItemTemplate> <asp:TextBox ID="quantityTextBox" runat="server" Width="30px" CssClass="prodGVTextBox"></asp:TextBox><br /> <asp:CompareValidator ID="CompareValidator2" runat="server" ControlToValidate="quantityTextBox" Display="Dynamic" ErrorMessage="> 0" ForeColor="Red" Operator="GreaterThan" Type="Integer" ValueToCompare="0" ValidationGroup="quantityTextBoxVal">> 0</asp:CompareValidator> </ItemTemplate> <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="60px" CssClass="prodGVItemStyle" /> </asp:TemplateField> <asp:ButtonField ButtonType="Image" Text="Add to Quote" ImageUrl="~/assets/images/buttons/addtocart_small.gif" CommandName="addToQuote"> <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="100px" CssClass="prodGVItemStyle" /> </asp:ButtonField> </Columns> <HeaderStyle HorizontalAlign="Center" VerticalAlign="Middle" BackColor="#EEEEEE" CssClass="prodGVHeader" ForeColor="#003399" Height="30px"></HeaderStyle> <PagerSettings Mode="NumericFirstLast" Position="TopAndBottom" /> <RowStyle BorderColor="#CCCCCC" BorderWidth="1px" BorderStyle="Solid"></RowStyle> </asp:GridView> <br /> <br /> </div> </asp:Content>C# Code Behind
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Web.Security; using System.Web.Configuration; namespace Website_1 { public partial class search_results : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // If a user comes direct to this page rather than from a search page // the QueryString will be empty, so... if (String.IsNullOrEmpty(Request.QueryString["SearchInput"])) { Response.Redirect("~/Default.aspx"); } if (!IsPostBack) { BindGridView(); } } protected void resultsGridView_RowCreated(Object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { ImageButton addButton = (ImageButton)(e.Row.Cells[5].Controls[0]); addButton.CommandArgument = e.Row.RowIndex.ToString(); TextBox quantityTB = (TextBox)e.Row.FindControl("quantityTextBox"); quantityTB.Text = "0"; quantityTB.Attributes.Add("onfocus", "if(this.value.substr(0, 1) == '0') this.value = '';"); quantityTB.Attributes.Add("onblur", "javascript: if(this.value=='') this.value='0';"); } } // AT EACH "Add To Quote" BUTTON CLICK... protected void resultsGridView_RowCommand(object sender, GridViewCommandEventArgs e) { GridViewRow row = ((System.Web.UI.WebControls.GridView)(sender)).Rows[int.Parse(e.CommandArgument.ToString())]; string idString = row.Cells[0].Text; int id = Convert.ToInt32(idString); string codeString = row.Cells[1].Text; int quantity = Convert.ToInt32(((TextBox)row.FindControl("quantityTextBox")).Text); if (quantity < 1) { Response.Redirect(Request.RawUrl); } string title = ((HyperLink)row.FindControl("productTitleHyperLink")).Text; string connectionString = WebConfigurationManager.ConnectionStrings["*****"].ConnectionString; using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd2; string commandString2 = "SELECT manufacturers.name FROM manufacturers INNER JOIN products ON manufacturers.manufacturer_id = products.manufacturer_id WHERE (products.product_id = @product_id)"; cmd2 = new SqlCommand(commandString2, conn); cmd2.Parameters.AddWithValue("product_id", id); SqlCommand cmd3; string commandString3 = "SELECT supplier_list_price, supplier_discount FROM products WHERE (product_id = @product_id)"; cmd3 = new SqlCommand(commandString3, conn); cmd3.Parameters.AddWithValue("product_id", id); // Open Connection conn.Open(); // Get Meta information and assign string man = Convert.ToString(cmd2.ExecuteScalar()); // Check if this user is part of ITS Admin or a Customer // (destination of added products is different for each!) if (User.IsInRole("its_teksis-admin-full")) { SqlCommand cmd = new SqlCommand("InsertProductsAdmin", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; //cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier); //cmd.Parameters[0].Value = guidStringUser; //cmd.Parameters.Add("@Date", SqlDbType.DateTime); //cmd.Parameters[1].Value = Session["Date"]; // As I have commented out the lines above, I have renumbered the Parameters below... // ...starting from 0. You'll need to renumber them again if you uncomment the lines above. cmd.Parameters.Add("@SessionId", SqlDbType.NVarChar); cmd.Parameters[0].Value = Session["MyID"].ToString(); cmd.Parameters.Add("@OrderId", SqlDbType.Int); cmd.Parameters[1].Value = Session["OrderId"]; cmd.Parameters.Add("@ProductId", SqlDbType.Int); cmd.Parameters[2].Value = id; cmd.Parameters.Add("@Manufacturer", SqlDbType.NVarChar); cmd.Parameters[3].Value = man; cmd.Parameters.Add("@Title", SqlDbType.NVarChar); cmd.Parameters[4].Value = title; cmd.Parameters.Add("@Code", SqlDbType.NVarChar); cmd.Parameters[5].Value = codeString; cmd.Parameters.Add("@Quantity", SqlDbType.Int); cmd.Parameters[6].Value = quantity; SqlDataReader rdr3 = cmd3.ExecuteReader(); while (rdr3.Read()) { cmd.Parameters.Add("@Supplier_List_Price", SqlDbType.Float); cmd.Parameters[7].Value = rdr3["supplier_list_price"]; cmd.Parameters.Add("@Supplier_Discount", SqlDbType.Int); cmd.Parameters[8].Value = rdr3["supplier_discount"]; double SLP = Convert.ToDouble(rdr3["supplier_list_price"]); int SD = Convert.ToInt32(rdr3["supplier_discount"]); cmd.Parameters.Add("@Unit_ITS_Cost", SqlDbType.Float); cmd.Parameters[9].Value = SLP - ((SLP * SD) / 100); double UIC = SLP - ((SLP * SD) / 100); cmd.Parameters.Add("@Total_ITS_Cost", SqlDbType.Float); cmd.Parameters[10].Value = (SLP - ((SLP * SD) / 100)) * quantity; double TIC = UIC * quantity; cmd.Parameters.Add("@ITS_Discount", SqlDbType.Int); cmd.Parameters[11].Value = 0; // As, at this stage in the order process, ITS Discount = 0%, // this is Supplier List Price cmd.Parameters.Add("@Unit_Customer_Cost", SqlDbType.Float); cmd.Parameters[12].Value = SLP; // As, at this stage in the order process, ITS Discount = 0%, // this is just Supplier List Price * Quantity cmd.Parameters.Add("@Total_Customer_Cost", SqlDbType.Float); cmd.Parameters[13].Value = SLP * quantity; double TCC = SLP * quantity; // As, at this stage in the order process, ITS Discount = 0%, // this is simply SLP (what ITS charge the customer) - UIC (SLP after discount) cmd.Parameters.Add("@ITS_Profit", SqlDbType.Float); cmd.Parameters[14].Value = TCC - TIC; } rdr3.Close(); cmd.Parameters.Add("@Active", SqlDbType.Char); cmd.Parameters[15].Value = "Y"; cmd.ExecuteNonQuery(); conn.Close(); } else { SqlCommand cmd = new SqlCommand("InsertProducts", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; //cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier); //cmd.Parameters[0].Value = guidStringUser; //cmd.Parameters.Add("@Date", SqlDbType.DateTime); //cmd.Parameters[1].Value = Session["Date"]; // As I have commented out the lines above, I have renumbered the Parameters below... // ...starting from 0. You'll need to renumber them again if you uncomment the lines above. cmd.Parameters.Add("@SessionId", SqlDbType.NVarChar); cmd.Parameters[0].Value = Session["MyID"].ToString(); cmd.Parameters.Add("@ProductId", SqlDbType.Int); cmd.Parameters[1].Value = id; cmd.Parameters.Add("@Manufacturer", SqlDbType.NVarChar); cmd.Parameters[2].Value = man; cmd.Parameters.Add("@Title", SqlDbType.NVarChar); cmd.Parameters[3].Value = title; cmd.Parameters.Add("@Code", SqlDbType.NVarChar); cmd.Parameters[4].Value = codeString; cmd.Parameters.Add("@Quantity", SqlDbType.Int); cmd.Parameters[5].Value = quantity; SqlDataReader rdr3 = cmd3.ExecuteReader(); while (rdr3.Read()) { cmd.Parameters.Add("@Supplier_List_Price", SqlDbType.Float); cmd.Parameters[6].Value = rdr3["supplier_list_price"]; cmd.Parameters.Add("@Supplier_Discount", SqlDbType.Int); cmd.Parameters[7].Value = rdr3["supplier_discount"]; double SLP = Convert.ToDouble(rdr3["supplier_list_price"]); int SD = Convert.ToInt32(rdr3["supplier_discount"]); cmd.Parameters.Add("@Unit_ITS_Cost", SqlDbType.Float); cmd.Parameters[8].Value = SLP - ((SLP * SD) / 100); double UIC = SLP - ((SLP * SD) / 100); cmd.Parameters.Add("@Total_ITS_Cost", SqlDbType.Float); cmd.Parameters[9].Value = (SLP - ((SLP * SD) / 100)) * quantity; double TIC = UIC * quantity; cmd.Parameters.Add("@ITS_Discount", SqlDbType.Int); cmd.Parameters[10].Value = 0; // As, at this stage in the order process, ITS Discount = 0%, // this is Supplier List Price cmd.Parameters.Add("@Unit_Customer_Cost", SqlDbType.Float); cmd.Parameters[11].Value = SLP; // As, at this stage in the order process, ITS Discount = 0%, // this is just Supplier List Price * Quantity cmd.Parameters.Add("@Total_Customer_Cost", SqlDbType.Float); cmd.Parameters[12].Value = SLP * quantity; double TCC = SLP * quantity; // As, at this stage in the order process, ITS Discount = 0%, // this is simply SLP (what ITS cjharge the customer) - UIC (SLP after discount) cmd.Parameters.Add("@ITS_Profit", SqlDbType.Float); cmd.Parameters[13].Value = TCC - TIC; } rdr3.Close(); cmd.Parameters.Add("@Active", SqlDbType.Char); cmd.Parameters[14].Value = "Y"; cmd.ExecuteNonQuery(); conn.Close(); } } if (User.IsInRole("its_teksis-admin-full ")) { string lastPage = Convert.ToString(Request.Url); // Gets the full URL (http://...) of the current page. //string lastPage = HttpContext.Current.Request.Url.AbsoluteUri; //if (HttpContext.Current.Request.Url.Query.Length > 0) // lastPage = lastPage.Replace(HttpContext.Current.Request.Url.Query, string.Empty); Session["lastProductPage"] = lastPage; Response.Redirect("/admin/quotes.aspx?returnUrl=" + Session["lastProductPage"] + "&order=" + Session["OrderId"]); } else { string lastPage = Convert.ToString(Request.Url); // Gets the full URL (http://...) of the current page. //string lastPage = HttpContext.Current.Request.Url.AbsoluteUri; //if (HttpContext.Current.Request.Url.Query.Length > 0) // lastPage = lastPage.Replace(HttpContext.Current.Request.Url.Query, string.Empty); Session["lastProductPage"] = lastPage; Response.Redirect("/view_quote.aspx?returnUrl=" + Session["lastProductPage"]); } } protected void resultsGridView_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Cells[0].Visible = false; } } protected void resultsGridView_PageIndexChanging(object sender, GridViewPageEventArgs e) { resultsGridView.PageIndex = e.NewPageIndex; resultsGridView.DataBind(); } private void BindGridView() { string connectionString = WebConfigurationManager.ConnectionStrings["*****"].ConnectionString; SqlConnection conn = new SqlConnection(connectionString); conn.Open(); string sortDirection = null; string sortExpression = null; string strSearch = Request.QueryString["SearchInput"]; SqlDataAdapter sda1 = new SqlDataAdapter("SiteSearch", conn); sda1.SelectCommand.Parameters.Add("@textbox", SqlDbType.Text).Value = strSearch; sda1.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); sda1.Fill(ds); if (ds.Tables.Count > 0) { DataView dv = ds.Tables[0].DefaultView; if (ViewState["SortDirection"] != null) { sortDirection = ViewState["SortDirection"].ToString(); } if (ViewState["SortExpression"] != null) { sortExpression = ViewState["SortExpression"].ToString(); dv.Sort = string.Concat(sortExpression, " ", sortDirection); } resultsGridView.DataSource = dv; resultsGridView.DataBind(); conn.Close(); } } protected void resultsGridView_Sorting(object sender, GridViewSortEventArgs e) { if (ViewState["SortDirection"] == null || ViewState["SortExpression"].ToString() != e.SortExpression) { ViewState["SortDirection"] = "ASC"; resultsGridView.PageIndex = 0; } else if (ViewState["SortDirection"].ToString() == "ASC") { ViewState["SortDirection"] = "DESC"; } else if (ViewState["SortDirection"].ToString() == "DESC") { ViewState["SortDirection"] = "ASC"; } ViewState["SortExpression"] = e.SortExpression; BindGridView(); } } }I understand this is a lot of code to wade through. Many thanks to you if you're prepared to search for the issue.
vinz
All-Star
127026 Points
17936 Posts
MVP
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 30, 2013 09:05 AM|LINK
You need to bind the grid with the DataSource before you call DataBind(). So try like this:
protected void resultsGridView_PageIndexChanging(object sender, GridViewPageEventArgs e){ resultsGridView.PageIndex = e.NewPageIndex; BindGridView(); }MessageBox Controls for WebForms | Blog | Twitter | Linkedin
banksidepoet
Participant
774 Points
862 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 30, 2013 10:46 AM|LINK
Thank you for sticking with me on this: banging my head against a brick wall, here.
I've changed the handler to yours, as per your advice, but still nothing. The Pager reports 4 pages but clicking 2, 3 or 4 does nothing: no page change.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Jan 31, 2013 12:19 AM|LINK
Hi,
You can try to use dv.ToTable() instead of a pure "dv" to bind to GridView.
banksidepoet
Participant
774 Points
862 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Feb 01, 2013 11:53 AM|LINK
Decker-Dong,
Thank you for your response.
As above, though: 4 pages reported - clicking on 2, 3 or 4 does nothing.
Can you spot anything else?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Quick help needed... GridView Pager doesn't work = C# DataAdapter v SqlDataSource
Feb 01, 2013 12:07 PM|LINK
Hi,
You haven't set the DataSource as the data contents to GridView. Please try this:
protected void resultsGridView_PageIndexChanging(object sender, GridViewPageEventArgs e) { resultsGridView.PageIndex = e.NewPageIndex; resultGridView.DataSource = your DataSource here; resultsGridView.DataBind(); }