Wrox Programmer Forums
|
BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003
This is the forum to discuss the Wrox book Professional VB.NET 2003 by Bill Evjen, Billy Hollis, Rockford Lhotka, Tim McCarthy, Jonathan Pinnock, Rama Ramachandran, Bill Sheldon; ISBN: 9780764559921
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 9th, 2003, 02:18 PM
Registered User
 
Join Date: Oct 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sort and Update the DataGrid

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
 
Old February 10th, 2004, 03:16 PM
Registered User
 
Join Date: Feb 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)

 
Old February 10th, 2004, 07:55 PM
Registered User
 
Join Date: Oct 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old February 12th, 2004, 07:10 PM
Registered User
 
Join Date: Feb 2004
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 7th, 2004, 12:23 PM
Registered User
 
Join Date: Apr 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 28th, 2004, 11:48 AM
Registered User
 
Join Date: Apr 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!

 
Old April 28th, 2004, 02:51 PM
Registered User
 
Join Date: Apr 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old September 19th, 2004, 10:01 PM
Registered User
 
Join Date: Sep 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old October 9th, 2004, 07:19 AM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
how to sort datagrid columns sumith ASP.NET 1.0 and 1.1 Professional 3 February 19th, 2007 01:12 AM
datagrid does SORT integer data properly chayanvinayak Flash (all versions) 3 July 27th, 2006 02:59 PM
DataGrid Sort ChrisP VB.NET 2002/2003 Basics 0 January 15th, 2006 12:47 PM
Sort Datagrid Sheikha ASP.NET 1.x and 2.0 Application Design 1 June 29th, 2005 04:46 AM
how to sort the items in the datagrid Ramakrishna.G General .NET 1 September 24th, 2004 02:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.