MySql load Listview and run time column names + autosize, checked, color items

example using ado.net to load values into a list box
at run time setup column names, autosize columns
select checked
color items a color with bold, then black regular

mouse down select first column to display other text in a rtb
etc...

add Try - Catch as you like, I leave it out at first and watch for exception in the debug window.

Code:

---

        'example of working code
        'https://stackoverflow.com/questions/57584/how-can-i-make-a-listviews-columns-auto-resize-programmatically

        'https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.listviewitem.checked?view=windowsdesktop-8.0
        'ListView1.View = View.Details
        'ListView1.Columns.Add("stuff")
        'ListView1.Columns.Add("stuff1")
        'ListView1.Columns.Add("stuff2")
        'ListView1.Columns.Add("stuff3")
        'For Xx = 1 To 100
        'Dim Litem As New ListViewItem With {
        '.Text = "Around the Horn"
        '        }
        'Litem.SubItems.Add("Hardy Thomas")
        'Litem.SubItems.Add("(171) 555-7788")
        'Litem.SubItems.Add("(171) 555-6750")
        'ListView1.Items.Add(Litem)
        'Next
        'ListView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)

        With ListView1
                .Items.Clear()
                .Columns.Clear()
                .View = System.Windows.Forms.View.Details
                .Alignment = System.Windows.Forms.ListViewAlignment.Top
                .LabelEdit = False
                .View = View.Details
                .Columns.Add("ItemID")
                .Columns.Add("Barcode")
                .Columns.Add("Deletion Date")
                .Columns.Add("Title")

                ' Display check boxes.
                .CheckBoxes = True
                ' Select the item and subitems when selection is made.
                .FullRowSelect = True
                ' Display grid lines.
                .GridLines = True
                'goofy sort on first column Id not numerical order
                ' Sort the items in the list in ascending order.
                '.Sorting = SortOrder.Ascending
        End With


        'get count(id) from unbookdata
        Dim numberID As Integer = 0
        Dim ConStrUserVar As String = frmlogonConnectstring ' & "Allow User Variables=True;"
        Dim conn As New MySqlConnection(ConStrUserVar)
        Using conn
                conn.Open()
                Dim cmd1 As New MySqlCommand("Select count(Id) as TOPID from unbookdata", conn)
                Using RDR = cmd1.ExecuteReader()
                        If RDR.Read() Then numberID = RDR("TOPID")
                End Using

                If numberID = 0 Then
                        conn.Close()
                        MsgBox("No records available to restore", MsgBoxStyle.Information, "Record Management")
                        Me.Close()
                        Exit Sub
                End If

                Me.Text = "Record Undelete Program - " & numberID & " records available."
                'load list view with data
                ListView1.BeginUpdate()
                cmd1.CommandText = "Select unID, unDatetime, unTitles, unBarcode From unbookdata"

                Using RDR = cmd1.ExecuteReader()

                        Do While RDR.Read()
                                Dim LItem As New ListViewItem()
                                LItem.Text = RDR("unID").ToString() '0
                                LItem.SubItems.Add(RDR("unBarcode").ToString()) '3
                                LItem.SubItems.Add(RDR("unDateTime").ToString()) '"yyyy-MM-dd HH:mm:ss")) '1
                                LItem.SubItems.Add(RDR("unTitles").ToString()) '2

                                ListView1.Items.Add(LItem)
                        Loop

                End Using
                ListView1.EndUpdate()
                ListView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent)

                conn.Close()
        End Using

End Sub

---

Uses couple buttons to select all items in lisview, then unselect all items in list view
with Try Catch

Code:

---

        Private Sub cmdSelect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdSelect2.Click
                'selects all
                Try
                        Xx = 0
                        Do Until Xx > ListView1.Items.Count - 1
                                ListView1.Items.Item(Xx).Checked = True
                                ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Bold)
                                ListView1.Items.Item(Xx).ForeColor = System.Drawing.Color.Blue
                                ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = System.Drawing.Color.Blue
                                ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = System.Drawing.Color.Blue
                                ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = System.Drawing.Color.Blue
                                Xx += 1
                        Loop
                Catch ex As Exception
                        Debug.WriteLine(ex.ToString())
                        Dim msg = ex.ToString()
                        MsgBox(msg,  , "Error")
                End Try


        End Sub

        Private Sub cmdUnselect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdUnselect2.Click
                'unselects all
                Try
                        Xx = 0
                        Do Until Xx > ListView1.Items.Count - 1
                                ListView1.Items.Item(Xx).Checked = False
                                ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Regular)
                                ListView1.Items.Item(Xx).ForeColor = System.Drawing.Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = System.Drawing.Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = System.Drawing.Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = System.Drawing.Color.Black
                                Xx += 1
                        Loop
                Catch ex As Exception
                        Debug.WriteLine(ex.ToString())
                        Dim msg = ex.ToString()
                        MsgBox(msg,  , "Error")

                End Try

        End Sub

---

when mouse down on an item row, red line gets the text for the first column to get data to display in a rtb

Code:

---

        Private Sub ListView1_MouseDown(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles ListView1.MouseDown
                'Dim info As ListViewHitTestInfo = ListView1.HitTest(e.X, e.Y)
                'MsgBox(info.Location.ToString())
                'If Not IsNothing(info.SubItem) Then
                'info will contain the information of the clicked listview column. You can then go through it's subitems for more information, if any.
                'Debug.Print(info.SubItem.Text)
                'want only the id out of the clicked on item!!
                'IdNumber = CInt(ListView1.Items.Item(ListView1.FocusedItem.Index).Text)
                'Debug.Print(IdNumber)
                'End If

                Dim ConStrUserVar As String = frmlogonConnectstring ' & "Allow User Variables=True;"
                Dim conn As New MySqlConnection(ConStrUserVar)
                Using conn
                        IdNumber = (Val(ListView1.Items.Item(ListView1.FocusedItem.Index).SubItems.Item(0).Text))
                        conn.Open()
                        Dim cmd1 As New MySqlCommand("select unmarcdata From unbookdata where unId = " & IdNumber, conn)
                        Using RDR = cmd1.ExecuteReader()
                                If RDR.Read() Then rtxtMarc.Text = RDR("unmarcdata").ToString()
                        End Using
                        conn.Close()
                End Using
        End Sub

---

Will add example code for checked items later, as in I check a row, then I click a button to delete that row in table or restore that row to another table

Here is the cmd button delete from unbookdata whatever checked items are in a listview
then it does a restore to reload the whole box
With 800 rows it is instant for me in the restorelistview.

Redline gets the idnumber of a row to delete in the unbookdata table

Code:

---

Private Sub cmdDelete2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.Ev
        '*********************************************
        'for demo only
        'If frmlogondemotest = True Then NagMe
        '**********************************************
        'delete selected items

        Dim IdNumber As Integer
        Dim Xx As Integer = 0

        Dim ConStrUserVar As String = frmlogonConnectstring ' & "Allow User Variables=True;"
        Dim conn As New MySqlConnection(ConStrUserVar)
        Dim cmd1 As New MySqlCommand
        cmd1.Connection = conn
        Using conn
                conn.Open()
                Do Until Xx > ListView1.Items.Count - 1
                        If ListView1.Items.Item(Xx).Checked = True Then
                                'delete  from unbookdata
                                IdNumber = CInt(ListView1.Items.Item(Xx).Text)
                                cmd1.CommandText = "delete from unbookdata where unid = " & IdNumber
                                cmd1.ExecuteNonQuery()
                        End If
                        Xx += 1
                Loop

        End Using

        RestoreListBox()
        'zero out textbox
        rtxtMarc.Text = ""

End Sub

---

Here is column sorting, every column is sortable
2 places for code in the form with a listview
and a seperate module

Code:

---

        'value to keep track of which column was previously sorted in the ListView
        Private sortColumn As Integer = -1

---

Code:

---

Private Sub listview1_ColumnClick(ByVal eventSender As System.Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles ListView1.ColumnClick
        '<Code begin -  Place within the module code for the form containing the ListView>
        '<The Listview in my example is named "lvMail", amend accordingly>


        'if current column is not the previously clicked column
        If e.Column <> sortColumn Then
                'set the sort column to the new column
                sortColumn = e.Column
                'default to ascending sort order
                Me.ListView1.Sorting = SortOrder.Ascending
        Else
                'flip the sort order
                If ListView1.Sorting = SortOrder.Ascending Then
                        Me.ListView1.Sorting = SortOrder.Descending
                Else
                        Me.ListView1.Sorting = SortOrder.Ascending
                End If
        End If

        'set ListViewItemSorter property to a new ListViewItemComparer object
        Me.ListView1.ListViewItemSorter = New ListViewItemComparer(e.Column, ListView1.Sorting)

        'call the sort method to manually sort
        Me.ListView1.Sort()
        '<Code End -  Place within the module code for the form containing the ListView>

End Sub

---

Module code
Place in its own Class Module named ListViewItemComparer.vb

Code:

---


' <Code Begin - Place in its own Class Module named ListViewItemComparer>
Imports System.Windows.Forms      'required to ensure debugger references correctly
Public Class ListViewItemComparer
    Implements IComparer
    Private col As Integer
    Private order As SortOrder

    Public Sub New()
        col = 0
        order = SortOrder.Ascending
    End Sub
    Public Sub New(column As Integer, order As SortOrder)
        col = column
        Me.order = order
    End Sub

    Public Function Compare(x As Object, y As Object) As Integer Implements IComparer.Compare
        Dim returnVal As Integer
        Try
            If IsDate(CType(x, ListViewItem).SubItems(col).Text) And IsDate(CType(y, ListViewItem).SubItems(col).Text) Then
                returnVal = 1
                returnVal = DateTime.Compare((CType(x, ListViewItem).SubItems(col).Text), CType(y, ListViewItem).SubItems(col).Text)
            Else
                If IsNumeric(CType(x, ListViewItem).SubItems(col).Text) And IsNumeric(CType(y, ListViewItem).SubItems(col).Text) Then
                    'compare as numeric
                    returnVal = Val(CType(x, ListViewItem).SubItems(col).Text).CompareTo(Val(CType(y, ListViewItem).SubItems(col).Text))
                Else
                    'Not numeric, so compare as string
                    returnVal = [String].Compare(CType(x, ListViewItem).SubItems(col).Text, CType(y, ListViewItem).SubItems(col).Text)
                End If
            End If
        Catch ex As Exception
            Return 1 'to avoid an error, probably caused by user interaction with the listview during re-population and sorting at same time
            'or if the column contains an object which is not a date, not numeric and not a string
        End Try

        'if order is descending then invert value
        If order = SortOrder.Descending Then
            returnVal *= -1
            'return the REVERSE value of the current value by multiplying it by "-1"
        End If

        Return returnVal

    End Function
End Class
' <Code End - Place in its own Class Module named ListViewItemComparer>

---

coloring individual items in a listview when item is checked and clearing it back to black when unchecked

Code:

---

Private Sub ListView1_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles ListView1.ItemCheck
        'makes certain focus is here, like if a form on a timer loads
        ListView1.Select()

      ' stops a null exception as in you check an item, then do a column sort by clicking the header
      'doing that causes this focused item to be nothing
        If ListView1.FocusedItem Is Nothing Then
                Debug.Print("nothing")
                Exit Sub
        End If

        Dim x As Integer = ListView1.FocusedItem.Index
        Debug.Print(x)

        'not checked yet so this runs
        If Not ListView1.Items.Item(x).Checked Then
                ListView1.Items(x).UseItemStyleForSubItems = False
                ListView1.Items(x).SubItems(0).ForeColor = Color.Green 'first column
                ListView1.Items(x).SubItems(1).ForeColor = Color.Red 'second column
                ListView1.Items(x).SubItems(2).ForeColor = Color.Purple 'third column
                ListView1.Items(x).SubItems(3).ForeColor = Color.Aqua 'fourth column
                ListView1.Items(x).SubItems(4).ForeColor = Color.BlueViolet 'fifth column
                ListView1.Items(x).Font = New Font(ListView1.Font, FontStyle.Bold)

        Else

                ListView1.Items(x).UseItemStyleForSubItems = False
                ListView1.Items(x).SubItems(0).ForeColor = Color.Black 'first column
                ListView1.Items(x).SubItems(1).ForeColor = Color.Black 'second column
                ListView1.Items(x).SubItems(2).ForeColor = Color.Black 'third column
                ListView1.Items(x).SubItems(3).ForeColor = Color.Black 'fourth column
                ListView1.Items(x).SubItems(4).ForeColor = Color.Black 'fifth column
                ListView1.Items(x).Font = New Font(ListView1.Font, FontStyle.Regular)
        End If
End Sub

including a snip image

---

Improved the select-unselect all for listview

I found to set individual colors in select all, unselect all, had to prevent ListView1_ItemCheck from running using a boolean as when you say 'ListView1.Items.Item(Xx).Checked = True', that sub will run. And here we don't want it to run.

And set it to false in the form load

Code:

---

        Private Sub ListView1_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles ListView1.ItemCheck
                'makes certain focus is here, like if a form on a timer loads
                ListView1.Select()
                If boolcheck = True Then Exit Sub

                Dim x As Integer = ListView1.FocusedItem.Index

              <etc more code>

      end sub

---

Code:

---

        Private Sub cmdSelect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdSelect2.Click
                'selects all

                'makes certain focus is here, like if a form on a timer loads
                ListView1.Select()

                'prevents ListView1_ItemCheck from running
                boolcheck = True

                'these 2 are the same thing
                'ListView1.Items.Item(Xx).text
                'ListView1.Items.Item(Xx).SubItems.Item(0).text

                Try
                        Xx = 0
                        Do Until Xx > ListView1.Items.Count - 1
                                ListView1.Items.Item(Xx).Checked = True
                                ListView1.Items(Xx).UseItemStyleForSubItems = False
                                ListView1.Items.Item(Xx).SubItems.Item(0).ForeColor = Color.Green
                                ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = Color.Red
                                ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = Color.Purple
                                ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = Color.Purple
                                ListView1.Items.Item(Xx).SubItems.Item(4).ForeColor = Color.BlueViolet
                                ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Bold)
                                Xx += 1
                        Loop
                        boolcheck = False
                Catch ex As Exception
                        boolcheck = False
                        Debug.WriteLine(ex.ToString())
                        MsgBox(ex.Message,  , "Error")
                End Try
        End Sub
        Private Sub cmdUnselect2_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdUnselect2.Click
                'unselects all

                'makes certain focus is here, like if a form on a timer loads
                ListView1.Select()

                'prevents ListView1_ItemCheck from running
                boolcheck = True

                'these 2 are the same thing
                'ListView1.Items.Item(Xx).text
                'ListView1.Items.Item(Xx).SubItems.Item(0).text

                Try
                        Xx = 0
                        Do Until Xx > ListView1.Items.Count - 1
                                ListView1.Items.Item(Xx).Checked = False
                                ListView1.Items(Xx).UseItemStyleForSubItems = False
                                ListView1.Items.Item(Xx).SubItems.Item(0).ForeColor = Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(1).ForeColor = Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(2).ForeColor = Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(3).ForeColor = Color.Black
                                ListView1.Items.Item(Xx).SubItems.Item(4).ForeColor = Color.Black
                                ListView1.Items.Item(Xx).Font = New Font(ListView1.Items.Item(Xx).Font, FontStyle.Regular)
                                Xx += 1
                        Loop
                        boolcheck = False
                Catch ex As Exception
                        boolcheck = False
                        Debug.WriteLine(ex.ToString())
                        MsgBox(ex.Message,  , "Error")
                End Try
        End Sub

---

snip pic showing select all button clicked, unselect all just sets back to regular black and unchecked
This does the entire listview, as in all rows

All this took several days of thinking and googling, so hopefully, will give some ideas on how to use a listview control to those who don't know much about them.

Improved code with help from themindgoblin.

Eliminated the focus error check for is Nothing
Switches ItemCheck to ItemChecked in defining the subroutine.

Code:

---

        Private Sub ListView1_ItemChecked(sender As Object, e As ItemCheckedEventArgs) Handles ListView1.ItemChecked

                If boolcheck Then Exit Sub
                'Dim _index As Integer = e.Item.Index
                If e.Item.Checked Then
                        e.Item.UseItemStyleForSubItems = False
                        e.Item.SubItems(0).ForeColor = Color.Green 'first column id
                        e.Item.SubItems(1).ForeColor = Color.Red 'second column barcode
                        e.Item.SubItems(2).ForeColor = Color.Purple 'third column date
                        e.Item.SubItems(3).ForeColor = Color.Purple 'fourth column title
                        e.Item.SubItems(4).ForeColor = Color.BlueViolet 'fifth column status
                        e.Item.Font = New Font(ListView1.Font, FontStyle.Bold)
                Else
                        e.Item.UseItemStyleForSubItems = True
                        e.Item.ForeColor = Color.Black
                        e.Item.Font = New Font(ListView1.Font, FontStyle.Regular)
                End If

        End Sub

---