Retrieve the source from a data grid

[Guest]

Hi,

I'm new to asp.net and tryign to learn how to work with data grids.

I'm retrieving data from a stored procedure and putting it in a data grid.
I then want to be able to sort the data when clicking on the headers, using
the Sub grdList_SortCommand.

When clicking a header I get the error message:
"System.Data.DataException: DataTable must be set prior to using DataView."
on the row --> dv.Sort = dataGrid.Attributes("SortExpression").

Is this because of the way I retrieve the data source?
How should I do it instead?

The code looks like this:

Sub BindGrid()
Dim objConnect As SqlConnection
Dim objCommand As SqlCommand
Dim objDataReader As SqlDataReader
Dim strConnection As String
Dim strSql As String

strConnection =
ConfigurationSettings.AppSettings("myConnectionString")

strSql = "procedureToRun"

objConnect = New SqlConnection(strConnection)

objCommand = New SqlCommand(strSql, objConnect)

objCommand.CommandType = CommandType.StoredProcedure

objConnect.Open()

objDataReader = objCommand.ExecuteReader()

myGrid.DataSource = objDataReader
myGrid.DataBind()

objDataReader.Close()
objConnect.Close()
End Sub

-- This is the sub called when clicking a header in the grid
Private Sub grdList_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
myGrid.SortCommand
' Sorts the grid in both directions. Default is ascending.
Dim dataGrid As DataGrid = source
Dim strSort = dataGrid.Attributes("SortExpression")
Dim strASC = dataGrid.Attributes("SortASC")

Dim dt As DataTable
Dim dv As DataView
dt = CType(dataGrid.DataSource, DataTable)
dv = New DataView(dt)

dataGrid.Attributes("SortExpression") = e.SortExpression
dataGrid.Attributes("SortASC") = "Yes"

dv.Sort = dataGrid.Attributes("SortExpression")

If (e.SortExpression = strSort) And (strASC = "Yes") Then
dataGrid.Attributes("SortASC") = "No"
dv.Sort &= " DESC"
Else
dataGrid.Attributes("SortASC") = "Yes"
End If

dataGrid.DataSource = dv
dataGrid.DataBind()
End Sub

Greatful for any help!

Regards,
Linda

 

[Joe Fallon]

You have to either re-fetch the data from the DB and re-bind the grid or
store the data in Sesssion (or Cache) and re-bind the grid.

I think your Dataset is empty when you sort.
Set a breakpoint and check the variables.