Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

vb.net paging and reordering dataset

Posted on 2008-11-11
10
Medium Priority
?
806 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:flynny
  • 5
  • 4
10 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22938052
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
 
LVL 22

Expert Comment

by:prairiedog
ID: 22939895
Are you using SqlDataSource for the datagrid's DataSource?
0
 

Author Comment

by:flynny
ID: 22940058
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22940491
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
 

Author Comment

by:flynny
ID: 22941090
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 22941338
change this line

search_datagrid.DataSource = oDataSet

to

search_datagrid.DataSource = myView
0
 

Author Comment

by:flynny
ID: 22944989
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 22945305
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
 

Author Comment

by:flynny
ID: 22948949
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
 

Author Closing Comment

by:flynny
ID: 31515630
quick and intelligent responses and helped quickly direct to the solution!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question