Hi All,

Sorry to bring this up again. I've been through most all of the posts on this topic and tried the suggestions offered but I just can't seem to make the paging work without getting the index error.

Invalid CurrentPageIndex value. It must be >= 0 and < the PageCount.

I know what the problem is but I can't seem to find anything to fix it. The current DataSet doesn't have as many pages as the previous search results so the index is out of range.

I've tried adding this code but then it doesn't return the correct result set.

    Sub pageProducts(ByVal s As Object, ByVal e As DataGridPageChangedEventArgs)
        grdProducts.CurrentPageIndex = e.NewPageIndex
        If (e.NewPageIndex >= 0 And e.NewPageIndex < grdProducts.PageCount) Then
            grdProducts.CurrentPageIndex = e.NewPageIndex
        Else
            grdProducts.CurrentPageIndex = 0
        End If
        getProducts(s, e)
    End Sub

Here's is what I have. The DataSet loads, building the SQL query from a series of three list boxes.

    Sub getProducts(ByVal s As Object, ByVal e As EventArgs)
        Dim SQL As String = "SELECT Manufacturers.ManName, Products.ProdID, Products.ProdName, Products.ProdDesc, Products.ProdYear, Products.ProdPrice, Products.ProdSold FROM Products INNER JOIN Manufacturers ON Manufacturers.ManID = Products.ManID INNER JOIN Subcategories ON Subcategories.SubID = Products.SubID WHERE Manufacturers.Status > 0 AND Products.Status > 0 "
        If ddlCatID.SelectedValue > 0 Then
            SQL += "AND Subcategories.CatID = " & ddlCatID.SelectedValue & " "
            If ddlSubID.SelectedValue > 0 Then
                SQL += "AND Products.SubID = " & ddlSubID.SelectedValue & " "
            End If
        End If
        If ddlManID.SelectedValue > 0 Then
            SQL += "AND Products.ManID = " & ddlManID.SelectedValue & " "
        End If
        If Len(txtProdName.Text) > 0 Then
            SQL += "AND Products.ProdName LIKE '%" & txtProdName.Text & "%' "
        End If
        SQL += "ORDER BY Products.ProdDate DESC;"
        Dim oDA As New SqlDataAdapter(SQL, _oConn)
        Dim oDS As New DataSet
        oDA.Fill(oDS)
        grdProducts.DataSource = oDS
        grdProducts.DataBind()
    End Sub

    Sub pageProducts(ByVal s As Object, ByVal e As DataGridPageChangedEventArgs)
        grdProducts.CurrentPageIndex = e.NewPageIndex
        getProducts(s, e)
    End Sub

Your response is very much appreciated.

Thank you,
Richard

Hi Richard,

You almost answered the question yourself...

Whenever you know the underlying data has changed, you should reset the CurrentPageIndex.

Where you do this depends on your application. For example, if you have a search box where users can search, you can rest it in its Click event (or in the TextChanged event of the TextBox)

If there are other controls that change the underlying data, use an appropriate event to reset the index.

Looking at your code, it looks like the query is driven by a drop-down control. That event fires its SelectedIndexChanged event. That is a great please to reset the CurrentPageIndex and then call getProducts again to update your grid.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

I didn't see this response. Sorry for the duplicat post. I'm glad they're going to combine some of these forums since I'm having trouble keeping them straight.

Yes, the query for the search is driven by three drop-down boxes. The dataset doesn't actually change until the Search button click event which fires getProducts.

I added this to getProducts and it doesn't help.

        If grdProducts.CurrentPageIndex > grdProducts.PageCount Or grdProducts.CurrentPageIndex < 0 Then
            grdProducts.CurrentPageIndex = 0
        End If
        grdProducts.DataBind()


Yes, I understand the logic.... I just don't know the code to use and where to put it to resolve the problem. I know what's happening and I've tried everything that I can possibly think of.

Thank you for your response. Very much appreciated.

Regards,
Richard

No problem about the duplicate post. Did you know you can subscribe to posts so you get replies in your e-mail?

I think the code you added is not enough. Look at this:

If grdProducts.CurrentPageIndex > grdProducts.PageCount Or grdProducts.CurrentPageIndex < 0 Then
  grdProducts.CurrentPageIndex = 0
End If

You all this before you call DataBind, right? In that case, the first statement will never be true, as the current index will always be smaller than the PageCount.

However, the problem occurs when you then bind a DataSet with less pages, so you have a CurrentPageIndex that's too large.

There are two ways to fix it:

1. Always reset the CurrentPageIndex whenever the Search button is clicked. IMO, this makes a lot of sense, because a new query is performed and the user expects a new result set at page 1.

2. Calculate the last possible page based on the number of records in your DataSet and use that. That way, if you are on page 9, perform a new search which has only 7 pages, you stay on the "last page" which is then page 7. IMO, not a nice solution.

If all this doesn't help, can you post some more code?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi Imar,

OK. I think its fixed. So far sooooo good.

I created a copy of getProducts, a duplicate sub called gettheProducts that fires on the button click event and added the line:

grdProducts.CurrentPageIndex = 0

So that everytime there is a new dataset the CurrentPageIndex = 0, which is what should happen anyway.

Changed Sub pageProducts to fire the other Sub getProducts.

    Sub pageProducts(ByVal s As Object, ByVal e As DataGridPageChangedEventArgs)
        grdProducts.CurrentPageIndex = e.NewPageIndex
        getProducts()
    End Sub

IT'S ALIIIIIIIIIIIIVE!!!!!!!!!!!!!!!!!!

Thank you oh so much Imar. Very much appreciated. I'm going to go buy a bottle of aquavit and celebrate now.

Richard

You're welcome. Glad it's working now, although it sounds like it could still be optimized a little.... ;)

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I'm subscribed to this group now. When I was searching through all the groups for solutions I didn't realize I was actually posting to a group I wasn't subscribed to. I was expecting email, that's why I wasn't aware that you had posted a reply and reposted. Thanks.

OK. I'll hold off on the celebration for a while. Anyway..... optimizations???

Maybe I misunderstood your solution, and there is nothing to optimize.

Generally, the pattern you should follow is this: It's not fully functional code but I am sure you get the idea. If you have a pattern like this, there is not much to optimize.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

OK. Yes, the btnSearch event setting the CurrentPageIndex = 0 was exactly what solved the problem except that I had to have two separate bind subs, one to turn the page and the other to load the new dataset from the changed SQL query. I'm not sure if this is absolutely necessary but like Jim says, "If it works, then its good", but I'm also hiding the datagrid if any of the drop-down selecteIndexes change.

Its up and running now if you care to take a look.

http://www.printingequipment.net/catalog.aspx

OK, I'll buy you the aquavit and you can celebrate too.

Cheers,

Richard

Looks great, and thanks for the aquavit.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.