flynny
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.SelectedIndexChange d, pageSize.SelectedIndexChan ged
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).Default View
myView.Sort = sortBy.Text + " DESC"
search_datagrid.PageSize = pageSize.SelectedItem.Valu e
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.
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.SelectedIndexChange
Dim oDataSet As DataSet = search_datagrid.datasource
Dim myView As DataView
myView = oDataSet.Tables(0).Default
myView.Sort = sortBy.Text + " DESC"
search_datagrid.PageSize = pageSize.SelectedItem.Valu
search_datagrid.DataSource
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.
Are you using SqlDataSource for the datagrid's DataSource?
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;Databas e=xxx;User =xxx;Passw ord=xxx;OP TION=3"/>
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;Databas
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.
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.SelectedIndexChange d, pageSize.SelectedIndexChan ged
oDataSet = Session("searchDataSet")
Dim myView As DataView
myView = oDataSet.Tables(0).Default View
myView.Sort = sortBy.Text + " DESC"
search_datagrid.PageSize = pageSize.SelectedItem.Valu e
search_datagrid.DataSource = oDataSet
Try
search_datagrid.DataBind()
Catch
Try
search_datagrid.CurrentPag eIndex = 0
search_datagrid.DataBind()
Catch
End Try
End Try
End Sub
any ideas why?
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.SelectedIndexChange
oDataSet = Session("searchDataSet")
Dim myView As DataView
myView = oDataSet.Tables(0).Default
myView.Sort = sortBy.Text + " DESC"
search_datagrid.PageSize = pageSize.SelectedItem.Valu
search_datagrid.DataSource
Try
search_datagrid.DataBind()
Catch
Try
search_datagrid.CurrentPag
search_datagrid.DataBind()
Catch
End Try
End Try
End Sub
any ideas why?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(ByV al sender As Object, ByVal e As DataGridPageChangedEventAr gs)
search_datagrid.CurrentPag eIndex = 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?
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(ByV
search_datagrid.CurrentPag
search_datagrid.DataSource
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).Default View.Sort = sortBy.Text + " DESC"
search_datagrid.PageSize = pageSize.SelectedItem.Valu e
search_datagrid.DataSource = oDataSet.Tables(0).Default View
oDataSet = Session("searchDataSet")
oDataSet.Tables(0).Default
search_datagrid.PageSize = pageSize.SelectedItem.Valu
search_datagrid.DataSource
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!
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!
ASKER
quick and intelligent responses and helped quickly direct to the solution!
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.