user3005409 user3005409 - 6 months ago 50
Vb.net Question

sorting on vb.net gridview

trying to implement a sort on my gridview as below, its not quite working as I'd expect 1. it doesn't sort by the first column as i'd like, first column descending 2. when i click each of the columns they appear to change order but i can't determine what is actually sorting asc/desc. i really only need the first column which is the id to sort desc.

Imports System.Data.SqlClient

Public Class Query
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindGrid()
End If
End Sub

Protected Sub BindGrid()
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString()
Dim strQuery As String = "select id,relates_to,'../../' + location as location from Files;"
Dim cmd As New SqlCommand(strQuery)
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
ViewState("dt") = dt
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception
Response.Write(ex.Message)
Finally
con.Close()
sda.Dispose()
con.Dispose()
End Try
End Sub

Protected Sub OnRowDataBound(sender As Object, e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim item As String = e.Row.Cells(0).Text
For Each button As Button In e.Row.Cells(3).Controls.OfType(Of Button)()
If button.CommandName = "Delete" Then
button.Attributes("onclick") = "if(!confirm('Do you want to delete " + item + "?')){ return false; };"
End If
Next
End If
End Sub

Protected Sub OnRowDeleting(sender As Object, e As GridViewDeleteEventArgs)
Try
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ToString())
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandText = "DELETE FROM Files WHERE id = @id"
cmd.CommandType = CommandType.Text
Dim strBetID As String = GridView1.Rows(e.RowIndex).Cells(0).Text
cmd.Parameters.Add("@id", SqlDbType.Int).Value = strBetID
conn.Open()
cmd.ExecuteNonQuery()
End Using
BindGrid()
Catch ex As Exception
Response.Write(ex.Message)
End Try
End Sub

Private Sub LinkButtonUsers_Click(sender As Object, e As EventArgs) Handles LinkButtonUsers.Click
Response.Redirect("/Admin/Admin/Users.aspx")
End Sub

Private Sub LinkButtonTips_Click(sender As Object, e As EventArgs) Handles LinkButtonTips.Click
Response.Redirect("/Admin/Admin/Admin.aspx")
End Sub

Private Sub LinkButtonEmail_Click(sender As Object, e As EventArgs) Handles LinkButtonEmail.Click
Response.Redirect("/Admin/Admin/Email.aspx")
End Sub

Private Sub LinkButtonKnowledge_Click(sender As Object, e As EventArgs) Handles LinkButtonKnowledge.Click
Response.Redirect("/Admin/Admin/Knowledge.aspx")
End Sub

Protected Sub LinkButtonQuery_Click(sender As Object, e As EventArgs) Handles LinkButtonQuery.Click
Response.Redirect("/Admin/Admin/Query.aspx")
End Sub

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
GridView1.PageIndex = e.NewPageIndex
Me.BindGrid()
End Sub

Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs)
Dim dt As DataTable = ViewState.Item("dt")
Dim dv As DataView = dt.DefaultView
Dim sd As String = ""

If Not dt Is Nothing Or Not dt Is "" Then
If e.SortDirection.ToString.Contains("asc") Then
sd = "asc"
ElseIf e.SortDirection.ToString.Contains("desc") Then
sd = "desc"
Else
sd = "asc"
End If
End If

Try
dv.Sort = e.SortExpression + " " + sd
dt = dv.ToTable
GridView1.DataSource = dt
GridView1.DataBind()
Catch ex As Exception

End Try
End Sub
End Class


aspx is as such:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowDeleting="OnRowDeleting"
OnRowDataBound="OnRowDataBound" EnableModelValidation="True" AllowSorting="true"
OnSorting="GridView1_Sorting" AllowPaging="True" OnPageIndexChanging="OnPageIndexChanging">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="id" />
<asp:BoundField DataField="relates_to" HeaderText="relates_to" SortExpression="relates_to" />
<asp:TemplateField HeaderText="Preview Image" SortExpression="location">
<ItemTemplate>
<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl='<%# Eval("location")%>'
Width="100px" Height="100px" Style="cursor: pointer" OnClientClick="return LoadDiv(this.src);" />
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" ButtonType="Button" />
</Columns>
</asp:GridView>


am tempted to re-write the lot again using another approach as i have kind of cobbled this together from a couple of sources on the net and presumed it would be okay? probably something minor like the viewstate aspect but can't fathom it out at the moment!?..

Answer

Instead of storing the DataTable in the ViewState, you can call BindGrid from the Sorting event to populate the GridView. If you include the data sorting in that method, it will apply in all situations.

The sorting parameters can be stored in the ViewState (or in Session):

Protected Property SortExpression As String
    Get
        Dim value as Object = ViewState("SortExpression")
        Return If(Not IsNothing(value), CStr(value), "id")
    End Get
    Set(value As String)
        ViewState("SortExpression") = value
    End Set
End Property

Protected Property IsAscendingSort As Boolean
    Get
        Dim value as Object = ViewState("IsAscendingSort")
        Return If(Not IsNothing(value), CBool(value), False)
    End Get
    Set(value As Boolean)
        ViewState("IsAscendingSort") = value
    End Set
End Property

They can be set in the Sorting event handler:

Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs)
    If e.SortExpression = SortExpression Then
        IsAscendingSort = Not IsAscendingSort
    Else
        SortExpression = e.SortExpression
    End If
    BindGrid()
End Sub

And used in the BindGrid method:

Protected Sub BindGrid()
    ...
    sda.Fill(dt)
    Dim dv As DataView = dt.DefaultView
    dv.Sort = SortExpression + " " + If(IsAscendingSort, "ASC", "DESC")
    GridView1.DataSource = dv
    GridView1.DataBind()
    ...
End Sub
Comments