Solved

VB.Net Listview Refresh to Show Top N Rows

Posted on 2014-03-13
9
65 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
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…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

21 Experts available now in Live!

Get 1:1 Help Now