Sharepoint 2007-Web Part - GridView -Paging Problem

Question

I am displaying the data in a gridview and I have enabled the paging and it can show 10 records. But when I click on the next Page number, then it comes up error " An unexpected error has occurred. Troubleshoot issues with Windows SharePoint Services " Could any one tell me what is missed here.

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;

using System.Data;
using System.Data.SqlClient;


using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace EVentHandlerWebPartGridView
{
    [Guid("d75310a6-2db3-431c-a45d-351e4ba0bda1")]
    public class EventHandlerWebPartGridView : System.Web.UI.WebControls.WebParts.WebPart
    {

        // Constructors for the web Part
        public EventHandlerWebPartGridView()
        {

        }

        // Member variable for the class

        private string _cnString = string.Empty;

        // control definitions
        protected Label lblError;
        protected Button btnExecuteSQL;
        protected TextBox txtSql;
        protected GridView gvResults;

        // WebPart Parameter for getting the Connection string
        [
        WebBrowsable(true),
        WebDisplayName("Connection String"),
        Personalizable(PersonalizationScope.User)

        ]

        public string CNString
        {
            get { return _cnString; }
            set { _cnString = value; }
        }

        // Override Method- place to initialize the controls
        protected override void CreateChildControls()
        {
            //label initialization and Properties
            lblError = new Label();
            this.Controls.Add(lblError);


            // textbox control and Propetrties
            txtSql = new TextBox();
            txtSql.Width = Unit.Pixel(400);
            txtSql.Height = Unit.Pixel(200);
            txtSql.TextMode = TextBoxMode.MultiLine;
            this.Controls.Add(txtSql);


            //button Initialization and Properties
            btnExecuteSQL = new Button();
            btnExecuteSQL.Text = "Execute SQL";
            btnExecuteSQL.ToolTip = "Click On this to see the Reults of the SQL Query";
            btnExecuteSQL.Click += new EventHandler(btnExecuteSQL_Click);
            this.Controls.Add(btnExecuteSQL);

            //gridview Initialization 

            gvResults = new GridView();
            gvResults.Width = Unit.Percentage(100);
            gvResults.AlternatingRowStyle.BackColor = System.Drawing.Color.Blue;

            gvResults.AllowPaging = true;
            gvResults.PageSize = 10;
            Controls.Add(gvResults);
        }

        //Button Click Event handler
        protected void btnExecuteSQL_Click(object sender, EventArgs e)
        {
            // if the connection string is not empty
            if (_cnString.Trim() != string.Empty)
            {
                try
                {
                    SqlConnection con = new SqlConnection(_cnString);
                    SqlCommand cmd = new SqlCommand(txtSql.Text, con);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    gvResults.DataSource = ds.Tables[0].DefaultView;
                    gvResults.DataBind();

                }
                // Any Errors in the above Code
                catch (Exception ex)
                {

                    gvResults.DataSource = null;
                    gvResults.DataBind();
                    lblError.Text = ex.Message;
                    lblError.Visible = true;
                }
            }
            // If the Connection string is Empty
            else
            {
                gvResults.DataSource = null;
                gvResults.DataBind();
                lblError.Text = "Please Enter a Connection string";
                lblError.Visible = true;

            }


        }

        // Rendering the Data nows
        protected override void Render(HtmlTextWriter writer)
        {
            writer.RenderBeginTag(HtmlTextWriterTag.Table); //table Starts

            writer.RenderBeginTag(HtmlTextWriterTag.Tr); // Tr Starts

            writer.AddAttribute(HtmlTextWriterAttribute.Colspan, "2");// Adds an attribute tag

            writer.RenderBeginTag(HtmlTextWriterTag.Td); // Td tag starts Here
            lblError.RenderControl(writer);
            writer.RenderEndTag();
            writer.RenderEndTag(); //tr nd

            writer.AddAttribute(HtmlTextWriterAttribute.Valign, "top");
            writer.RenderBeginTag(HtmlTextWriterTag.Tr); // tr
            writer.RenderBeginTag(HtmlTextWriterTag.Td); //td
            writer.Write("Enter a SQL Statement");
            writer.RenderEndTag();// end td

            // Now Display the Text Box

            writer.RenderBeginTag(HtmlTextWriterTag.Td); // Td start Tag
            txtSql.RenderControl(writer);
            writer.RenderEndTag(); // td End Tag
            writer.RenderEndTag();//tr End

            // Now Display the Button
            writer.RenderBeginTag(HtmlTextWriterTag.Tr);
            writer.RenderBeginTag(HtmlTextWriterTag.Td);
            btnExecuteSQL.RenderControl(writer);
                writer.RenderEndTag();
            writer.RenderEndTag();

            //Now Display the GridView
            writer.RenderBeginTag(HtmlTextWriterTag.Tr);
            writer.RenderBeginTag(HtmlTextWriterTag.Td);
            gvResults.RenderControl(writer);
            writer.RenderEndTag();
            writer.RenderEndTag();

            writer.RenderEndTag(); //Table End


        }


    }






}

Answer 1

By just looking at your code I see two possibilities.

After you define:

 gvResults.AllowPaging = true;
 gvResults.PageSize = 10;

you should add:

 gvResults.PageIndexChanging += new GridViewPageEventHandler(grid_PageIndexChanging);

and then you need to add event handler:

 void grid_PageIndexChanging(object sender, GridViewPageEventArgs e)
 {
      grid.Paent geIndex = e.NewPageIndex;
      grid.DataBind();
 }

Further IMO you should not override Render method because by doing so you are breaking SharePoint DOM. Here is one older (but still good) article by Andrew Connell on this issue:

• Understanding how Web Parts are rendered, why to never use Render()

It applies on Microsoft.SharePoint.WebPartPages.WebPart but even if you are using System.Web.UI.WebControls.WebParts.WebPart you are still developing webpart on SharePoint so keeping things neat is essential.

Unfortunately I don't have 2007 dev environment handy and I cannot do any testing to debug your problem.

Edited:

IMO best way is to use SPGridView and ObjectDataSource like explained here:

• Building A SPGridView Control

Quote:

I’ve found the ObjectDataSource easier to use than a DataSet (for example, sorting and paging require no additional code whatsoever)

Your code can be easily converted to use ObjectDataSource.

Some additional info can be found on Kit Menke's Blog:

• SharePoint’s SPGridView, filtering, and apostrophes

And yes I am posting this link only because @KitMenke is SPSE moderator :)