Solved

VB.Net Listview Refresh to Show Top N Rows

Posted on 2014-03-13
9
70 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
ID: 39928478
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
ID: 39929242
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
ID: 39929308
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:trbbhm
ID: 39929335
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39929374
Sorry, before i continue...

Is this a web application or windows forms?
0
 

Author Comment

by:trbbhm
ID: 39929381
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
ID: 39929862
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
ID: 39930172
Great!  Thanks!  I'll look into the LINQ approach - I've never used this before.

Thanks again!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

777 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