vb.net paging and reordering dataset

hi all i have a search page displaying a number of products in a datagrid.

i have added a couple of dropdownboxes to the page for changing the pagesize of the datagrid and reordering it.

to eliminate the amount of times the db is polled i want to do this using the dataset rather than repolling with a different ORDER BY.

there are a couple of problems.  at the moment i have the following

    Sub sortByClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles sortBy.SelectedIndexChanged, pageSize.SelectedIndexChanged

        Dim oDataSet As DataSet = search_datagrid.datasource 'CreateDataSource() <- old method will poll the db for results (with generic orderby product name)

        Dim myView As DataView
        myView = oDataSet.Tables(0).DefaultView

        myView.Sort = sortBy.Text + " DESC"

        search_datagrid.PageSize = pageSize.SelectedItem.Value
        search_datagrid.DataSource = oDataSet
        search_datagrid.DataBind()

    End Sub

a) how do i get the dataset from the datagrid as the above code doesn't like me trying to grab it here

b)if i use my CreateDatasource method instead then the code runs however running the change on order seems to have no effect (even though the code is being ran)

c)if i have the datagrid on the last page and then try resizing the pagesize i get the following error

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

can anyone tell me why?

many thanks in advance,

matt.
flynnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
a) How are you setting the datagrid? Is it created by a wizard? In that case, there should be a typed dataset in the solution which you can access directly.

b) It would be disireable to use the dataset.

c) The error comes up because when you increase the page size then the number of pages is reduced. So the currently last page number becomes invalid.
0
prairiedogCommented:
Are you using SqlDataSource for the datagrid's DataSource?
0
flynnyAuthor Commented:
hi thanks for your reply.

a) the dataset is created by a createDatasource() method passing an SQL string to my mysql db in the page_load method. e.g.

       Dim oQry As String = "SELECT ... FROM ... ORDER BY product.name" 'Default to order by product name

        Dim oDataAdapter As OdbcDataAdapter
        Dim oDataSet As DataSet = New DataSet()

        oDataAdapter = New OdbcDataAdapter(oQRY, getConnectionString())
        oDataAdapter.Fill(oDataSet)

        Return oDataSet

b) are you saying i should maybe store the dataset in a session varible on the page_load method?

c) thanks for that i've sorted that problem now

the datasource is MySql i.e.

      <add key="Connectionweb" value="DRIVER={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;User=xxx;Password=xxx;OPTION=3"/>
      
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

CodeCruiserCommented:
I would suggest that, instead of creating the dataset within the method, you should create a global dataset and populate it within the createdatasource method. This dataset could then be saved to the session and sorted and whatever operation required.
0
flynnyAuthor Commented:
ok i have the following but it still doesn't seem to be updating the datagrid (when i changed the dropdownlist it runs the code but doesn't change the order in the dataset)

i have oDataSet declared globally as

Dim oDataSet As DataSet

the click method is as follows

    Sub sortByClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles sortBy.SelectedIndexChanged, pageSize.SelectedIndexChanged

        oDataSet = Session("searchDataSet")

        Dim myView As DataView
        myView = oDataSet.Tables(0).DefaultView

        myView.Sort = sortBy.Text + " DESC"
        search_datagrid.PageSize = pageSize.SelectedItem.Value
        search_datagrid.DataSource = oDataSet

        Try
            search_datagrid.DataBind()

        Catch
            Try

                search_datagrid.CurrentPageIndex = 0
                search_datagrid.DataBind()
            Catch
            End Try
        End Try

    End Sub

any ideas why?
0
CodeCruiserCommented:
change this line

search_datagrid.DataSource = oDataSet

to

search_datagrid.DataSource = myView
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flynnyAuthor Commented:
ok that working great. apart from one little problem now.

to keep the datasets new ordering i set the session varible to = myview also. i.e.

Session("searchDataSet") = myView

i do this so when the page on the dataset is changed i can invoke

    Sub PageIndexChanged_Click(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs)
        search_datagrid.CurrentPageIndex = e.NewPageIndex
        search_datagrid.DataSource = Session("searchDataSet")
        search_datagrid.DataBind()
    End Sub

however if i change the ordering for a second time i get the following error

Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataSet'.

when trying to set oDataSet = Session("searchDataSet")

is there anyway around this?
0
CodeCruiserCommented:
Yes. Try this code

       oDataSet = Session("searchDataSet")
        oDataSet.Tables(0).DefaultView.Sort = sortBy.Text + " DESC"
        search_datagrid.PageSize = pageSize.SelectedItem.Value
        search_datagrid.DataSource = oDataSet.Tables(0).DefaultView
0
flynnyAuthor Commented:
hi thanks for the reply again,

i've managed to fix it i think by setting the following at the end

Session("searchDataGrid") = myView.Table.DataSet

thans for all your help!
0
flynnyAuthor Commented:
quick and intelligent responses and helped quickly direct to the solution!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.