I have been trying to combine the two functions into a Datagrid that is both Editable and Sortable (per the suggestion on page 726) and have learned alot, However I Just cannot get them to work together.

This would be the perfect tool for many of my webs that require displaying Tables of Info that need to be edited.

Would it be possible to get your version of the combination of both?
Ideally it would be done thru code and not directly bound to a DataSet object embedded in the form.

Thank you in advance,
Steve Solberg

Ok, from the looks of it you are doing web apps with ASP.Net? Or are you creating a VB web app? Well, it doesn't really matter. To make a datagrid sortable, assign its datasource to a dataVIEW instead of dataset. Using the rowfilter property of the dataview, you can filter out what you need. Ex:

(Assumed is a declaration/dimension of the dataview object right below the connection, data adapter, and dataset objects at the top of the form1 class declaration)

' btnSearch, generic button created to show the "search/sort" ability of dataview
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ' Set the DataView object to the DataSet object
        myDataView = New DataView(myDataSet.Tables("authors"))
        myDataView.RowFilter = "au_lname = '" & TextBox1.Text & "'"
        grdAuthorTitles.DataSource = myDataView
        grdAuthorTitles.Refresh()
End Sub

The major downfall to making something searchable this way is the delay from filtering the data. If anyone else knows a faster (response time, not code-wise) way, I'd be all ears.
Now, sorting (not searching) can be done with or without the rowfilter property, by using the sort property of the dataview.

This all pertains to VB.Net, so I'm pretty sure if you are using ASP.Net you'll be able to use this in your inline or code-behind module, although I could not guarantee that (the ASP.Net programmer that I work with is at lunch :P)

Thanks for the reply,
I still don't see the light. I followed the code for the sample in the book. The Sorting(by the Column Header) works Great. On the other example, editing is very easy to do, the problem comes when you mix the two methods together. When you try to edit after a sort, You do get the edit Textbox, but the row being edited is wrong. It is in the correct place in the datagrid, but the information reverts back to what it was before the sort as if it never was sorted.
I can usually program around it, but was really hoping someone had done what the book suggested at the end of the chapter about combining them, so I could see what i've done wrong.
Work arounds are ok in a pinch, but I have to believe it can be done easily and quickly.

Ahhh. ok, well, I understand your concern now. I will run through the project this afternoon and see if I have any runins.

Hi,

I'd like to know whether anyone discovered or suggested a resolution for this issue. I also have linked column headers that sort correctly, but attempting to call update/edit methods on a record causes the view to revert to the initial datagrid sort, which prepares the wrong record's columns for editing...

Suggestions? TIA

Hey - I am having the same problem. I discovered that the reason is that I am sorting using a dataview as opposed to a sorted datatable (sorted using a sort parameter sent to the database). Did you figure out how to fix it even with using the dataview sort?? Thanks!

ok - I realized that I was using the DataView to sort the grid (rather than going back to the database and getting a sorted datatable or whatever). When you do this, the e.Item.Index returns the primary key on the original datatable. I had to resolve this quickly without changing too much code so I added a textbox of 0 pixel width (it must be visible else it does not return the correct value but it may be read only) and put the primary key in there. On the dataGridDelete command, I find the textbox using the e.Item.Cells(0).FindControl("zeropixeltextboxid") syntax and get the key from the text field.

This worked well for me. It may not the recommended solution if you are starting from scratch but works just fine as a quick fix.

Hi, i face the same problem too. Anyone can update on this subject?

All,

The long anticipated answer lies below.

Thearon

This is the HTML code for your DataGrid:
    <asp:DataGrid id=grdAuthors
        style="Z-INDEX: 101; LEFT: 12px; POSITION: absolute; TOP: 12px"
        runat="server"
        AlternatingItemStyle-BackColor=WhiteSmoke
        AutoGenerateColumns=false
        BackColor=White
        CellPadding=3
        DataKeyField="title_id"
        Font-Name=Verdana
        Font-Size=8pt
        GridLines=None
        HeaderStyle-BackColor=Maroon
        HeaderStyle-Font-Bold=true
        HeaderStyle-ForeColor=White
        OnEditCommand="EditGridData"
        OnCancelCommand="CancelGridData"
        OnUpdateCommand="UpdateGridData"
        AllowSorting="true">
        <Columns>
            <asp:EditCommandColumn
                EditText="Edit Row"
                CancelText="Cancel Edit"
                UpdateText="Update Row"
                ItemStyle-Wrap="False"/>

            <asp:BoundColumn
                DataField="title_id"
                Visible="False"/>

            <asp:BoundColumn
                DataField="au_lname"
                HeaderText="Last Name"
                ReadOnly="True"
                SortExpression="au_lname"
                ItemStyle-Wrap="False"/>

            <asp:BoundColumn
                DataField="au_fname"
                HeaderText="First Name"
                ReadOnly="True"
                SortExpression="au_fname"
                ItemStyle-Wrap="False"/>

            <asp:TemplateColumn
                HeaderText="Title"
                ItemStyle-Wrap="False"
                SortExpression="title">
                <ItemTemplate>
                <asp:Label runat="server"
                    Text='<%# DataBinder.Eval (Container.DataItem, "title") %>' ID="Label1"/>
                </ItemTemplate>

                <EditItemTemplate>
                <asp:TextBox runat="server"
                    ID="edit_title"
                    Font-Name="Verdana"
                    Font-Size="8pt"
                    Width="400"
                    Text='<%# DataBinder.Eval(Container.DataItem, "title") %>'/>
                </EditItemTemplate>
            </asp:TemplateColumn>

            <asp:TemplateColumn
                HeaderText="Price"
                HeaderStyle-Font-Bold="True"
                ItemStyle-HorizontalAlign=Right
                SortExpression="price">
                <ItemTemplate>
                <asp:Label runat="server"
                    Text='<%# DataBinder.Eval(Container.DataItem, "price", "{0:C2}")%>' ID="Label2"/>
                </ItemTemplate>
                <EditItemTemplate>
                <asp:TextBox runat="server"
                    ID="edit_price"
                    Font-Name="Verdana"
                    Font-Size="8pt"
                    Width="50"
                    Text='<%# DataBinder.Eval(Container.DataItem, "price", "{0:C2}") %>'/>
                </EditItemTemplate>
            </asp:TemplateColumn>
        </Columns>
    </asp:DataGrid>

This is the VB.Net code for your code behind file:
    'Declare a Connection object that is global in scope
    Dim objConnection As SqlConnection

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Initialize the Connection object
        objConnection = New SqlConnection( _
            "Server=localhost;Database=Pubs;User ID=sa;Password=P5662ab#;")

        'Only bind the data the first time the page is built
        'Subsequent post backs will be to sort the data in the grid
        If Not (IsPostBack) Then
            'Set a session variable with the sort column
            Session("SortColumn") = "au_lname"
            BindGrid(Session("SortColumn"))
        End If
    End Sub

    Sub BindGrid(ByVal strSortField As String)
        'Declare objects
        Dim objDataSet As DataSet
        Dim objDataAdapter As SqlDataAdapter

        'Set the SQL string
        objDataAdapter = New SqlDataAdapter( _
            "SELECT au_lname, au_fname, titles.title_id, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY " & strSortField, _
            objConnection)

        'Initialize the DataSet object and fill it
        objDataSet = New DataSet
        objDataAdapter.Fill(objDataSet, "Authors")

        'Bind the DataGrid
        grdAuthors.DataSource = objDataSet
        grdAuthors.DataBind()

        'Cleanup
        objDataAdapter = Nothing
        objDataSet = Nothing
    End Sub

    Private Sub grdAuthors_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEvent Args) Handles grdAuthors.SortCommand
        'Save the selected sort column in the session variable
        Session("SortColumn") = e.SortExpression

        'Bind the data grid using the sort column requested
        BindGrid(Session("SortColumn"))
    End Sub

    Sub EditGridData(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
        grdAuthors.EditItemIndex = CInt(E.Item.ItemIndex)
        'Bind the DataGrid using the saved sort column
        BindGrid(Session("SortColumn"))
    End Sub

    Sub CancelGridData(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
        grdAuthors.EditItemIndex = -1
        'Bind the DataGrid using the saved sort column
        BindGrid(Session("SortColumn"))
    End Sub

    Sub UpdateGridData(ByVal Sender As Object, ByVal E As DataGridCommandEventArgs)
        Dim objCommand As SqlCommand
        Dim objTextBox As TextBox

        Dim strSQL As String = "UPDATE titles " & _
            "SET title = @Title, price = @Price " & _
            "WHERE title_id = @ID"

        objCommand = New SqlCommand(strSQL, objConnection)

        objCommand.Parameters.Add(New SqlParameter("@ID", _
           SqlDbType.VarChar, 6))
        objCommand.Parameters.Add(New SqlParameter("@Title", _
           SqlDbType.VarChar, 80))
        objCommand.Parameters.Add(New SqlParameter("@Price", _
           SqlDbType.Money, 8))

        objCommand.Parameters("@Id").Value = _
           grdAuthors.DataKeys(CInt(E.Item.ItemIndex))

        objTextBox = E.Item.FindControl("edit_title")
        objCommand.Parameters("@Title").Value = objTextBox.Text

        objTextBox = E.Item.FindControl("edit_price")
        If Left(objTextBox.Text, 1) = "$" Then
            objTextBox.Text = _
             Right(objTextBox.Text, Len(objTextBox.Text) - 1)
        End If
        objCommand.Parameters("@Price").Value = objTextBox.Text

        objCommand.Connection.Open()

        objCommand.ExecuteNonQuery()

        grdAuthors.EditItemIndex = -1

        objCommand.Connection.Close()

        'Bind the DataGrid using the saved sort column
        BindGrid(Session("SortColumn"))

        objCommand = Nothing
        objTextBox = Nothing
    End Sub

Notice the SortExpression property in the BoundColumn's and TemplateColumn's in the HTML code. The key for the code behind file lies in using a Session variable to store the sort field that the user has chosen. This sort field is then used in the SQL SELECT statement when retreiving the data from the database and in the calls to the BindGrid procedure.