Solved

VB.Net Listview Refresh to Show Top N Rows

Posted on 2014-03-13
9
63 Views
Last Modified: 2016-07-11
Is there a way to refresh the items currently in a listview control so that it shows only the top N rows?  I have a listview control in a VB form that shows customer data for a particular salesperson.  I've configured the listbox so that it can be sorted by a variety of column values.  I would like to introduce functionality so that the salesperson can show all of their customers or only the top N customers.

I really cannot modify the SQL select statement as it's pulling various totals for different values.  So saying something like "Select Top 10..." really won't work in this specific instance.

I'm hoping that someone can help me out with this.
0
Comment
Question by:trbbhm
  • 4
  • 3
9 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 250 total points
Comment Utility
Are you able to use LINQ in any way (Entity Framework, LINQ-to-SQL)? If yes you could define your query and only take the top n records

Table.OrderBy(Function(x) x.Column).Take(n)

 or run your SP and take only the top n records

SP(param1,param2,...).OrderBy(Function(x) x.Column).Take(n)

If you are not familiar with LINQ you could load a DataTable with all data from the db for the specific salesperson and then you may check this post on how to do it:
http://stackoverflow.com/questions/2546240/return-top-5-records-of-a-table-in-a-dataset

Giannis
0
 

Author Comment

by:trbbhm
Comment Utility
I'm not sure that this approach would work in my specific case.  The query includes spending totals (12 mos, 24, mos, 36 mos, lifetime, etc) that are plugged into my listview control, and the user has the ability to sort the data on any number of these totals by clicking on the column headers.  I don't want to have to hit the database each time they change the sort criteria.  

I was hoping for a solution that would allow me to fill the listview with my data and then, somehow, magically, set my listview to only show "x" number of records (regardless of how the data is sorted).

Maybe it's not possible.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
You could do it with jscript. I had seen a library that supported LINQ. But i wouldn't know how you would apply it exactly in your case.

In principle you would have an array loaded from the db once, and then apply LINQ on that on client side.

If you can be more specific on code etc, we could probably see a more specific approach.

Giannis
0
 

Author Comment

by:trbbhm
Comment Utility
Below is the code that I use to populate the Listview from my query.  Perhaps this can help.

Sub Load_SQL_Data(ByVal SQLString As String)
        Dim strPrevCustNum As String = ""
        Dim intVendCount As Integer = 0

        Dim con As New SqlConnection
        con.ConnectionString = "server=SERVER1;database=CRM_BR;Trusted_Connection=True;"
        
        con.Open()

        SQLString = "Use CRM_BR " & SQLString

        Dim cmd As New SqlCommand
        cmd.Connection = con
        cmd.CommandText = SQLString

        Dim rdr As SqlDataReader = cmd.ExecuteReader()

        ListView1.Items.Clear()

        While rdr.Read
            Dim strCustName As String = Trim(rdr("FirstName").ToString) & " " & Trim(rdr("LastName").ToString)
            Dim lvwItem As ListViewItem

            If rdoSalesHist.Checked = True Then
                'LOAD SALES HISTORY
                lvwItem = New ListViewItem
                lvwItem.Text = rdr("customernum")
                lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                lvwItem.SubItems.Add(strCustName)

                If Not IsDBNull(rdr("LifetimePurchase")) Then
                    lvwItem.SubItems.Add(Val(rdr("LifetimePurchase")))
                    lvwItem.SubItems.Add(rdr("lifetimeATransCount"))
                End If

                If Not IsDBNull(rdr("Month12Spend")) Then
                    lvwItem.SubItems.Add(Val(rdr("Month12Spend")))
                    lvwItem.SubItems.Add(rdr("Month12TranCount"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("Month18Spend")) Then
                    lvwItem.SubItems.Add(Val(rdr("Month18Spend")))
                    lvwItem.SubItems.Add(rdr("Month18TranCount"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("Year4Spend")) Then
                    lvwItem.SubItems.Add(Val(rdr("Year4Spend")))
                    lvwItem.SubItems.Add(rdr("Year4TranCount"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                ListView1.Items.Add(lvwItem)
                '====================================================================
            ElseIf rdoPricePts.Checked = True Then
                'LOAD PRICE POINTS
                lvwItem = New ListViewItem
                lvwItem.Text = rdr("customernum")
                lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                lvwItem.SubItems.Add(strCustName)

                If Not IsDBNull(rdr("0-100_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("0-100_dols")))
                    lvwItem.SubItems.Add(rdr("0-100_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("100-250_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("100-250_Dols")))
                    lvwItem.SubItems.Add(rdr("100-250_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("250-500_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("250-500_dols")))
                    lvwItem.SubItems.Add(rdr("250-500_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("500-750_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("500-750_Dols")))
                    lvwItem.SubItems.Add(rdr("500-750_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("750-1000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("750-1000_dols")))
                    lvwItem.SubItems.Add(rdr("750-1000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("1000-1500_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("1000-1500_Dols")))
                    lvwItem.SubItems.Add(rdr("1000-1500_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("1500-2000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("1500-2000_dols")))
                    lvwItem.SubItems.Add(rdr("1500-2000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("2000-2500_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("2000-2500_Dols")))
                    lvwItem.SubItems.Add(rdr("2000-2500_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("2500-3000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("2500-3000_dols")))
                    lvwItem.SubItems.Add(rdr("2500-3000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("3000-4000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("3000-4000_Dols")))
                    lvwItem.SubItems.Add(rdr("3000-4000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("4000-5000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("4000-5000_dols")))
                    lvwItem.SubItems.Add(rdr("4000-5000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("5000-7500_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("5000-7500_Dols")))
                    lvwItem.SubItems.Add(rdr("5000-7500_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("7500-10000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("7500-10000_dols")))
                    lvwItem.SubItems.Add(rdr("7500-10000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("10000-15000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("10000-15000_Dols")))
                    lvwItem.SubItems.Add(rdr("10000-15000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("15000-20000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("15000-20000_dols")))
                    lvwItem.SubItems.Add(rdr("15000-20000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("20000-30000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("20000-30000_Dols")))
                    lvwItem.SubItems.Add(rdr("20000-30000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("30000-40000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("30000-40000_dols")))
                    lvwItem.SubItems.Add(rdr("30000-40000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("40000-50000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("40000-50000_Dols")))
                    lvwItem.SubItems.Add(rdr("40000-50000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("50000-75000_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("50000-75000_dols")))
                    lvwItem.SubItems.Add(rdr("50000-75000_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                If Not IsDBNull(rdr("75000-999999_Dols")) Then
                    lvwItem.SubItems.Add(Val(rdr("75000-999999_Dols")))
                    lvwItem.SubItems.Add(rdr("75000-999999_tran_count"))
                Else
                    lvwItem.SubItems.Add("0")
                    lvwItem.SubItems.Add("0")
                End If

                ListView1.Items.Add(lvwItem)
                '====================================================================
            ElseIf rdoCustInfo.Checked = True Then
                lvwItem = New ListViewItem
                lvwItem.Text = rdr("customernum")
                lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                lvwItem.SubItems.Add(strCustName)

                lvwItem.SubItems.Add(Val(rdr("LifetimeSalesDollars")))
                lvwItem.SubItems.Add(rdr("lifetimeTranCount"))

                If Not IsDBNull(rdr("dateofbirtha")) Then
                    lvwItem.SubItems.Add(rdr("dateofbirtha"))
                Else
                    lvwItem.SubItems.Add("")
                End If

                If Not IsDBNull(rdr("PartnerFirstName")) Then
                    lvwItem.SubItems.Add(Trim(rdr("PartnerFirstName")))
                Else
                    lvwItem.SubItems.Add("")
                End If

                If Not IsDBNull(rdr("DateOfAnniversary")) Then
                    lvwItem.SubItems.Add(rdr("DateOfAnniversary"))
                Else
                    lvwItem.SubItems.Add("")
                End If

                If Not IsDBNull(rdr("partnerdob")) Then
                    lvwItem.SubItems.Add(rdr("partnerdob"))
                Else
                    lvwItem.SubItems.Add("")
                End If

                If Not IsDBNull(rdr("CustomerEmailAddress")) Then
                    lvwItem.SubItems.Add(Trim(rdr("CustomerEmailAddress")))
                Else
                    lvwItem.SubItems.Add("")
                End If

                If Not IsDBNull(rdr("PartnerEmailAddress")) Then
                    lvwItem.SubItems.Add(Trim(rdr("PartnerEmailAddress")))
                Else
                    lvwItem.SubItems.Add("")
                End If

                ListView1.Items.Add(lvwItem)
                '====================================================================
            ElseIf rdoCustVendPrefs.Checked = True Then
                If strPrevCustNum = "" Then
                    strPrevCustNum = rdr("customernum")
                    lvwItem = New ListViewItem
                    lvwItem.Text = rdr("customernum")
                    lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                    lvwItem.SubItems.Add(strCustName)
                End If
                If rdr("customernum") = strPrevCustNum And intVendCount < 4 Then
                    lvwItem.SubItems.Add(Trim(rdr("vendorname")))
                    lvwItem.SubItems.Add(Int(rdr("vendorpurchamt")))
                    lvwItem.SubItems.Add(rdr("vendortranscount"))
                    intVendCount += 1
                Else
                    If rdr("customernum") <> strPrevCustNum Then
                        strPrevCustNum = rdr("customernum")
                        ListView1.Items.Add(lvwItem)
                        intVendCount = 1
                        lvwItem = New ListViewItem
                        lvwItem.Text = rdr("customernum")
                        lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                        lvwItem.SubItems.Add(strCustName)
                        lvwItem.SubItems.Add(Trim(rdr("vendorname")))
                        lvwItem.SubItems.Add(Int(rdr("vendorpurchamt")))
                        lvwItem.SubItems.Add(rdr("vendortranscount"))
                    ElseIf (rdr("customernum") = strPrevCustNum And intVendCount >= 4) Then
                    End If
                End If

                '====================================================================
            ElseIf rdoSpecific.Checked = True Then

                lvwItem = New ListViewItem
                lvwItem.Text = rdr("customernum")
                lvwItem.SubItems.Add(rdr("CustLevel").ToString)
                lvwItem.SubItems.Add(strCustName)
                lvwItem.SubItems.Add(Int(rdr("LifetimeSalesDollars")))

                If strVendor1.ToString <> "" Then
                    If IsDBNull(rdr(5)) Then
                        lvwItem.SubItems.Add("0")
                    Else
                        lvwItem.SubItems.Add(Int(rdr(5)))
                    End If

                Else
                    lvwItem.SubItems.Add(Int(rdr("sales")))
                    lvwItem.SubItems.Add(rdr("trcount"))

                End If

                If strVendor2.ToString <> "" Then
                    If IsDBNull(rdr(6)) Then
                        lvwItem.SubItems.Add("0")
                    Else
                        lvwItem.SubItems.Add(Int(rdr(6)))
                    End If
                End If

                If strVendor3.ToString <> "" Then
                    If IsDBNull(rdr(7)) Then
                        lvwItem.SubItems.Add("0")
                    Else
                        lvwItem.SubItems.Add(Int(rdr(7)))
                    End If
                End If

                ListView1.Items.Add(lvwItem)

                'lvwItem.SubItems.Add(rdr("SALES"))
            End If

        End While

        con.Close()
        con.Dispose()
        AppNewAutosizeColumns(ListView1, -2)

        Alternate_Colors()
    End Sub

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Sorry, before i continue...

Is this a web application or windows forms?
0
 

Author Comment

by:trbbhm
Comment Utility
Uh oh - that question doesn't sound good.  It's a Windows Form.
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 250 total points
Comment Utility
Here are some comments:

1) The ListView does not have a "Top N" property.

2) LINQ is a good way to approach this, as it is an in-memory operation, and you wouldn't have to hit the database again.

3) Statements like this can be simplified:

 If Not IsDBNull(rdr("DateOfAnniversary")) Then
      lvwItem.SubItems.Add(rdr("DateOfAnniversary"))
 Else
      lvwItem.SubItems.Add("")
 End If

Open in new window


   to this:

       
  lvwItem.SubItems.Add(rdr("DateOfAnniversary").ToString())

Open in new window


   The ToString method will translate DBNull to "" for you.
0
 

Author Comment

by:trbbhm
Comment Utility
Great!  Thanks!  I'll look into the LINQ approach - I've never used this before.

Thanks again!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

18 Experts available now in Live!

Get 1:1 Help Now