Solved

How to sort an unbound DataGridView control in vb.net

Posted on 2009-04-07
5
977 Views
Last Modified: 2013-12-26
I have a vb.net form with datagridview control that bound programmatically. The data source for this control is coming from a SQL query, I want to take advantage of the sorting capabilities that are part of the datagridview. How can i accomplish the column sorting. I want to give the user the ability to sort by clicking on any of the columns.

Here is how I'm supplying the datasource:

 TicketList = TicketsDB.GetTickets("Open")

            DataGridViewTickets.DataSource = TicketList
0
Comment
Question by:mkarkoukli
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:Avelan
ID: 24092595
Hey,

Without seeing your project, there's one suggestion that i'll make.
Check the SortMode property of each column in your datagridview and
make it Automatic. Although in a testproject i made, all of the columns are
set to Automatic by default and sorting works without any changes to
anywhere.

-Avelan
0
 

Author Comment

by:mkarkoukli
ID: 24098666
Here is the code that populates the datagridview


Try
            TicketList = TicketsDB.GetTickets("Open")

            DataGridViewTickets.DataSource = TicketList

            DataGridViewTickets.Columns("TicketId").Visible = False
            DataGridViewTickets.Columns("User").Visible = False
            DataGridViewTickets.Columns("Category").Visible = False
            DataGridViewTickets.Columns("Product").Visible = False
            DataGridViewTickets.Columns("Status").Visible = False
            DataGridViewTickets.Columns("TicketDescription").Visible = False
            DataGridViewTickets.Columns("fkDepartmentID").Visible = False
            DataGridViewTickets.Columns("AssignedTo").Visible = False
            DataGridViewTickets.Columns("MarkForDeletion").Visible = False

            DataGridViewTickets.Columns("TicketNumber").HeaderText = "Ticket Number"
            DataGridViewTickets.Columns("DateEntered").HeaderText = "Date Created"
            DataGridViewTickets.Columns("UserFullName").HeaderText = "Client Name"
            DataGridViewTickets.Columns("TicketAging").HeaderText = "Aging"
            DataGridViewTickets.Columns("Priority").HeaderText = "Priority"
            DataGridViewTickets.Columns("StatusDesc").HeaderText = "Status"
            DataGridViewTickets.Columns("AssignedToName").HeaderText = "Assigned To"
            DataGridViewTickets.Columns("DateClosed").HeaderText = "Date Closed"
            DataGridViewTickets.Columns("TicketType").HeaderText = "Ticket Type"
            DataGridViewTickets.Columns("Department").HeaderText = "Department"
            DataGridViewTickets.Columns("CategoryName").HeaderText = "Category"
            DataGridViewTickets.Columns("ProductServiceName").HeaderText = "Product/Service"


            DataGridViewTickets.Columns("TicketNumber").DisplayIndex = 1
            DataGridViewTickets.Columns("StatusDesc").DisplayIndex = 2
            DataGridViewTickets.Columns("Priority").DisplayIndex = 3
            DataGridViewTickets.Columns("DateEntered").DisplayIndex = 4
            DataGridViewTickets.Columns("DateClosed").DisplayIndex = 5
            DataGridViewTickets.Columns("TicketAging").DisplayIndex = 6
            DataGridViewTickets.Columns("UserFullName").DisplayIndex = 7
            DataGridViewTickets.Columns("Department").DisplayIndex = 8
            DataGridViewTickets.Columns("AssignedToName").DisplayIndex = 9
            DataGridViewTickets.Columns("TicketType").DisplayIndex = 10
            DataGridViewTickets.Columns("CategoryName").DisplayIndex = 11
            DataGridViewTickets.Columns("ProductServiceName").DisplayIndex = 12

            'DataGridViewTickets.ReadOnly = True
            DataGridViewTickets.AllowUserToResizeColumns = False
            DataGridViewTickets.SelectionMode = DataGridViewSelectionMode.FullRowSelect
            DataGridViewTickets.AutoResizeColumns()

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        End Try
0
 
LVL 4

Expert Comment

by:Avelan
ID: 24099250
What is your datasource?

Your datasource needs to support sorting.

-Avelan
0
 

Author Comment

by:mkarkoukli
ID: 24099347
My datasource is a list  "List(Of Tickets)"


Public Shared Function GetTickets(ByVal strMode As String) As List(Of Tickets)

        Dim TicketList As New List(Of Tickets)

        Dim connection As SqlConnection = DBConnection.GetConnection(True)

        Dim SQL As String = ""

        Const closed As Integer = 5
 

        If strMode = "Open" Then

            SQL = "SELECT tblTicketsQueue.pkTicketID, tblTicketsQueue.DateEntered, tblUsers.UserFirstName, " _

                      & "tblUsers.UserLastName, tblUsers_1.UserFirstName AS AFirstName, tblUsers_1.UserLastName AS ALastName, tblDepartment.DepartmentName, " _

                      & "tblTicketsQueue.TicketNumber, " _

                      & "tblTicketsQueue.TicketPriority, tblTicketsQueue.TicketType, tblCategory.CategoryDesc, " _

                      & "tblProductsAndServices.ProductsAndServicesDesc, tblTicketsQueue.TicketDescription, " _

                      & "tblTicketsQueue.CloseDate, tblStatus.StatusDesc, tblTicketsQueue.fkUser, tblTicketsQueue.fkCategory, " _

                      & "tblTicketsQueue.fkProduct, tblTicketsQueue.fkStatus, tblTicketsQueue.AssignedTo, tblUsers.fkDepartmentID " _

                & "FROM tblTicketsQueue INNER JOIN " _

                      & "tblStatus ON tblTicketsQueue.fkStatus = tblStatus.pkStatusID INNER JOIN " _

                      & "tblUsers ON tblTicketsQueue.fkUser = tblUsers.pkUserID INNER JOIN " _

                      & "tblCategory ON tblTicketsQueue.fkCategory = tblCategory.pkCategoryID INNER JOIN " _

                      & "tblDepartment ON tblUsers.fkDepartmentID = tblDepartment.pkDepartmentID INNER JOIN " _

                      & "tblProductsAndServices ON tblTicketsQueue.fkProduct = tblProductsAndServices.pkProductAndServices LEFT OUTER JOIN " _

                      & "tblUsers AS tblUsers_1 ON tblTicketsQueue.AssignedTo = tblUsers_1.pkUserID " _

                & "WHERE tblTicketsQueue.fkStatus NOT LIKE " & closed & " AND tblTicketsQueue.MarkForDeletion NOT LIKE 1 " _

                & "ORDER BY pkTicketID DESC"
 

        End If

        Dim selectCommand As New SqlCommand(SQL, connection)
 

        Try

            connection.Open()

            Dim reader As SqlDataReader

            reader = selectCommand.ExecuteReader()

            Dim i As Integer

            i = reader.RecordsAffected
 

            Dim Ticket As Tickets

            Do While reader.Read

                Ticket = New Tickets

                Ticket.TicketId = CLng(reader("pkTicketID"))

                Ticket.DateEntered = CDate(reader("DateEntered"))

                If Not IsDBNull(reader("CloseDate")) Then

                    Ticket.TicketAging = DateDiff(DateInterval.DayOfYear, CDate(reader("DateEntered")), CDate(reader("CloseDate")))

                Else

                    Ticket.TicketAging = DateDiff(DateInterval.DayOfYear, CDate(reader("DateEntered")), Now)

                End If

                Ticket.TicketNumber = reader("TicketNumber").ToString

                Ticket.UserFullName = reader("UserFirstName").ToString & " " & reader("UserLastName").ToString

                Ticket.AssignedToName = reader("AFirstName").ToString & " " & reader("ALastName").ToString

                Ticket.Department = reader("DepartmentName").ToString

                Ticket.Priority = reader("TicketPriority").ToString

                Ticket.TicketType = reader("TicketType").ToString

                Ticket.CategoryName = reader("CategoryDesc").ToString

                Ticket.ProductServiceName = reader("ProductsAndServicesDesc").ToString

                Ticket.StatusDesc = reader("StatusDesc").ToString

                Ticket.User = CInt(reader("fkUser"))

                Ticket.Category = CInt(reader("fkCategory"))

                Ticket.Product = CLng(reader("fkProduct"))

                Ticket.Status = CInt(reader("fkStatus"))

                Ticket.fkDepartmentID = CInt(reader("fkDepartmentID"))

                Ticket.TicketDescription = reader("TicketDescription").ToString

                If Not IsDBNull(reader("CloseDate")) Then

                    Ticket.DateClosed = CDate(reader("CloseDate"))

                End If

                If Not IsDBNull(reader("AssignedTo")) Then

                    Ticket.AssignedTo = CLng(reader("AssignedTo"))

                End If

                TicketList.Add(Ticket)

            Loop

            reader.Close()
 

        Catch ex As Exception

            Throw ex

        Finally

            connection.Close()

        End Try

        Return TicketList

    End Function

Open in new window

0
 
LVL 4

Accepted Solution

by:
Avelan earned 500 total points
ID: 24099568
Ok, you will need to implement sorting in a custom list.

I won't paste any links since there were too many of them,
just put 'sortable bindinglist' in google and you'll probably find
your answer quicker that way :)

-Avelan
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now