Hi

I'm experiencing another problem. I want to sort and page my data using alphabets. Usually in the in-built GridView from Visual Web Developer, paging is done by numbers.
Is there any codes that I can refer to that I can program my GridView codes to be able to sort in alphabets(sort by name, then take the initials), and page them accordingly?

An example is like if I have the following records
-Anna
-Andy
-Betty
-Cassandra

So when I click the paging hyperlink that says "[u]A</u>", the gridview will only allow me to view Anna's and Andy's record. Like wise for "[u]B</u>" and "[u]C</u>".

Please advice me on how can I come about it.
Thank you!

This isn't really "sorting", it's filtering. You'll have to filter the datasource before it is bound to the control. The filtering logic will depend on the data source you are using.

-Peter

I find the DataView is a handy object for doing this. I put 26 LinkButtons (A, B etc.) in the footer row of the GridView, assign them Commands "A", "B" etc, then handle the GridView RowCommand event with something like...

Protected Sub gv_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) handles myGridView.RowCommand
I think Left is a T-Sql statement so it depends on the database you're using. Good luck.

Hi!

Thanks for replying!
Err, just want to confirm more things with you. I'm currently using AccessDataSource control to pull my data out. I saw a dataset you put in the coding. Do I have to change it to DataSet also? Another question is, I'm using gridview to display the data, do I have to change it to the DataView or just use the DataView object?

Thank you

The RowFilter property you assign the actual filter expression to is on the System.Data.DataView class so it is not tied to any particular database engine. It is a function of the DataTable/DataView itself. It does have many similarities to T-SQL syntax however.

-Peter

A DataView is a property of a DataTable. In fact, DataTable.DefaultView is a DataView. The DataView contains the RowFilter property which you can assign the filter expression to. This way you can get at a subset of your DataTable rows without affecting the actual DataTable. You can filter and sort the DefaultView to provide alternate views of the underlying DataTable.

The GridView is the server control that displays the data. It is a control class, not a data class. You can display the data using a GridView or another data display control. However you will need to implement the alpha character mechanism differently based on the display control you use.

-Peter

Hi! I've found this source code online, but they use SQL, DataGrid, and repeater.
Is there anything that I can use the gridview control instead of DataGrid, and how do I change the code for data access of SQL to MS Access? Thank you! The codes are as follows:

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
    Dim strFilter As String
    Dim DBConn As New SqlConnection("Server=<SERVER>,<PORT>;UID=<USERNAM E>;PWD=<PASSWORD>;Database=<DATABASE>;")
    Dim DBComm As SqlCommand
    Dim DBAdapt As SqlDataAdapter

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim ThisFilter As Object = ViewState("vsFilter")
        If Not (ThisFilter Is Nothing) Then
            strFilter = CStr(ThisFilter)
        Else
            strFilter = "All"
        End If
        If Not Page.IsPostBack Then
            BindData(True)
        End If
    End Sub

    Sub BindData(ByVal GetFresh As Boolean)
        Dim DTable As DataTable = Nothing
        If ViewState("vsSortData") Is Nothing Or GetFresh Then
            DBComm = New SqlCommand("SELECT tblProductName FROM tblProducts", DBConn)
            DBAdapt = New SqlDataAdapter(DBComm)
            Dim DSet As New DataSet()
            Try
                DBAdapt.Fill(DSet)
                DTable = DSet.Tables(0)
            Catch EXC As SqlException
                Me.lblError.Text = EXC.Message
                Return
            Finally
                DBConn.Close()
            End Try
            ViewState("vsSortData") = DTable
        Else
            DTable = CType(ViewState("vsSortData"), DataTable)
        End If
        If strFilter = "All" Then
            DTable.DefaultView.RowFilter = String.Empty
        Else
            DTable.DefaultView.RowFilter = "tblProductName LIKE '" & strFilter & "%'"
        End If
        Me.dgProducts.DataSource = DTable.DefaultView
        Me.dgProducts.DataBind()
        BuildAlphaPager()
    End Sub

    Sub BuildAlphaPager()
        Dim DTable As DataTable
        If ViewState(("strLetter")) Is Nothing Then
            Dim arrLetters As String() = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "All"}
            DTable = New DataTable()
            DTable.Columns.Add(New DataColumn("Letter", GetType(String)))
            Dim i As Integer
            For i = 0 To arrLetters.Length - 1
                Dim DRow As DataRow = DTable.NewRow()
                DRow(0) = arrLetters(i)
                DTable.Rows.Add(DRow)
            Next i
            ViewState("strLetter") = DTable
        Else
            DTable = CType(ViewState("strLetter"), DataTable)
        End If
        Me.rptLetters.DataSource = DTable.DefaultView
        Me.rptLetters.DataBind()
    End Sub

    Protected Sub rptLetters_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
        If (e.Item.ItemType = ListItemType.Header) Then
        ElseIf (e.Item.ItemType = ListItemType.Item) Or (e.Item.ItemType = ListItemType.AlternatingItem) Then
            Dim lnkAlpha As LinkButton = CType(e.Item.FindControl("lnkAlpha"), LinkButton)
            lnkAlpha.Text = DataBinder.Eval(e.Item.DataItem, "Letter")
            lnkAlpha.CommandName = "Filter"
            lnkAlpha.CommandArgument = DataBinder.Eval(e.Item.DataItem, "Letter")
            Dim DRView As DataRowView = CType(e.Item.DataItem, DataRowView)
            If CStr(DRView(0)) = strFilter Then
                lnkAlpha.Enabled = False
            End If
        ElseIf (e.Item.ItemType = ListItemType.Footer) Then
        End If
    End Sub
#63728;
    Protected Sub rptLetters_ItemCommand(ByVal source As Object, ByVal e As RepeaterCommandEventArgs)
        If e.CommandName = "Filter" Then
            strFilter = CStr(e.CommandArgument)
            ViewState("vsFilter") = strFilter
            BindData(False)
        End If
    End Sub
</script>

<html>
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form runat="server">
        <asp:Label ID="lblError" runat="server" EnableViewState="False" />
        <asp:Repeater ID="rptLetters" runat="server" OnItemDataBound="rptLetters_ItemDataBound" OnItemCommand="rptLetters_ItemCommand">
            <ItemTemplate>
                <asp:LinkButton ID="lnkAlpha" runat="server" />
            </ItemTemplate>
        </asp:Repeater>
        <asp:DataGrid ID="dgProducts" runat="server" Width="100%" CellPadding="3" BackColor="#DEBA84" BorderWidth="1px" CellSpacing="2" BorderStyle="None" BorderColor="#DEBA84">
            <ItemStyle ForeColor="#8C4510" BackColor="#FFF7E7" />
            <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#A55129" />
        </asp:DataGrid>
    </form>
</body>
</html>

yukijocelyn -

Is this a new question from the original post? Is so, please start a new thread.

Have you researched the GridView control? Have you researched connecting to Access? I doubt someone will (nor should they) take the time to just convert your page for you. Please do some research on these and see what you can come up with on your own.

-Peter

I've found the answer to it le! In a shorter way even!
This time in VB, MS Access. This time I use DropDownList instead because I don't like using arrays of LinkButtons. Hope this benefits others who might be doing the same thing as I am.

Private Sub BindData()
        Dim dbconn As OleDbConnection
        Dim sql As String
        Dim dbcomm As OleDbCommand
        Dim dataAdap As OleDbDataAdapter
        Dim ds As DataSet

        dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("your database path here"))
        dbconn.Open()
        sql = "SELECT * FROM table"
        dbcomm = New OleDbCommand(sql, dbconn)
        dataAdap = New OleDbDataAdapter(dbcomm)

        ds = New DataSet
        dataAdap.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()

    End Sub

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim selectQuery, dbconn As String
        Dim myConnection As OleDbConnection
        Dim ad As OleDbDataAdapter
        Dim ds As DataSet
        Dim acctname As DropDownList
        Dim nameInitial As String

        acctname = sender
        nameInitial = acctname.SelectedValue.ToString 'get the value of the alphabet

        dbconn = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("your
        database path here")
        selectQuery = "SELECT * FROM table WHERE Name LIKE '" + nameInital + "%'"

        myConnection = New OleDbConnection(dbconn)

        ad = New OleDbDataAdapter(selectQuery, myConnection)

        ds = New DataSet
        ad.Fill(ds)

        GridView1.DataSource = ds
        GridView1.DataBind()

        DropDownList1.SelectedIndex = 0 'resets the value of the list
    End Sub

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedInde xChanged">
<asp:ListItem></asp:ListItem>
  <asp:ListItem>A</asp:ListItem>
  <asp:ListItem>B</asp:ListItem>
  <asp:ListItem>C</asp:ListItem>
  <asp:ListItem>D</asp:ListItem>
  <asp:ListItem>E</asp:ListItem>
  <asp:ListItem>F</asp:ListItem>
  <asp:ListItem>G</asp:ListItem>
  <asp:ListItem>H</asp:ListItem>
  <asp:ListItem>I</asp:ListItem>
  <asp:ListItem>J</asp:ListItem>
  <asp:ListItem>K</asp:ListItem>
  <asp:ListItem>L</asp:ListItem>
  <asp:ListItem>M</asp:ListItem>
  <asp:ListItem>N</asp:ListItem>
  <asp:ListItem>O</asp:ListItem>
  <asp:ListItem>Q</asp:ListItem>
  <asp:ListItem>R</asp:ListItem>
  <asp:ListItem>S</asp:ListItem>
  <asp:ListItem>T</asp:ListItem>
  <asp:ListItem>U</asp:ListItem>
  <asp:ListItem>V</asp:ListItem>
  <asp:ListItem>W</asp:ListItem>
  <asp:ListItem>X</asp:ListItem>
  <asp:ListItem>Y</asp:ListItem>
  <asp:ListItem>Z</asp:ListItem>
</asp:DropDownList>

PS: Thanks Peter, though I still had the help of people here, but will keep that in mind.

Hi,

Instead of Creating Arrays, Dropdown List and putting 26 link buttons, Use following best way.

Create a ASP.NET Repeater and put a Hyperlink and bind with following query.
SELECT DISTINCT SUBSTR(UPPER(Column), 1, 1) Alphabet FROM TABLE

Example:
        <asp:Repeater ID="rAlphaLinks" runat="server" Visible="true">
         <ItemTemplate>
          <asp:HyperLink ID="hlAlpha" Text='<%# Eval("Alphabet") %>' NavigateUrl='<%# "Filename.aspx?Alpha=" + Eval("Alphabet") %>'
           runat="server" />&nbsp;
         </ItemTemplate>
        </asp:Repeater>

Page behind:
        rAlphaLinks.DataSource = dt
        rAlphaLinks.DataBind()

Hope you got it?



Rams