Hello, wow! this topic is popular. I need help with my code for paging and sorting. It is not working and I get this error message:
Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataTable'.
on this line:
gvQuotes.DataSource = SortDataTable(gvQuotes.DataSource,
False)
I really need some help, please. What is wrong with this code?
'sub used to search for available quotes based on user supplied criteria
Private Sub SearchQuotes()
'set connection strig
Dim objConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("quoteConnectionString").ToString)
'set SPROC command object
Dim objCmd As SqlCommand = New SqlCommand("BB_QH_QuoteLookup", objConn)
objCmd.CommandType = CommandType.StoredProcedure
'stored procedure parameters
Dim SearchQuoteNumber As SqlParameter = objCmd.Parameters.Add("@txtSearchQuoteNumber", SqlDbType.VarChar, 12)
SearchQuoteNumber.Value = txtSearchQuotesQuoteNumber.Text.Trim()
Dim SearchCustnmbr As SqlParameter = objCmd.Parameters.Add("@txtSearchCustnmbr", SqlDbType.Char, 15)
SearchCustnmbr.Value = txtSearchQuotesCustnmbr.Text.Trim()
Dim SearchQuotedFor As SqlParameter = objCmd.Parameters.Add("@ddlSearchQuotedFor", SqlDbType.Char, 21)
SearchQuotedFor.Value = lblSearchQFUserName.Text.Trim()
Dim SearchAreaManager As SqlParameter = objCmd.Parameters.Add("@ddlSearchAreaManager", SqlDbType.Char, 21)
SearchAreaManager.Value = lblSearchAMUserName.Text.Trim()
Dim SearchEngineer As SqlParameter = objCmd.Parameters.Add("@ddlSearchEngineer", SqlDbType.Char, 21)
SearchEngineer.Value = lblSearchENUserName.Text.Trim()
Dim SearchCreatedBy As SqlParameter = objCmd.Parameters.Add("@ddlSearchCreatedBy", SqlDbType.Char, 21)
SearchCreatedBy.Value = ddlSearchQuotesCreatedBy.SelectedValue.Trim()
'open the connection to the db
objConn.Open()
'set dataadapter and dataset and execute the sproc
Dim objDA As New SqlDataAdapter(objCmd)
Dim objDS As New DataTable("QuoteNumber")
objDA.Fill(objDS)
'fill gvQuotes with results of search
Dim dataTableRowCount As Integer = objDS.Rows.Count
If dataTableRowCount > 0 Then
gvQuotes.DataSource = objDS
gvQuotes.DataBind()
End If
'close the connection to the db
objConn.Close()
'give the user a message if the search did not return any results
If gvQuotes.Rows.Count = 0 Then
lblSearchQuotesMessage.Visible =
True
lblSearchQuotesMessage.Text =
"Your Search did not return any results."
Else
'if results are true then show the gvQuotes
gvQuotes.Visible =
True
End If
End Sub
Private Property GridViewSortDirection() As String
Get
Return IIf(ViewState("SortDirection") = Nothing, "ASC", ViewState("SortDirection"))
End Get
Set(ByVal value As String)
ViewState(
"SortDirection") = value
End Set
End Property
Private Property GridViewSortBLOCKED EXPRESSION As String
Get
Return IIf(ViewState("SortExpression") = Nothing, String.Empty, ViewState("SortExpression"))
End Get
Set(ByVal value As String)
ViewState(
"SortExpression") = value
End Set
End Property
Private Function GetSortDirection() As String
Select Case GridViewSortDirection
Case "ASC"
GridViewSortDirection =
"DESC"
Case "DESC"
GridViewSortDirection =
"ASC"
End Select
Return GridViewSortDirection
End Function
Protected Function SortDataTable(ByVal dataTable As DataTable, ByVal isPageIndexChanging As Boolean) As DataView
If Not dataTable Is Nothing Then
Dim dataView As New DataView(dataTable)
If GridViewSortExpression <> String.Empty Then
If isPageIndexChanging Then
dataView.Sort =
String.Format("{0} {1}", GridViewSortExpression, GridViewSortDirection)
Else
dataView.Sort =
String.Format("{0} {1}", GridViewSortExpression, GetSortDirection())
End If
End If
Return dataView
Else
Return New DataView()
End If
End Function
Protected Sub gvQuotes_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvQuotes.DataSource = SortDataTable(gvQuotes.DataSource,
True)
gvQuotes.PageIndex = e.NewPageIndex
gvQuotes.DataBind()
End Sub
Protected Sub gvQuotes_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gvQuotes.Sorting
GridViewSortExpression = e.SortExpression
Dim pageIndex As Integer = gvQuotes.PageIndex
gvQuotes.DataSource = SortDataTable(gvQuotes.DataSource,
False)
gvQuotes.DataBind()
gvQuotes.PageIndex = pageIndex
End Sub