Sorting/Paging a GridView 1

[runmd]

I have a GridView control that I attach data to in the Page_Load event. I have set AllowPaging and AllowSorting to True. The data comes from an oracle database so that means that the data binding is one way. The sorting and paging of data is a two way event so I have added two events to handle this:
OnPageIndexChanging="GridView1_PageIndexChanging"
OnSorting="GridView1_Sorting"

I do not have any issues with the OnPageIndexChanging. I have a run-time error and a build error with the OnSorting event. I've commented both lines of code where the errors are occuring.

    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting

        Dim dataTable As New DataTable
        dataTable = GridView1.DataSource  '// Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'.

        If Not dataTable Is Nothing Then
            Dim dataView As New DataView
            'dataView = dataTable  '// Value of type 'System.Data.DataTable' to type 'System.Data.DataView'.
            dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection)

            GridView1.DataSource = dataView
            GridView1.DataBind()
        End If

    End Sub

I've found lots of examples written in C-sharp that shows that a DataTable can be converted into a DataView but none of those examples, once written in VB, work. It creates the same error as above during compiling. The DataSet to a conversion to a DataTable is a runtime error and I am not sure how to fix that. it's most likely syntax since I have seen this done in C-sharp as well.

 

[runmd]

One more thing, I just tested the paging in IE and the pages do not show up in the browser. The page numbers only show up in Firefox. Ugh....

 

[runmd]

I've fixed the paging issue and that was actually easy but I am still stuck on the sorting problem. After researching it, I have seen others fill the GridView by running the query a 2nd time on the post back. I'm not sure I would need to do that since I already access the db on page load. Isn't that the same thing? So far I have changed one line of code which I think lets me create the DataTable.

'dataTable = GridView1.DataSource  '// Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'.
dataTable = GridView1.DataSource.Tables(0)

I'm still having a syntax issue of converting the DataTable to a DataView and it is preventing the code from compiling.

dataView = dataTable  '// Value of type 'System.Data.DataTable' to type 'System.Data.DataView'.

Does anyone have any ideas on how to fix the sorting problem?

 

[runmd]

I've tried this line of code:

Dim dataTable As DataTable = TryCast(GridView1.DataSource, DataSet)

I still get the same error:

Value of type unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'.

Ugh. So, does anyone have any ideas? Do I need to use a DataTable to bind to the GridView datasource prior to do any kind of sorting on the gridview? How would I do that?

 

[jbenson001]

You can't cast a dataset to a datatable. Understand that a dataset consists of one or more datatables.

Try:

Dim ds as DataSet = DirectCast(GridView1.DataSource, DataSet).Tables(0)

 

[runmd]

Thanks jbenson001! That makes perfect sense.

So, I just did something crazy and instead of binding a dataset to the GridView.DataSource, I converted it to a DataTable. This was in hope to alleviate the problem and it worked. I've been working on this stuff for a couple of days and everytime I post something, I come up with a solution. I'm going from a DataTable to a DataView in order to sort the data which you can see the code in the first post of this thread.

'// Convert DataSet to DataTable
Dim dataTable As New DataTable
dataTable = dataSet.Tables(0)

'// Set GridView1 DataSource to dataTable and bind it
GridView1.DataSource = dataTable
GridView1.DataBind()

The sorting works like a charm now since I just needed a DataTable. I have one more question, if I wanted to clean up the headers in the GridView so that they are not just displaying the column names from the db, what would I need to do to change? Does it have to do with using a DataTable and setting the DataTable columns to the fields I am pulling out of the database?

 

[jbenson001]

You have to set the AutoGenerateColumns to false on the GridView and create them yourself.

 

[runmd]

I've got the sorting and paging working ok. Now I'm taking the gridview control a few steps further by making it expandable/heirarchical. For every row, there is a row below it showing more information. I'm working on an oracle store procedure to run a query and get a result. I'm going to post my error in this forum but in a different thread since it's not related to the sorting/paging problem.