Dynamic SQL cause paging issues in GridView?

Discussion in 'ASP .Net' started by Froefel, Jul 4, 2007.

  1. Froefel

    Froefel Guest

    I'm very new to the ADO.NET and WebForms arena, so the problem that
    I'm having is probably trivial.

    Using VS2005, I'm just creating a small test project that has a text
    box allowing you to enter a SQL statement, a button to execute the
    statement and a GridView control to display the results. The GridView
    control should AllowPaging and AllowSorting. The data comes from a SQL
    datasource, which I access through a connection string stored in
    web.config (no problems there).

    When I click the Execute Button, the SQL statement is correctly
    executed and the GridView control is properly populated. The problem
    arises when I want to jump to the next (or any other) page. It appears
    the statement is executed but the gridview control disappears. Any
    idea what's causing this?

    Here is the relevant excerpt from the aspx page:
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:TextBox ID="TextBox1" runat="server"
    Text="SELECT * FROM
    customerversion" Width="423px"></asp:TextBox>
    <br />
    <asp:Button ID="Button1" runat="server"
    Text="Execute"
    OnClick="Button1_Click" Width="109px" />
    <br />
    <br />
    <asp:GridView ID="GridView1"
    runat="server"
    AllowPaging="True"

    EnableSortingAndPagingCallbacks="true"

    OnPageIndexChanging="GridView1_PageIndexChanging">
    </asp:GridView>
    &nbsp;
    </div>
    </form>

    Below is the code that lives in the Code-Behind file:

    protected void Button1_Click(object sender, EventArgs e)
    {
    GetData();
    }

    protected void GridView1_PageIndexChanging(object sender,
    GridViewPageEventArgs e)
    {
    GridView1.PageIndex = e.NewPageIndex;
    GetData();
    }

    private int GetData()
    {
    SqlConnection conn = new
    SqlConnection(ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString);
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = this.TextBox1.Text;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    DataSet ds = new DataSet();
    da.Fill(ds);
    GridView1.DataSource = ds;
    GridView1.DataBind();

    return ds.Tables[0].Rows.Count;
    }
     
    Froefel, Jul 4, 2007
    #1
    1. Advertising

  2. Froefel

    Froefel Guest

    I figured out what's happening... kind of.

    Apparently I also need to call the GetData() function in the Page_Load
    function as follows:
    protected void Page_Load(object sender, EventArgs e)
    {
    if (IsPostBack)
    GetData();
    }

    -- Hans


    On Jul 4, 8:35 am, Froefel <> wrote:
    > I'm very new to the ADO.NET and WebForms arena, so the problem that
    > I'm having is probably trivial.
    >
    > Using VS2005, I'm just creating a small test project that has a text
    > box allowing you to enter a SQL statement, a button to execute the
    > statement and a GridView control to display the results. The GridView
    > control should AllowPaging and AllowSorting. The data comes from a SQL
    > datasource, which I access through a connection string stored in
    > web.config (no problems there).
    >
    > When I click the Execute Button, the SQL statement is correctly
    > executed and the GridView control is properly populated. The problem
    > arises when I want to jump to the next (or any other) page. It appears
    > the statement is executed but the gridview control disappears. Any
    > idea what's causing this?
    >
    > Here is the relevant excerpt from the aspx page:
    > <body>
    > <form id="form1" runat="server">
    > <div>
    > <asp:TextBox ID="TextBox1" runat="server"
    > Text="SELECT * FROM
    > customerversion" Width="423px"></asp:TextBox>
    > <br />
    > <asp:Button ID="Button1" runat="server"
    > Text="Execute"
    > OnClick="Button1_Click" Width="109px" />
    > <br />
    > <br />
    > <asp:GridView ID="GridView1"
    > runat="server"
    > AllowPaging="True"
    >
    > EnableSortingAndPagingCallbacks="true"
    >
    > OnPageIndexChanging="GridView1_PageIndexChanging">
    > </asp:GridView>
    > &nbsp;
    > </div>
    > </form>
    >
    > Below is the code that lives in the Code-Behind file:
    >
    > protected void Button1_Click(object sender, EventArgs e)
    > {
    > GetData();
    > }
    >
    > protected void GridView1_PageIndexChanging(object sender,
    > GridViewPageEventArgs e)
    > {
    > GridView1.PageIndex = e.NewPageIndex;
    > GetData();
    > }
    >
    > private int GetData()
    > {
    > SqlConnection conn = new
    > SqlConnection(ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString);
    > SqlCommand cmd = conn.CreateCommand();
    > cmd.CommandType = CommandType.Text;
    > cmd.CommandText = this.TextBox1.Text;
    > SqlDataAdapter da = new SqlDataAdapter();
    > da.SelectCommand = cmd;
    > DataSet ds = new DataSet();
    > da.Fill(ds);
    > GridView1.DataSource = ds;
    > GridView1.DataBind();
    >
    > return ds.Tables[0].Rows.Count;
    > }
     
    Froefel, Jul 5, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UGF0cmljay5PLklnZQ==?=

    DataSet paging vs Datareader paging

    =?Utf-8?B?UGF0cmljay5PLklnZQ==?=, Oct 8, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    10,779
    Scott Allen
    Oct 8, 2004
  2. OJ
    Replies:
    0
    Views:
    536
  3. OJ
    Replies:
    2
    Views:
    4,046
  4. sloan
    Replies:
    3
    Views:
    409
    James Irvine
    Apr 4, 2007
  5. Cirene

    Wierd paging issues with Gridview

    Cirene, Nov 29, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    327
    Cirene
    Nov 29, 2007
Loading...

Share This Page