Listview help needed

Question

I am confused being a relatively new user to vb.net. Why my listview is not amending the value in the list? If I may, so I have a correct working of how listview displays its data from database. I have a general question in addition to my code problem.

I have five columns in my listview (0-4). Am I correct in saying that if my access database contained say 10 fields but I only needed to display five of them but one of them was field (9), then would code the list like my code below, which is not changing the value and will only display the list if I remove the 'else' statement.

What is the error? Many thanks

UPDATED CODE:

oledbCnn.ConnectionString = My.Settings.storageConnectionString
        oledbCnn.Open()

        'drcount = Convert.ToInt32(dr("RowCount"))

        sql = "Select TOP 100 * from Requests ORDER BY [Date-time received] DESC"
        Debug.Print(sql)
        Dim oledbCmd As OleDbCommand = New OleDbCommand(sql, oledbCnn)


        Using dr = oledbCmd.ExecuteReader()


            'clear items in the list before populating with new values
            'lvRequests.Items.Clear()

            While dr.Read()
                If dr.HasRows Then
                    Dim LVI As New ListViewItem

                    With LVI
                        .Text = dr(0).ToString()
                        .UseItemStyleForSubItems = False
                        .SubItems.Add(CDate(dr(5)).ToShortDateString())
                        .SubItems.Add(dr(1).ToString())
                        .SubItems.Add(dr(3).ToString())
                        If dr(3).ToString = "D" Then
                            .SubItems(3).Text = "Destroyed"
                        ElseIf dr(3).ToString = "O" Then
                            .SubItems(3).Text = "Out"
                        ElseIf dr(3).ToString = "I" Then
                            .SubItems(3).Text = "Intake"
                        End If
                        .SubItems.Add(dr(9).ToString())
                        If dr(9).ToString = "DEMO" Then
                            .SubItems(9).Text = "Done"

                        End If
                    End With
                    lvRequests.Items.Add(LVI)

                    lvcount += 1

                End If

            End While
        End Using

Answer 1

There is not enough info to be really sure what is going on. It appears that you pull 10 columns from the DB, but if you only post 5 of them to the LV, then there should only be 5 subitems. So this is wrong:

If dr(9).ToString() = "DEMO" Then
    lvRequests.Items(lvRequests.Items.Count - 1).SubItems(9).Text = "Done"

' should probably be: 
If dr(9).ToString() = "DEMO" Then
    lvRequests.Items(lvRequests.Items.Count - 1).SubItems(4).Text = "Done"

What might make it clearer at least in code would be to use Names for the subitems. If you instance a SubItem object rather than use a default constructor, you can assign a name and reference them that way instead:

Dim si As New ListViewItem.ListViewSubItem
With si
    .Text = dr(X).ToString        ' dunno whats in there
    ' this is probably not exactly right, but give the SubItem 
    ' the same name as the db column
    .Name = dr.Table.Columns(X).ColumnName
End With

thisItem.SubItems.Add(si)         ' add sub to Item collection

Now, your code can use the name rather than index:

If dr(9).ToString() = "DEMO" Then
    lvReq.Items(lvReq.Items.Count - 1).SubItems("TheColumnName").Text = "Done"

it no longer really matters how many columns or subitems there are. The ListViewItem.SubItems collection does not require sub item names to be unique, so it is possible to end up with 2 with the same name, but if you map from the DR/DT/DB correctly, it should take care of itself.

If the LV is bound to the datasource (you did not say), then there could be as many SubItems as db/datasource columns (never actually used an LV that way).