Link to home
Start Free TrialLog in
Avatar of flynny
flynnyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Are you using SqlDataSource for the datagrid's DataSource?
Avatar of flynny

ASKER

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"/>
      
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.
Avatar of flynny

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of flynny

ASKER

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?
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
Avatar of flynny

ASKER

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!
Avatar of flynny

ASKER

quick and intelligent responses and helped quickly direct to the solution!