• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

VB.Net Listview Refresh to Show Top N Rows

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
trbbhm
Asked:
trbbhm
  • 4
  • 3
2 Solutions
 
Ioannis ParaskevopoulosCommented:
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
 
trbbhmAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
trbbhmAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
Sorry, before i continue...

Is this a web application or windows forms?
0
 
trbbhmAuthor Commented:
Uh oh - that question doesn't sound good.  It's a Windows Form.
0
 
Bob LearnedCommented:
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
 
trbbhmAuthor Commented:
Great!  Thanks!  I'll look into the LINQ approach - I've never used this before.

Thanks again!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now