Solved

Sorting In List View VB2005

Posted on 2010-11-16
4
237 Views
Last Modified: 2012-05-10
I have code designed to sort a list view based on which column head is clicked. It works fine for the first two columns - sorting by User Last Name or Customer Name - but none of the other ones work.

Since all of the data in the list view is text, I would understand why I might have to trick the system for sorting on the data or number values, but CampaignName, Starts and Ends are just other string data in the table. So I must have some additional work to do.

Thanks!
'The Click Event

Private Sub lvwCampaigns_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles lvwCampaigns.ColumnClick

        Select Case e.Column

            Case Is = 0

                intSelectedSortOrder = 0 'Rep

            Case Is = 1

                intSelectedSortOrder = 1 'Client

            Case Is = 3

                intSelectedSortOrder = 2 'Campaign

            Case Is = 4

                intSelectedSortOrder = 3 'Entered

            Case Is = 5

                intSelectedSortOrder = 4 'Starts

            Case Is = 6

                intSelectedSortOrder = 5 'End

            Case Is = 7

                intSelectedSortOrder = 6 'Total

        End Select

        Me.LoadCampaignsListView()



    End Sub



'The Load Function

Public Sub LoadCampaignsListView()

        Dim strSQL As String

        Dim boolWhereIn As Boolean = False

        Dim objListViewItem As ListViewItem



        'Set Up The SQL For The List Based on the Control State on the Form

        strSQL = "SELECT Campaigns.RevLineID, ([Users]![Last] & ', ' " & _

                    " & Left([Users]![First],1) & '.') AS Rep, Accounts.Customer, " & _

                    " Campaigns.CampaignName, Campaigns.Entered, Campaigns.Starts, " & _

                    " Campaigns.Ends, Campaigns.TotalAmount " & _

                    " FROM (Users INNER JOIN Campaigns ON Users.UserID = Campaigns.UserID) " & _

                    " INNER JOIN Accounts ON Campaigns.AcctID = Accounts.CustomerID "



        If Me.ckClosedCampaigns.CheckState = CheckState.Unchecked Then

            'Excluding Inactive Campaigns

            strSQL = strSQL & "WHERE Campaigns.Inactive <> True "

            boolWhereIn = True

        End If



        If lngSelectedSalesRepID <> 0 Then

            'Filter on the Selected Sales Rep

            If boolWhereIn = True Then

                strSQL = strSQL & "AND Users.UserID = " & lngSelectedSalesRepID



            Else

                strSQL = strSQL & "WHERE Users.UserID = " & lngSelectedSalesRepID

                boolWhereIn = True

            End If

        End If



        If lngSelectedBrandSpecialistID <> 0 Then

            'Filter on the Selected Brand Specialist Rep

            If boolWhereIn = True Then

                strSQL = strSQL & "AND Campaigns.BrandSpecID = " & lngSelectedBrandSpecialistID

            Else

                strSQL = strSQL & "WHERE Campaigns.BrandSpecID = " & lngSelectedBrandSpecialistID

                boolWhereIn = True

            End If

        End If



        Select Case intSelectedSortOrder

            Case Is = 0

                strSQL = strSQL & " ORDER BY Users.Last"

            Case Is = 1

                strSQL = strSQL & " ORDER By Accounts.Customer "

            Case Is = 2

                strSQL = strSQL & " ORDER By Campaigns.CampaignName "

            Case Is = 3

                strSQL = strSQL & " ORDER By Campaigns.Entered "

            Case Is = 4

                strSQL = strSQL & " ORDER By Campaigns.Starts "

            Case Is = 5

                strSQL = strSQL & " ORDER By Campaigns.Ends "

            Case Is = 6

                strSQL = strSQL & " ORDER By Campaigns.TotalAmount "

        End Select





        'Initialize a new instance of the data access base class

        Using objCampaignList As New WDABase

            Try

                'Get the Campaign List - add criteria to this later

                objCampaignList.SQL = strSQL

                objCampaignList.InitializeCommand()

                objCampaignList.OpenConnection()

                objCampaignList.DataReader = objCampaignList.Command.ExecuteReader



                'Check for Rows

                If objCampaignList.DataReader.HasRows Then

                    'Clear the List

                    Me.lvwCampaigns.Items.Clear()



                    'Process All Rows

                    While objCampaignList.DataReader.Read

                        'Create a new ListViewItem

                        objListViewItem = New ListViewItem



                        'Add the data to the List View Item

                        objListViewItem.Tag = objCampaignList.DataReader.Item("RevLineID")

                        objListViewItem.Text = objCampaignList.DataReader.Item("Rep")

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Customer"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("CampaignName"))

                        objListViewItem.SubItems.Add(Format(objCampaignList.DataReader.Item("Entered"), "d"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Starts"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Ends"))

                        objListViewItem.SubItems.Add(FormatCurrency(objCampaignList.DataReader.Item("TotalAmount"), 0))

                        Me.lvwCampaigns.Items.Add(objListViewItem)

                    End While



                End If

            Catch ExceptionErr As Exception

                MessageBox.Show(ExceptionErr.Message, "No Matching Records")

            End Try

        End Using



        'Cleanup

        objListViewItem = Nothing



    End Sub

Open in new window

0
Comment
Question by:Buck_Beasom
  • 2
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
>>but none of the other ones work. <<
What do you mean by this?

Your code looks ok at first glance, although you can simplify it like below

'The Click Event

Private Sub lvwCampaigns_ColumnClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles lvwCampaigns.ColumnClick

        Select Case e.Column

            Case 0

                intSelectedSortOrder = 2 'Rep

            Case 1

                intSelectedSortOrder = 3 'Client

            Case 3

                intSelectedSortOrder = 4 'Campaign

            Case 4

                intSelectedSortOrder = 5 'Entered

            Case 5

                intSelectedSortOrder = 6 'Starts

            Case 6

                intSelectedSortOrder = 7 'End

            Case 7

                intSelectedSortOrder = 8 'Total

        End Select

        Me.LoadCampaignsListView()



    End Sub



'The Load Function

Public Sub LoadCampaignsListView()

        Dim strSQL As String

        Dim boolWhereIn As Boolean = False

        Dim objListViewItem As ListViewItem



        'Set Up The SQL For The List Based on the Control State on the Form

        strSQL = "SELECT Campaigns.RevLineID, ([Users]![Last] & ', ' " & _

                    " & Left([Users]![First],1) & '.') AS Rep, Accounts.Customer, " & _

                    " Campaigns.CampaignName, Campaigns.Entered, Campaigns.Starts, " & _

                    " Campaigns.Ends, Campaigns.TotalAmount " & _

                    " FROM (Users INNER JOIN Campaigns ON Users.UserID = Campaigns.UserID) " & _

                    " INNER JOIN Accounts ON Campaigns.AcctID = Accounts.CustomerID "



        If Me.ckClosedCampaigns.CheckState = CheckState.Unchecked Then

            'Excluding Inactive Campaigns

            strSQL = strSQL & "WHERE Campaigns.Inactive <> True "

            boolWhereIn = True

        End If



        If lngSelectedSalesRepID <> 0 Then

            'Filter on the Selected Sales Rep

            If boolWhereIn = True Then

                strSQL = strSQL & "AND Users.UserID = " & lngSelectedSalesRepID



            Else

                strSQL = strSQL & "WHERE Users.UserID = " & lngSelectedSalesRepID

                boolWhereIn = True

            End If

        End If



        If lngSelectedBrandSpecialistID <> 0 Then

            'Filter on the Selected Brand Specialist Rep

            If boolWhereIn = True Then

                strSQL = strSQL & "AND Campaigns.BrandSpecID = " & lngSelectedBrandSpecialistID

            Else

                strSQL = strSQL & "WHERE Campaigns.BrandSpecID = " & lngSelectedBrandSpecialistID

                boolWhereIn = True

            End If

        End If



	'Sorting

	strSQL = strSQL & " ORDER BY " & cSTr(intSelectedSortOrder)





        'Initialize a new instance of the data access base class

        Using objCampaignList As New WDABase

            Try

                'Get the Campaign List - add criteria to this later

                objCampaignList.SQL = strSQL

                objCampaignList.InitializeCommand()

                objCampaignList.OpenConnection()

                objCampaignList.DataReader = objCampaignList.Command.ExecuteReader



                'Check for Rows

                If objCampaignList.DataReader.HasRows Then

                    'Clear the List

                    Me.lvwCampaigns.Items.Clear()



                    'Process All Rows

                    While objCampaignList.DataReader.Read

                        'Create a new ListViewItem

                        objListViewItem = New ListViewItem



                        'Add the data to the List View Item

                        objListViewItem.Tag = objCampaignList.DataReader.Item("RevLineID")

                        objListViewItem.Text = objCampaignList.DataReader.Item("Rep")

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Customer"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("CampaignName"))

                        objListViewItem.SubItems.Add(Format(objCampaignList.DataReader.Item("Entered"), "d"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Starts"))

                        objListViewItem.SubItems.Add(objCampaignList.DataReader.Item("Ends"))

                        objListViewItem.SubItems.Add(FormatCurrency(objCampaignList.DataReader.Item("TotalAmount"), 0))

                        Me.lvwCampaigns.Items.Add(objListViewItem)

                    End While



                End If

            Catch ExceptionErr As Exception

                MessageBox.Show(ExceptionErr.Message, "No Matching Records")

            End Try

        End Using



        'Cleanup

        objListViewItem = Nothing



    End Sub

Open in new window

0
 

Author Comment

by:Buck_Beasom
Comment Utility
I will try your code. What I mean is, when I click on the headers for the first two columns (Rep and Client) the list view sorts the way I want. But when I click on the other headers, nothing changes.

Thanks. Will let you know how I make out.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
Hi,

Any luck with my suggestion?
0
 

Author Closing Comment

by:Buck_Beasom
Comment Utility
Thanks!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Normally the drop down box control found in the .Net framework tools is able to select just one data and value at a time, which is displayed on the text area.   But what if you want to have multiple values to be selected in the drop down box? As …
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

15 Experts available now in Live!

Get 1:1 Help Now