mkarkoukli
asked on
How to sort an unbound DataGridView control in vb.net
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.DataSo urce = TicketList
Here is how I'm supplying the datasource:
TicketList = TicketsDB.GetTickets("Open
DataGridViewTickets.DataSo
ASKER
Here is the code that populates the datagridview
Try
TicketList = TicketsDB.GetTickets("Open ")
DataGridViewTickets.DataSo urce = TicketList
DataGridViewTickets.Column s("TicketI d").Visibl e = False
DataGridViewTickets.Column s("User"). Visible = False
DataGridViewTickets.Column s("Categor y").Visibl e = False
DataGridViewTickets.Column s("Product ").Visible = False
DataGridViewTickets.Column s("Status" ).Visible = False
DataGridViewTickets.Column s("TicketD escription ").Visible = False
DataGridViewTickets.Column s("fkDepar tmentID"). Visible = False
DataGridViewTickets.Column s("Assigne dTo").Visi ble = False
DataGridViewTickets.Column s("MarkFor Deletion") .Visible = False
DataGridViewTickets.Column s("TicketN umber").He aderText = "Ticket Number"
DataGridViewTickets.Column s("DateEnt ered").Hea derText = "Date Created"
DataGridViewTickets.Column s("UserFul lName").He aderText = "Client Name"
DataGridViewTickets.Column s("TicketA ging").Hea derText = "Aging"
DataGridViewTickets.Column s("Priorit y").Header Text = "Priority"
DataGridViewTickets.Column s("StatusD esc").Head erText = "Status"
DataGridViewTickets.Column s("Assigne dToName"). HeaderText = "Assigned To"
DataGridViewTickets.Column s("DateClo sed").Head erText = "Date Closed"
DataGridViewTickets.Column s("TicketT ype").Head erText = "Ticket Type"
DataGridViewTickets.Column s("Departm ent").Head erText = "Department"
DataGridViewTickets.Column s("Categor yName").He aderText = "Category"
DataGridViewTickets.Column s("Product ServiceNam e").Header Text = "Product/Service"
DataGridViewTickets.Column s("TicketN umber").Di splayIndex = 1
DataGridViewTickets.Column s("StatusD esc").Disp layIndex = 2
DataGridViewTickets.Column s("Priorit y").Displa yIndex = 3
DataGridViewTickets.Column s("DateEnt ered").Dis playIndex = 4
DataGridViewTickets.Column s("DateClo sed").Disp layIndex = 5
DataGridViewTickets.Column s("TicketA ging").Dis playIndex = 6
DataGridViewTickets.Column s("UserFul lName").Di splayIndex = 7
DataGridViewTickets.Column s("Departm ent").Disp layIndex = 8
DataGridViewTickets.Column s("Assigne dToName"). DisplayInd ex = 9
DataGridViewTickets.Column s("TicketT ype").Disp layIndex = 10
DataGridViewTickets.Column s("Categor yName").Di splayIndex = 11
DataGridViewTickets.Column s("Product ServiceNam e").Displa yIndex = 12
'DataGridViewTickets.ReadO nly = True
DataGridViewTickets.AllowU serToResiz eColumns = False
DataGridViewTickets.Select ionMode = DataGridViewSelectionMode. FullRowSel ect
DataGridViewTickets.AutoRe sizeColumn s()
Catch ex As Exception
MessageBox.Show(ex.Message , ex.GetType.ToString)
End Try
Try
TicketList = TicketsDB.GetTickets("Open
DataGridViewTickets.DataSo
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
DataGridViewTickets.Column
'DataGridViewTickets.ReadO
DataGridViewTickets.AllowU
DataGridViewTickets.Select
DataGridViewTickets.AutoRe
Catch ex As Exception
MessageBox.Show(ex.Message
End Try
What is your datasource?
Your datasource needs to support sorting.
-Avelan
Your datasource needs to support sorting.
-Avelan
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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