Dynamic Datagrid, Strange Paging Issue

[chris3vic]

I have a dynamically created datagrid, populated from a dataset that is filled by a sql string triggered by a command button. The application itself allows a user to input a selection of criteria and hit search, the results ome back in the grid.

Due to the probably amount of records that will be returd, I decided to allow paging to make the data more visually appealing. So far, so good. Everything is running smoothly until.. you try to selct a new page on the datagrid. The grid just disappears!!!

here is the vb code that populates the datagrid.

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Chris\Desktop\incidents.mdb"
        con.Open()
        sql = "SELECT * FROM tbl_incidents WHERE Address Like '%" & txtAddress.Text & "%' And Borough Like '%" & ddlborough.SelectedValue & "%'"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "incidents")
        GridView1.DataSource = ds.Tables("incidents")
        GridView1.DataBind()
        con.Close()
    End Sub

and here is how I have been handling the pageindexchangi ng:

    Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging
        GridView1.PageIndex = e.NewPageIndex
        If Page.IsPostBack Then
            GridView1.DataSource = ds.Tables("incidents")
            GridView1.DataBind()
        End If
    End Sub
 

I have tried this various ways, the latest handling the postback as I believe that this was causing the issue but the results are the same.

An odd thing is that I have a clear button on the app which clears any text in textboxes left over from the search and resets the drop down lists to their defaults. On click, the gridview re-appears again on the page you requested when you lost it, however, it has the records from the first page in it still.

I cannot for the life of me solve this issue and I would be forever grateful for a solution.

Thanks

[BeemerBiker]

IANE, but I will take a stab at this because I had the same problem.

I assume you have "EnableViewStat e" set to true for the gridview. You do not need any of the code you got in that PageIndexChangi ng. If you have some unbound checkboxes you might want to use that callback to preserve them so they can be repopulated.

When the page loads the first time (IsPostBack false) you should call some routine that populates the grid and saves the sql command so it can be reused in the postback. I had the function "FormSqlCommand " and it stored the sql command in the tooltip for my "search" button (which is also EnableViewState = true). The callback for my "search" button also executed that same FormSqlCommand since it gathered all the filter parameters that were to go into the sql command. Thus after the callback was executed, the tooltip was always set to the correct sql select command and the IsPostBack true was able to get access to that string. All I did in the postback true was to set
SqlDataSource1. SelectCommand = btnExecuteSearc h.ToolTip.ToStr ing():
Want to point out that I could have used a session variable, but the tooltip was nice because I could look at the command and debug it visually.

Your case you are filling in a dataset. You could store the dataset into a session variable and then on postback, set the dataset to the session variable and rebind the grid. You should not have to do any GridView1.PageI ndex = e.NewPageIndex as the paging is handled automatically.

Someone may have a cleaner way.

HTH