Avoiding Listview Duplicates

[Tddupre]

I am populating the list view using mysql and i dont know how to avoid duplicate rows.

 Private Sub Btnrefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnrefresh.Click


        Using connection As New MySqlConnection(";")

            Using command As New MySqlCommand("SELECT Distinct  name, type, uploaded, uploader, location FROM programs", connection)

                connection.Open()



                Using reader As MySqlDataReader = command.ExecuteReader()

                    While (reader.Read())
                        Dim name As String = reader("name")
                        Dim type As String = reader("type")
                        Dim uploaded As String = reader("uploaded")
                        Dim uploader As String = reader("uploader")
                        Dim location As String = reader("location")

                        Dim lv As ListViewItem = ListView1.Items.Add(location)


                        lv.SubItems.Add(name)

                        lv.SubItems.Add(type)

                        lv.SubItems.Add(uploaded)
                        lv.SubItems.Add(uploader)

                    End While

                End Using

            End Using

        End Using

    End Sub

[keystone_paul]

You can either amend your query so that it only returns unique rows, or at the listview level you can add a key to each item as you add it (check out the overloaded options for Listview.Items.Add) - depending on what you class as a unique row you may want to concatenate all the appropriate columns into a single string to make up the key.

Then when you want to check for a duplicate you can check if the key already exists using :

ListView1.Items.ContainsKey(MyKey)

and if not then add it.

You may wish to force the key to be all lower (or upper) case so as to ensure that rows which are identical except for case are considered.

[Tddupre]

what would i put in my sql statement? I put distinct but that didnt work

[Tddupre]

Bump

I still need help i have looked at key but do not understand how they work

[RunsWithScissors]

The Key is a unique Character string. The ListView Will throw an exception if you try to add an item with a key that is not unique in the collection.

A. If your database is set up properly, you SHOULD have some type of Unique Identifier as part of your table Schema. THis is the foundation of how Databases work. Usually, one would use an Auto-incremeting Identity Field to produce a new unique value for each record.

Also, using the Primary DB Key allows you to tuck this ID value into a SubItems (Or I often use the .Tag Property for this). That way, when the user selected an item, it is easy to grab the Database record ID, and retreive that record based upon the ID.

B. Find some unique combination of fields in your query return that has a high probability of being unique to that record. Concatenate those values together per keystone_paul's suggestion, and use them as the key for your listViewItem for each record.

C. Also note: you may or may not mean to do this, but the way you have this set up now, every time the Button is clicked, you will be adding whatever items are returned in your query to the already populated list. Meaning, this may be one reason you were seeing duplicates.

If you do NOT wish to add repetitive items resulting from your SQL query, use the ListView1.Items.Clear before you populate the list. I have added it to the code below. If you DON"T want to clear the items, remove that line.

    Private Sub Btnrefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btnrefresh.Click

        'Clear any existing items in the control before re-populating:
        Me.ListView1.Items.Clear()

        Using connection As New MySqlConnection(";")
            Using command As New MySqlCommand("SELECT Distinct  name, type, uploaded, uploader, location FROM programs", connection)

                connection.Open()
                Using reader As MySqlDataReader = command.ExecuteReader()

                    While (reader.Read())
                        Dim name As String = reader("name")
                        Dim type As String = reader("type")
                        Dim uploaded As String = reader("uploaded")
                        Dim uploader As String = reader("uploader")
                        Dim location As String = reader("location")

                        'Set a key based on a combination of Values you know will 
                        'be UNIQUE in your List:
                        Dim strKey As String = name & type & location

                        Dim lst As ListView = Me.ListView1

                        'THEN. test for the presence of your key in the List:
                        If Not lst.Items.ContainsKey(strKey) Then

                            'The ListView.Items.Add Method is overloaded to accept a number of 
                            'different signatures. This one adds a KEY, and the Item Name:
                            Dim lv As ListViewItem = lst.Items.Add(strKey, location)
                            lv.SubItems.Add(name)
                            lv.SubItems.Add(type)
                            lv.SubItems.Add(uploaded)
                            lv.SubItems.Add(uploader)
                        End If

                    End While
                End Using
            End Using
        End Using

    End Sub

Hope THat helps . . .

[Tddupre]

OK, I get that logic, i tried without the clear items and it didn't work but it worked with the clear items