Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VB.Net Listview Refresh to Show Top N Rows

Posted on 2014-03-13
9
Medium Priority
?
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 1000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

618 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