Sorting In List View VB2005

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

Buck_BeasomDatabase DesignerAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
>>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
 
Buck_BeasomDatabase DesignerAuthor Commented:
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
 
ralmadaCommented:
Hi,

Any luck with my suggestion?
0
 
Buck_BeasomDatabase DesignerAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.