[RESOLVED][02/03] How to filter data on the listview

[shyguyjeff]

Hello everyone.. It's me again.. Could anyone help me upon filtering my listview?


My listview goes like this

This is my listview display

Source    ItemCode    Date      Qty  Description
123         ABC       1/11/05     1     TV
123         DEF       1/11/05     1     VCD
123         GHI       1/11/05     1     TVRACK
234         KLM       1/12/06     1     MIC



I want my data on listview to become like this

Source    ItemCode    Date      Qty  Description
123         ABC       1/11/05     1     Various Item
234         KLM       1/12/06     1     MIC

What happen to my display is that everytime the data exist already or it display multiple Source I want to to merge and on my description i want to display Various Item or Multiple item...Could anyone help me like this?

[.paul.]

that way you're discarding the itemcodes, dates, quantities, + descriptions.

you could group items in your listview by source instead

[shyguyjeff]

Last time i decided to do this on my query but i could not use group on it because i made a cross joins on my query so i decided to filter out my data on my application part... How can I group it?

[.paul.]

heres how to add groups to your listview

Public Class Form1
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim group1 As New ListViewGroup("Group 123", HorizontalAlignment.Left)
        group1.Name = "Group 123"
        ListView1.Groups.Add(group1)
 
        With ListView1.Items
            .Add("123")
            .Item(.Count - 1).SubItems.Add("ABC")
            .Item(.Count - 1).SubItems.Add("1/11/05")
            .Item(.Count - 1).SubItems.Add("1")
            .Item(.Count - 1).SubItems.Add("TV")
            .Item(.Count - 1).Group = group1
 
 
            .Add("123")
            .Item(.Count - 1).SubItems.Add("DEF")
            .Item(.Count - 1).SubItems.Add("1/11/05")
            .Item(.Count - 1).SubItems.Add("1")
            .Item(.Count - 1).SubItems.Add("VCD")
            .Item(.Count - 1).Group = group1
 
            .Add("123")
            .Item(.Count - 1).SubItems.Add("GHI")
            .Item(.Count - 1).SubItems.Add("1/11/05")
            .Item(.Count - 1).SubItems.Add("1")
            .Item(.Count - 1).SubItems.Add("TVRACK")
            .Item(.Count - 1).Group = group1
 
            Dim group2 As New ListViewGroup("Group 234", HorizontalAlignment.Left)
            group2.Name = "Group 234"
            ListView1.Groups.Add(group2)
 
            .Add("234")
            .Item(.Count - 1).SubItems.Add("KLM")
            .Item(.Count - 1).SubItems.Add("1/12/06")
            .Item(.Count - 1).SubItems.Add("1")
            .Item(.Count - 1).SubItems.Add("MIC")
            .Item(.Count - 1).Group = group2
        End With
 
    End Sub
 
End Class

[shyguyjeff]

Nice paul but how can I group that display? What I mean is that for those who have the same source i want to display one row only....Like for example since the they have the same source my itemcode will display only the first and on the description i want to display various item...i want them to merge at one row since they have the same source..How can I do that?

Instead of this...

Source    ItemCode    Date      Qty  Description
123         ABC       1/11/05     1     TV
123         DEF       1/11/05     1     VCD
123         GHI       1/11/05     1     TVRACK


It will become like this:

Source    ItemCode    Date      Qty  Description
123         ABC       1/11/05     1     Various Item

[.paul.]

where are you getting the data from?
you can easily filter it as you add it to the listview.
post your code + i'll have another look

[.paul.]

also, the itemcodes are all different. do you just want to use the first one?
what if the dates are different?
do you want the qty to be 1 various item or 3 various item?

[shyguyjeff]

here it goes paul:

    Dim mySelectedDatabase = "EMCOR" + txtStore.Text
        mySQLConnection = New SqlConnection("server=" & myServerName & ";trusted_connection=true;" & _
               "database=" & mySelectedDatabase & "")
        ' create a command that would query to database 
        Dim cm As New SqlCommand("SELECT DISTINCT cust_id, storeid, department, date, a.source, i.itemcode, Qty, salesman, status = 'C', itemname, serialno, saletype = 'CS' FROM cashsale as cs INNER JOIN sale_aux as a ON  cs.source = a.source LEFT OUTER JOIN sale_sn as n ON cs.source = n.source LEFT OUTER JOIN item as i ON a.itemcode = i.itemcode WHERE cs.cust_id = '" & txtCustID.Text & _
          "' UNION SELECT DISTINCT cust_id, storeid, department, date, a.source, i.itemcode, Qty, salesman, status, itemname, serialno, saletype FROM sale as s INNER JOIN sale_aux as a ON  s.source = a.source LEFT OUTER JOIN sale_sn as n ON s.source = n.source LEFT OUTER JOIN item as i ON a.itemcode = i.itemcode  WHERE s.cust_id = '" & txtCustID.Text & "'", mySQLConnection)

        mySQLConnection.Open()

        Dim t As New DataTable
        ' initializing its column to complement on how many fields I want to return in my query command 
        t.Columns.Add("source")
        t.Columns.Add("itemcode")
        t.Columns.Add("date")
        t.Columns.Add("department")
        t.Columns.Add("Qty")
        t.Columns.Add("itemname")
        t.Columns.Add("saletype")
        t.Columns.Add("serialno")
        t.Columns.Add("salesman")

        ' a method to access read-only the result set. 
        Dim reader As SqlDataReader = cm.ExecuteReader()
        While reader.Read()
            ' create new row 
            Dim r As DataRow = t.NewRow()
            r(0) = reader("source")
            r(1) = reader("itemcode")
            r(2) = reader("date")
            r(3) = reader("department")
            r(4) = reader("Qty")
            r(5) = reader("itemname")
            r(6) = reader("saletype")
            r(7) = reader("serialno")
            r(8) = reader("salesman")

            ' add a row to a datatable 
            t.Rows.Add(r)
        End While

        ' close reader 
        reader.Close()
        ' close the connection 
        mySQLConnection.Close()

        For i As Integer = 0 To t.Rows.Count - 1
            Dim li As ListViewItem = ListView1.Items.Add(t.Rows(i)("source").ToString())
            li.SubItems.Add(t.Rows(i)("itemcode").ToString())
            li.SubItems.Add(t.Rows(i)("date").ToString())
            li.SubItems.Add(t.Rows(i)("department").ToString())
            li.SubItems.Add(t.Rows(i)("Qty").ToString())
            li.SubItems.Add(t.Rows(i)("itemname").ToString())
            li.SubItems.Add(t.Rows(i)("saletype").ToString())
            li.SubItems.Add(t.Rows(i)("serialno").ToString())
            li.SubItems.Add(t.Rows(i)("salesman").ToString())

        Next
        Try
            If (ListView1.Items.Count > 0) Then
                ListView1.Items(0).Selected = True
            Else
                f.Visible = True
                MessageBox.Show("No transaction found!")
                Me.Visible = False
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

a million thanks for the time and effort

[.paul.]

you could narrow down or filter your listview items most easily by improving your select statement. this isn't the best forum for database questions. try asking in the database development forum

[.paul.]

or you could filter it as you add it to the listview

For i As Integer = 0 To t.Rows.Count - 1
            If i = 0 Or ListView1.Items.Item(ListView1.Items.Count - 1).Text <> t.Rows(i)("source").ToString() Then
                Dim li As ListViewItem = ListView1.Items.Add(t.Rows(i)("source").ToString())
                li.SubItems.Add(t.Rows(i)("itemcode").ToString())
                li.SubItems.Add(t.Rows(i)("date").ToString())
                li.SubItems.Add(t.Rows(i)("department").ToString())
                li.SubItems.Add(t.Rows(i)("Qty").ToString())
                li.SubItems.Add(t.Rows(i)("itemname").ToString())
                li.SubItems.Add(t.Rows(i)("saletype").ToString())
                li.SubItems.Add(t.Rows(i)("serialno").ToString())
                li.SubItems.Add(t.Rows(i)("salesman").ToString())
            ElseIf ListView1.Items.Count > 0 Then
                ListView1.Items.Item(ListView1.Items.Count - 1).SubItems(3).Text = "Various Items"
            End If
Next

[shyguyjeff]

a million thanks again to you paul....Thank you very much...i will try that also to post my query so that they Some of the member here can advice on my query...Thank you so much paul..

[shyguyjeff]

but w8 a minute paul..why is it that on my program it run but my problem is that it displays. "Specified argument was out of range of valid values. Parameter name: '-1' is not a valid value for 'displayIndex'

[.paul.]

you'd probably need to sort your sql query too.

something like SORT BY cust_id

[.paul.]

try this

For i As Integer = 0 To t.Rows.Count - 1
     If i > 0 then
        if ListView1.Items.Item(ListView1.Items.Count - 1).Text <> t.Rows(i)("source").ToString() Then
           Dim li As ListViewItem = ListView1.Items.Add(t.Rows(i)("source").ToString())
           li.SubItems.Add(t.Rows(i)("itemcode").ToString())
           li.SubItems.Add(t.Rows(i)("date").ToString())
           li.SubItems.Add(t.Rows(i)("department").ToString())
           li.SubItems.Add(t.Rows(i)("Qty").ToString())
           li.SubItems.Add(t.Rows(i)("itemname").ToString())
           li.SubItems.Add(t.Rows(i)("saletype").ToString())
           li.SubItems.Add(t.Rows(i)("serialno").ToString())
           li.SubItems.Add(t.Rows(i)("salesman").ToString())
       ElseIf ListView1.Items.Count > 0 Then
           ListView1.Items.Item(ListView1.Items.Count - 1).SubItems(3).Text = "Various Items"
       End If
    else
        Dim li As ListViewItem = ListView1.Items.Add(t.Rows(i)("source").ToString())
        li.SubItems.Add(t.Rows(i)("itemcode").ToString())
        li.SubItems.Add(t.Rows(i)("date").ToString())
        li.SubItems.Add(t.Rows(i)("department").ToString())
        li.SubItems.Add(t.Rows(i)("Qty").ToString())
        li.SubItems.Add(t.Rows(i)("itemname").ToString())
        li.SubItems.Add(t.Rows(i)("saletype").ToString())
        li.SubItems.Add(t.Rows(i)("serialno").ToString())
        li.SubItems.Add(t.Rows(i)("salesman").ToString())
    end if
Next

[shyguyjeff]

now it's working paul..A again a million thanks to you...God Bless and Have a nice day!