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

[sdowney1]

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.

	'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

	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

	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

[sdowney1]

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

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

[sdowney1]

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

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

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 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>

[sdowney1]

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

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

[sdowney1]

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

	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

	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

[sdowney1]

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.

[sdowney1]

Improved code with help from themindgoblin.

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

	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